DEMO: HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE

Here is a query to a data dictionary table for finding out all the object privileges that a particular user or a role has. It also displays if a privilege is transferred directly or through a role.

DEMO

-- 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.

NOTE

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;

REFERENCES

Hierarchical Queries: Databases for Developers