Control Data Access by Using Views


  • Control data access by using views.

So far, we've looked at most of the aspects of views and data modifications. What we have not examined yet is security and permissions. The next section explains how to apply permissions through several layers of views, and ownership chains.

Granting Permissions and Ownership Chains

Views help you apply security to your database applications by how permissions are checked in your view and underlying tables. Views make it easy to control access to this data. Managing permission for views is similar to managing permissions for tables.

There are three basic commands to set permissions and five different actions that they can control. The commands are GRANT , REVOKE , and DENY . The actions are SELECT , INSERT , UPDATE , DELETE , and DRI . GRANT and DENY allow or disallow access to the view, whereas REVOKE removes a previous GRANT or DENY . SELECT , INSERT , UPDATE , and DELETE should be self-explanatory, whereas DRI enables users to create references to the view, which would be required to create an object that refers to the view with the WITH SCHEMABINDING clause. For complete information about these statements and applying permissions, refer back to Chapter 6, "Programming SQL Server 2000."

If you use the following script to create a new table and view,

 CREATE TABLE dbo.DBOPermsTable (   id int,   name varchar(20),   description varchar(20),   address varchar(20) ) GO CREATE VIEW dbo.DBOPermsView AS   SELECT id, name     FROM DBOPermsTable 

then you can set permissions with the following statements:

 REVOKE all ON DBOPermsTable TO public DENY all ON DBOPermsTable TO Mary REVOKE all ON DBOPermsView TO public GRANT SELECT ON DBOPermsView to Mary 

Even though you have not granted permissions to the underlying table, Mary still has permissions to the view, and that gives her access to the requested data. In this way, views provided additional data security because users do not need to be granted access to the source tables, and in this example, can actually be denied access to the base tables. This magic is accomplished through the ownership chain .

Ownership chains were designed to make it easier for you to assign permissions, and to enhance security by requiring users to have permissions to only the upper-level objects, such as views or stored procedures. As long as the same person owns all the objects in the chain, permission is only checked at the first object that she accesses . In this case, Mary was granted permission to the view ( DBOPermsView ), but was denied access to the table ( DBOPermsTable ). Because the dbo owns both objects, as access moves from the view to the table, the permissions are not checked, and Mary has access to the data. However, if Mary attempts to access the table directly, the permissions are checked at the table, and she is denied access. The reasoning behind the ownership chain works like this: If I own a table and I own the view, then when I grant permissions to the view, I obviously want the user to have access to the table. By not granting specific permissions to the table, you also restrict access to the data because this data is accessible only through the view.

Dealing with Broken Ownership Chains

One problem that can arise when you are using views occurs when you have different owners for objects in your database. Whenever there is a change in ownership, the owner of each object has to grant permissions to the object. When the ownership of objects in a chain is changed, there is a break in ownership or you have a broken ownership chain.

The following script creates tables and views for a database. It then applies permissions to the upper-layer objects. This script creates a broken ownership and illustrates the issues that you should be aware of with different object owners.

 CREATE TABLE dbo.DBOTable1 ( id int, name varchar(20) ) CREATE TABLE bob.BobTable1 ( id int, description varchar(20) ) CREATE TABLE jane.JaneTable1 ( id int, address varchar(20) ) GO INSERT INTO dbo.DBOTable1 VALUES (1, 'Buddy') INSERT INTO Jane.JaneTable1 VALUES (1, '123 Some Street') INSERT INTO Bob.BobTable1 VALUES (1, 'What was his name?') GO CREATE VIEW Jane.JaneView1 AS   SELECT dbo.DBOTable1.id, name, address     FROM dbo.DBOTable1     INNER JOIN Jane.JaneTable1     ON dbo.DBOTable1.id = Jane.JaneTable1.id GO CREATE VIEW Jane.JaneView2 AS   SELECT id, description     FROM Bob.BobTable1 GO CREATE VIEW Bob.BobView1 AS   SELECT Jane.JaneView1.id, name, address, description     FROM Jane.JaneView1     INNER JOIN Jane.JaneView2     ON Jane.JaneView1.id = Jane.JaneView2.id GO REVOKE all ON dbo.DBOTable1 TO public REVOKE all ON Jane.JaneTable1 TO public REVOKE all ON Bob.BobTable1 TO public GRANT SELECT ON Bob.BobView1 to Mary 

The hierarchy of data access can be seen in Figure 7.17. With the current permissions, Mary cannot access the upper-level view ( BobView1 ), even though Bob has granted her SELECT permissions to BobView1 . Mary's error message from SELECT * FROM Bob.BobView1 would look like this:

 Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'DBOTable1', database 'pubs', owner 'dbo'. Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object 'BobTable1', database 'pubs', owner 'Bob'. Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object 'JaneView1', database 'pubs', owner 'Jane'. Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object 'JaneView2', database 'pubs', owner 'Jane'. 
Figure 7.17. The data access hierarchy can lead to broken ownership chains.

graphics/07fig17.gif

To gain access to the view, Jane has to grant SELECT on JaneView1 . This additional grant automatically gives Mary access to JaneTable1 , but not DBOTable1 . With the dbo providing a grant, the last holdout would be Bob to grant SELECT on BobTable1 . Even though Bob created the top-level view, there is an ownership chain between his view and his table. Each time the owner changes, permissions have to be re-granted. It makes sense that if I have created a view based on your table, that does not mean you want people who access my view to have access to your table.

When dealing with ownership chains, the single point that will reduce permission management for you is to have a single owner for the entire chain. The dbo makes a nice owner for all objects in the database. This means that you have to apply permissions only once ”to the upper-level objects. If you have a broken ownership chain, then you may have to apply permissions to objects along the entire chain, which makes it more difficult to implement permissions and you will have reduced security. The reduced security is caused by the additional permissions granted to subsequent objects, which may create holes in your data security.

To assign dbo as the owner of objects you are creating, you have to specify dbo in the object name your are creating. For example, to create a view for dbo, you could use CREATE VIEW dbo.NewView AS . . . . You must be a member of the role.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net