Example: Creating a database view in DB2 using the sdetable command
You can use the sdetable command to create a view in a geodatabase in IBM DB2. Doing so automatically registers the view with ArcSDE.
The example in this topic shows how a view created by sdetable can be used to restrict user access to specific data. 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);
Notice that the table contains a not null integer column that can be used as an ArcSDE row ID.
Create a view using the sdetable command
Suppose you want the manager of department 101 to see all the columns in the table employees but only the rows for employees in department 101. Use sdetable to create a view with all rows in which the department number is 101. The –w option specifies the WHERE clause of the query stored with the view definition.
sdetable –o create_view –T view_dept_101 –t employees –c 'emp_id,name,department,hire_date' -w "department = 101" –u gdb –p gdb.bdg –i sde:db2 -s mydb2server -D testdb Attribute Administration Utility __________________________________ Successfully created view view_dept_101.
For more information on the sdetable command, see the Administration Command Reference.
The sdetable command defines the view in the DB2 system catalog, just as if you created the view yourself using the DB2 CREATE VIEW command. To see this, query the DB2 SYSCAT.VIEWS system view.
db2 => SELECT viewschema, viewname, text FROM syscat.views WHERE viewname = 'VIEW_DEPT_101';
CREATE VIEW view_dept_101 AS SELECT = emp_id, name, department, hire_date FROM GDB.EMPLOYEES WHERE department = 101
ArcSDE adds the owner name to the table name in the query. If you create your own view with Structured Query Language (SQL), this is not done for you automatically unless you qualify the table name when creating the view.
Notice that the names of the view and its owner are converted to uppercase before being stored in SYSCAT.VIEWS. This is done by DB2, not ArcSDE. Therefore, when querying for these specific text values, you must type them in uppercase.
Some users create registered views with sdetable –o create_view and alter the view definition using SQL. This is done to avoid complex sdetable –o create_view command line syntax (especially the –c option) or to include a more complex or extended view definition, such as one that uses connections to external databases. Although altering a view can solve some problems, it is also possible to create a view that does not work well with ArcSDE or ArcSDE clients such as ArcGIS Desktop.
Grant privileges on the view
The view owner 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 mgr100 is granted select privileges on the view, view_dept_101:
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 view_dept_101 TO USER mgr100; DB20000I The SQL command completed successfully.
In this example, the table owner and the view owner are the same user. If they were different, the table owner would need to grant the view owner privileges to select from the table plus the ability to grant select to other users. If the privilege is granted using SQL, include the WITH GRANT OPTION in the statement. If the privilege is granted using the sdetable command with the grant operation, the inherit (–I) option must be specified.
Log in as mgr100 and select records from view_dept_101.
db2 => connect to testdb user mgr100 using mgr100 Database Connection Information Database server = DB2 9.5.5 SQL authorization ID = MGR100 Local database alias = TESTDB db2 => SELECT * FROM gdb.view_dept_101; emp_id name dept hire_date 29 YAN WU 101 04/15/2002 67 LEE VAN 101 11/01/2004 78 SUE CHOO 101 05/31/2005 105 DAN HO 101 10/01/2006 111 ANN ANG 101 12/15/2006 135 BILL BO 101 10/15/2007
As expected, only records for employees in department 101 are returned.
Views exist as schema objects, independent of the tables that populate them. In the following query, the user mgr100 has no access to the table, employees, so it appears to mgr100 that the employees table does not exist.
db2 => SELECT * FROM gdb.employees; SQL0551N "MGR100" does not have the privilege to perform operation "SELECT" on object "GDB.EMPLOYEES". SQLSTATE=42501