DEMO: HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE
-- CONNECT AS SYS USER
SQL> SHO USER
USER is "SYS"
-- CREATE AN OWNER OF DUMMY OBJECTS
CREATE USER USER_OWNER IDENTIFIED BY DUMMY;
GRANT CONNECT, RESOURCE TO USER_OWNER;
GRANT UNLIMITED TABLESPACE TO USER_OWNER;
GRANT CREATE MATERIALIZED VIEW TO USER_OWNER;
GRANT CREATE VIEW TO USER_OWNER;
-- CREATE AN GRANTEE USER
CREATE USER USER_GRANTEE IDENTIFIED BY DUMMY;
-- CREATE SOME DUMMY ROLES
CREATE ROLE ROLE_1;
CREATE ROLE ROLE_2;
CREATE ROLE ROLE_3;
CREATE ROLE ROLE_4;
Create some dummy objects
-- CONNECT AS AN OWNER OF DUMMY OBJECTS
SQL> CONNECT USER_OWNER/DUMMY
Connected.
SQL> SHO USER
USER is "USER_OWNER"
--------------------------------------------------
-- CREATE SOME DUMMY OBJECTS FOR USER "USER_OWNER"
--------------------------------------------------
-- A TABLE
CREATE TABLE USER_OWNER.TABLE_1(COL1 NUMBER);
-- A MATERIALIZED VIEW
CREATE MATERIALIZED VIEW USER_OWNER.MATVIEW_1
AS
SELECT * FROM USER_OWNER.TABLE_1;
-- A PROCEDURE
CREATE OR REPLACE PROCEDURE USER_OWNER.PROC_1
AS
BEGIN
NULL;
END;
/
-- VIEWS
CREATE OR REPLACE VIEW USER_OWNER.VIEW_1
AS
SELECT * FROM USER_OWNER.TABLE_1;
CREATE OR REPLACE VIEW USER_OWNER.VIEW_2
AS
SELECT * FROM USER_OWNER.TABLE_1;
Grant privileges on dummy objects to the user USER_GRANTEE and ROLES
-- CONNECT AS OWNER OF DUMMY OBJECTS OR AS SYS USER
SQL> SHO USER
USER is "USER_OWNER"
-- GRANT DIRECT PRIVILEGES ON THE "USER_OWNER" TABLE
GRANT SELECT, UPDATE, DELETE, INSERT ON USER_OWNER.TABLE_1 TO USER_GRANTEE;
-- GRANT PRIVILEGES ON "USER_OWNER" VIEWS TO DUMMY ROLES
GRANT SELECT, UPDATE, DELETE, INSERT ON USER_OWNER.VIEW_1 TO ROLE_1;
GRANT SELECT, UPDATE, DELETE, INSERT ON USER_OWNER.VIEW_2 TO ROLE_2;
-- GRANT PRIVILEGES ON "USER_OWNER" PROCEDURE AND MATERIALIZED VIEW TO DUMMY ROLE "ROLE_3"
GRANT SELECT ON USER_OWNER.MATVIEW_1 TO ROLE_3;
GRANT EXECUTE ON USER_OWNER.PROC_1 TO ROLE_3;
-- CONNECT AS SYS USER AND GRANT PRIVILEGES ON ALL OBJECTS BUT TABLE_1 TO USER "USER_GRANTEE" THROUGH ROLE "ROLE_4"
SQL> SHO USER
USER is "SYS"
GRANT ROLE_1 TO ROLE_4;
GRANT ROLE_2 TO ROLE_4;
GRANT ROLE_3 TO ROLE_4;
GRANT ROLE_4 TO USER_GRANTEE;
By previous steps, the user USER_GRANTEE got direct privileges on the table USER_OWNER.TABLE_1 and the role ROLE_4, and indirect privileges on ROLE_1, ROLE_2, and ROLE_3 through the role ROLE_4.
Now let's execute the DBT_OPFAU script and see what it returns. The script will prompt for a username. Just type the USER_GRANTEE.
@dbt_opfau.sql
Enter value for username: USER_GRANTEE
OWNER | OBJECT_NAME | OBJECT_TYPE | GRANTED_TO | GRANTED_DIRECTLY | GRANTED_THROUGH | PRIVILEGE | GRANTABLE
--------------- | --------------- | --------------- | --------------- | ---------------- | --------------- | ---------- | ---------
USER_OWNER | TABLE_1 | TABLE | USER_GRANTEE | YES | DIRECT GRANT | DELETE | NO
USER_OWNER | TABLE_1 | TABLE | USER_GRANTEE | YES | DIRECT GRANT | INSERT | NO
USER_OWNER | TABLE_1 | TABLE | USER_GRANTEE | YES | DIRECT GRANT | SELECT | NO
USER_OWNER | TABLE_1 | TABLE | USER_GRANTEE | YES | DIRECT GRANT | UPDATE | NO
USER_OWNER | VIEW_1 | VIEW | USER_GRANTEE | NO | ROLE_1 | DELETE | NO
USER_OWNER | VIEW_1 | VIEW | USER_GRANTEE | NO | ROLE_1 | UPDATE | NO
USER_OWNER | VIEW_1 | VIEW | USER_GRANTEE | NO | ROLE_1 | SELECT | NO
USER_OWNER | VIEW_1 | VIEW | USER_GRANTEE | NO | ROLE_1 | INSERT | NO
USER_OWNER | VIEW_2 | VIEW | USER_GRANTEE | NO | ROLE_2 | INSERT | NO
USER_OWNER | VIEW_2 | VIEW | USER_GRANTEE | NO | ROLE_2 | SELECT | NO
USER_OWNER | VIEW_2 | VIEW | USER_GRANTEE | NO | ROLE_2 | DELETE | NO
USER_OWNER | VIEW_2 | VIEW | USER_GRANTEE | NO | ROLE_2 | UPDATE | NO
USER_OWNER | PROC_1 | PROCEDURE | USER_GRANTEE | NO | ROLE_3 | EXECUTE | NO
USER_OWNER | MATVIEW_1 | TABLE | USER_GRANTEE | NO | ROLE_3 | SELECT | NO
14 rows selected.
In the above script output, we can see all objects’ privileges provided directly or indirectly to the user USER_GRANTEE.
Here the role ROLE_4 is not in the list as the role ROLE_4 does not have any object privileges. All it has is privileges on other roles.
SQL> UNDEFINE USERNAME
SQL> /
Enter value for username: ROLE_4
OWNER | OBJECT_NAME | OBJECT_TYPE | GRANTED_TO | GRANTED_DIRECTLY | GRANTED_THROUGH | PRIVILEGE | GRANTABLE
--------------- | --------------- | --------------- | --------------- | ---------------- | --------------- | ---------- | ---------
USER_OWNER | VIEW_1 | VIEW | ROLE_4 | NO | ROLE_1 | SELECT | NO
USER_OWNER | VIEW_1 | VIEW | ROLE_4 | NO | ROLE_1 | UPDATE | NO
USER_OWNER | VIEW_1 | VIEW | ROLE_4 | NO | ROLE_1 | INSERT | NO
USER_OWNER | VIEW_1 | VIEW | ROLE_4 | NO | ROLE_1 | DELETE | NO
USER_OWNER | VIEW_2 | VIEW | ROLE_4 | NO | ROLE_2 | UPDATE | NO
USER_OWNER | VIEW_2 | VIEW | ROLE_4 | NO | ROLE_2 | SELECT | NO
USER_OWNER | VIEW_2 | VIEW | ROLE_4 | NO | ROLE_2 | INSERT | NO
USER_OWNER | VIEW_2 | VIEW | ROLE_4 | NO | ROLE_2 | DELETE | NO
USER_OWNER | PROC_1 | PROCEDURE | ROLE_4 | NO | ROLE_3 | EXECUTE | NO
USER_OWNER | MATVIEW_1 | TABLE | ROLE_4 | NO | ROLE_3 | SELECT | NO
10 rows selected.
The demo is completed. Drop created dummy users, roles, and objects.
-- CONNECT AS SYS USER
SQL> SHO USER
USER is "SYS"
-- DROP DUMMY OBJECTS
DROP USER USER_OWNER CASCADE;
DROP USER USER_GRANTEE;
DROP ROLE ROLE_1;
DROP ROLE ROLE_2;
DROP ROLE ROLE_3;
DROP ROLE ROLE_4;
Hierarchical Queries: Databases for Developers
Tags In
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Sessions
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum