Privilege command
/*for tables*/
select 'grant select on '||owner||'.'||table_name||' to <user name>' from dba_tables where owner=<schema>;
/*for procedures*/
select 'grant execute on '||owner||'.'||object_name||' to <user name>' from dba_objects where owner=<schema>
and object_type='PROCEDURE';
where OBJECT_TYPE='PROCEDURE'
Select 'GRANT SELECT ON GBO_DB.'||Table_Name||'TO GBO_READONLY' From All_Tables Where Owner='GBO_DB';
select 'GRANT SELECT ON GBO_DB.'||view_name||' TO GBO_READONLY' From dba_views where Owner='GBO_DB';
BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || GBO_DB.table_name || ' TO readon';
END LOOP;
END;
/
==================================================================================
SQL> CREATE USER books IDENTIFIED BY books
2 ;
User created.
SQL> GRANT CONNECT TO books;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE, DBA TO books;
select * from dba_sys_privs
grant debug connect session to SCDM;
GRANT DEBUG ANY PROCEDURE TO SCDM;
password expire
SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';
What are the privileges that role contain
================================================================================
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DBMON';
select privilege from dba_sys_privs where grantee='DWUSER';
select * from dba_role_privs where grantee='DWUSER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBMON APP_BASE_ROLE YES YES
DBMON CONNECT NO YES
DBMON SQLT_USER_ROLE NO YES
DBMON DBA NO YES
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DWUSER';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SCOTT')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;
=================================================================================
Listing Privilege and Role Information
To list the grants made for objects, a user can query the following data dictionary views:
ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES
See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.
Listing Privilege and Role Information: Examples
For the following examples, assume the following statements are issued:
CREATE ROLE security_admin IDENTIFIED BY honcho;
GRANT create profile, alter profile, drop profile,
create role, drop any role, grant any role, audit any,
audit system, create user, become user, alter user, drop user
TO security_admin WITH ADMIN OPTION;
GRANT SELECT, DELETE ON sys.aud$ TO security_admin;
GRANT security_admin, create session TO swilliams;
GRANT security_admin TO system_administrator;
GRANT create session TO jward;
GRANT SELECT, DELETE ON emp TO jward;
GRANT INSERT (ename, job) ON emp TO swilliams, jward;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Listing All System Privilege Grants
The following query indicates all system privilege grants made to roles and users:
SELECT * FROM sys.dba_sys_privs;
GRANTEE PRIVILEGE ADM
-------------- --------------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES
SWILLIAMS CREATE SESSION NO
JWARD CREATE SESSION NO
Listing All Role Grants
The following query returns all the roles granted to users and other roles:
SELECT * FROM sys.dba_role_privs;
GRANTEE GRANTED_ROLE ADM
------------------ ------------------------------------ ---
SWILLIAMS SECURITY_ADMIN NO
Listing Object Privileges Granted to a User
The following query returns all object privileges (not including column specific privileges) granted to the specified user:
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'JWARD';
TABLE_NAME PRIVILEGE GRANTABLE
----------- ------------ ----------
EMP SELECT NO
EMP DELETE NO
To list all the column specific privileges that have been granted, use the following query:
SELECT grantee, table_name, column_name, privilege FROM sys.dba_col_privs where grantee='GOURC';
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
JWARD EMP JOB INSERT
Listing the Current Privilege Domain of Your Session
The following query lists all roles currently enabled for the issuer:
SELECT * FROM session_roles;
If SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:
ROLE
------------------------------
SECURITY_ADMIN
The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:
SELECT * FROM session_privs;
If SWILLIAMS has the SECURITY_ADMIN role enabled and issues this query, Oracle returns the following results:
PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
If the SECURITY_ADMIN role is disabled for SWILLIAMS, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.
Listing Roles of the Database
The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:
SELECT * FROM sys.dba_roles;
ROLE PASSWORD
---------------- --------
CONNECT NO
RESOURCE NO
DBA NO
SECURITY_ADMIN YES
Listing Information About the Privilege Domains of Roles
The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.
For example, the following query lists all the roles granted to the SYSTEM_ADMIN role:
SELECT granted_role, admin_option
FROM role_role_privs
WHERE role = 'SYSTEM_ADMIN';
GRANTED_ROLE ADM
---------------- ----
SECURITY_ADMIN NO
The following query lists all the system privileges granted to the SECURITY_ADMIN role:
SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';
ROLE PRIVILEGE ADM
----------------------- ----------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES
The following query lists all the object privileges granted to the SECURITY_ADMIN role:
SELECT table_name, privilege FROM role_tab_privs
WHERE role = 'SECURITY_ADMIN';
SELECT table_name, privilege FROM role_tab_privs WHERE role ='QUERY_ALL_ROLE';
TABLE_NAME PRIVILEGE
--------------------------- ----------------
AUD$ DELETE
AUD$ SELECT
SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
SQL> CONNECT u1/u1
Connected.
SQL> GRANT SELECT ON scott.t2 TO u2;
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'employees';
GRANTEE OWNER
------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
U2 SCOTT
SCOTT SELECT NO
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';
GRANTEE OWNER GRANTOR PRIVILEGE
-------------- -------------- ------------- ---------------
ESRVT_ROLE ESRVP ESRVP DELETE
-------------------------------------------------------------------------------------------------------------------------------------------------
----->SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
========================================================================================
grant select, insert, update, delete on suppliers to smithj;
grant select, insert, update, delete on suppliers to smithj;
grant select on Eagan to gourc;
===========================================================================================
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege Description
Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
SELECT grantee, table_name, column_name, privilege
FROM sys.dba_col_privs;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
SQL> select privilege from dba_sys_privs where GRANTEE='ERMUSR';
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
UNLIMITED TABLESPACE
9 rows selected.
grant sysdba to ermusr;
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='QUERY_ALL_ROLE';
select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
SELECT * FROM sys.dba_role_privs where grantee='MASTERSAF'
select * from dba_role_privs where granted_role='APPDBA';
SQL> desc dba_role_privs
Name Null? Type
------------ -------- ------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
To find a list of all users with DBA privilege execute the following code:
SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES
WHAT ARE THE PRIVILEGES ASSAIGN TO ROLE
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='APPDBA';
=================================================================================
SELECT * FROM sys.dba_sys_privs where grantee='DW_DML_ROLE';
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='BI_SEC';
revoke role from user
=========================
REVOKE <role_name> FROM <user_name>;
Roles
SELECT granted_role, admin_option FROM role_role_privs WHERE role = 'HS_ADMIN_SELECT_ROLE';
SQL>FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ;
END LOOP ;
Roles
++++
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE like '%DBA%' and ADMIN_OPTION='YES';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED
_ROLE like '%DBA%';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE='DBA';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTEE
like '%ORACLE%';
select GRANTEE,PRIVILEGE,ADMIN_OPTION from dba_sys_privs where grantee like
'%ORACLE%';
to check what are the privilege assign to role
-------------------------------------------------------------
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='DW_DML_ROLE';
Karthik, Team,
The below incident was not worked upon correctly. The request was to:
Create a new role APPSDBARESTRICT in UNNPSHRD. It should have the following privileges:
CONNET
CREATE SESSION
SELECT ANY DICTIONARY
ADVISOR
EXECUTE privilege on DBMS_WORKLOAD_REPOSITORY
Create a new user SARATHJ1 in database UNNPSHRD. Assign APPSDBARESTRICT role to SARATHJ1.
Of this, the role APPSDBARESTRICT was created , but the role has not been assigned to SARATHJ1 but I see that it has been assigned to SYSTEM!!!. See below queries. Going forward, please do not close the Incident unless the requester agrees.
SQL> select name, sysdate from v$database;
NAME SYSDATE
--------- ---------
UNNPSHRD 28-NOV-12
SQL> set lines 300
SQL> set pages 1000
SQL> select * from dba_sys_privs where grantee='SARATHJ1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SARATHJ1 UNLIMITED TABLESPACE NO
SQL> select * from dba_ROLE_privs where grantee='SARATHJ1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SARATHJ1 CONNECT NO YES
SARATHJ1 RESOURCE NO YES
SQL> select * from dba_roles where role='APPSDBARESTRICT';
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
APPSDBARESTRICT NO NONE
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='APPSDBARESTRICT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM APPSDBARESTRICT YES YES
SQL> select * from dba_sys_privs where grantee='APPSDBARESTRICT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
APPSDBARESTRICT ADVISOR NO
APPSDBARESTRICT CREATE SESSION NO
APPSDBARESTRICT SELECT ANY DICTIONARY NO
-- System privileges granted to an user ( scott)
SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';
-- Roles granted to an user ( scott)
SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';
-- Object privileges granted to an user ( SCOTT)
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';
-- Column specific privileges granted
SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';/*for tables*/
select 'grant select on '||owner||'.'||table_name||' to <user name>' from dba_tables where owner=<schema>;
/*for procedures*/
select 'grant execute on '||owner||'.'||object_name||' to <user name>' from dba_objects where owner=<schema>
and object_type='PROCEDURE';
where OBJECT_TYPE='PROCEDURE'
Select 'GRANT SELECT ON GBO_DB.'||Table_Name||'TO GBO_READONLY' From All_Tables Where Owner='GBO_DB';
select 'GRANT SELECT ON GBO_DB.'||view_name||' TO GBO_READONLY' From dba_views where Owner='GBO_DB';
BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || GBO_DB.table_name || ' TO readon';
END LOOP;
END;
/
==================================================================================
SQL> CREATE USER books IDENTIFIED BY books
2 ;
User created.
SQL> GRANT CONNECT TO books;
Grant succeeded.
SQL> GRANT CONNECT, RESOURCE, DBA TO books;
select * from dba_sys_privs
grant debug connect session to SCDM;
GRANT DEBUG ANY PROCEDURE TO SCDM;
password expire
SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='SIMON';
What are the privileges that role contain
================================================================================
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DBMON';
select privilege from dba_sys_privs where grantee='DWUSER';
select * from dba_role_privs where grantee='DWUSER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBMON APP_BASE_ROLE YES YES
DBMON CONNECT NO YES
DBMON SQLT_USER_ROLE NO YES
DBMON DBA NO YES
select role,privilege,admin_option from ROLE_SYS_PRIVS where role='QUERY_ALL_ROLE';
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'DWUSER';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('SCOTT')
UNION
SELECT grantee,granted_role
FROM dba_role_privs
UNION
SELECT grantee,privilege
FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;
=================================================================================
Listing Privilege and Role Information
To list the grants made for objects, a user can query the following data dictionary views:
ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES
See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.
Listing Privilege and Role Information: Examples
For the following examples, assume the following statements are issued:
CREATE ROLE security_admin IDENTIFIED BY honcho;
GRANT create profile, alter profile, drop profile,
create role, drop any role, grant any role, audit any,
audit system, create user, become user, alter user, drop user
TO security_admin WITH ADMIN OPTION;
GRANT SELECT, DELETE ON sys.aud$ TO security_admin;
GRANT security_admin, create session TO swilliams;
GRANT security_admin TO system_administrator;
GRANT create session TO jward;
GRANT SELECT, DELETE ON emp TO jward;
GRANT INSERT (ename, job) ON emp TO swilliams, jward;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Listing All System Privilege Grants
The following query indicates all system privilege grants made to roles and users:
SELECT * FROM sys.dba_sys_privs;
GRANTEE PRIVILEGE ADM
-------------- --------------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES
SWILLIAMS CREATE SESSION NO
JWARD CREATE SESSION NO
Listing All Role Grants
The following query returns all the roles granted to users and other roles:
SELECT * FROM sys.dba_role_privs;
GRANTEE GRANTED_ROLE ADM
------------------ ------------------------------------ ---
SWILLIAMS SECURITY_ADMIN NO
Listing Object Privileges Granted to a User
The following query returns all object privileges (not including column specific privileges) granted to the specified user:
SELECT table_name, privilege, grantable FROM sys.dba_tab_privs WHERE grantee = 'JWARD';
TABLE_NAME PRIVILEGE GRANTABLE
----------- ------------ ----------
EMP SELECT NO
EMP DELETE NO
To list all the column specific privileges that have been granted, use the following query:
SELECT grantee, table_name, column_name, privilege FROM sys.dba_col_privs where grantee='GOURC';
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
JWARD EMP JOB INSERT
Listing the Current Privilege Domain of Your Session
The following query lists all roles currently enabled for the issuer:
SELECT * FROM session_roles;
If SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:
ROLE
------------------------------
SECURITY_ADMIN
The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:
SELECT * FROM session_privs;
If SWILLIAMS has the SECURITY_ADMIN role enabled and issues this query, Oracle returns the following results:
PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
If the SECURITY_ADMIN role is disabled for SWILLIAMS, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.
Listing Roles of the Database
The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:
SELECT * FROM sys.dba_roles;
ROLE PASSWORD
---------------- --------
CONNECT NO
RESOURCE NO
DBA NO
SECURITY_ADMIN YES
Listing Information About the Privilege Domains of Roles
The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.
For example, the following query lists all the roles granted to the SYSTEM_ADMIN role:
SELECT granted_role, admin_option
FROM role_role_privs
WHERE role = 'SYSTEM_ADMIN';
GRANTED_ROLE ADM
---------------- ----
SECURITY_ADMIN NO
The following query lists all the system privileges granted to the SECURITY_ADMIN role:
SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';
ROLE PRIVILEGE ADM
----------------------- ----------------------------- ---
SECURITY_ADMIN ALTER PROFILE YES
SECURITY_ADMIN ALTER USER YES
SECURITY_ADMIN AUDIT ANY YES
SECURITY_ADMIN AUDIT SYSTEM YES
SECURITY_ADMIN BECOME USER YES
SECURITY_ADMIN CREATE PROFILE YES
SECURITY_ADMIN CREATE ROLE YES
SECURITY_ADMIN CREATE USER YES
SECURITY_ADMIN DROP ANY ROLE YES
SECURITY_ADMIN DROP PROFILE YES
SECURITY_ADMIN DROP USER YES
SECURITY_ADMIN GRANT ANY ROLE YES
The following query lists all the object privileges granted to the SECURITY_ADMIN role:
SELECT table_name, privilege FROM role_tab_privs
WHERE role = 'SECURITY_ADMIN';
SELECT table_name, privilege FROM role_tab_privs WHERE role ='QUERY_ALL_ROLE';
TABLE_NAME PRIVILEGE
--------------------------- ----------------
AUD$ DELETE
AUD$ SELECT
SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1;
SQL> CONNECT u1/u1
Connected.
SQL> GRANT SELECT ON scott.t2 TO u2;
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'employees';
GRANTEE OWNER
------------------------------ ------------------------------
GRANTOR PRIVILEGE GRA
------------------------------ ---------------------------------------- ---
U2 SCOTT
SCOTT SELECT NO
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';
GRANTEE OWNER GRANTOR PRIVILEGE
-------------- -------------- ------------- ---------------
ESRVT_ROLE ESRVP ESRVP DELETE
-------------------------------------------------------------------------------------------------------------------------------------------------
----->SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
========================================================================================
grant select, insert, update, delete on suppliers to smithj;
grant select, insert, update, delete on suppliers to smithj;
grant select on Eagan to gourc;
===========================================================================================
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege Description
Execute Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
SELECT grantee, table_name, column_name, privilege
FROM sys.dba_col_privs;
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
----------- ------------ ------------- --------------
SWILLIAMS EMP ENAME INSERT
SWILLIAMS EMP JOB INSERT
JWARD EMP NAME INSERT
SQL> select privilege from dba_sys_privs where GRANTEE='ERMUSR';
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
UNLIMITED TABLESPACE
9 rows selected.
grant sysdba to ermusr;
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='QUERY_ALL_ROLE';
select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
SELECT * FROM sys.dba_role_privs where grantee='MASTERSAF'
select * from dba_role_privs where granted_role='APPDBA';
SQL> desc dba_role_privs
Name Null? Type
------------ -------- ------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
To find a list of all users with DBA privilege execute the following code:
SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS DBA YES YES
SYSTEM DBA YES YES
WHAT ARE THE PRIVILEGES ASSAIGN TO ROLE
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='APPDBA';
=================================================================================
SELECT * FROM sys.dba_sys_privs where grantee='DW_DML_ROLE';
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='BI_SEC';
revoke role from user
=========================
REVOKE <role_name> FROM <user_name>;
Roles
SELECT granted_role, admin_option FROM role_role_privs WHERE role = 'HS_ADMIN_SELECT_ROLE';
SQL>FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || your.table_names || ' TO <<user>>' ;
END LOOP ;
Roles
++++
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE like '%DBA%' and ADMIN_OPTION='YES';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED
_ROLE like '%DBA%';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTED_ROLE='DBA';
select GRANTED_ROLE,ADMIN_OPTION,GRANTEE from dba_role_privs where GRANTEE
like '%ORACLE%';
select GRANTEE,PRIVILEGE,ADMIN_OPTION from dba_sys_privs where grantee like
'%ORACLE%';
to check what are the privilege assign to role
-------------------------------------------------------------
SELECT 'GRANT ' ||PRIVILEGE||' TO'||' '||grantee|| ';' from dba_sys_privs where grantee='DW_DML_ROLE';
Karthik, Team,
The below incident was not worked upon correctly. The request was to:
Create a new role APPSDBARESTRICT in UNNPSHRD. It should have the following privileges:
CONNET
CREATE SESSION
SELECT ANY DICTIONARY
ADVISOR
EXECUTE privilege on DBMS_WORKLOAD_REPOSITORY
Create a new user SARATHJ1 in database UNNPSHRD. Assign APPSDBARESTRICT role to SARATHJ1.
Of this, the role APPSDBARESTRICT was created , but the role has not been assigned to SARATHJ1 but I see that it has been assigned to SYSTEM!!!. See below queries. Going forward, please do not close the Incident unless the requester agrees.
SQL> select name, sysdate from v$database;
NAME SYSDATE
--------- ---------
UNNPSHRD 28-NOV-12
SQL> set lines 300
SQL> set pages 1000
SQL> select * from dba_sys_privs where grantee='SARATHJ1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SARATHJ1 UNLIMITED TABLESPACE NO
SQL> select * from dba_ROLE_privs where grantee='SARATHJ1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SARATHJ1 CONNECT NO YES
SARATHJ1 RESOURCE NO YES
SQL> select * from dba_roles where role='APPSDBARESTRICT';
ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
APPSDBARESTRICT NO NONE
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='APPSDBARESTRICT';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM APPSDBARESTRICT YES YES
SQL> select * from dba_sys_privs where grantee='APPSDBARESTRICT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
APPSDBARESTRICT ADVISOR NO
APPSDBARESTRICT CREATE SESSION NO
APPSDBARESTRICT SELECT ANY DICTIONARY NO
