By using the CREATE VIEW statement with the WITH CHECK OPTION, you can ensure that users query and modify only the data they can see. This option forces all data modification statements executed against the view to adhere to the criteria set within the SELECT statement defining the view. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data will remain visible through the view after the modification has been committed.
Using the GRANT and REVOKE statements, you can restrict each user's database access to specific database objects¾including views. If the view and all the tables and views from which it was derived are owned by the same user, that owner can grant permission to others to use the view, and that owner can deny access to the view's underlying tables and views. This is a simple but effective security mechanism.
By defining different views and selectively granting permissions on them, you can limit a user's (or any combination of users') access to specific subsets of data. To provide security, you might confine a user's access to:
For example, in the pubs sample database, you might define a view that contains only the rows for business and psychology books, keeping information about other types of books hidden from some users.
For example, you might define a view that contains all the rows of the titles table but omit the royalty and advance columns since that financial information is confidential.
For example, you might define a view that joins the titles, authors, and titleauthor tables to display the names of the authors and the books they have written. This view would hide personal data about authors and financial information about the books.
For example, through a view called category_price, users can access only the average price of each type of book.
For example, through a view called hiprice_computer, users can access the title and price of computer books that meet the qualifications in the view definition of hiprice.
To create a view, a user must be granted CREATE VIEW permission by the database owner and must have appropriate permissions on any tables or views referenced in the view definition.
As the owner of an object on which other users have created views, you must be aware of who can see what data through which views. Consider this situation: the database owner has granted Harold CREATE VIEW permission, and a user named Margaret has granted Harold permission to select from a table she owns. Given these permissions, Harold can create a view that selects all columns and rows from Margaret's table. However, if Margaret subsequently revokes permission for Harold to select from her table, he can still look at her data through the view he has created.