Pete Finnigan's Oracle Security Weblog

I was playing around with the PUBLIC user the other day, or is it a role? or what is it? - PUBLIC is actually defined as a user group. This is a great idea that Oracle introduced to group users together into a group. But they only ever created the PUBLIC group. I think it would be useful to have the possibility to create other user groups. As it stands every user is a member of the user group PUBLIC, presumably if the feature was extended it would have been possible to create multiple groups and decide which users were members of the group.

So let's play. Is PUBLIC a role or a user according to the SYS.USER$ table:

SQL> select user#,name,type#
  2  from sys.user$;

     USER# NAME                                TYPE#
---------- ------------------------------ ----------
         0 SYS                                     1
         1 PUBLIC                                  0
         2 CONNECT                                 0
         3 RESOURCE                                0
         4 DBA                                     0
         5 SYSTEM                                  1
.....
        57 QS_CB                                   1
        58 QS_CS                                   1
        59 SCOTT                                   1
       130 PUP                                     1
.....

This shows that PUBLIC has a type# of "0" so its a ROLE. Or is it.

Let's check DBA_ROLES:

SQL> select * from dba_roles
  2  where role='PUBLIC';

no rows selected

So it’s not a ROLE after all. But if we check the source code of DBA_ROLES then we will see that PUBLIC is specifically excluded.

OK, so it could be a user? - has it got a password:

SQL> select password from sys.user$
  2  where name='PUBLIC';

PASSWORD
------------------------------


SQL> 

No, can we log in?

SQL> connect public/@sans
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where   ::= [/][@] | /
SQL> connect public
Enter password: 
ERROR:
ORA-01005: null password given; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 

No, we cannot, maybe there is another way?

Next I will connect as a DBA, check the users who have been granted SYSDBA and SYSOPER. Then I will grant SYSDBA and SYSOPER to the user SCOTT:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SQL> connect sys/change_on_install@sans as sysdba
Connected.
SQL> 
SQL> sho parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> 
SQL> grant sysdba to scott;

Grant succeeded.

SQL> grant sysoper to scott;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SCOTT                          TRUE  TRUE

OK, I want to show you a short SQL script next that i will use a few times here, this is the contents of the file sql_context.sql:

col curr for a6
col currid for a6
col curruse for a6
col isdba for a5
col osuser for a10
col sessuse for a6
col sessuseid for a6


select 	sys_context('userenv','current_schema') curr,
	sys_context('userenv','current_schemaid') currid,
	sys_context('userenv','current_user') curruse,
	sys_context('userenv','isdba') isdba,
	sys_context('userenv','os_user') osuser,
	sys_context('userenv','session_user') sessuse,
	sys_context('userenv','session_userid') sessuseid
from dual
/

Next we can connect as SCOTT and "AS SYSDBA" -

SQL> connect scott/tiger@sans as sysdba
Connected.
SQL> sho user
USER is "SYS"



SQL> @sys_context

CURR   CURRID CURRUS ISDBA OSUSER     SESSUS SESSUS
------ ------ ------ ----- ---------- ------ ------
SYS    0      SYS    TRUE  ZULIA\pete SYS    0

OK, we have connected "AS SYSDBA" as the current user, current schema and session user are all SYS. We are effectively logged in as SYS as everyone knows. What if we just connect normally as SCOTT to compare:

SQL> connect scott/tiger
Connected.
SQL> sho user
USER is "SCOTT"
SQL> @sys_context

CURR   CURRID CURRUS ISDBA OSUSER     SESSUS SESSUS
------ ------ ------ ----- ---------- ------ ------
SCOTT  59     SCOTT  FALSE ZULIA\pete SCOTT  59

SQL> 

OK, same results, except this time we are firmly in as SCOTT. Now let’s connect as PUBLIC:

SQL> connect scott/tiger@sans as sysoper
Connected.
SQL> sho user
USER is "PUBLIC"
SQL> @sys_context

CURR   CURRID CURRUS ISDBA OSUSER     SESSUS SESSUS
------ ------ ------ ----- ---------- ------ ------
PUBLIC 1      PUBLIC FALSE ZULIA\pete PUBLIC 1

SQL> 

Very interesting, we are connected to the database as PUBLIC, that is the current user and session user and the current schema is PUBLIC. But it is a role? And has no password and we cannot connect normally with a connect statement, only via "AS SYSOPER".

Can we create objects as PUBLIC? – Let’s try AS SYSDBA and SCOTT first:

SQL>  connect scott/tiger
Connected.
SQL> sho user
USER is "SCOTT"
SQL> create table user_test1 (col01 number) tablespace users;

Table created.

SQL> connect scott/tiger@sans as sysdba
Connected.
SQL> create table user_test2 (col01 number) tablespace users;

Table created.

SQL> select owner,table_name 
  2  from dba_tables
  3  where table_name like 'USER_TEST%';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          USER_TEST1
SYS                            USER_TEST2

SQL> 

Obviously we can create objects as SCOTT and AS SYSDBA, what about PUBLIC, i mean AS SYSOPER:

SQL> connect scott/tiger@sans as sysoper
Connected.
SQL> sho user
USER is "PUBLIC"
SQL> create table user_test3 (col01 number) tablespace users;
create table user_test3 (col01 number) tablespace users
*
ERROR at line 1:
ORA-01918: user 'PUBLIC' does not exist


SQL> 

No... This is an interesting exercise though. I knew before we started that PUBLIC is not a user or role but Oracle lumps it in the USER$ table with roles but no where else. It is in fact a user group. You cannot connect directly as PUBLIC but using AS SYSOPER actually connects you as PUBLIC according to the current schema, current user and session user. It certainly seems like a fudge!