Home/Tutorials/Database Management Systems/Using GRANT and REVOKE commands

0 of 2 saved

Database Management Systems3 min read

Using GRANT and REVOKE commands

Read this chapter, then pass a short check to open the next one.

Grant and Revoke SQL commands are used to provide a user with permissions to access and perform operations in a database. Data Control Language(DCL) is used to control privilege in Database.

Using GRANT and REVOKE

Data Control Language (DCL) is among the five commands in SQL. Data Control Language is used to control privileges in the database system. To perform any operation in the database, such as for creating tables, sequences, or views, a user needs privileges.

Privileges are of two types,

  • System: This includes permissions for creating a session, table, etc, and all types of other system privileges.
  • Object: This includes permissions for any command or query to perform any operation on the database tables.

Types of DCL Commands

Data Control Language provides two main commands: GRANT and REVOKE. Let us discuss them using different examples.

1. GRANT Command

It is used to provide any user’s access privileges or other privileges to the database. This command allows specified users to perform specific tasks.

Syntax:

GRANT <privilege list>
ON <relation name or view name>
TO <user/role list>;

Example: Allow a User to Create a Session

When we create a user in SQL, it is not even allowed to login and create a session until and unless proper permissions/privileges are granted to the user. The below SQL command can be used to grant the session creating privileges.

GRANT CREATE SESSION TO username;

Example: Allow a User to Create a Table

The below SQL command helps you to allow a user to create tables in the database system.

GRANT CREATE TABLE TO username;

Example: Grant Permissions to Create any Table

Sometimes a user is restricted from creating some tables with names that are reserved for system tables, but we can grant privileges to a user to create any table using the below SQL command.

GRANT CREATE ANY TABLE TO username;

Example: Grant Permissions to Drop any Table

If you want to allow a user to drop any table from the database, use the below SQL command to grant this privilege to the user.

GRANT DROP ANY TABLE TO username;

Example: Grant All Privileges to a User

sysdba is a set of privileges that has all the permissions in it. So, if we want to provide all the privileges to any user, we can simply grant them the sysdba permission.

GRANT sysdba TO username;

Example: Provide the User with Space on Tablespace to Store Table

Allowing a user to create a table is not enough to start storing data in that table. We also must provide the user with privileges to use the available tablespace for their table and data.

The below SQL command will alter the user details and will provide him access to unlimited tablespace on the database system.

ALTER USER username QUOTA UNLIMITED ON SYSTEM;

Note: Generally unlimited quota is provided to Admin users.

2. REVOKE Command

It is used to take back permissions or user’s access privileges given by using the GRANT command.

Syntax:

REVOKE <privilege list>
ON <relation name or view name>
FROM <user name>;

Example: Take Back Permissions

If you want to take back the privileges from any user, use the below REVOKE command.

REVOKE CREATE TABLE FROM username;

Conclusion

In this article, we discussed different examples of granting and taking back permission of users in a database system using GRANT and REVOKE commands.