Database Security Part I Elisa Bertino CERIAS and CS ECE Departments Purdue University Pag 1 Topics The access control model of System R Extensions to the System R model Views and content based access control Oracle VPD Multi level relational data model The Orion authorization model Pag 2 Access Control in Commercial DBMSs All commercial systems adopt DAC Current discretionary authorization models for relational DBMS are based on the System R authorization model Griffiths and Wade76 It is based on ownership administration with administration delegation Pag 3 The System R Authorization Model Objects to be protected are tables and views Privileges include select update insert delete drop index only for tables alter only for tables Groups are supported whereas roles are not Privileges can be granted with the GRANT OPTION Pag 4 The System R Delegation Privilege delegation is supported through the grant option if a privilege is granted with the grant option the user receiving it can not only exercise the privilege but can also grant it to other users a user can only grant a privilege on a given relation if he she is the table owner or if he she has received the privilege with grant option Pag 5 Grant operation GRANT PrivilegeList ALL PRIVILEGES ON Relation View TO UserList PUBLIC WITH GRANT OPTION it is possible to grant privileges on both relations and views privileges apply to entire relations or views for the update privilege one needs to specify the columns to which it applies Pag 6 Grant operation example Bob GRANT select insert ON Employee TO Ann WITH GRANT OPTION Bob GRANT select ON Employee TO Jim WITH GRANT OPTION Ann GRANT select insert ON Employee TO Jim Jim has the select privilege received from both Bob and Ann and the insert privilege received from Ann Jim can grant to other users the select privilege because it has received it with grant option however he cannot grant the insert privilege Pag 7 Grant operation The authorization catalogs keep track for each users of the privileges the user possesses and of the ones that the user can delegate whenever a user u executes a Grant operation the system intersects the delegable privileges of u with the set of privileges specified in the command if the intersection is empty the command is not executed Pag 8 Grant operation example Bob GRANT select insert ON Employee TO Jim WITH GRANT OPTION Bob GRANT select ON Employee TO Ann WITH GRANT OPTION Bob GRANT insert ON Employee TO Ann Jim GRANT update ON Employee TO Tim WITH GRANT OPTION Ann GRANT select insert ON Employee TO Tim Pag 9 Grant operation example The first three GRANT commands are fully executed Bob is the owner of the table The fourth command is not executed because Jim does not have the update privilege on the table The fifth command is partially executed Ann has the select and insert but she does not have the grant option for the insert Tim only receives the select privilege Pag 10 Revoke operation REVOKE PrivilegeList ALL PRIVILEGES ON Relation View FROM UserList PUBLIC a user can only revoke the privileges he she has granted it is not possible to only revoke the grant option upon execution of a revoke operation the user from whom the privileges have been revoked looses these privileges unless has them from some source independent from that that has executed the revoke Pag 11 Revoke operation example Bob GRANT select ON Employee TO Jim WITH GRANT OPTION Bob GRANT select ON Employee TO Ann WITH GRANT OPTION Jim GRANT insert ON Employee TO Tim Ann GRANT select ON Employee TO Tim Jim REVOKE select ON Employee FROM Tim Tim continues to hold the select privilege on table Employee after the revoke operation since he has independently obtained such privilege from Ann Pag 12 Revoke operations Recursive revocation whenever a user revokes an authorization on a table from another user all the authorizations that the revokee had granted because of the revoked authorization are removed The revocation is iteratively applied to all the subjects that received the access authorization from the revokee Pag 13 Recursive revoke Let G1 Gn be a sequence of grant operations with a single privilege on the same relations such that i k 1 n if i k then Gi is executed before Gk Let Ri be the revoke operation for the privilege granted with operation G i The semantics of the recursive revoke requires that the state of the authorization system after the execution of the sequence G1 Gn Ri be identical to the state that one would have after the execution of the sequence G1 Gi 1 G i 1 Gn Pag 14 Recursive Revocation 10 Ann 30 Bob Sue 40 70 Jim 20 Chris 10 60 50 Ann Bob Jim 20 Pat Chris 50 Pag 15 60 Pat Dave Recursive revocation Recursive revocation in the System R takes into account the timestamps denoting when each authorization has been granted Variations to this approach have been proposed that do not take into account the timestamps the reason is to avoid cascades of revoke In such variations the authorizations granted by the revokee are kept as long as the revokee has other authorizations for the same privilege even if these authorizations have a larger timestamps with respect to the timestamps of the grant operations performed by the revokee Pag 16 Recursive revocation without timestamp 10 Ann 30 Bob Sue 40 70 Dave Jim 20 Chris 10 60 50 Ann Jim Chris Sue 40 Bob 20 Pat 50 Pag 17 60 70 Pat Dave Noncascading Revoke Recursive revoke can be a very disruptive operation A recursive revoke entails revoking all authorizations the revokee granted for which no other supporting authorizations exist and recursively revoking all authorizations granted through them Invalidating application programs and views Pag 18 Noncascading Revoke The noncascading revoke allows a user to revoke a privilege on a table from another user without entailing automatic revocation of the authorizations for the privilege on the table the latter may have granted Instead of deleting the authorizations the revokee may have granted by using the privilege received by the revoker all these authorizations are restated as if they had been granted by the revoker Pag 19 Noncascading Revoke The semantics of the revocation without cascade of privilege p on table t from user y by user x is To restate with x as grantor all authorizations that y granted by using the authorization being revoked Pag 20 Noncascading Revoke Note that since y may have received the grant option for the privilege on the table from some other users different from x not