Desktop Help 10.0 - Example: Creating a database view in Oracle 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 Oracle. 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      number(38) unique not null,
  name        varchar2(32),
  department  number not null,
  hire_date   date not null);

Notice that the table contains a not null number 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 "where department = 101" 
–u gdb –p gdb.bdg –i sde:oracle11g:ora1 -s myodbserver

Attribute        Administration Utility
__________________________________
Successfully created view view_dept_101.

Tip:

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

You could subsequently query ALL_VIEWS to see a description of view_dept_101.

set lines 60
set pages 10000
set long 2000
column text format a60 wrapped;

SELECT view_name, text_length, text 
FROM all_views
WHERE owner = 'GDB' and view_name = 'VIEW_DEPT_101';

VIEW_NAME	TEXT_LENGTH	TEXT
VIEW_DEPT_101	112	SELECT "EMP_ID","NAME","DEPARTMENT","HIRE_DATE", FROM GDB.EMPLOYEES WHERE department = 101

VIEW_NAME

TEXT_LENGTH

TEXT

VIEW_DEPT_101

112

SELECT "EMP_ID","NAME","DEPARTMENT","HIRE_DATE", 
FROM GDB.EMPLOYEES 
WHERE department = 101

In this example, ArcSDE has added the owner name to the table name in the query. If you create your own view with SQL, this is not done automatically.

Notice that the names of the view and its owner are converted to uppercase before being stored in ALL_VIEWS. This is done by Oracle, not ArcSDE. Therefore, when querying for these specific text values, you must type them in uppercase.

Caution:

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:

SQL> GRANT SELECT ON view_dept_101 TO mgr100;

Grant succeeded.

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

conn mgr100/mgr100
Connected.
SQL> 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 CHO    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.

SQL> SELECT * FROM gdb.employees;
select * from gdb.employees
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

8/19/2013