1 2 Previous Next 16 Replies Latest reply: Sep 26, 2013 8:46 PM by Hemant K Chitale RSS

    revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang

    user9074881

      I don't know why it occurred, but revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang.

       

      DECLARE

      BEGIN

       

       

           FOR l IN (select privilege from dba_sys_privs where privilege in ('SELECT ANY DICTIONARY') and grantee='PUBLIC')

           LOOP

                 EXECUTE IMMEDIATE 'REVOKE '||l.privilege||' FROM PUBLIC';

           END LOOP;

       

       

      END;

      /

       

      Any insight into the problem would be appreciated.

       

      Thank you

        • 1. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
          Eduardo Legatti

          Hi,

           

          Oracle version?  I did a test on Oracle 11g and I didn't have any problem.

           

          Cheers

           

          Legatti

          • 2. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
            user9074881

            Oracle version is 11.2.0.3.7 on RedHat 5 update 5.

            • 3. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
              EdStevens

              user9074881 wrote:

               

              I don't know why it occurred, but revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang.

               

              DECLARE

              BEGIN

               

               

                   FOR l IN (select privilege from dba_sys_privs where privilege in ('SELECT ANY DICTIONARY') and grantee='PUBLIC')

                   LOOP

                         EXECUTE IMMEDIATE 'REVOKE '||l.privilege||' FROM PUBLIC';

                   END LOOP;

               

               

              END;

              /

               

              Any insight into the problem would be appreciated.

               

              Thank you

               

              I don't know what happened to cause things to hang, but I am curious as to why you thought you needed to construct dynamic sql.  The result of your SELECT PRIVILEGE statement will only return the one result, which you already knew and were select for, so you could have just issued a simple, ad hoc sql statement:

               

              sql> revoke select any dictionary from public;

              • 4. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                user9074881

                I don't know, it was created by the developer that I simply copied and pasted. It doesn't make sense to me either, but it also doesn't hurt.

                • 5. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                  sb92075

                  user9074881 wrote:

                   

                  I don't know, it was created by the developer that I simply copied and pasted. It doesn't make sense to me either, but it also doesn't hurt.

                   

                  if you believe that hanging the DB as being classified as "doesn't hurt", then I am glad you can't touch any DB I care about.

                  • 6. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                    user9074881

                    Establishing connections hung on all 5 DBs when I ran the revoke. I'm not sure what the state of already established connections would be because during that time there were none. Our maintenance window was over and we decided to disable restricted session and continue running the job. It ran for a long time and unless I killed the job, it would not allow connections to be established. However, I didn't have any issues with bequeath connections. Also, for the 2 databases that I didn't cancel the job for, they eventually ran out of process memory ORA-04030.

                    • 7. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                      user9074881

                      Are you saying the loop caused the DB to hang?

                      • 8. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                        Alvaro

                        Hi,

                         

                        What is the load profile on these databases ? Did they have lots of sessions doing dictionary Selects?

                         

                        The only thing I can imagine here is that you have a very heavy load on the shared pool with lots of sessions querying the data dictionary, and when you revoke their privilege suddenly lots of cursors have to be invalidated which could cause the hang (library cache mutex contention).

                         

                        Could that be the case here ?

                        • 9. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                          sb92075

                          Alvaro wrote:

                           

                          Hi,

                           

                          What is the load profile on these databases ? Did they have lots of sessions doing dictionary Selects?

                           

                          The only thing I can imagine here is that you have a very heavy load on the shared pool with lots of sessions querying the data dictionary, and when you revoke their privilege suddenly lots of cursors have to be invalidated which could cause the hang (library cache mutex contention).

                           

                          Could that be the case here ?

                           

                          does "revoke select any dictionary from public;" change privileges of existing sessions or only privileges of new sessions?

                          • 10. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                            Alvaro

                            Hey SB,

                             

                            Check this out. My R&D personal enviroment is 12.1.0.1

                             

                            It is my understanding that when you revoke from public, it revokes immediately including sesssions that are logged on at the moment.

                             

                            Just to be sure, and to demonstrate it, I decided to go ahead and do a little teste. What happened was that I was actually able to reproduce a hang.

                             

                            Check this out:

                             

                            SQL> create user teste identified by teste;

                             

                             

                            User created.

                             

                             

                            SQL>

                            SQL> grant create session to teste;

                             

                             

                            Grant succeeded.

                             

                            SQL> conn teste/teste

                            Connected.

                            SQL>

                            SQL> select count(*) from v$session;

                            select count(*) from v$session

                                                 *

                            ERROR at line 1:

                            ORA-00942: table or view does not exist

                             

                             

                             

                            Now granting to public:

                             

                             

                            SQL> conn / as sysdba

                            Connected.

                            SQL>

                            SQL>

                            SQL> grant select any dictionary to public;

                             

                             

                            Grant succeeded.

                             

                            Testing again:

                             

                             

                             

                            SQL> conn teste/teste

                            Connected.

                            SQL> select count(*) from v$session;

                             

                             

                              COUNT(*)

                            ----------

                                    37

                             

                            Ok, everything looks good. We have a cursor opened on data dictionary view with the TESTE session. Now I log in as sys in a separate session and revoke the privilege:

                             

                             

                             

                            SQL>

                            SQL> revoke select any dictionary from public;

                             

                             

                             

                             

                             

                            It hanged! It is still hanging while I'm writing this.

                             

                            Now, the TESTE session, I decided to force another execution of the same cursor, check it out:

                             

                            SQL>  select count(*) from v$session;

                             

                             

                            Also Hanging !  When I check the wait:

                             

                                   SID EVENT

                            ---------- ----------------------------------------------------------------

                                     3 row cache lock

                                     7 library cache load lock

                                    69 library cache lock

                                   181 library cache lock

                                   188 SQL*Net message to client

                                   244 library cache lock

                                   246 library cache lock

                                   303 library cache lock

                                   417 library cache lock

                             

                            When I check v$session_blockers:

                             

                                   SID BLOCKER_SID WAIT_EVENT

                            ---------- ----------- ----------

                                     3         246        293

                                     7         303        307

                                    69         246        306

                                   126         246        306

                                   181         246        306

                                   244         246        306

                                   246           7        306

                                   303         246        306

                                   417         246        306

                             

                            SID 246 is the revoke. It is blocking and also being blocked by sid 7 which is the W000 process, strange indeed. It waiting on a library cache LOAD lock being blocked by the sid 303, which is the TESTE session!

                             

                            Invalidation of cursors seem to be the problem here. After around 5 minutes the revoked returned, and the TESTE session returned the ORA-00942. Imagine this with dozens of sessions and you have a serious hang.

                             

                            Apologies for not formating the code, still haven't got around to using the new formating tools of the forum

                            • 11. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                              Hemant K Chitale

                              In general, there is no need to GRANT SELECT ANY DICTIONARY TO PUBLIC

                               

                              Do you know why the grant was issued in the 5 databases ?  Do the applications in these 5 databases actually query data dictionary tables ?

                               

                               

                              Hemant K Chitale


                              • 12. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                                EdStevens

                                user9074881 wrote:

                                 

                                I don't know, it was created by the developer that I simply copied and pasted. It doesn't make sense to me either, but it also doesn't hurt.

                                 

                                So why are developers writing pl/sql to do the dba's job?

                                 

                                And why is the dba blindly executing such code?

                                • 14. Re: revoking SELECT ANY DICTIONARY from public caused connections to the DB to hang
                                  jgarry

                                  The formatting tools are silly, just go into the advanced editor, select the desired code, and change to courier font.

                                  1 2 Previous Next