Home

Data Control Language:

  • Deals with the security of the database.
  • Maintains concurrent access.
  • Grants and revokes various permissions to access the database.

Forms of authorization on parts of the database: * Read - allows reading, but not modification of data * Insert - allows insertion of new data, but not modification of existing data * Update - allows modification, but not deletion of data * Delete - allows deletion of data

Forms of authorization to modify the database schema:

  • Index - allows creation and deletion of indices
  • Resources - allows creation of new relations
  • Alteration - allows addition or deletion of attributes in a relation
  • Drop - allows deletion of relations

Grant

  • used to confer authorisation

SYNTAX

grant <privilege list>
on <relation name or view name> to <user list>

where:
\ includes:

  • select: allows read access to relation .
  • insert: the ability to insert tuples.
  • update: the ability to update using the SQL update statement.
  • delete: the ability to delete tuples.
  • all privileges: used as a short form for all the allowable privileges.

Example:

grant select on instructor to U1,U2,U3

  • The above query grants the select privilege on the instructor table to the users U1, U2 and U3. \ includes:

  • a user-id

  • public - which allows all valid users the privilege granted
  • A role - groups users under a name so that privileges can be added powerfully to a group directly rather than granting one by one to each user.

Creating a role:

create role instructor
grant select on takes to instructor
  • The above query creates a role named instructor and grants the select privilege of the table takes to the users falling under the role of instructor. 
create role teaching_assistant
grant teaching_assistant to instructor;
  • The above query creates a role named teaching_assistant and grants the privileges available to the role to the users falling under the role of instructor. This means instructor inherits the permissions from teaching_assistant.
  • A chain of roles can be constructed likewish in an organisational hierarchy.

NOTE : * Granting a privilege on a view does not imply granting any privileges on the underlying relations. * The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).

Revoke

  • Used to revoke authorization.

SYNTAX

revoke <privilege list>
on <relation name or view name> from <user list>

Example:

revoke select on branch from U1,U2,U3
  • The above query revokes the select privilege on the instructor table for the users U1, U2 and U3.

NOTE: * If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. * All privileges that depend on the privilege being revoked are also revoked.

Authorisation on Views:

create view geo_instructor as
(select *
from instructor
where dept_name = 'Geology');
grant select on geo_instructor to geo_staff
  • The above query creates a view named geo_instructor that contains all the tuples from the relation instructor where department name is Geology. It then grants the select privilege of the view created to the role named geo_staff.

Other features:

• references privilege to create foreign key

grant reference (dept_name) on department to Mariano;
  • This is required to grant the referential integrity constraint.

Transfer of privileges:

grant select on department to Amit with grant option
  • Here the keywords with grant option signify that the select privilege has been given to Amit and he will further be able to grant it to any other user or role. 
revoke select on department from Amit, Satoshi cascade
  • Cascade signifies that select privilege will be revoked along with all other privileges which depend on it.
revoke select on department from Amit, Satoshi restrict
  • Restrict signifies that select privilege will only be revoked it there are no other privileges depending on it.