PUBLIC role access privliages 1

[j2willi4]

Hello,

Can anyone provide me with some insight or further documentation on the PUBLIC role within Oracle.

I am new to Oracle. Is the role architecture in Oracle similar to that of SQL server?

(Multiple DBs with in Oracle. Each DB with their own roles defined differently. Users assigned to databases then assigned a role within that database)

Thanks for any help you can provide!

 

[LKBrwnDBA]

Whatever privileges you grant to PUBLIC role, are automatically granted to ANYONE who can create a session on that db.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

To be precise, PUBLIC is not a ROLE, per se:

select role from dba_roles;

ROLE
------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
LOGSTDBY_ADMINISTRATOR
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
OEM_MONITOR
RECOVERY_CATALOG_OWNER
GLOBAL_AQ_USER_ROLE
HS_ADMIN_ROLE
PLUSTRACE
ROLE1
WHATEVER
DEV_USERS
YADA
RAM

22 rows selected.

Notice that PUBLIC does not appear above. Neither is PUBLIC a user, per se:

select username from dba_users;

USERNAME
--------
SYS
SYSTEM
OUTLN
DBSNMP
DHUNT

But, PUBLIC can own objects such as SYNONYMS:

SQL> 1  select object_type,count(*)
  2  from dba_objects
  3  where owner = 'PUBLIC'
  4* group by object_type
SQL> /

OBJECT_TYPE          COUNT(*)
------------------ ----------
SYNONYM                  1543

...And, as LKBrwnDBA correctly pointed out, "Whatever privileges you grant to PUBLIC...are automatically granted to ANYONE who can create a session on that db.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

If you are new to oracle, you might want to read the concepts manual. See the following link.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

Bill
Oracle DBA/Developer
New York State, USA

 

[j2willi4]

Thank you for the help!


Would this be an accurate statement???

Access to the SYSTEM table-space does not grant access to any user data. SYSTEM table-space is used to store system catalogue/metadata.
PUBLIC, by default, does not provide a user any permissions (SELECT, DELETE, UPDATE) on user data. Permissions to user data must be explicitly granted to a role to gain access.

 

[Beilstwh]

You are correct. The ability (or quota) to write into the SYSTEM tablespace is granted to a specific user (Schema). Public only has those rights that the DBA explecently grants it. Except in very specific cases, public should NEVER be given any rights to any objects (except synonyms).

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

>>> Access to the SYSTEM table-space does not grant access to any user data.

"Access", here, is a loaded term. Everyone that has permission to create objects has, at least indirect "access" to the SYSTEM tablespace. This is because whenever anyone does DDL (Data-Definition Language) commands (CREATE, ALTER, DROP), you are affecting the SYSTEM tablespace. Additionally, all users have read access to objects in the SYSTEM tablespace by virtue of SELECT statements against data dictionary objects.

Now, when you mention accessing user data that resides in the SYSTEM tablespace, this disobeys another presciption: user data should not reside in the SYSTEM tablespace. User data should reside in application tablespaces besides tablespaces for SYSTEM data-dictionary objects, rollback segments, and temporary objects.

Given that provision, one cannot access application tables only via permissions granted explicitly 1) to that user, 2) to a role to which that user is a member, or 3) to PUBIC.

>>> ...SYSTEM table-space is used to store system catalogue/metadata.

Correct.

>>> PUBLIC, by default, does not provide a user any permissions (SELECT, DELETE, UPDATE) on user data.

Incorrect. If I issue the command, "GRANT SELECT, DELETE, UPDATE on <some table> TO PUBLIC;" then certainly anyone that can connect to the database can SELECT, DELETE, or UPDATE data in <some table>.

>>> Permissions to user data must be explicitly granted to a role to gain access.

Incorrect. As I mentioned above, a user that does not own an object can have privileges via either 1) specific GRANTs to that user, 2) GRANTs to a role in which the user is a member or 3) GRANTs to PUBLIC;

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[j2willi4]

by default" was intended to mean "has not been specifically granted any additional permissions"

Other roles (non-PUBLIC) that grant permissions are out of scope for what i am doing...

Thanks for all your help!