Security of and Through Views






Security of and Through Views

A GRANT statement can refer not only to tables, but also to views. (See the definition of the GRANT statement in Section 23.3.) Let's look at this more closely.

Because privileges can also be granted for views, you can provide users with access to only a part of a table or only to information derived or summarized from tables. The following are examples of both features.

Figure. Give DIANE the privilege to read only the names and addresses of noncompetitive players.

First, DIANE must be entered with a CREATE USER statement.

CREATE USER DIANE IDENTIFIED BY 'SECRET'

Second, a view is created specifying which data she may see:

CREATE   VIEW NAME_ADDRESS AS
SELECT   NAME, INITIALS, STREET, HOUSENO,
         TOWN
FROM     PLAYERS
WHERE    LEAGUENO IS NULL

The last step is to grant DIANE the SELECT privilege on the NAME_ADDRESS view:

GRANT   SELECT
ON      NAME_ADDRESS
TO      DIANE

With this statement, DIANE has access to only that part of the PLAYERS table defined in the view formula of NAME_ADDRESS.

Figure. Make sure that user GERARD can look at only the number of players in each town.

First, we introduce GERARD.

CREATE USER GERARD IDENTIFIED BY 'XYZ1234'

The view that we use looks like this:

CREATE   VIEW RESIDENTS (TOWN, NUMBER_OF) AS
SELECT   TOWN, COUNT(*)
FROM     PLAYERS
GROUP BY TOWN

Now we give GERARD the privilege for the previous view:

GRANT   SELECT
ON      RESIDENTS
TO      GERARD

All types of table privilege can be granted on views.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows