Desktop Help 10.0 - Example: Creating a database view in Informix using the sdetable command

Complexity: Beginner Data Requirement: Use your own data

You can use the sdetable command to create a view in a geodatabase in IBM Informix. 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 datetime 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:informix -s myidsserver -D testdb

For more information on the sdetable command, see the Administration Command Reference.

The sdetable command defines the view in the Informix sysviews system catalog table, just as it would if you created the view using Structured Query Language (SQL). You can query the sysviews system table to see this.

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:

GRANT SELECT ON view_dept_101 TO mgr100;

Note:

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.

Test privileges

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.

SELECT * FROM gdb.employees;

Invalid object name 'gdb.employees'

8/19/2013