Example: Creating a database view in Informix using SQL

Complexity:

Beginner

Data Requirement:

Use your own data

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 IBM Informix to restrict user access to specific columns. 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;

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.

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.

GRANT SELECT 
 ON gdb.employees 
 TO rocket;

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:

GRANT SELECT
 ON rocket.view_dept_201
 TO mgr200;

Test privileges

Log in as mgr200 and select records from view_dept_201:

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.