Tuesday, April 23, 2013

Short Look for Oracle Privileges

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 here
check 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

1 comment:

  1. 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

    ReplyDelete