3 Replies Latest reply on Feb 6, 2014 1:29 PM by Partha Sarathy S

    Regarding providing grant on DBMS_LOCK

    1043868

      Hi All,

       

      I observed the below situation, and wuold like to know the reason -

       

      We have one user USER1 who has previlege on dbms_lock with admin option. The user USER1 has provided grant on dbms_lock to another user USER2.

      What we saw is that when the USER1 is dropped from the database , the grant previlege on dbms_lock gets revoked from USER2.

        • 1. Re: Regarding providing grant on DBMS_LOCK
          John Stegeman

          What you saw is what is documented by Oracle as the correct behaviour:

           

          • The object privilege grants propagated using the GRANT OPTION are revoked if the object privilege of a grantor is revoked. For example, assume thatuser1 is granted the SELECT object privilege with the GRANT OPTION, and grants the SELECT privilege on emp to user2. Subsequently, the SELECTprivilege is revoked from user1. This REVOKE is cascaded to user2 as well. Any objects that depend on the revoked SELECT privilege of user1 anduser2 can also be affected, as described earlier.
          • 2. Re: Regarding providing grant on DBMS_LOCK
            KarK

            DBMS_LOCK is object privilege , cant give GRANT privilege WITH ADMIN option , you can give only grant WITH GRANT option.

             

            WITH GRANT OPTION 

             

            1. We can grant object privileges only with grant option like (select,update,insert) 

            2. A--------->B--------->C

             

            If A want to revoke privileges directly from C , it is not possible. A can revoke from B and B can revoke from C.

            The user who granted the privilege can only revoke.

            If A want to revoke from C, then A can revoke from B which automatically revoke the privileges from C.

             

             

            WITH ADMIN OPTION

             

            1. We can give the system privileges only with admin option(CREATE TABLE,CREATE INDEX,CREATE SESSION)

            2. A------>B-------->C

             

            In admin option,it is possible to revoke the privileges from both B and C by A. If  A revoke the privileges from B means it didn’t revoke the privileges of C,

            A want to explicitly revoke from C

            1 person found this helpful
            • 3. Re: Regarding providing grant on DBMS_LOCK
              Partha Sarathy S

              Check whether USER1 has WITH ADMIN OPTION or WITH GRANT OPTION.

              1) Because if USER1 has WITH ADMIN option and he has granted to USER2. If the privilege is revoked from USER1,  then USER2 will still have the privilege.

              2)  But if USER1 has WITH GRANT option and he has granted to USER2.  If the privilege is revoked from USER1,  then USER2 's privilege will also be revoked (revoking will cascade).

               

              ADMIN OPTION - Only for System privileges

              GRANT OPTION - Only for Object privileges

              1 person found this helpful