Oracle Database Privileges and Roles Concepts :

Oracle Database Privileges and Roles Concepts :

A privilege is the right to execute a particular type of SQL statement. It can allow a user to access database objects or execute stored programs that are owned by another user or to perform system level actions.

There are two types of privileges:

system privileges

schema object privileges

Step 1: system privileges

The system privileges are not related to a certain object. They control the ability of an user to perform system level actions such as connecting to the database(creating a session), creating a table, altering a user, etc. or to run a certain type of SQL statement on any schema(select any table, create any procedure).

All the system privileges are listed in the SYSTEM_PRIVILEGE_MAP table:

SQL> select name from SYSTEM_PRIVILEGE_MAP;

Object Privileges

The object privileges control the access to a certain objects. For different object types there are different privileges( for a procedure we do have an execute object privilege but do not have a select privilege).

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';

To Find User Default TableSapce :

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username like '%HR%';

To Find a list of all privilege to a particular User :

SQL> SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = '&User' ORDER BY privilege;

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='&USer';

SQL> SELECT grantee , COUNT(privilege)  FROM dba_sys_privs GROUP BY grantee;

All the object privileges granted to a certain user can be viewed in DBA_TAB_PIVS:

SQL> select owner, table_name, privilege from dba_tab_privs where grantee='SYSTEM';

++++++++++++++++++++++++++++++++++

CREATE USER XXX  IDENTIFIED BY PWDXXX   DEFAULT TABLESPACE XXX_DATA

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

                    

  Tablespace Quotas for XXX

  ALTER USER XXX  QUOTA UNLIMITED ON XXX_DATA;

  ALTER USER XXX  QUOTA UNLIMITED ON XXX_DATA;

 ALTER USER XXX  QUOTA UNLIMITED ON TEMP;

  To Grant a Role, System Privileges for XXX user :

  GRANT CONNECT TO XXX;

  ALTER USER XXX DEFAULT ROLE ALL;

  GRANT CREATE SYNONYM TO XXX;  //TABLE,VIEW, SESSION,SEQUENCE

  GRANT ALTER SESSION TO XXX;

+++++++++++++++++++++++++++++++++++++

180028.1,1016552.102,1347470.1

+++++++++++++++++++++++++++++++++++++