I just reproduce two (2) tutorials.
This procedure replicate the tutorials from the "Database Vault Administrator's Guide".
Followed the procedure in "Tutorial: Analyzing ANY Privilege Use" with some modifications.
Step 1: Create User Accounts
Followed the advice in "Step 1: Create User Accounts" for the PLUM PDB:
SQL> alter session set container=plum; Session altered. SQL> create user pa_admin identified by password1; User created. SQL> create user app_user identified by password3; User created.
Grant the following privileges:
SQL> grant create session, capture_admin to pa_admin; Grant succeeded. SQL> grant create session, read any table to app_user; Grant succeeded.
Step 2: Create and Enable a Privilege Analysis Policy
Followed the advice in "Step 2: Create and Enable a Privilege Analysis Policy" for the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'ANY_priv_analysis_pol', description => 'Analyzes system privilege use', type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT, condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''APP_USER'''); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('ANY_priv_analysis_pol'); PL/SQL procedure successfully completed.
Step 3: Use the READ ANY TABLE System Privilege
Followed the advice in "Step 3: Use the READ ANY TABLE System Privilege" for PLUM PDB:
SQL> connect app_user/password3@localhost/plum.yaocm.id.au Connected. SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY > 12000 ORDER BY SALARY DESC; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 John Russell 14000 Karen Partners 13500 Michael Hartstein 13000 Shelley Higgins 12008 Nancy Greenberg 12008 8 rows selected.
Step 4: Disable the Privilege Analysis Policy
Followed the advice in "Step 4: Disable the Privilege Analysis Policy" for the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('ANY_priv_analysis_pol'); PL/SQL procedure successfully completed.
Step 5: Generate and View a Privilege Analysis Report
Followed the advice in "Step 5: Generate and View a Privilege Analysis Report" for the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('ANY_priv_analysis_pol'); PL/SQL procedure successfully completed. SQL> col username format a10 col sys_priv format a16 col object_owner format a13 col object_name format a23 Step 1: Create User Accounts Step 2: Create and Enable a Privilege Analysis Policy Step 3: Perform the Database Tuning Operations Step 4: Disable the Privilege Analysis Policy Step 5: Generate and View Privilege Analysis Reports Step 6: Remove the Components for This Tutorial SQL> SQL> SQL> SQL> SQL> SELECT USERNAME, SYS_PRIV, OBJECT_OWNER, OBJECT_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'APP_USER'; USERNAME SYS_PRIV OBJECT_OWNER OBJECT_NAME ---------- ---------------- ------------- ----------------------- APP_USER SYS DUAL APP_USER CREATE SESSION APP_USER SYS DBMS_APPLICATION_INFO APP_USER SYS DUAL APP_USER SYSTEM PRODUCT_PRIVS APP_USER READ ANY TABLE HR EMPLOYEES 6 rows selected.
Step 6: Remove the Components for This Tutorial
Followed the advice in "Step 6: Remove the Components for This Tutorial" for the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('ANY_priv_analysis_pol'); PL/SQL procedure successfully completed. SQL> connect / as sysdba Connected. SQL> alter session set container=plum; Session altered. SQL> drop user pa_admin; User dropped. SQL> drop user app_user; User dropped.
The procedure is in "Tutorial: Analyzing Privilege Use by a User Who Has the DBA Role".
Step 1: Create User Accounts
Followed the advice in "Step 1: Create User Accounts" in the PLUM PDB:
SQL> connect / as sysdba Connected. SQL> alter session set container=plum; Session altered. SQL> create user pa_admin identified by password1; User created. SQL> create user tjones identified by password2; User created. SQL> GRANT CREATE SESSION, CAPTURE_ADMIN TO pa_admin; Grant succeeded. SQL> GRANT CREATE SESSION, DBA TO tjones; Grant succeeded.
Step 2: Create and Enable a Privilege Analysis Policy
Followed the advice in "Step 2: Create and Enable a Privilege Analysis Policy" in the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'dba_tuning_priv_analysis_pol', description => 'Analyzes DBA tuning privilege use', type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT, condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''TJONES'''); END; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('dba_tuning_priv_analysis_pol'); PL/SQL procedure successfully completed.
Step 3: Perform the Database Tuning Operations
Followed the advice in "Step 3: Perform the Database Tuning Operations" in the PLUM PDB:
SQL> connect tjones/password2@localhost/plum.yaocm.id.au Connected. SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Table created. SQL> EXPLAIN PLAN SET STATEMENT_ID = 'Raise in Tokyo' INTO PLAN_TABLE FOR UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1.10 WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS WHERE LOCATION_ID = 110); 2 3 4 5 6 7 Explained. SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sql Table created. SQL> ANALYZE TABLE HR.EMPLOYEES LIST CHAINED ROWS INTO CHAINED_ROWS; Table analyzed.
Step 4: Disable the Privilege Analysis Policy
Followed the advice in "Step 4: Disable the Privilege Analysis Policy" in the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('dba_tuning_priv_analysis_pol'); PL/SQL procedure successfully completed.
Step 5: Generate and View Privilege Analysis Reports
Followed the advice in "Step 5: Generate and View Privilege Analysis Reports" in the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('dba_tuning_priv_analysis_pol'); PL/SQL procedure successfully completed. SQL> col username format a8 col sys_priv format a18 col used_role format a20 col path format a150 col obj_priv format a10 col object_owner format a10 col object_name format a10 col object_type format a10SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SELECT USERNAME, SYS_PRIV, USED_ROLE, PATH FROM DBA_USED_SYSPRIVS_PATH WHERE USERNAME = 'TJONES' ORDER BY 1, 2, 3; 2 3 4 USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA') TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'OLAP_DBA') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES UNLIMITED TABLESPA TJONES CE GRANT_PATH('TJONES') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'OLAP_DBA') 31 rows selected. SQL> col username format a9 col used_role format a10 col object_name format a22 col object_type format a12 SELECT USERNAME, OBJ_PRIV, USED_ROLE, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_USED_OBJPRIVS WHERE USERNAME = 'TJONES' ORDER BY 1, 2, 3, 4, 5, 6;SQL> SQL> SQL> SQL> SQL> 2 3 4 5 USERNAME OBJ_PRIV USED_ROLE OBJECT_OWN OBJECT_NAME OBJECT_TYPE --------- ---------- ---------- ---------- ---------------------- ------------ TJONES EXECUTE PUBLIC SYS DBMS_APPLICATION_INFO PACKAGE TJONES SELECT PUBLIC SYS DUAL TABLE TJONES SELECT PUBLIC SYS DUAL TABLE TJONES SELECT PUBLIC SYSTEM PRODUCT_PRIVS VIEW SQL> col username format a9 col sys_priv format a35 SELECT USERNAME, SYS_PRIV FROM DBA_UNUSED_SYSPRIVS WHERE USERNAME = 'TJONES' ORDER BY 1, 2;SQL> SQL> SQL> 2 3 4 USERNAME SYS_PRIV --------- ----------------------------------- TJONES ADMINISTER ANY SQL TUNING SET TJONES ADMINISTER DATABASE TRIGGER TJONES ADMINISTER RESOURCE MANAGER TJONES ADMINISTER SQL MANAGEMENT OBJECT TJONES ADMINISTER SQL TUNING SET TJONES ADVISOR TJONES ALTER ANY ASSEMBLY TJONES ALTER ANY CLUSTER TJONES ALTER ANY CUBE TJONES ALTER ANY CUBE BUILD PROCESS TJONES ALTER ANY CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER ANY DIMENSION TJONES ALTER ANY EDITION TJONES ALTER ANY EVALUATION CONTEXT TJONES ALTER ANY INDEX TJONES ALTER ANY INDEXTYPE TJONES ALTER ANY LIBRARY TJONES ALTER ANY MATERIALIZED VIEW TJONES ALTER ANY MEASURE FOLDER TJONES ALTER ANY MINING MODEL TJONES ALTER ANY OPERATOR TJONES ALTER ANY OUTLINE USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER ANY PROCEDURE TJONES ALTER ANY ROLE TJONES ALTER ANY RULE TJONES ALTER ANY RULE SET TJONES ALTER ANY SEQUENCE TJONES ALTER ANY SQL PROFILE TJONES ALTER ANY SQL TRANSLATION PROFILE TJONES ALTER ANY TABLE TJONES ALTER ANY TRIGGER TJONES ALTER ANY TYPE TJONES ALTER DATABASE USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER PROFILE TJONES ALTER RESOURCE COST TJONES ALTER ROLLBACK SEGMENT TJONES ALTER SESSION TJONES ALTER SYSTEM TJONES ALTER TABLESPACE TJONES ALTER USER TJONES ANALYZE ANY DICTIONARY TJONES AUDIT ANY TJONES AUDIT SYSTEM TJONES BACKUP ANY TABLE USERNAME SYS_PRIV --------- ----------------------------------- TJONES BECOME USER TJONES CHANGE NOTIFICATION TJONES COMMENT ANY MINING MODEL TJONES COMMENT ANY TABLE TJONES CREATE ANY ASSEMBLY TJONES CREATE ANY CLUSTER TJONES CREATE ANY CONTEXT TJONES CREATE ANY CREDENTIAL TJONES CREATE ANY CREDENTIAL TJONES CREATE ANY CUBE TJONES CREATE ANY CUBE BUILD PROCESS USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY CUBE DIMENSION TJONES CREATE ANY DIMENSION TJONES CREATE ANY DIRECTORY TJONES CREATE ANY EDITION TJONES CREATE ANY EVALUATION CONTEXT TJONES CREATE ANY INDEX TJONES CREATE ANY INDEXTYPE TJONES CREATE ANY JOB TJONES CREATE ANY JOB TJONES CREATE ANY LIBRARY TJONES CREATE ANY MATERIALIZED VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY MEASURE FOLDER TJONES CREATE ANY MINING MODEL TJONES CREATE ANY OPERATOR TJONES CREATE ANY OUTLINE TJONES CREATE ANY PROCEDURE TJONES CREATE ANY RULE TJONES CREATE ANY RULE SET TJONES CREATE ANY SEQUENCE TJONES CREATE ANY SQL PROFILE TJONES CREATE ANY SQL TRANSLATION PROFILE TJONES CREATE ANY SYNONYM USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY TABLE TJONES CREATE ANY TRIGGER TJONES CREATE ANY TYPE TJONES CREATE ANY VIEW TJONES CREATE ASSEMBLY TJONES CREATE CLUSTER TJONES CREATE CREDENTIAL TJONES CREATE CREDENTIAL TJONES CREATE CUBE TJONES CREATE CUBE BUILD PROCESS TJONES CREATE CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE DATABASE LINK TJONES CREATE DIMENSION TJONES CREATE EVALUATION CONTEXT TJONES CREATE EXTERNAL JOB TJONES CREATE EXTERNAL JOB TJONES CREATE INDEXTYPE TJONES CREATE JOB TJONES CREATE JOB TJONES CREATE LIBRARY TJONES CREATE MATERIALIZED VIEW TJONES CREATE MEASURE FOLDER USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE MINING MODEL TJONES CREATE OPERATOR TJONES CREATE PLUGGABLE DATABASE TJONES CREATE PROCEDURE TJONES CREATE PROFILE TJONES CREATE PUBLIC DATABASE LINK TJONES CREATE PUBLIC SYNONYM TJONES CREATE ROLE TJONES CREATE ROLLBACK SEGMENT TJONES CREATE RULE TJONES CREATE RULE SET USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE SEQUENCE TJONES CREATE SQL TRANSLATION PROFILE TJONES CREATE SYNONYM TJONES CREATE TABLESPACE TJONES CREATE TRIGGER TJONES CREATE TYPE TJONES CREATE USER TJONES CREATE VIEW TJONES DEBUG ANY PROCEDURE TJONES DEBUG CONNECT SESSION TJONES DELETE ANY CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES DELETE ANY MEASURE FOLDER TJONES DELETE ANY TABLE TJONES DEQUEUE ANY QUEUE TJONES DROP ANY ASSEMBLY TJONES DROP ANY CLUSTER TJONES DROP ANY CONTEXT TJONES DROP ANY CUBE TJONES DROP ANY CUBE BUILD PROCESS
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('dba_tuning_priv_analysis_pol'); PL/SQL procedure successfully completed. SQL> col username format a8 col sys_priv format a18 col used_role format a20 col path format a150 col obj_priv format a10 col object_owner format a10 col object_name format a10 col object_type format a10SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SELECT USERNAME, SYS_PRIV, USED_ROLE, PATH FROM DBA_USED_SYSPRIVS_PATH WHERE USERNAME = 'TJONES' ORDER BY 1, 2, 3; 2 3 4 USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA') TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES ANALYZE ANY IMP_FULL_DATABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES') TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA') TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES CREATE TABLE DATAPUMP_EXP_FULL_DA TABASE GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'OLAP_DBA') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'EXP_FULL_DATABASE') TJONES SELECT ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA') TJONES SELECT ANY TABLE OLAP_DBA USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE') TJONES UNLIMITED TABLESPA TJONES CE GRANT_PATH('TJONES') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE') USERNAME SYS_PRIV USED_ROLE -------- ------------------ -------------------- PATH -------------------------------------------------------------------------------- TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA') TJONES UPDATE ANY TABLE OLAP_DBA GRANT_PATH('TJONES', 'DBA', 'OLAP_DBA') 31 rows selected. SQL> col username format a9 col used_role format a10 col object_name format a22 col object_type format a12 SELECT USERNAME, OBJ_PRIV, USED_ROLE, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_USED_OBJPRIVS WHERE USERNAME = 'TJONES' ORDER BY 1, 2, 3, 4, 5, 6;SQL> SQL> SQL> SQL> SQL> 2 3 4 5 USERNAME OBJ_PRIV USED_ROLE OBJECT_OWN OBJECT_NAME OBJECT_TYPE --------- ---------- ---------- ---------- ---------------------- ------------ TJONES EXECUTE PUBLIC SYS DBMS_APPLICATION_INFO PACKAGE TJONES SELECT PUBLIC SYS DUAL TABLE TJONES SELECT PUBLIC SYS DUAL TABLE TJONES SELECT PUBLIC SYSTEM PRODUCT_PRIVS VIEW SQL> col username format a9 col sys_priv format a35 SELECT USERNAME, SYS_PRIV FROM DBA_UNUSED_SYSPRIVS WHERE USERNAME = 'TJONES' ORDER BY 1, 2;SQL> SQL> SQL> 2 3 4 USERNAME SYS_PRIV --------- ----------------------------------- TJONES ADMINISTER ANY SQL TUNING SET TJONES ADMINISTER DATABASE TRIGGER TJONES ADMINISTER RESOURCE MANAGER TJONES ADMINISTER SQL MANAGEMENT OBJECT TJONES ADMINISTER SQL TUNING SET TJONES ADVISOR TJONES ALTER ANY ASSEMBLY TJONES ALTER ANY CLUSTER TJONES ALTER ANY CUBE TJONES ALTER ANY CUBE BUILD PROCESS TJONES ALTER ANY CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER ANY DIMENSION TJONES ALTER ANY EDITION TJONES ALTER ANY EVALUATION CONTEXT TJONES ALTER ANY INDEX TJONES ALTER ANY INDEXTYPE TJONES ALTER ANY LIBRARY TJONES ALTER ANY MATERIALIZED VIEW TJONES ALTER ANY MEASURE FOLDER TJONES ALTER ANY MINING MODEL TJONES ALTER ANY OPERATOR TJONES ALTER ANY OUTLINE USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER ANY PROCEDURE TJONES ALTER ANY ROLE TJONES ALTER ANY RULE TJONES ALTER ANY RULE SET TJONES ALTER ANY SEQUENCE TJONES ALTER ANY SQL PROFILE TJONES ALTER ANY SQL TRANSLATION PROFILE TJONES ALTER ANY TABLE TJONES ALTER ANY TRIGGER TJONES ALTER ANY TYPE TJONES ALTER DATABASE USERNAME SYS_PRIV --------- ----------------------------------- TJONES ALTER PROFILE TJONES ALTER RESOURCE COST TJONES ALTER ROLLBACK SEGMENT TJONES ALTER SESSION TJONES ALTER SYSTEM TJONES ALTER TABLESPACE TJONES ALTER USER TJONES ANALYZE ANY DICTIONARY TJONES AUDIT ANY TJONES AUDIT SYSTEM TJONES BACKUP ANY TABLE USERNAME SYS_PRIV --------- ----------------------------------- TJONES BECOME USER TJONES CHANGE NOTIFICATION TJONES COMMENT ANY MINING MODEL TJONES COMMENT ANY TABLE TJONES CREATE ANY ASSEMBLY TJONES CREATE ANY CLUSTER TJONES CREATE ANY CONTEXT TJONES CREATE ANY CREDENTIAL TJONES CREATE ANY CREDENTIAL TJONES CREATE ANY CUBE TJONES CREATE ANY CUBE BUILD PROCESS USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY CUBE DIMENSION TJONES CREATE ANY DIMENSION TJONES CREATE ANY DIRECTORY TJONES CREATE ANY EDITION TJONES CREATE ANY EVALUATION CONTEXT TJONES CREATE ANY INDEX TJONES CREATE ANY INDEXTYPE TJONES CREATE ANY JOB TJONES CREATE ANY JOB TJONES CREATE ANY LIBRARY TJONES CREATE ANY MATERIALIZED VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY MEASURE FOLDER TJONES CREATE ANY MINING MODEL TJONES CREATE ANY OPERATOR TJONES CREATE ANY OUTLINE TJONES CREATE ANY PROCEDURE TJONES CREATE ANY RULE TJONES CREATE ANY RULE SET TJONES CREATE ANY SEQUENCE TJONES CREATE ANY SQL PROFILE TJONES CREATE ANY SQL TRANSLATION PROFILE TJONES CREATE ANY SYNONYM USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE ANY TABLE TJONES CREATE ANY TRIGGER TJONES CREATE ANY TYPE TJONES CREATE ANY VIEW TJONES CREATE ASSEMBLY TJONES CREATE CLUSTER TJONES CREATE CREDENTIAL TJONES CREATE CREDENTIAL TJONES CREATE CUBE TJONES CREATE CUBE BUILD PROCESS TJONES CREATE CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE DATABASE LINK TJONES CREATE DIMENSION TJONES CREATE EVALUATION CONTEXT TJONES CREATE EXTERNAL JOB TJONES CREATE EXTERNAL JOB TJONES CREATE INDEXTYPE TJONES CREATE JOB TJONES CREATE JOB TJONES CREATE LIBRARY TJONES CREATE MATERIALIZED VIEW TJONES CREATE MEASURE FOLDER USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE MINING MODEL TJONES CREATE OPERATOR TJONES CREATE PLUGGABLE DATABASE TJONES CREATE PROCEDURE TJONES CREATE PROFILE TJONES CREATE PUBLIC DATABASE LINK TJONES CREATE PUBLIC SYNONYM TJONES CREATE ROLE TJONES CREATE ROLLBACK SEGMENT TJONES CREATE RULE TJONES CREATE RULE SET USERNAME SYS_PRIV --------- ----------------------------------- TJONES CREATE SEQUENCE TJONES CREATE SQL TRANSLATION PROFILE TJONES CREATE SYNONYM TJONES CREATE TABLESPACE TJONES CREATE TRIGGER TJONES CREATE TYPE TJONES CREATE USER TJONES CREATE VIEW TJONES DEBUG ANY PROCEDURE TJONES DEBUG CONNECT SESSION TJONES DELETE ANY CUBE DIMENSION USERNAME SYS_PRIV --------- ----------------------------------- TJONES DELETE ANY MEASURE FOLDER TJONES DELETE ANY TABLE TJONES DEQUEUE ANY QUEUE TJONES DROP ANY ASSEMBLY TJONES DROP ANY CLUSTER TJONES DROP ANY CONTEXT TJONES DROP ANY CUBE TJONES DROP ANY CUBE BUILD PROCESS TJONES DROP ANY CUBE DIMENSION TJONES DROP ANY DIMENSION TJONES DROP ANY DIRECTORY USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP ANY EDITION TJONES DROP ANY EVALUATION CONTEXT TJONES DROP ANY INDEX TJONES DROP ANY INDEXTYPE TJONES DROP ANY LIBRARY TJONES DROP ANY MATERIALIZED VIEW TJONES DROP ANY MEASURE FOLDER TJONES DROP ANY MINING MODEL TJONES DROP ANY OPERATOR TJONES DROP ANY OUTLINE TJONES DROP ANY PROCEDURE USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP ANY ROLE TJONES DROP ANY RULE TJONES DROP ANY RULE SET TJONES DROP ANY SEQUENCE TJONES DROP ANY SQL PROFILE TJONES DROP ANY SQL TRANSLATION PROFILE TJONES DROP ANY SYNONYM TJONES DROP ANY TABLE TJONES DROP ANY TRIGGER TJONES DROP ANY TYPE TJONES DROP ANY VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP PROFILE TJONES DROP PUBLIC DATABASE LINK TJONES DROP PUBLIC SYNONYM TJONES DROP ROLLBACK SEGMENT TJONES DROP TABLESPACE TJONES DROP USER TJONES EM EXPRESS CONNECT TJONES ENQUEUE ANY QUEUE TJONES EXECUTE ANY ASSEMBLY TJONES EXECUTE ANY CLASS TJONES EXECUTE ANY CLASS USERNAME SYS_PRIV --------- ----------------------------------- TJONES EXECUTE ANY EVALUATION CONTEXT TJONES EXECUTE ANY INDEXTYPE TJONES EXECUTE ANY LIBRARY TJONES EXECUTE ANY OPERATOR TJONES EXECUTE ANY PROCEDURE TJONES EXECUTE ANY PROGRAM TJONES EXECUTE ANY PROGRAM TJONES EXECUTE ANY RULE TJONES EXECUTE ANY RULE SET TJONES EXECUTE ANY TYPE TJONES EXECUTE ASSEMBLY USERNAME SYS_PRIV --------- ----------------------------------- TJONES EXEMPT DDL REDACTION POLICY TJONES EXEMPT DML REDACTION POLICY TJONES EXEMPT REDACTION POLICY TJONES EXPORT FULL DATABASE TJONES FLASHBACK ANY TABLE TJONES FLASHBACK ARCHIVE ADMINISTER TJONES FORCE ANY TRANSACTION TJONES FORCE TRANSACTION TJONES GLOBAL QUERY REWRITE TJONES GRANT ANY OBJECT PRIVILEGE TJONES GRANT ANY PRIVILEGE USERNAME SYS_PRIV --------- ----------------------------------- TJONES GRANT ANY ROLE TJONES IMPORT FULL DATABASE TJONES INSERT ANY CUBE DIMENSION TJONES INSERT ANY MEASURE FOLDER TJONES INSERT ANY TABLE TJONES LOCK ANY TABLE TJONES LOGMINING TJONES MANAGE ANY FILE GROUP TJONES MANAGE ANY QUEUE TJONES MANAGE FILE GROUP TJONES MANAGE SCHEDULER USERNAME SYS_PRIV --------- ----------------------------------- TJONES MANAGE SCHEDULER TJONES MANAGE TABLESPACE TJONES MERGE ANY VIEW TJONES ON COMMIT REFRESH TJONES QUERY REWRITE TJONES READ ANY FILE GROUP TJONES READ ANY TABLE TJONES REDEFINE ANY TABLE TJONES RESTRICTED SESSION TJONES RESUMABLE TJONES SELECT ANY CUBE USERNAME SYS_PRIV --------- ----------------------------------- TJONES SELECT ANY CUBE BUILD PROCESS TJONES SELECT ANY CUBE DIMENSION TJONES SELECT ANY DICTIONARY TJONES SELECT ANY MEASURE FOLDER TJONES SELECT ANY MINING MODEL TJONES SELECT ANY SEQUENCE TJONES SELECT ANY TRANSACTION TJONES SET CONTAINER TJONES UNDER ANY TABLE TJONES UNDER ANY TYPE TJONES UNDER ANY VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES UPDATE ANY CUBE TJONES UPDATE ANY CUBE BUILD PROCESS TJONES UPDATE ANY CUBE DIMENSION TJONES USE ANY SQL TRANSLATION PROFILE 224 rows selected.
TJONES DROP ANY CUBE DIMENSION TJONES DROP ANY DIMENSION TJONES DROP ANY DIRECTORY USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP ANY EDITION TJONES DROP ANY EVALUATION CONTEXT TJONES DROP ANY INDEX TJONES DROP ANY INDEXTYPE TJONES DROP ANY LIBRARY TJONES DROP ANY MATERIALIZED VIEW TJONES DROP ANY MEASURE FOLDER TJONES DROP ANY MINING MODEL TJONES DROP ANY OPERATOR TJONES DROP ANY OUTLINE TJONES DROP ANY PROCEDURE USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP ANY ROLE TJONES DROP ANY RULE TJONES DROP ANY RULE SET TJONES DROP ANY SEQUENCE TJONES DROP ANY SQL PROFILE TJONES DROP ANY SQL TRANSLATION PROFILE TJONES DROP ANY SYNONYM TJONES DROP ANY TABLE TJONES DROP ANY TRIGGER TJONES DROP ANY TYPE TJONES DROP ANY VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES DROP PROFILE TJONES DROP PUBLIC DATABASE LINK TJONES DROP PUBLIC SYNONYM TJONES DROP ROLLBACK SEGMENT TJONES DROP TABLESPACE TJONES DROP USER TJONES EM EXPRESS CONNECT TJONES ENQUEUE ANY QUEUE TJONES EXECUTE ANY ASSEMBLY TJONES EXECUTE ANY CLASS TJONES EXECUTE ANY CLASS USERNAME SYS_PRIV --------- ----------------------------------- TJONES EXECUTE ANY EVALUATION CONTEXT TJONES EXECUTE ANY INDEXTYPE TJONES EXECUTE ANY LIBRARY TJONES EXECUTE ANY OPERATOR TJONES EXECUTE ANY PROCEDURE TJONES EXECUTE ANY PROGRAM TJONES EXECUTE ANY PROGRAM TJONES EXECUTE ANY RULE TJONES EXECUTE ANY RULE SET TJONES EXECUTE ANY TYPE TJONES EXECUTE ASSEMBLY USERNAME SYS_PRIV --------- ----------------------------------- TJONES EXEMPT DDL REDACTION POLICY TJONES EXEMPT DML REDACTION POLICY TJONES EXEMPT REDACTION POLICY TJONES EXPORT FULL DATABASE TJONES FLASHBACK ANY TABLE TJONES FLASHBACK ARCHIVE ADMINISTER TJONES FORCE ANY TRANSACTION TJONES FORCE TRANSACTION TJONES GLOBAL QUERY REWRITE TJONES GRANT ANY OBJECT PRIVILEGE TJONES GRANT ANY PRIVILEGE USERNAME SYS_PRIV --------- ----------------------------------- TJONES GRANT ANY ROLE TJONES IMPORT FULL DATABASE TJONES INSERT ANY CUBE DIMENSION TJONES INSERT ANY MEASURE FOLDER TJONES INSERT ANY TABLE TJONES LOCK ANY TABLE TJONES LOGMINING TJONES MANAGE ANY FILE GROUP TJONES MANAGE ANY QUEUE TJONES MANAGE FILE GROUP TJONES MANAGE SCHEDULER USERNAME SYS_PRIV --------- ----------------------------------- TJONES MANAGE SCHEDULER TJONES MANAGE TABLESPACE TJONES MERGE ANY VIEW TJONES ON COMMIT REFRESH TJONES QUERY REWRITE TJONES READ ANY FILE GROUP TJONES READ ANY TABLE TJONES REDEFINE ANY TABLE TJONES RESTRICTED SESSION TJONES RESUMABLE TJONES SELECT ANY CUBE USERNAME SYS_PRIV --------- ----------------------------------- TJONES SELECT ANY CUBE BUILD PROCESS TJONES SELECT ANY CUBE DIMENSION TJONES SELECT ANY DICTIONARY TJONES SELECT ANY MEASURE FOLDER TJONES SELECT ANY MINING MODEL TJONES SELECT ANY SEQUENCE TJONES SELECT ANY TRANSACTION TJONES SET CONTAINER TJONES UNDER ANY TABLE TJONES UNDER ANY TYPE TJONES UNDER ANY VIEW USERNAME SYS_PRIV --------- ----------------------------------- TJONES UPDATE ANY CUBE TJONES UPDATE ANY CUBE BUILD PROCESS TJONES UPDATE ANY CUBE DIMENSION TJONES USE ANY SQL TRANSLATION PROFILE 224 rows selected.
Step 6: Remove the Components for This Tutorial
Followed the advice in "Step 6: Remove the Components for This Tutorial" in the PLUM PDB:
SQL> connect pa_admin/password1@localhost/plum.yaocm.id.au Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('dba_tuning_priv_analysis_pol'); PL/SQL procedure successfully completed. SQL> connect / as sysdba Connected. SQL> alter session set container=plum; Session altered. SQL> drop user pa_admin; User dropped. SQL> drop user tjones cascade; User dropped.