How to Grant and Revoke Privileges | Create and Drop any User in Oracle Database

I will explain How to Grant and Revoke Privileges in Oracle Database in this post. Let’s review the Grant and Revoke Privileges in Oracle.

Grant and Revoke Privileges in Oracle

There are two Data Control Language Statements ( Grant and Revoke ) in Oracle database that are used to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.

GRANT  is used to grant privileges to Users or Roles.
REVOKE  is used to take back privileges from Users or Roles.

There are two types of Priviliges as follows.

• System Privileges
• Object privileges

System Privileges are in the Oracle database as follows.

Object Privileges are in the Oracle database as follows.

SCHEMA in Oracle 

If you don’t know What is the segments,extents and tablespace, read the following article.

https://ittutorial.org/what-is-the-tablespace-in-oracle-how-to-add-datafile-a-tablespace-and-tablespaces-usage-size-check/

CREATE USER "USERNAME" PROFILE "DEFAULT" IDENTIFIED BY PASSWORD DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

You can create any user in the database as follows.

SQL> create user mehmetsalih identified by deveci;

User created.

SQL>

Database Role

Role consist of priviliges and aims to facilitate privilige management and dynamic. You can grant the necessary privileges under a role to users.  When the privileges under the role change, this affects all users with that role.

Role creation :

In SQL language, if you want to create a role, the following steps are followed.

CREATE ROLE role_name;

You can assign the role to a user  as follows.

GRANT role_name to user_name;

The following roles are already defined automatically for Oracle databases.

• CONNECT
• RESOURCE
• DBA
• EXP_FULL_DATABASE
• IMP_FULL_DATABASE

Connect and Resource roles should be granted for the new users, if they will use only their schema.

If you grant any user as DBA ( Database Admin ), then you should grant DBA to that user as follows.

SQL> grant connect to mehmetsalih;

Grant succeeded.

SQL> grant resource to mehmetsalih;

Grant succeeded.

SQL> grant dba to mehmetsalih;

Grant succeeded.

SQL>

Grant System Priviliges

System and object priviliges are granted as follows.

Grant System priviliges Syntax

GRANT  "System_priviliges_name"  to "user_name";

SQL> grant create any table to mehmetsalih;

Grant succeeded.

SQL> grant drop any table to mehmetsalih;

Grant succeeded.

SQL> grant insert any table to mehmetsalih;

Grant succeeded.

SQL> grant update any table to mehmetsalih;

Grant succeeded.

SQL> grant delete any table to mehmetsalih;

Grant succeeded.

SQL> grant select any table to mehmetsalih;

Grant succeeded.

You can grant multiple system priviliges in a Grant clause as follows.

SQL> grant create any index,alter any table to mehmetsalih;

Grant succeeded.

SQL>

Grant Object Priviliges

Grant Object priviliges Syntax

Syntax,

GRANT object_priv [(columns)] ON object TO  {user | role | PUBLIC} [WITH GRANT OPTION];



GRANT object_privileges ON object TO user;

SQL> GRANT SELECT ON CUSTOMERS TO MEHMETSALIH;

SQL> 
SQL> grant select on test_table to mehmetsalih;

Grant succeeded.

SQL> grant insert on test_table to mehmetsalih;

Grant succeeded.

SQL> grant update on test_table to mehmetsalih;

Grant succeeded.

SQL> grant delete on test_table to mehmetsalih;

Grant succeeded.

SQL>

Revoke Priviliges in Oracle

You can revoke any existing priviliges from user or roles as follows.

Syntax,

REVOKE {privilege [, privilege...] | ALL} ON object FROM {user [,user] | role | PUBLIC } [CASCADE CONSTRAINTS];

You can revoke the roles from any user as follows.

SQL> 
SQL> revoke dba from mehmetsalih;

Revoke succeeded.

SQL> revoke resource from mehmetsalih;

Revoke succeeded.

SQL> revoke connect from mehmetsalih;

Revoke succeeded.

SQL>

You can revoke the system priviliges from any user as follows

SQL> revoke create any table from mehmetsalih;

Revoke succeeded.

SQL> revoke drop any table from mehmetsalih;

Revoke succeeded.

SQL> revoke insert any table from mehmetsalih;

Revoke succeeded.

SQL> revoke update any table from mehmetsalih;
Revoke succeeded.

SQL> revoke delete any table from mehmetsalih;

Revoke succeeded.

SQL>revoke select any table from mehmetsalih;

Revoke succeeded.

SQL>

You can revoke the object priviliges from any user as follows.

SQL> revoke select on test_table from mehmetsalih;

Revoke succeeded.

SQL> 
SQL> revoke insert on test_table from mehmetsalih;

Revoke succeeded.

SQL> 
SQL> revoke update on test_table from mehmetsalih;

Revoke succeeded.

SQL> 
SQL> revoke delete on test_table from mehmetsalih;

Revoke succeeded.

SQL>

You can list the roles, dba users and users priviliges using the following dba views.

select * from dba_roles;

select * from dba_users;

select username,account_status,lock_date,expiry_date,profile from dba_users;


select * from dba_role_privs where granted_role='DBA';

select * from v$pwfile_users;


select * from dba_tab_privs;

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )