Filter
Top Products
Using views and procedures for extra security
370
For information on how to create views, see “Working with views”.
Using views for tailored 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 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