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

Hi,

I will explain How to Grant and Revoke Privileges in Oracle Database in this post.

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.

Privilege Description
ADMIN Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
ALTER ANY CACHE GROUP Enables a user to alter any cache group in the database.
ALTER ANY INDEX Enables a user to alter any index in the database.
ALTER ANY MATERIALIZED VIEW Enables a user to alter any materialized view in the database.
ALTER ANY PROCEDURE Enables a user to alter any PL/SQL procedure, function or package in the database.
ALTER ANY SEQUENCE Enables a user to alter any sequence in the database.
ALTER ANY TABLE Enables a user to alter any table in the database.
ALTER ANY VIEW Enables a user to alter any view in the database.
CACHE_MANAGER Enables a user to perform operations related to cache groups.
CREATE ANY CACHE GROUP Enables a user to create a cache group owned by any user in the database.
CREATE ANY INDEX Enables a user to create an index on any table or materialized view in the database.
CREATE ANY MATERIALIZED VIEW Enables a user to create a materialized view owned by any user in the database.
CREATE ANY PROCEDURE Enables a user to create a PL/SQL procedure, function or package owned by any user in the database.
CREATE ANY SEQUENCE Enables a user to create a sequence owned by any user in the database.
CREATE ANY SYNONYM Enables a user to create a private synonym owned by any user in the database.
CREATE ANY TABLE Enables a user to create a table owned by any user in the database.
CREATE ANY VIEW Enables a user to create a view owned by any user in the database.
CREATE CACHE GROUP Enables a user to create a cache group owned by that user.
CREATE MATERIALIZED VIEW Enables a user to create a materialized view owned by that user.
CREATE PROCEDURE Enables a user to create a PL/SQL procedure, function or package owned by that user.
CREATE PUBLIC SYNONYM Enables a user to create a public synonym.
CREATE SEQUENCE Enables a user to create a sequence owned by that user.
CREATE SESSION Enables a user to create a connection to the database.
CREATE SYNONYM Enables a user to create a private synonym.
CREATE TABLE Enables a user to create a table owned by that user.
CREATE VIEW Enables a user to create a view owned by that user.
DELETE ANY TABLE Enables a user to delete from any table in the database.
DROP ANY CACHE GROUP Enables a user to drop any cache group in the database.
DROP ANY INDEX Enables a user to drop any index in the database.
DROP ANY MATERIALIZED VIEW Enables a user to drop any materialized view in the database.
DROP ANY PROCEDURE Enables a user to drop any PL/SQL procedure, function or package in the database.
DROP ANY SEQUENCE Enables a user to drop any sequence in the database.
DROP ANY SYNONYM Enables a user to drop a synonym owned by any user in the database.
DROP ANY TABLE Enables a user to drop any table in the database.
DROP ANY VIEW Enables a user to drop any view in the database.
DROP PUBLIC SYNONYM Enables a user to drop a public synonym.
EXECUTE ANY PROCEDURE Enables a user to execute any PL/SQL procedure, function or package in the database.
FLUSH ANY CACHE GROUP Enables a user to flush any cache group in the database.
INSERT ANY TABLE Enables 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 GROUP Enables a user to load any cache group in the database.
REFRESH ANY CACHE GROUP Enables a user to flush any cache group in the database.
SELECT ANY SEQUENCE Enables a user to select from any sequence or synonym on a sequence in the database.
SELECT ANY TABLE Enables a user to select from any table, view, materialized view, or synonym in the database.
UNLOAD ANY CACHE GROUP Enables a user to unload any cache group in the database.
UPDATE ANY TABLE Enables a user to update any table or synonym in the database.
XLA Enables a user to connect to a database as an XLA reader.

 

 

Object Privileges are in the Oracle database as follows.

Privilege Object type Description
DELETE Table Enables a user to delete from a table.
EXECUTE PL/SQL package, procedure or function Enables a user to execute a PL/SQL package, procedure or function directly.
FLUSH Cache group Enables a user to flush a cache group.
INDEX Table or materialized view Enables a user to create an index on a table or materialized view.
INSERT Table or synonym Enables a user to insert into a table or into the table through a synonym.
LOAD Cache group Enables a user to load a cache group.
REFERENCES Table or materialized view
The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table.
REFRESH Cache group Enables a user to refresh a cache group.
SELECT Table, sequence, view, materialized view, or synonym Enables 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.
UNLOAD Cache group Enables a user to unload a cache group.
UPDATE Table Enables a user to update a table.

 

SCHEMA 

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/

 

 

Creating User

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

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 read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 

 692 views last month,  2 views today

About Mehmet Salih Deveci

blank
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 mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  1,508 views …

Leave a Reply