Oracle Database Discussions

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.