The Data Control Language (or DCL) is typically used to control privileges in a Database. In other to perform a certain operation in the database, like creating tables, sequences or views; a user needs privileges.
There are two types of privileges, they include;
However, in Data Control Language DCL, we have two types of commands,
When a user is created in SQL, it is not permitted to log in and creates a session except when proper permissions/privileges are granted to the user.
The command described below can be used to grant the session creating privileges.
GRANT CREATE SESSION TO username;
In other to allow a user to create tables in the database, the command below can be used.
GRANT CREATE TABLE TO username;
Giving a user permission to create a table is not enough to store data in that table. The user must be provided with privileges to use the available tablespace for their table and data.
ALTER USER username QUOTA UNLIMITED ON SYSTEM;The command above can alter the user details, as well as provide it access to unlimited tablespace on the system.
Note: An unlimited quota is provided to Admin users generally.
The ‘sysdba’ is a set of privileges that has all the permissions embedded in it. Therefore, if you want to provide all the privileges to any user, you can simply grant them the ‘sysdba’ permission.
GRANT sysdba TO username
In some instances, a user may be restricted from creating some tables with names that are reserved for system tables. However, privileges can be granted to a user to create any table using the below command.
GRANT CREATE ANY TABLE TO username
In this case, when you want to allow a user to drop any table from the database, you can grant this privilege to the user.
GRANT DROP ANY TABLE TO username
More so, when you want to take back the privileges from any user, you can make use of the ‘revoke’ command.
REVOKE CREATE TABLE FROM username