Roles in relational databases make it easier to grant and revoke privileges from multiple users at once. Multiple users can be grouped into one or more roles in a database. Rather than revoking or granting a privilege to a user or a group of users, the privilege can be granted or revoked from the ROLE. While users can create their own roles and assign one or more privileges to the roles, most databases come with a pre-defined role called PUBLIC.
In this blog, I will try to explain what the PUBLIC role means in Oracle and some of the do’s and don’ts when it comes to Oracle database. There is a lot of confusion when comes to the PUBLIC role in Oracle. Is it a role, a user, or a privilege? If you refer to the Oracle documentation, Oracle calls it a USER GROUP. If you refer to the Oracle 11g documentation, Oracle refers to PUBLIC as a role. The documentation also states that the PUBLIC role is a special role that every database user implicitly inherits on creation. The PUBLIC role has no privileges granted to it by default except for grants on Java objects. Also, it should be noted that this role cannot be manually granted or revoked to a database user, at least according to the Oracle documentation. Technically, the PUBLIC role can be granted and revoked from a user, however, it has no meaning as the user will always assume this role. To further clarify, let us see this behavior in action.
SQL> CREATE USER john IDENTIFIED BY MyPassword; SQL>GRANT PUBLIC TO john; SQL>REVOKE PUBLIC FROM john;
It is also interesting to note that the DBA_ROLES view does not list the PUBLIC role as it is explicitly excluded when creating the role by executing the catalog.sql script, which is executed when the database is created.
SQL> SELECT * FROM DBA_ROLES WHERE ROLE =’PUBLIC’; no rows selected
Querying the SYS.USER$ table shows that the PUBLIC role does indeed exist. This table contains users and roles indicated by the type#, a value of 0 from type# indicates that it is a role and a value of 1 for type# indicates that it is a user.
SQL> SELECT user#, type#, name FROM SYS.USER$ WHERE type# = 0 ORDER BY 1;
The implicit nature of PUBLIC role assignment to all the database users can be seen in the following example. Granting the CONNECT privilege to the PUBLIC role will allow it to be inherited by the new user without being granted explicitly.
SQL> CREATE USER bob IDENTIFIED BY MyPassword; SQL> conn bob/MyPassword;
It should be noted that the PUBLIC role, while convenient, should not be used for user privilege management. Never assign a privilege or role to PUBLIC unless the intent is to grant those privileges and roles to all the existing and new users in the database. Granting privileges and roles directly to the PUBLIC role should be exercised with caution and poses a potential security risk. It is classified as a finding by Defense Information Systems Agency’s (DISA) Security Technical Implementation Guide (STIG). DISA STIG vulnerability ID V-61435 states that database or system privileges should not be granted to PUBLIC and V-61443 states that application role permissions should not be assigned to PUBLIC.
In an Oracle multitenant environment, things are a bit more complicated when it comes to roles. In the container database and pluggable database (CDB/PDB) environment, there is a concept of common roles and local roles. Common roles are created in the root (CDB) and are known to all current and future containers (CDB). Local roles are local to a specific PDB and can only be used within the PDB they are defined in. By default, all the privileges that Oracle grants to the PUBLIC role are granted locally and commonly. According to Oracle, privileges should never be granted to PUBLIC commonly. In other words, never grant any type of privilege to the PUBLIC role in the root or CDB. While not recommended unless necessary, it is possible to modify the PUBLIC role within each container (CDB) separately.
While any user granted privileges to the PUBLIC role can be revoked with no adverse consequences, care should be taken when revoking default privileges granted by Oracle as part of the database creation. You might risk breaking future upgrades and patches as these privileges may be re-granted during an upgrade or patching process. StealthAUDIT is a Data Access Governance that can enumerate all the Oracle roles and privileges including the PUBLIC role and produce detailed entitlement reports out-of-the-box. To learn more about how STEALTHbits can help with auditing your Oracle databases, visit our website: https://www.stealthbits.com/stealthaudit-for-oracle-product
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.