4 Replies Latest reply on May 24, 2012 10:14 PM by 750907

    Propagating privileges on a role granted to a role

    713555
      Assign a priv on v_$session to a role, assign that role to public, the privs dont propagate down. If I assign privs directly to public to v_$session, anyone with public has the rights on it, is this correct behaviour?
      SQL> conn / as sysdba
      Connected.
      
      SQL> create user A identified by A;
      User created.
      SQL> create user B identified by B;
      User created.
      
      SQL> grant create session to A;
      Grant succeeded.
      SQL> grant create session to B;
      Grant succeeded.
      SQL> grant select on v_$session to A;
      
      Grant succeeded.
      
      SQL> create view a.sessview as select * from v$session where status = 'KILLED';
      
      View created.
      
      SQL> create public synonym sessview for a.sessview;
      
      Synonym created.
      
      
      SQL> conn a/A
      Connected.
      SQL> select count(*) from sessview;
      
        COUNT(*)
      ----------
               0
      
      
      SQL> conn / as sysdba
      Connected.
      SQL> grant select on a.sessview to public;
      
      Grant succeeded.
      
      SQL> create synonym sessview for a.sessview;
      
      Synonym created.
      
      SQL> create public synonym sessview for a.sessview;
      
      Synonym created.
      
      -- now test selecting from sessview with user B
      
      SQL> conn B/B
      Connected.
      SQL> select count(*) from sessview;
      select count(*) from sessview
                           *
      ERROR at line 1:
      ORA-01031: insufficient privileges
      -- ok, no privs, lets assign select to v$session to public
      SQL> conn / as sysdba
      Connected.
      SQL> grant select on v_$session to public;
      
      Grant succeeded.
      
      SQL> conn B/B
      Connected.
      SQL> select count(*) from sessview;
      
        COUNT(*)
      ----------
               0
      
      SQL> conn / as sysdba
      Connected.
      -- so I need to explicitly assign privs to public, I have a number of these I need to create so to give me greater control if I ever want to revoke from public I can do so by just revoking that role lets assign to a role, assign that role to public.
      SQL> revoke select on v_$session from public;
      
      Revoke succeeded.
      
      SQL> create role testrole;
      
      Role created.
      
      SQL> grant select on v_$session to testrole;
      
      Grant succeeded.
      
      SQL> grant testrole to public
        2  /
      
      Grant succeeded.
      
      SQL> conn B/B
      Connected.
      SQL> select count(*) from sessview;
      select count(*) from sessview
                           *
      ERROR at line 1:
      ORA-01031: insufficient privileges
      
      
      SQL>
      -- but this is saying privs assigned to a role do not propagate down to public if that role is assigned to public. this doesnt sound right to me. My thinking must be wrong or maybe because its the object its on.

      Im getting this on a 9.2.0.1.0 databse but the above replicated example is on 11.2.0.3.0 XE.

      Can anyone explain?


      http://docs.oracle.com/cd/E11882_01/network.112/e10574/authorization.htm#BABHJFCF

      specifically
      >
      ....Because PUBLIC is accessible to every database user, all privileges and roles granted to PUBLIC are accessible to every database user.
        • 1. Re: Propagating privileges on a role granted to a role
          Marcus Rangel
          Privileges granted through roles are not valid in every situation, you must be facing one of those cases when you have to grant directly.
          • 2. Re: Propagating privileges on a role granted to a role
            750907
            Try:
            grant select on v_$session to a with grant option;
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm#BGBCIIEG

            To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.

            PUBLIC is actually a schema that you inherit rather than a role. So granting a privilege or role to public is just like granting a privilege or role to yourself. You'll find the same behavior if you create private synonyms in "b" and grant the role directly to "b".

            Kyte:
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

            PUBLIC acts like a role but is really a schema -- and a schema that everyone "inherits"
            1 person found this helpful
            • 3. Re: Propagating privileges on a role granted to a role
              713555
              thanks for that link joe, I'll chase it up tomorrow.

              Tom says several times in the link that

              >
              anything granted to PUBLIC is granted to every user that exists or will exist.

              PUBLIC is like a "special role" that everyone has.


              If you grant to public, EVERYONE has it, period.
              >

              which was my thinking. So it may just be "everyone has everything public has but T&Cs apply"
              • 4. Re: Propagating privileges on a role granted to a role
                750907
                Yeah, it'd be interesting to know how the Oracle DB fuses the PUBLIC schema to every logged in user. It'd be easier to understand I think if I knew how to "become" a different user without actually having to log in as them. Kind of like Sylar from that tv show heros. I'm still Sylar, but when I cut your head open and steal your special powers I now have your power but continue on as myself.