You can use SQL to create a view on tables and feature classes in an enterprise geodatabase to restrict what columns or records are available to view users, or you can define a view to join information from two tables or a feature class and a table. When you include the spatial column in the view definition, view users can visualize the features in a map in an ArcGIS Desktop client.
The examples in this topic show how to create views in an IBM Informix database. One view restricts user access to specific columns. The other view presents content from two different tables. The examples are based on tables with the following definitions:
Table definition for employees
CREATE TABLE employees(
emp_id integer not null,
name varchar(32),
department smallint not null,
hire_date datetime not null;
Table definition for regions
CREATE TABLE regions(
objectid integer not null,
emp_id integer not null,
reg_id integer not null,
rname varchar(32),
region st_geometry
);
Grant privileges on the tables
If the user creating the view is not the owner of the table or tables on which the view is based, the table owner must grant the view creator at least the privilege to select from the tables.
In this example, the tables on which the views are based (employees and regions) are owned by the user gdb. The user creating the views is user rocket.
GRANT SELECT
ON gdb.employees
TO rocket;
GRANT SELECT
ON gdb.regions
TO rocket;
Create a view to restrict access
In this example, user rocket creates a view (view_dept_201) on the employees table to restrict access to only those rows where the department is 201:
CREATE VIEW view_dept_201
AS SELECT emp_id, name, department, hire_date
FROM gdb.employees
WHERE department = 201;
Create a view to join two tables
In this example, the view—emp_regions_view—joins a spatial table (feature class) with a nonspatial table based on the emp_id column. The view includes the ObjectID, spatial column (region), and region name (rname) from the regions feature class along with the employee name and ID from the employees table.
CREATE VIEW emp_regions_view
AS SELECT (e.emp_name,e.emp_id,r.objectid,r.rname,r.region)
FROM employees e, regions r
WHERE e.emp_id = r.emp_id;
Grant privileges on the views
You can grant privileges on the views to specific users without having to grant those users access to the base tables (employees and regions). In this example, the dispatch_mgr user is granted select privileges on both views:
GRANT SELECT
ON rocket.view_dept_201
TO dispatch_mgr;
GRANT SELECT
ON rocket.emp_region_view
TO dispatch_mgr;
The dispatch_mgr user can now access view_dept_201 to see employee records for all department 201 employees and access emp_region_view from a database connection in ArcMap or ArcGIS Pro to see all regions. When dispatch_mgr queries a region in the view, ArcGIS returns the region name and the name and ID of all employees in that region.