ORACLE PRIVILEGES

PRIVILEGE
"PRIVILEGE" is a type of authority to execute particular SQL statement or right access another user's objects.

Oracle define two types of privileges

• System Privilege and
• Object Privilege

System Privilege
Each user needs some system privilege to perform particular database operations.
You can see all system privileges by query

select privilege, name from system_privilege_map


Some Commonly used System privileges are:-

System Privilege	Capability
ALTER DATABASE	Make changes to the database
CREATE ANY INDEX	Create an index in any schema
CREATE PROCEDURE	Create a function, procedure, or package in your own schema.
CREATE SESSION	Connect to the database.
CREATE SYNONYM	Create a private synonym in your own schema.
CREATE PUBLIC SYNONYM	Create a public synonym.
CREATE VIEW	Create a view in your Schema
CREATE TABLE	Create a table in your own schema.
CREATE TABLESPACE	Create a new tablespace in the database.
CREATE USER	Create a user account/schema.
ALTER USER	Make changes to a user account/schema.

By Query Examples:-

grant create session, create table, unlimited tablespace to shariq


Here "shariq" is a Table, by above privileges Table "shariq" can connect to Database, creates table with unlimited table space.


However, you can also see how many privileges you have assigned from dba by query.
select * from session_privs;

Object Privilege

Object Privilege allow users to perform certain action on Database objects such as executing DML statements on tables.

Some Commonly used System privileges are:-

Object Privilege Allows a User to SELECT Perform a select INSERT Perform an insert UPDATE Perform an update DELETE Perform a delete EXECUTE Execute a stored procedure

By Query Examples:-

grant select, insert, update on emp to sha

By above query, user "sha" can access "scott's" schema for Table emp selection, insertion as well as updation.

Connect to "sha" user

conn sha/sha

For Selection

select * from scott.emp;

For Updation 

 update scott.emp set ename = 'Sharique' where empno = 7369;