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.

PrivilegeDescription
ADMINEnables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
ALTER ANY CACHE GROUPEnables a user to alter any cache group in the database.
ALTER ANY INDEXEnables a user to alter any index in the database.
ALTER ANY MATERIALIZED VIEWEnables a user to alter any materialized view in the database.
ALTER ANY PROCEDUREEnables a user to alter any PL/SQL procedure, function or package in the database.
ALTER ANY SEQUENCEEnables a user to alter any sequence in the database.
ALTER ANY TABLEEnables a user to alter any table in the database.
ALTER ANY VIEWEnables a user to alter any view in the database.
CACHE_MANAGEREnables a user to perform operations related to cache groups.
CREATE ANY CACHE GROUPEnables a user to create a cache group owned by any user in the database.
CREATE ANY INDEXEnables a user to create an index on any table or materialized view in the database.
CREATE ANY MATERIALIZED VIEWEnables a user to create a materialized view owned by any user in the database.
CREATE ANY PROCEDUREEnables a user to create a PL/SQL procedure, function or package owned by any user in the database.
CREATE ANY SEQUENCEEnables a user to create a sequence owned by any user in the database.
CREATE ANY SYNONYMEnables a user to create a private synonym owned by any user in the database.
CREATE ANY TABLEEnables a user to create a table owned by any user in the database.
CREATE ANY VIEWEnables a user to create a view owned by any user in the database.
CREATE CACHE GROUPEnables a user to create a cache group owned by that user.
CREATE MATERIALIZED VIEWEnables a user to create a materialized view owned by that user.
CREATE PROCEDUREEnables a user to create a PL/SQL procedure, function or package owned by that user.
CREATE PUBLIC SYNONYMEnables a user to create a public synonym.
CREATE SEQUENCEEnables a user to create a sequence owned by that user.
CREATE SESSIONEnables a user to create a connection to the database.
CREATE SYNONYMEnables a user to create a private synonym.
CREATE TABLEEnables a user to create a table owned by that user.
CREATE VIEWEnables a user to create a view owned by that user.
DELETE ANY TABLEEnables a user to delete from any table in the database.
DROP ANY CACHE GROUPEnables a user to drop any cache group in the database.
DROP ANY INDEXEnables a user to drop any index in the database.
DROP ANY MATERIALIZED VIEWEnables a user to drop any materialized view in the database.
DROP ANY PROCEDUREEnables a user to drop any PL/SQL procedure, function or package in the database.
DROP ANY SEQUENCEEnables a user to drop any sequence in the database.
DROP ANY SYNONYMEnables a user to drop a synonym owned by any user in the database.
DROP ANY TABLEEnables a user to drop any table in the database.
DROP ANY VIEWEnables a user to drop any view in the database.
DROP PUBLIC SYNONYMEnables a user to drop a public synonym.
EXECUTE ANY PROCEDUREEnables a user to execute any PL/SQL procedure, function or package in the database.
FLUSH ANY CACHE GROUPEnables a user to flush any cache group in the database.
INSERT ANY TABLEEnables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table.
LOAD ANY CACHE GROUPEnables a user to load any cache group in the database.
REFRESH ANY CACHE GROUPEnables a user to flush any cache group in the database.
SELECT ANY SEQUENCEEnables a user to select from any sequence or synonym on a sequence in the database.
SELECT ANY TABLEEnables a user to select from any table, view, materialized view, or synonym in the database.
UNLOAD ANY CACHE GROUPEnables a user to unload any cache group in the database.
UPDATE ANY TABLEEnables a user to update any table or synonym in the database.
XLAEnables a user to connect to a database as an XLA reader.

 

 

Object Privileges are in the Oracle database as follows.

PrivilegeObject typeDescription
DELETETableEnables a user to delete from a table.
EXECUTEPL/SQL package, procedure or functionEnables a user to execute a PL/SQL package, procedure or function directly.
FLUSHCache groupEnables a user to flush a cache group.
INDEXTable or materialized viewEnables a user to create an index on a table or materialized view.
INSERTTable or synonymEnables a user to insert into a table or into the table through a synonym.
LOADCache groupEnables a user to load a cache group.
REFERENCESTable or materialized view
The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table.
REFRESHCache groupEnables a user to refresh a cache group.
SELECTTable, sequence, view, materialized view, or synonymEnables a user to select from a table, sequence, view, materialized view, or synonym.
The SELECT privilege enables a user to perform all operations on a sequence.
A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table.
UNLOADCache groupEnables a user to unload a cache group.
UPDATETableEnables a user to update a table.

 

SCHEMA in Oracle 

Schemas are called database objects aggregated together and Schemas are equal to User in Oracle database. A schema contains tables, procedures, sequences,synonyms, index and database links. Each of users has a schema, each schema has the same name as the user name. When a user is created, the schema with the same name is created.

While schemas logically hold users’ objects, their containers are tablespace.

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 in Oracle

The user is created on the database as follows. A normal user does not have the priviliges to create users on the database.

 

Basic syntax ;

CREATE USER "USERNAME" PROFILE "DEFAULT" IDENTIFIED BY PASSWORD DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
  • DEFAULT TABLESPACE “USERS” specify the user’s permanent storage in tablespace.
  • TEMPORARY TABLESPACE “TEMP” specify user’s temporary tablespace.
  • PROFILE “DEFAULT” which profile will be included

 

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_nameto "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 )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

3 comments

  1. Sir,
    Thank you so much .It’s very informatic material.

Leave a Reply

Your email address will not be published. Required fields are marked *