Using views for security

Views can be used to restrict access to information in the database. For example, suppose you wanted to create a user ID for the sales department head, Moira Kelly, and restrict her user ID so that it can only examine information about employees in the sales department.

First you need to create the new user ID for Moira Kelly using the GRANT statement. From DBISQL, connected to the sample database as dba, type the following:

GRANT CONNECT TO M_Kelly 
IDENTIFIED BY SalesHead

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

CREATE VIEW SalesEmployee AS
SELECT emp_id, emp_lname, emp_fname
FROM "dba".employee
WHERE dept_id = 200

The table should be identified as "dba".employee for the M_Kelly user ID to be able to use the view.

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

GRANT SELECT ON SalesEmployee TO M_Kelly

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

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 "dba".employee ;
SELECT * FROM "dba".department

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.