Sybase 12.4.2 User Manual

Using views and procedures for extra security

 

 For information on how to create views, see “Working with views”.

Views are computed tables that contain a selection of rows and columns from 
base tables. Views are useful for security when it is appropriate to give a user 
access to just one portion of a table. The portion can be defined in terms of rows 
or in terms of columns. For example, you may wish to disallow a group of users 
from seeing the salary column of an employee table, or you may wish to limit 
a user to see only the rows of a table that they have created.

Example

The Sales manager needs access to information in the database concerning 
employees in the department. However, there is no reason for the manager to 
have access to information about employees in other departments.

This example describes how to create a user ID for the sales manager, create 
views that provide the information she needs, and grants the appropriate 
permissions to the sales manager user ID.

1

Create the new user ID using the GRANT statement, from a user ID with 
DBA authority. Enter the following:

CONNECT "DBA" 

IDENTIFIED by SQL;

GRANT CONNECT 

TO SalesManager 

IDENTIFIED BY sales

(You must enclose DBA in quotation marks because it is a SQL keyword, 
just like SELECT and FROM.)

2

Define a view which only looks at sales employees as follows:

CREATE VIEW emp_sales AS

SELECT emp_id, emp_fname, emp_lname

FROM "DBA".employee

WHERE dept_id = 200

The table should be identified as "DBA".employee, with the owner of the 
table explicitly identified, for the 

SalesManager

 user ID to be able to use 

the view. Otherwise, when SalesManager uses the view, the SELECT 
statement refers to a table that user ID does not recognize.

3

Give SalesManager permission to look at the view: 

GRANT SELECT 

ON emp_sales