Using Views for Security

Views can be used to restrict access to information in the database.

Scenario

You want to create user ID for the sales department head, Moira Kelly, and restrict its use so that it can only examine information about employees in the sales department.

Creating the New User ID

First, create the new user ID for Moira Kelly using the GRANT statement. From dbisql, connect to the demo database as dba, type the following:

GRANT CONNECT TO M_Kelly 
IDENTIFIED BY SalesHead

Granting Permissions

Next you need to grant user M_Kelly the right to look at employees of the sales department.

CREATE VIEW SalesEmployee AS
SELECT EmployeeID, Surname, GivenName
FROM Employees
WHERE DepartmentID=200

Now you must give M_Kelly permission to look at the new view by entering:

GRANT SELECT ON SalesEmployee TO M_Kelly

Looking at the View

Connect to the database as M_Kelly and now try looking at the view:

CONNECT USER M_Kelly IDENTIFIED BY SalesHead;
SELECT * FROM "dba".SalesEmployee

emp_id	emp_lname	emp_fname
129	Chin	Philip
195	Dill	Marc
299	Overbey	Rollin
467	Klobucher	James
641	Powell	Thomas
…	…	…

However, you do not have permission to look directly at the employee and department tables. If you execute the following commands, you will get permission errors.

SELECT * FROM Employees;
SELECT * FROM Departments

Using Built-In Functions for Secure Views

You can also take advantage of built-in Sybase IQ functions when creating secure views. In the following example, the view secure_view is intended to restrict access to records in the secure_table to specific users.

The view definition uses the suser_name( ) function, a built-in Sybase IQ function that stores a server user name for each user. The following view allows a user to see records if he or she has all of the following:

A specific server user name (suser_name)
A grade equal to or higher than the viewed record(s) (as defined by records in the security_auth_table)

CREATE VIEW secure_view AS
SELECT a.secure_name, a.security_code  
FROM secure_table a, security_auth_table b 
WHERE SUSER_NAME() = b.opr_name
  AND a.department_id = b.department_id
  AND a.grade <= b.grade

You may also create a view that restricts access by using the built-in function suser_id( ), which stores a unique identification number for each user.