Public is everyone access to database .PUBLIC is an entity that can be granted any privilege and assigned any role. All database users automatically inherit all the rights assigned to PUBLIC. It’s a simple concept designed for convenience. Granting permissions to PUBLIC is easy to do, and often the easiest way to get a system working, however, the security implications of using the PUBLIC group to assign permissions are significant.PUBLIC should be treated with care and used sparinglyPUBLIC should be treated with care and used sparingly.
Before go deeply with this subject we need to understand Role and Privileges in oracle database,as you all know Oracle Privileges control the rights to see, you could Modify, create or alter database depend on privileged you have,as i mention earlier two Privileges in database exists System and Object.
System privileges are not related to any specific object or schema. Object privileges are just the opposite, those that are directly related to a specific object or schema.check the examples below
|
System Privilege
|
Object Privilege
|
|
GRANT ANY PRIVILEGE
|
GRANT
|
|
ALTER ANY ROLE
|
ALTER
|
|
ALTER DATABASE
|
SELECT
|
to check system privileges in database you need to check the following DBA_ DICTIONARY :
SQL> desc dba_sys_privs;
Name Null? Type
—————————————– ——– —————————-
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
Sample data From table :
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
SYS CREATE OPERATOR NO
SYS EXECUTE ANY OPERATOR NO
SYS CREATE DIMENSION NO
SYS ADMINISTER RESOURCE MANAGER NO
Another example i want to check scott and his system privileges :
SQL> select * from dba_sys_privs where GRANTEE = ‘SCOTT’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
SCOTT UNLIMITED TABLESPACE NO
SQL> desc dba_tab_privs;
Name Null? Type
—————————————– ——– —————————-
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
SQL> select OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE from dba_tab_privs where GRANTEE = ‘SCOTT’;
no rows selected.
What about Roles ? as you we knows Oracle Comes with Pre defined such as DBA, Resource , and connect also you can create your own Role which is Set of privileges to manage Users Privileges.
user can be a member of more then one role, and roles can even be members of other roles.
Users in company leaves their jobs , Travel or even don’t need privileges in futures Create Oracle Role will make all this Manage is easy since . Privileges are then granted only to the roles, never to a specific user,if the privileges ever change, they are changed on the role and automatically take effect for all users with the role.and you check
SQL> desc dba_roles ;
Name Null? Type
—————————————– ——– —————————-
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
SQL> desc dba_role_privs
Name Null? Type
—————————————– ——– —————————-
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> select GRANTEE, ADMIN_OPTION, DEFAULT_ROLE from dba_role_privs where
GRANTED_ROLE = ‘DBA’; 2
GRANTEE ADM DEF
—————————— — —
SYS YES YES
SYSMAN NO YES
SYSTEM YES YES
SQL> select * from dba_role_privs where GRANTEE = ‘PUBLIC’;
no rows selected
Technology Geek 
Fantastic stuff Osama! Loved it, the explanation and SQL examples are amazing. Very nice too since Im starting out in Oracle. Also want to share a good page on related topic too:System vs object privileges
LikeLike