ASA Database Administration Guide
Managing User IDs and Permissions
Using views and procedures for extra security
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 they have created.
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 provides the information she needs, and grants the appropriate permissions to the sales manager user ID.
Create the new user ID using the GRANT statement. While logged in as a user ID with DBA authority, enter the following:
CONNECT "DBA" IDENTIFIED by SQL ; GRANT CONNECT TO SalesManager IDENTIFIED BY sales
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 therefore 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.
Give SalesManager permission to look at the view:
GRANT SELECT ON emp_sales TO SalesManager
You use exactly the same command to grant permission on views and on tables.
The next example creates a view which allows the Sales Manager to look at a summary of sales orders. This view requires information from more than one table for its definition:
Create the view.
CREATE VIEW order_summary AS SELECT order_date, region, sales_rep, company_name FROM "DBA".sales_order KEY JOIN "DBA".customer
Grant permission for the Sales Manager to examine this view.
GRANT SELECT ON order_summary TO SalesManager
To check that the process has worked properly, connect to the SalesManager user ID and look at the views you created:
CONNECT SalesManager IDENTIFIED BY sales ; SELECT * FROM "DBA".emp_sales ; SELECT * FROM "DBA".order_summary ;
No permissions have been granted to the Sales Manager to look at the underlying tables. The following commands produce permission errors.
SELECT * FROM "DBA".employee ; SELECT * FROM "DBA".sales_order
The previous example shows how to use views to tailor SELECT permissions. You can grant INSERT, DELETE, and UPDATE permissions on views in the same way.
For more information about allowing data modification on views, see Using views.