9 Replies Latest reply: Feb 5, 2014 4:09 PM by rp0428 RSS

    The view to see the an USER/SCHEMA default_role setting.

    1907307

      If you do an "alter user userA default role none;"  any role granted to userA will have a default default_role of "NO" from now on until you do a "alter user userA default role all;".  I want to globally see what my user setting is for each and every user on the DB but but do not know what view/table to turn to to see this.  I am not talking about the dba_role_privs view.  This view is only showing me for what has been granted and this does not show the setting for the user.  Please help.

        • 1. Re: The view to see the an USER/SCHEMA default_role setting.
          JustinCave

          You say that you are looking for "the setting for the user".  That's in DBA_ROLE_PRIVS.  But you say that you don't want that value.

           

          Perhaps you mean that you want the setting for the session?  One user could have many different sessions.  And in each session, the user could have a different set of roles enabled.  Within a session, you can query SESSION_ROLES to see which roles are enabled in the current session.  I'm not aware of a way to look across sessions for this information.

           

          Justin    

          • 2. Re: The view to see the an USER/SCHEMA default_role setting.
            jgarry

            OK, this just worked for me, don't know if it will work for you, or if it is what you want, or even if I should be saying anything about it.

             

            session 1:

            go into sqlplus, set role none

             

            On a second screen:

            ps -ef|grep sqlplus (your ps may vary)

            pick out the process id for the session, I'll call it nnnn here

            (alternatively, look up process id in oracle or dbconsole)

             

            session 2:

            go into sqlplus / as sysdba

            oradebug setospid nnnn

            oradebug dump errorstack 3

            exit sqlplus, go to $ORACLE_BASE/admin/$ORACLE_SID/udump

            find the latest file, edit it, search for the string: set role

             

            Beware, this may crash user session or...?

            • 3. Re: The view to see the an USER/SCHEMA default_role setting.
              JustinCave

              Well, that is certainly some out of the box thinking!! 

               

              Justin

              • 4. Re: The view to see the an USER/SCHEMA default_role setting.
                rp0428
                If you do an "alter user userA default role none;"  any role granted to userA will have a default default_role of "NO" from now on until you do a "alter user userA default role all;".  I want to globally see what my user setting is for each and every user on the DB but but do not know what view/table to turn to to see this.  I am not talking about the dba_role_privs view.  This view is only showing me for what has been granted and this does not show the setting for the user.

                Sure that view shows the settings

                select * from dba_role_privs where grantee = 'SCOTT';

                 

                GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                SCOTT,RESOURCE,NO,YES

                SCOTT,DBA,NO,YES

                SCOTT,UT_REPO_ADMINISTRATOR,YES,YES

                SCOTT,CONNECT,NO,YES

                SCOTT,PLUSTRACE,NO,YES

                 

                alter user scott  default role none;

                select * from dba_role_privs where grantee = 'SCOTT';

                 

                GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                SCOTT,RESOURCE,NO,NO

                SCOTT,DBA,NO,NO

                SCOTT,UT_REPO_ADMINISTRATOR,YES,NO

                SCOTT,CONNECT,NO,NO

                SCOTT,PLUSTRACE,NO,NO

                • 5. Re: The view to see the an USER/SCHEMA default_role setting.
                  jgarry

                  Yeah, too bad it probably isn't what the OP wanted to know.  Looking at the other session with oradebug likely would require intimate details of the UGA or something.

                  • 6. Re: The view to see the an USER/SCHEMA default_role setting.
                    1907307

                    To better help you all understand better the situation I have.  This is an example what has occurred in the DB.

                     

                    alter user scott  default role none;

                    select * from dba_role_privs where grantee = 'SCOTT';

                    GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                    SCOTT,RESOURCE,NO,NO

                    SCOTT,DBA,NO,NO

                    SCOTT,UT_REPO_ADMINISTRATOR,YES,NO

                    SCOTT,CONNECT,NO,NO

                    SCOTT,PLUSTRACE,NO,NO

                     

                    alter user scott  default role RESOURCE, DBA, UT_REPO_ADMINISTRATOR, CONNECT,PLUSTRACE;

                    select * from dba_role_privs where grantee = 'SCOTT';

                    GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                    SCOTT,RESOURCE,NO,YES

                    SCOTT,DBA,NO,YES

                    SCOTT,UT_REPO_ADMINISTRATOR,YES,YES

                    SCOTT,CONNECT,NO,YES

                    SCOTT,PLUSTRACE,NO,YES

                     

                    No if I do the following

                    grant new_role to Scott;

                    select * from dba_role_privs where grantee = 'SCOTT';

                    GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                    SCOTT,RESOURCE,NO,YES

                    SCOTT,DBA,NO,YES

                    SCOTT,UT_REPO_ADMINISTRATOR,YES,YES

                    SCOTT,CONNECT,NO,YES

                    SCOTT,PLUSTRACE,NO,YES

                    SCOTT,NEW_ROLE,NO,NO

                     

                    Notice the new_role has default_role of NO.  I have over 3K users in this DB and need to find every user with this scenario.  Need to find the view that contains the default for default_role so I can change those user to default of all and set all roles back that are not default of all.

                    • 7. Re: The view to see the an USER/SCHEMA default_role setting.
                      JohnWatson

                      1907307 wrote:

                       

                      If you do an "alter user userA default role none;"  any role granted to userA will have a default default_role of "NO" from now on until you do a "alter user userA default role all;".  I want to globally see what my user setting is for each and every user on the DB but but do not know what view/table to turn to to see this.  I am not talking about the dba_role_privs view.  This view is only showing me for what has been granted and this does not show the setting for the user.  Please help.

                      select name,case defrole when 1 then 'all' when 0 then 'none' end from sys.user$ where type#=1;

                       

                      Any good?

                      • 8. Re: The view to see the an USER/SCHEMA default_role setting.
                        1907307

                        Wonderful!  select name,case defrole when 1 then 'all' when 0 then 'none' end from sys.user$ where type#=1; gives me exactly what I need.

                        • 9. Re: The view to see the an USER/SCHEMA default_role setting.
                          rp0428
                          No if I do the following

                          grant new_role to Scott;

                          select * from dba_role_privs where grantee = 'SCOTT';

                          GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE

                          SCOTT,RESOURCE,NO,YES

                          SCOTT,DBA,NO,YES

                          SCOTT,UT_REPO_ADMINISTRATOR,YES,YES

                          SCOTT,CONNECT,NO,YES

                          SCOTT,PLUSTRACE,NO,YES

                          SCOTT,NEW_ROLE,NO,NO

                           

                          Notice the new_role has default_role of NO.  I have over 3K users in this DB and need to find every user with this scenario.  Need to find the view that contains the default for default_role so I can change those user to default of all and set all roles back that are not default of all.

                          So why didn't you just query that DBA_ROLE_PRIVS view for the result?

                          select distinct grantee from dba_role_privs

                          where default_role = 'NO'

                           

                          GRANTEE

                          HR

                          Isn't that what you were looking for to begin with? If so then why did you say this:

                          I am not talking about the dba_role_privs view

                          For most management tasks you generally don't need to query the SYS tables directly.

                           

                          More importantly it can be a big security risk/hole to just blindly enable all roles for all users. In over 25 years I have not worked or consulted for ANY organization that would allow that.

                           

                          There are generally roles that are intentionally not enabled by default for some users and/or batch processes. Those roles are specifically enabled by code ONLY when the privileges needed are required.