Example: Creating a database view 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.

The examples in this topic show how to create a simple view in IBM DB2 to restrict user access to specific columns. The example is based on a table with the following definition:

CREATE TABLE employees(emp_id integer not null, name varchar(32), 
department smallint not null, hire_date date not null);

Grant privileges on the table

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 table. If the view owner needs to be able to grant privileges on the view to other users, the table owner must grant the view owner the ability to grant privileges to other users.

In this example, the table on which the view is based (employees) is owned by the user gdb. The user creating the view is user rocket. Additionally, the rocket user must grant privileges on the view to other users. Therefore, the gdb user must grant the rocket user the privilege to select from the employees table and include the WITH GRANT OPTION so rocket can grant other users SELECT privileges on the view.

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

db2 => GRANT SELECT 
ON employees 
TO USER rocket WITH GRANT OPTION;

DB20000I  The SQL command completed successfully.

Create a view

In this example, user rocket creates a view on the employees table to restrict access to only those records where the department is 201:

db2 => connect to testdb user rocket using nopeeking
   Database Connection Information
 Database server        = DB2 9.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;

DB20000I  The SQL command completed successfully.

Grant privileges on the view

You can grant privileges on the view to specific users without having to grant those users access to the base table (employees). In this example, the user mgr200 is granted SELECT privileges on the view, view_dept_201:

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

db2 => GRANT SELECT ON view_dept_201 TO USER mgr200;
DB20000I  The SQL command completed successfully.

Test privileges

Log in as mgr200 and select records from view_dept_201:

db2 => connect to testdb user mgr200 using mgr200
   Database Connection Information
 Database server        = DB2 9.7.4
 SQL authorization ID   = MGR200
 Local database alias   = TESTDB

db2 => SELECT * FROM rocket.view_dept_201;

emp_id         name        dept      hire_date
112            LOLLI POP   201       06/30/2007
134            VAN CHIN    201       10/15/2007
150            DON GUN     201       03/01/2009

As expected, only records for employees in department 201 are returned.