What is the concept of PUBLIC exactly in Oracle Database?

Question

What is PUBLIC exactly in Oracle?

Is it a role or a user? And does it have a schema? If it has, can we create objects in it?

I read many documents saying it is a user group as well. But I got confused about what exactly it is.

Any help will be very appreciated.

Answer 1

Think of PUBLIC as a group to which all users belong. You can grant privileges to it, allowing all users in the database to have access to something with a single grant.

grant select on myuser.mytable1 to public;

Primarily it is used to provide access to system-level functionality or data dictionary views such as ALL_TABLES or USER_TABLES; it is not generally recommended that you use PUBLIC when designing your own applications because it tends to violate the principle of least privilege.

Answer 2

In the data dictionary, it is defined as a role. You can see this by

SELECT * FROM SYS.USER$ WHERE TYPE# = 0;

However, PUBLIC is special - it is not really a role. You cannot, for example, GRANT or REVOKE it explicitly. And, in fact, the data dictionary view DBA_ROLES explicitly excludes it as a role with this predicate:

select ...
from  user$
where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')

So, it's really a special case, as evidenced by the special code Oracle included in its data dictionary to handle it.

If it has, can we create objects in it?

It does have its own schema, but it can only hold objects whose DDL syntax allows for CREATE PUBLIC.... That's only synonyms and database links, as far as I know.