Collection Contents Index Defining a view CHAPTER 17.  Introduction to Subqueries pdf/chap16.pdf

First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 16. Introduction to Views       

Using views for security


Example 

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.

Creating the new user ID 

First you need to create the new user ID for Moira Kelly using the GRANT statement. From Interactive SQL, connected to the sample database as dba, enter 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 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

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

Notes 

Make sure you connect back to the sample database before proceeding to the next chapter.


Collection Contents Index Defining a view CHAPTER 17.  Introduction to Subqueries pdf/chap16.pdf