GRANT
and REVOKE
Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types,
In DCL we have two commands,
GRANT
: Used to provide any user access privileges or other priviliges for the database.REVOKE
: Used to take back permissions from any user.When we create a user in SQL, it is not even allowed to login and create a session until and unless proper permissions/priviliges are granted to the user.
Following command can be used to grant the session creating priviliges.
GRANT CREATE SESSION TO username;
To allow a user to create tables in the database, we can use the below command,
GRANT CREATE TABLE TO username;
Allowing a user to create table is not enough to start storing data in that table. We also must provide the user with priviliges to use the available tablespace for their table and data.
ALTER USER username QUOTA UNLIMITED ON SYSTEM;
The above command will alter the user details and will provide it access to unlimited tablespace on system.
NOTE: Generally unlimited quota is provided to Admin users.
sysdba
is a set of priviliges which 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
Sometimes user is restricted from creating come tables with names which are reserved for system tables. But we can grant privileges to a user to create any table using the below command,
GRANT CREATE ANY TABLE TO username
As the title suggests, if you want to allow user to drop any table from the database, then grant this privilege to the user,
GRANT DROP ANY TABLE TO username
And, if you want to take back the privileges from any user, use the REVOKE
command.
REVOKE CREATE TABLE FROM username