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.
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.
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.
Spooling from session_privs and session_roles can be done and Just in case if you want a user to use a DBA privileges for a while you may make him connect as a DBA [PROXY] for a while once done alter user tempuser revoke connect through dba_user; and then carry on with old privileges.
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.