Re: user "PUBLIC" not in All

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Fri, 16 Jul 2004 10:04:06 +0100
Message-ID: <pOpGpCBGo59ARxvZ@peterfinnigan.demon.co.uk>

> 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

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Fri Jul 16 2004 - 04:04:06 CDT

Re: user "PUBLIC" not in All_USERS