DCL Command:




The Data Control Language statements are used to allow the user to have a control to check that who can access the specified objects in the database. The database user can be a person or the application program.

To perform any operations in the database the privileges are used and these privileges are controlled by the Data Control language that is DCL. In the Data Control Language, there are two privileges that are: System and Object.




SYSTEM: System privilege includes the creation of tables and sessions etc.

OBJECT: The object privilege includes commands and queries working on the tables.

There are two commands that are defined by the Data Control Language and are as follows:

  • The GRANT statement is used to grant the permission of right to the user.
  • The REVOKE statement is used to tack back the permission of right from the user.

 

Allowing the user to create Session

The session in SQL can be created by using the grant command as in the following statement:

grant create session to username;

The username can be any user name

 

Allowing the user to create a table

The table in SQL can be created by using the grant create command as in the following statement:

grant create table to username;

The username can be any username.

 

Providing the User some space on Table space to store Table

You can provide the user a space to store the table by using the following statement with the alter command:

alter user username quota unlimited on system;




Granting all privileges to the user

The user can be granted all the privileges by using the following statement in which grant command is used:

Grant sysdba to username

The username can be any username.

 

To Grant Permission to create any Table

The user can also be granted permission to create any table in SQL by using the following statement in which grant and create commands are used:

grant create any table to username

The username can be any username.

 

Granting Permission to drop any Table

By dropping any table we mean to delete the definition of the table. The DROP TABLE statement is used to delete the definition of the table from the database. The grant drop command is used. Consider the following example:

grant drop any table to username

The username can be any username.




Taking back Permissions

The REVOKE statement is used to tack back the permission of right from the user. In the following statement revoke command is used to tack back all the permissions from the user:

revoke create table from username

The username can be any username.