SQL script that retrieves all privileges of a specific user.

Below SQL script that retrieves all privileges of a specific user in an Oracle database, including:

• System privileges

• Object privileges

• Roles granted

• Privileges granted through roles (system & object)

-- Set the user you want to investigate
DEFINE target_user = 'PETER';
-- 1. System privileges directly granted to the user
SELECT 
    'SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_SYS_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 2. Object privileges directly granted to the user
SELECT 
    'OBJECT_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    OWNER,
    TABLE_NAME AS OBJECT_NAME,
    GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_TAB_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 3. Roles granted to the user
SELECT 
    'ROLE_GRANTED' AS PRIV_TYPE,
    GRANTEE,
    GRANTED_ROLE AS PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 4. System privileges granted via roles
SELECT 
    'ROLE_SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RSP.PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    RSP.ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_SYS_PRIVS RSP ON RP.GRANTED_ROLE = RSP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
UNION ALL
-- 5. Object privileges granted via roles
SELECT 
    'ROLE_OBJECT_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RTP.PRIVILEGE,
    RTP.OWNER,
    RTP.TABLE_NAME AS OBJECT_NAME,
    RTP.GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_TAB_PRIVS RTP ON RP.GRANTED_ROLE = RTP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
ORDER BY 
    PRIV_TYPE, PRIVILEGE;

How to find the priveleges under the role.

Below statement find the roles created by DBA.

select * from dba_roles where ORACLE_MAINTAINED !='Y';

ROLE                 PASSWORD AUTHENTICAT COM O

-------------------- -------- ----------- --- -

READ_ONLY            NO       NONE        NO  N

Below SQL query provide the details of the privelege under role "READ_ONLY"

SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'READ_ONLY';

ROLE                 OWNER        TABLE_NAME                     COLUMN_NAME          PRIVILEGE     GRA COM

-------------------- ------------ ------------------------------ -------------------- ------------- --- ---

READ_ONLY            METALS       MATERIAL_RECORDS                                    SELECT        NO  NO

READ_ONLY            METALS       MANUFACTURER_RECORDS                                SELECT        NO  NO

User Management : How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

 How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

-- 1. Create the user

CREATE USER ashwani IDENTIFIED BY ashwani
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;

-- 2. Allow the user to connect

GRANT CREATE SESSION TO ashwani;
-- 3. Grant privileges on all existing objects in METALS schema
BEGIN
    FOR r IN (SELECT owner, object_name, object_type
              FROM all_objects
              WHERE owner = 'METALS'
                AND object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE')) LOOP
        EXECUTE IMMEDIATE 'GRANT ALL ON ' || r.owner || '.' || r.object_name || ' TO ashwani';
    END LOOP;
END;
/
-- 4. Allow access to future objects in METALS schema

GRANT SELECT ANY TABLE TO ashwani;   -- optional for future read access

-- Note: To auto-grant all privileges for new objects, use schema triggers.

-- 5. (Optional) If you want ashwani to create objects in METALS schema

-- GRANT CREATE ANY TABLE TO ashwani;
-- GRANT CREATE ANY VIEW TO ashwani;