Example: Create database views in Db2 using SQL

Complexity:

Beginner

Data Requirement:

Use your own data

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 Db2 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 date 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.

db2 => connect to testdb user gdb using gdb.dbg
   Database Connection Information
 Database server        = DB2 10.5.5
 SQL authorization ID   = GDB
 Local database alias   = TESTDB

db2 => GRANT SELECT 
 ON employees 
 TO USER rocket;

db2 => GRANT SELECT 
 ON regions 
 TO USER 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:

db2 => connect to testdb user rocket using nopeeking
   Database Connection Information
 Database server        = DB2 10.5.5
 SQL authorization ID   = ROCKET
 Local database alias   = TESTDB

db2 => 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.

connect to testdb user gdb using gdb.dbg 
   Database Connection Information 
Database server      = DB2 10.5.5 
SQL authorization ID = GDB 
Local database alias = TESTDB

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:

db2 => connect to testdb user rocket using nopeeking
   Database Connection Information
 Database server        = DB2 10.5.5
 SQL authorization ID   = ROCKET
 Local database alias   = TESTDB

db2 => GRANT SELECT 
 ON rocket.view_dept_201 
 TO USER dispatch_mgr;

db2 => GRANT SELECT
 ON rocket.emp_region_view
 TO USER 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.