Call: +44 (0)7759 277220 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "PeteFinnigan.com white papers section updated for Roby Sherman papers"] [Next entry: "Interesting question about Sarbanes-Oxley on Oracle 7.3.3"]

Can I connect to the database as the user PUBLIC?



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!

There has been 1 Comment posted on this article