Work with database views in Oracle

Situatie

You can use SQL to create a view on tables and feature classes in an enterprise geodatabase to restrict what columns or records are available to view users, or you can define a view to join information from two tables or a feature class and a table. When you include the spatial column in the view definition, view users can visualize the features in a map in an ArcGIS Desktop client.

Solutie

Pasi de urmat

The examples in this topic show how to create views in an Oracle database. One view restricts user access to specific columns. The other view presents content from two different tables. The examples are based on tables with the following definitions:

Table definition for employees

CREATE TABLE employees (
 emp_id number(38) unique not null,
 name varchar2(32),
 department number not null,
 hire_date date not null
);

Table definition for regions

CREATE TABLE employees (
 objectid number(38) unique not null,
 reg_id number(38) unique not null,
 emp_id number(38) not null,
 rname nvarchar(32), 
 region st_geometry
);

Grant privileges on the tables

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 tables on which the views are based (employees and regions) are owned by the user gdb. The user creating the views is user rocket. Additionally, rocket will grant privileges on the views to other users. Therefore, gdb must grant rocket the privilege to select from the employees and regions tables and include the WITH GRANT OPTION so rocket can grant other users SELECT privileges on the views.

conn gdb/gdb.bdg

GRANT SELECT 
 ON gdb.employees 
 TO rocket WITH GRANT OPTION;

GRANT SELECT 
 ON gdb.regions 
 TO rocket WITH GRANT OPTION;

Create a view to restrict access

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

CREATE VIEW view_dept_201 
 AS (SELECT emp_id,name,department,hire_date)
 FROM gdb.employees 
 WHERE department = 201;

Create a view to join two tables

In this example, the view—emp_regions_view—joins a spatial table (feature class) with a nonspatial table based on the emp_id column. The view includes the ObjectID, spatial column (region), and region name (rname) from the regions feature class along with the employee name and ID from the employees table.

CREATE VIEW emp_region_view 
 AS SELECT (e.emp_name,e.emp_id,r.rname,r.objectid,r.region) 
 FROM employees e,region r 
 WHERE e.emp_id = r.emp_id;

Tip solutie

Permanent

Follow Us