What is DCL?

Data Control Language is known as DCL in SQL. By creating users and granting or removing privileges from users, DCL commands are used to regulate and manage access to the database.

There are two main DCL commands in SQL:

• GRANT
• REVOKE.

GRANT Query:

SQL GRANT command is used to grant specific privileges or permissions to one or more users on database objects such as tables, views, stored procedures, functions, etc.

Syntax:

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

where,

• privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
• object_name is the name of a database object like TABLE, VIEW, STORED PROC, and SEQUENCE.
• user_name is the name of the user to whom an access right is being granted.
• PUBLIC is used to grant access rights to all users.
• ROLES are a set of privileges grouped together.
• WITH GRANT OPTION – allows a user to grant access rights to other users.

REVOKE Query:

Previously granted privileges or permissions for one or more users or roles on database objects like tables, views, stored procedures, functions, etc. can be removed or revoked using the SQL REVOKE command.

Syntax:

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

where,

• privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
• object_name is the name of a database object like TABLE, VIEW, STORED PROC, and SEQUENCE.
• user_name is the name of the user to whom an access right is being granted
• PUBLIC is used to grant access rights to all users.
• ROLES are a set of privileges grouped together.

Examples:

• Allow a User to create session

To allow a user to create a session using GRANT in SQL, you can use the following syntax:

GRANT CREATE SESSION TO username;

Where “username” is the name of the user you want to grant the privilege to create a session.

• Allow a User to create table

To allow a user to create a table in SQL, we can use the following syntax:

GRANT CREATE TABLE TO username;

Where “username” is the name of the user we want to grant the privilege to create tables.

• Provide user with space on tablespace to store table

Giving a user the ability to create a table does not automatically start data storage in that table. Additionally, we must grant the user access rights so that they can use the available tablespace for their table and data.

To provide a user with the necessary privileges to use a tablespace, you can use the following steps:

GRANT UNLIMITED TABLESPACE TO username;

The “UNLIMITED TABLESPACE” privilege enables the user to create tables in any database tablespace with no size restriction.

• Grant all privilege to a User

To grant all privileges to a user in SQL, we can use the following syntax:

GRANT SYSDBA TO username;

When a user has the sysdba privilege, they can use Oracle Database to carry out specific administrative tasks like managing the database and its resources.

Note: granting the sysdba privilege should be done with caution, as it gives the user full control over the database and its resources.

• Grant permission to create any table

To grant permission to a user to create any table in a specific database in SQL, we can use the following syntax:

GRANT CREATE ANY TABLE TO username;

By using this command, the database user “username” is given permission to create any table. Be aware that this permission should only be granted minimally because it gives the user the ability to create tables in any database schema, which poses a security risk.

• Grant permission to drop any table

The following query can be used to grant permission to drop any table:

GRANT DROP ANY TABLE TO username

This command grants the user “username” permission to drop any table in any database on the server.

• Take back Permissions

To revoke permissions that were previously granted to a user in SQL, the following syntax is used:

REVOKE permission_name ON object_name FROM username;

Where,

• “permission_name” is the name of the permission we want to revoke (e.g. SELECT, INSERT, UPDATE, DELETE),
• “object_name” is the name of the object the permission was granted on (e.g. table, view, database),
• “username” is the name of the user the permission was granted to.

Note: also read about SQL: TCL Commands

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py