5 Replies Latest reply: May 26, 2009 4:39 AM by P.Forstmann RSS

    How to check current user privileges

    703456
      hi team request you to tell me how to check current users privileges .
        • 1. Re: How to check current user privileges
          iloveoracle
          use the following objects to query for the privileges...

          ALL_COL_PRIVS
          ALL_COL_PRIVS_MADE
          ALL_COL_PRIVS_RECD
          ALL_REPGROUP_PRIVILEGES
          ALL_TAB_PRIVS
          ALL_TAB_PRIVS_MADE
          ALL_TAB_PRIVS_RECD


          USER_AQ_AGENT_PRIVS
          USER_COL_PRIVS
          USER_COL_PRIVS_MADE
          USER_COL_PRIVS_RECD
          USER_REPGROUP_PRIVILEGES

          TNAME
          ------------------------------
          USER_ROLE_PRIVS
          USER_SYS_PRIVS
          USER_TAB_PRIVS
          USER_TAB_PRIVS_MADE
          USER_TAB_PRIVS_RECD
          • 2. Re: How to check current user privileges
            user3266490
            hi,

            select * from session_privs;

            Edited by: user3266490 on May 26, 2009 12:31 AM
            • 3. Re: How to check current user privileges
              Girish Sharma
              Welcome to Oracle world and Forum. This is your first post; so i wish to suggest you to next time start your question like:
              Oracle Version:
              OS Information:
              everytime to get the fast and right answers.
              select table_name from dict where table_name like '%PRIVS%';
              We can get any view by replacing "PRIVS" as per our need and then query those views by joining etc.

              Regards
              Girish Sharma
              • 4. Re: How to check current user privileges
                602725
                The most commonly used views for checking privileges are
                dba_role_privs
                dba_sys_privs
                dba_tab_privs
                • 5. Re: How to check current user privileges
                  P.Forstmann
                  In Oracle 10.2.0.1, DBMS_METADATA EXECUTE privilege is granted to PUBLIC.

                  So you can use following script to get privileges of current user:
                  set heading off
                  set pages 0
                  set long 9999999
                  select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)  
                  from dual;
                  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)  
                  from dual;
                  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)  
                  from dual;
                  SQL statement fail if there is no privilege for the requested category.
                  This script does not detail privileges granted through a role and it does not report privileges granted to PUBLIC.

                  Edited by: P. Forstmann on May 26, 2009 11:39 AM