This discussion is archived
9 Replies Latest reply: Jun 13, 2013 7:35 PM by DeepakBharambe RSS

How to know which privileges/roles are used by a session

DeepakBharambe Newbie
Currently Being Moderated
Hi,


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.
  • 1. Re: How to know which privileges/roles are used by a session
    sb92075 Guru
    Currently Being Moderated
    Deepak Bharambe wrote:
    Hi,


    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.
    no
  • 2. Re: How to know which privileges/roles are used by a session
    SalmanQureshi Expert
    Currently Being Moderated
    As told before, it is not possible, but, you can trace the session of your batch job and then analyze the statements in the trace file to know what tables it accessed and hence can know the privileges used to execute the statements. Certainly "create session" is always required.

    Salman
  • 4. Re: How to know which privileges/roles are used by a session
    sulimo Explorer
    Currently Being Moderated
    Audit, connect, run, query dba_audit_trail...
    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.
    run job (do select, insert, etc...)
    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
    HTH
  • 5. Re: How to know which privileges/roles are used by a session
    Aman.... Oracle ACE
    Currently Being Moderated
    I can't recall any view about the roles but for the privs, there is session_privs using which you can see the privs that are enabled for the session. Just a quick thought, for the roles, can you try to see the view session_roles ?

    HTH
    Aman....

    Edited by: Aman.... on Mar 9, 2013 10:57 AM
  • 6. Re: How to know which privileges/roles are used by a session
    DeepakBharambe Newbie
    Currently Being Moderated

    Seems like somebody somewhere in Oracle noticed this   and 12c has this feature as per below note.

     

    http://www.oracle-base.com/blog/2012/10/06/oracle-openworld-2012-day-5/

    Privilege Analysis:

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

  • 7. Re: How to know which privileges/roles are used by a session
    karan Pro
    Currently Being Moderated

    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.

  • 8. Re: How to know which privileges/roles are used by a session
    DeepakBharambe Newbie
    Currently Being Moderated

    Karan

    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.

  • 9. Re: How to know which privileges/roles are used by a session
    rp0428 Guru
    Currently Being Moderated

    DeepakBharambe wrote:

     

    Karan

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points