Pete Finnigan's Oracle Security Weblog

I was having a discussion a couple of weeks ago with a friend and he said that in the company he is working at the Oracle database security standard / guide that they are working to told them that they needed to revoke PUBLIC from users in the database. The guide they are using (I didn't get to read it so I only have what was said to me) does cover the normal revokes on some packages from PUBLIC as well.

I have talked about PUBLIC here in some details in the past but not this angle. The first post is "Can I connect to the database as the user PUBLIC?" and the second post was about XS$NULL but also involved PUBLIC a bit. That post is "XS$NULL - Can we login to it and does it really have no privileges?"

So, the first question; can we prove that PUBLIC is not directly granted and is in fact available to all users of the database - i.e. the privileges granted to PUBLIC are available without a grant specifically of PUBLIC to a user.

Lets connect to a 21c database:

C:\_aa\PD>sqlplus system/oracle1@//192.168.56.33:1539/xepdb1

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 2 10:55:03 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL>

Create a sample user that has a sample PL/SQL procedure so that we can grant EXECUTE on it to public and then prove we can use it from another user - which we will also create. Fisrt create the schema:

SQL> create user sch identified by sch;

User created.

SQL> grant create session, create procedure, unlimited tablespace to sch;

Grant succeeded.

SQL>

Connect to this user and create a dummy procedure, execute it as SCH and then grant execute to PUBLIC:

SQL> connect sch/sch@//192.168.56.33:1539/xepdb1
Connected.
SQL> create or replace procedure test as
  2  begin
  3  dbms_output.put_line('hello');
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on
SQL> exec test
hello

PL/SQL procedure successfully completed.

SQL>

Now grant EXECUTE to PUBLIC:

SQL> grant execute on test to public;

Grant succeeded.

SQL>

Connect to SYSTEM again and create a user that has just CREATE SESSION to connect and then see if it can execute SCH.TEST

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> create user use identified by use;

User created.

SQL> grant create session to use;

Grant succeeded.

SQL>

Connect to this user and test the PL/SQL procedure:

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

It does work and silently we can execute the PL/SQL code because its granted to PUBLIC. Lets now do what my friends suggests and revoke PUBLIC from the user USE and see what happens:

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> revoke public from use;
revoke public from use
*
ERROR at line 1:
ORA-01951: ROLE 'PUBLIC' not granted to 'USE'


SQL>

OK, the role PUBLIC is not granted to the user USE. This is true as we didn't grant it. As we didnt actually revoke it then nothing has changed. Lets just check the PUBLIC execute still works for completeness:

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

Quite obviously it works because the revoke failed. What if we grant PUBLIC to the user USE:

SQL> connect system/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> grant public to use;

Grant succeeded.

SQL>

That does work; is it visible in the meta data:

SQL> select granted_role from dba_role_privs where grantee='USE';

GRANTED_ROLE
--------------------------------------------------------------------------------
PUBLIC

SQL>

Yes, it is granted and it is visible in DBA_ROLE_PRIVS. What if we now revoke it and then test the execute again:

SQL> sho user
USER is "SYSTEM"
SQL> revoke public from use;

Revoke succeeded.

SQL> select granted_role from dba_role_privs where grantee='USE';

no rows selected

SQL> connect use/use@//192.168.56.33:1539/xepdb1
Connected.
SQL> set serveroutput on
SQL> exec sch.test;
hello

PL/SQL procedure successfully completed.

SQL>

So, obviously the test PL/SQL procedure still works. You can GRANT PUBLIC to a user and you can REVOKE PUBLIC from a user BUT PUBLIC is not removed from a user as its implicitly there as it is in fact not a role and is a USER GROUP; the only one in the Oracle database.

In one sense this is a pity as it would actually be great if we could actually revoke PUBLIC from a user and therefore remove all PUBLIC grants from that user. But we cannot do this. So the assumption of my friends Oracle security document is probably in part correct but not the meaning he thought to start with. Yes, we should revoke some rights from PUBLIC such as execute on SYS.UTL_FILE etc and yes we can revoke PUBLIC from users IF some has granted it to those users BUT it doesn't actually do anything as PUBLIC is still available to all users