SQL DCL Statements

SQL Data Control Language (DCL) statements are used to control privileges given to objects in a database. These statements are used to enforce database security in a multiple user database environment. The authorization to provide/remove privileges on a database object can be given by using database admin id only.

GRANT Statements

This GRANT Statement is used to provide privileges to users on the objects of a database

Syntax:

GRANT privilege_name ON object_name TO {user_name |PUBLIC | role_name} [WITH GRANT OPTION];

  • privilege_name = Access level or privilege granted to the user. Access levels are ALL, EXECUTE and SELECT
  • object_name = database object name like TABLE, VIEW, SEQUENCE, STORED_PROC etc
  • user_name = user id to which privilege is to be given
  • PUBLIC = is used to grant access right to all users.
  • WITH GRANT OPTION =gives privilege to a user to GRANT privilege to other users.

REVOKE Statements

This REVOKE statement is used to remove privilege from users to perform certain operation on database objects.

Syntax:

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name};

Privileges

Privileges are the right to execute particular SQL statements. Privileges are of two types:

  • System Privilege – Required by user to gain access to database
  • Object Privilege – Required by user to manipulate contents of the objects in a database

System Privilege

  • CREATE OBJECT – allow the user to create the specified objects in their own schema.
  • CREATE ANY OBJECT – allow the user to create the specified objects in any schema.
  • BACKUP ANY TABLE – allow the users to backup any table in any schema with the export utility

These similar rules apply to ALTER, DROP system privilege too. The objects can be SESSION, TABLE, VIEW, SEQUENCE, PROCEDURE.




Object Privilege

  • INSERT – allow users to insert rows in to a table
  • SELECT – allow users to select data from a table
  • UPDATE – allow users to update data in a table
  • EXECUTE – allow users to execute stored procedure or a function

Roles

A role is a group of privileges that can be granted to a user or multiple users. Roles are useful in maintaining multiuser access management. One user can have several roles and several users can be assigned one role.

Syntax:

CREATE ROLE role;

After this role is created, DBA can use the GRANT statement to assign this role to users as well as assign privilege to this role.

Example:

CREATE ROLE Inspector;

GRANT CREATE TABLE, CREATE VIEW TO Inspector;

GRANT Inspector to Vikrant, Pandey;

REVOKE CREATE TABLE from Inspector;

DROP ROLE Inspector;