Oracle Database Contain two type of privileges one called System and other called Objects each one grant it to use as they need, But interesting entity called Public. it’s more Role than users , and every user created in database assign to Public by default unfortunately nothing can be done to change that. so we can say it’s seems like User Group, so if you grant anything of privileges to public this means that everyone of users database will have this privilege ( System or Object ) So you need to becareful of you doing while dealing with public.
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
Now Let’s Talk About Object Privileges :
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)
more complex huh ? Read Oracle Documentation
Here
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)
For information about this table you can Read Oracle documentation
here.
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)
The above table checks the privileges granted to Role, you can refer to Oracle Documentation
herecheck the below query to determine Which User Have DBA Role :
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
As conclusion Public can be Granted any of Roles or users,While it is easy and fully supported to grant roles to PUBLIC i don’t recommended to do that give one case why to do that ?
Check the user who has Public :
SQL> select * from dba_role_privs where GRANTEE = ‘PUBLIC’;
no rows selected
And this exactly what we want not Public roles to any users.
Thank you
Osama Mustafa
Technology Geek 