Pete Finnigan wrote:
>> I have a quick question. Why am I not able to see the user "PUBLIC"
>>in ALL_USERS table.
>
> Hi,
>
> the "user" PUBLIC has a type of "0" in sys.user$ therefore its
> considered a ROLE. see:
>
> SQL> select user#,type#,name
> 2 from sys.user$;
>
> USER# TYPE# NAME
> ---------- ---------- ------------------------------
> 0 1 SYS
> 1 0 PUBLIC
> 2 0 CONNECT
> 3 0 RESOURCE
> 4 0 DBA
>
> But, its not listed in dba_roles?
>
> SQL> select role from dba_roles
> 2 where role='PUBLIC';
>
> no rows selected
>
> SQL>
>
> Also roles are not allowed to own objects, only users can:-
>
> SQL> select count(*),object_type
> 2 from dba_objects
> 3 where owner in (select 'x'
> 4 from dba_roles
> 5 where role=owner)
> 6 group by object_type;
>
> no rows selected
>
> But the role public owns synonyms:-
>
> SQL> select count(*),object_type
> 2 from dba_objects
> 3 where owner='PUBLIC'
> 4 group by object_type;
>
> COUNT(*) OBJECT_TYPE
> ---------- ------------------
> 11541 SYNONYM
>
> It is classed as a role not a user - type#=0 - but its treated
> internally slightly differently from other roles as its not explicitly
> granted to users but users have this role automatically. You cannot
> revoke PUBLIC from a user unless you grant it first, but its already
> available.
>
> SQL> revoke public from scott;
> revoke public from scott
> *
> ERROR at line 1:
> ORA-01951: ROLE 'PUBLIC' not granted to 'SCOTT'
>
> BUT
>
> SQL> grant public to scott;
>
> Grant succeeded.
>
> SQL> connect scott/tiger
> Connected.
> SQL> select * from user_role_privs;
>
> USERNAME GRANTED_ROLE ADM DEF
> OS_
> ------------------------------ ------------------------------ --- --- --
> -
> SCOTT APP_ROLE NO NO NO
> SCOTT CONNECT NO YES NO
> SCOTT PUBLIC NO YES NO
> SCOTT RESOURCE NO YES NO
>
> SQL> connect system/manager
> Connected.
> SQL> revoke public from scott;
>
> Revoke succeeded.
>
> SQL>
>
> So its definitely a role but remember you don't need to grant it
> separately like i just did.
>
> Also how about:-
>
> SQL> connect system/manager_at_sans as sysoper
> Connected.
> SQL> sho user
> USER is "PUBLIC"
>
> !!!!
>
> kind regards
>
> Pete
thanks Pete. I was looking at the TYPE#=0 but never looked in DBA_ROLES.
But ...
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> sho user
USER is "SYS"
SQL> conn system/manager
Connected.
SQL> sho user
USER is "SYSTEM"
SQL>
Daniel Morgan
Received on Fri Jul 16 2004 - 18:24:16 CDT
