DBMS Security Issues Computer Security CS 426 Lecture 18 Database security Grant Revoke Model Database access control Database encryption Availability and transactions Vulnerabilities of DBMS implementations Elisa Bertino Purdue University IN USA bertino cs purdue edu 1 Access Control in Commercial DBMSs Most 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 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 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 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 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 privileges that U can delegate with the set of privileges specified in the command If the intersection is empty the command is not executed Grant operation Bob is the owner of Employee table He thus has Select Insert Update Delete all with the Grant privileges on table Employee Grant operation example 1 Bob GRANT select insert ON Employee TO Jim WITH GRANT OPTION 2 Bob GRANT select ON Employee TO Ann WITH GRANT OPTION 3 Bob GRANT insert ON Employee TO Ann 4 Jim GRANT update ON Bob Employee TO Tim WITH GRANT OPTION 5 Ann GRANT select insert ON Bob Employee TO Tim 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 revoke the grant option only upon execution of a revoke operation the user from whom the privileges have been revoked looses these privileges unless s he has them from another user independent from the one that has executed the revoke 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 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 select 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 Revoke operation example Bob GRANT select ON Employee TO Jim WITH GRANT OPTION The grantor The permission The grantee Revoke operation example Bob GRANT select ON Employee TO Jim WITH GRANT OPTION The grantor Time 10 The grantee The permission Grantor BOB Privilege P1 G Grantee JIM Pag 14 Revoke operation tabular representation 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 select 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 Time Privilege 10 20 30 40 Grantor Revoker BOB BOB JIM ANN Grantee Revokee G P1 G JIM G P1 G ANN G P1 TIM G P1 TIM 50 JIM R P1 TIM Revoke operation graph representation 10 G P1 G JIM BOB 20 G P1 G 30 G P1 50 R P1 ANN TIM 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 40 G P1 The revocation is iteratively applied to all the subjects that received the access authorization from the revokee Recursive Revocation with timestamp 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 Gi 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 10 Ann 30 Bob Sue 40 70 Jim 20 Chris 10 60 50 Ann Bob Jim 20 Pat Chris 50 60 Pat Dave Recursive Revocation without timestamp 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 Views and content based authorization Views are a mechanism commonly used to support content based access control in RDBMS Content based access authorizations should be specified in terms of predicates Only the tuples of a relation verifying a given predicate are considered as the protected objects of the authorization 10 Ann Bob Sue 40 30 70 Dave Jim 20 Chris 10 60 50 Ann Jim Chris Sue 40 Bob 20 Pat 60 70 Dave Pat 50 Views and content based authorization The approach to support content based access control in RDBMS can be summarized as follows Define a view containing the predicates to select the tuples to be returned to