Public Role/User

The PUBLIC Role/User In Oracle

The PUBLIC role/User in Oracle.

When the Oracle Database is been created, the sql.bsq script is run which creates a role called PUBLIC. “create role public “

But you can't see from DBA_ROLES view that the role exists

SQL> select * from dba_roles where role=’PUBLIC';
no rows selected

So how we can see it or Find it, lets do some exercise

The DBA_ROLES view is created during the execution of the catalog.sql script:

create or replace view DBA_ROLES (ROLE, PASSWORD_REQUIRED)
as
select name, decode(password, null, ‘NO’, ‘EXTERNAL’, ‘EXTERNAL’,
‘GLOBAL’, ‘GLOBAL’, ‘YES’) from  user$
where type# = 0 and name not in (‘PUBLIC’, ‘_NEXT_USER’);

Now you see how the PUBLIC role is hidden from DBA_ROLES view.

In SYS.USER$, there are 2 types of objects: role (0) or user (1).

SQL> select user#, name from sys.user$ order by 1;

USER# NAME
———- ——————————
0 SYS
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM
6 SELECT_CATALOG_ROLE
7 EXECUTE_CATALOG_ROLE
8 DELETE_CATALOG_ROLE
9 EXP_FULL_DATABASE
10 IMP_FULL_DATABASE
11 OUTLN
12 RECOVERY_CATALOG_OWNER
13 GATHER_SYSTEM_STATISTICS
14 LOGSTDBY_ADMINISTRATOR
15 AQ_ADMINISTRATOR_ROLE
16 AQ_USER_ROLE

When we create a user if the create session role is granted to public. then the created user can connect without giving it the create session role.

Let’s try to see in this E.g:-

1. We create a user.

SQL> create user ABC123 identified by ABC123;

User created.

2. Try to connect that user.

SQL> connect ABC123/ABC123;
ERROR:
ORA-01045: user ABC123 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

3. Connect with Sys to grant create Prics

SQL> conn /as sysdba

Connected.
SQL> grant create session to public;

Grant succeeded.

4. Try to connect again. It is been connected,

SQL> connect ABC123/ABC123;
Connected.

NOTE: In fact the user, though not granted the CREATE SESSION privilege directly
got it from the PUBLIC role that had this privilege been granted previously.

SQL> select * from user_sys_privs;

USERNAME                        PRIVILEGE                                 ADM
——————————            ————————————                —
PUBLIC                             CREATE SESSION                         NO

So when you grant some privileges or roles to PUBLIC, this means that any
newly created user is automatically granted these without requiring any
explicit GRANT statement.

Hope you Like it