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 Oracle to limit user access to specific columns. 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);
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 grant privileges on the view to other users, the table owner must grant the view owner the ability to grant privileges on the table 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, rocket will grant privileges on the view to other users. Therefore, gdb must grant rocket 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.
conn gdb/gdb.bdg GRANT SELECT ON gdb.employees TO rocket WITH GRANT OPTION;
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:
CREATE VIEW view_dept_201 AS (SELECT emp_id,name,department,hire_date) FROM gdb.employees WHERE department = 201;
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:
conn rocket/nopeeking GRANT SELECT ON rocket.view_dept_201 TO mgr200;
Log in as mgr200 and select records from view_dept_201:
conn mgr200/topsecret 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.