First Guide to SQL Anywhere Studio
PART 3. Basic SQL
CHAPTER 16. Introduction to Views
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 Interactive SQL, connected to the sample database as dba, enter 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
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
Make sure you connect back to the sample database before proceeding to the next chapter.