Deepak Bharambe wrote:no
Is there any way we can know which privileges / roles are used by a session.
I want to know this so that only those privileges can be granted to separate user by which the said operation will ran later on.
Suppose, I want to create a new user by which the new batch job will be ran.
First time I want to run that batch job using sys/system or more privileged user and know the list of privileges which got used during execution of the batch job. Later on want to grant only the list those privileges/ role to the newly created user so that the user has all and only those privileges /roles required.
Audit, connect, run, query dba_audit_trail...
run job (do select, insert, etc...)
SQL> create user DROPME identified by dropme; User created. SQL> grant DBA to dropme; Grant succeeded. SQL> audit select table by DROPME by access; Audit succeeded. SQL> audit update table, delete table, insert table by DROPME by access; Audit succeeded. SQL> audit execute procedure by DROPME by access; Audit succeeded.
then query dba_audit_trail
SQL> select distinct owner, obj_name, action_name 2 from dba_audit_trail 3 where owner = 'DROPME' 4 order by 1,2,3; OWNER OBJ_NAME ACTION_NAME ------------------------------ ---------- --------------- DROPME TEST DELETE DROPME TEST INSERT DROPME TEST SELECT DROPME TEST UPDATE
Seems like somebody somewhere in Oracle noticed this and 12c has this feature as per below note.
- Track direct privileges and privileges via roles being used, so you can determine the least privileges needed.
- Monitoring controlled using DBMS_PRIVILEGE_CAPTURE.
- Report what is used and what is not used.
session_privs and session_roles gives us the entire list of privileges granted. My attempt is to find only the list of privileges actually used by session after it has done some work, so we come to which privileges are must.
In general that isn't possible. Different executions can take different code paths (IF, CASE, etc) and if dynamic sql is used the actual code made even be different from one execution to the next.
Then you need to factor in the effect that synonyms can have on which actual objects even get accessed, database links that might point to different servers, views that might use different base tables in different environments or might use functions, materialized views that may, or may not, get used due to query rewrite that may, or may not happen.