DATABASE SECURITY - Database Design and Development: An Essential Guide for IT Professionals - page 498

and EMPLOYEE tables, then you can satisfy their information requirements by
creating a view comprising just those columns. This view hides the unnecessary
parts of the database from the marketing group and shows them only those columns
they require.
Views are not like tables in the sense that they do not store actual data. You
know that views are just like windows into the database tables that store the data.
Views are virtual tables. When a user accesses data through a view, he or she is
getting the data from the base tables, but only from the columns defined in the view.
Views are intended to present to the user exactly what is needed from the data-
base and to make the rest of the data content transparent to the user. However,
views offer a flexible and simple method for granting access privileges in a person-
alized manner. Views are powerful security tools. When you grant access privileges
to a user for a specific view, the privileges apply only to those data items defined in
the views and not to the complete base tables themselves.
Let us review an example of a view and see how it may be used to grant access
privileges. For a user to create a view from multiple tables, the user must have access
privileges on those base tables. The view is dropped automatically if the access pri-
vileges are dropped. Note the following example granting access privilege to Miller
for reading EmployeeNo, FirstName, LastName, Address, and Phone information
of employees in the department where Miller works.
CREATE VIEW MILLER AS
SELECT EmployeeNo, FirstName, LastName, Address, Phone
FROM EMPLOYEE
WHERE DeptNo =
(SELECT DEPARTMENT.DeptNo
WHERE DEPARTMENT.DeptNo =
EMPLOYEE.DeptNo AND
(EMPLOYEE.LastName = 'Miller' )) ;
GRANT SELECT ON MILLER TO Miller;
SQL Examples
In Chapter 13, we considered a few SQL examples on granting and revoking of
access privileges. Now we will study a few more examples. These examples are in-
tended to reinforce your understanding of discretionary access control. We will use
the DEPARTMENT and EMPLOYEE tables shown above for our SQL examples.
DBA gives privileges to Miller to create the schema:
GRANT CREATETAB TO Miller;
Miller defines schema, beginning with create schema statement:
CREATE SCHEMA EmployeeDB AUTHORIZATION Miller; (other DDL
statements follow to define DEPARTMENT and EMPLOYEE tables)
Miller gives privileges to Rodriguez for inserting data in both tables:
GRANT INSERT ON DEPARTMENT, EMPLOYEE TO Rodriguez;