Chapter 11 : MANAGING PRIVILEGES
CHAPTER NO 11
MANAGING PRIVILEGES
WHAT IS PRIVILEGE :
A privilege is a right to execute a particular type of SQL statement or to access another user's object. These include the right to
1- Connect to database
2- Create a table
3- Select rows from another user table
4- Execute another user stored procedure
There are two types of oracle user privileges
1- System privileges:
2- Object privileges:
1- SYSTEM PRIVILEGES:
Each system privilege allows a user to perform a particular database action or class of database operations.
For example the privilege of create tablespace is the system privilege.
2- OBJECT PRIVILEGES:
Each object privilege allows a user to perform particular action on a specific object for example a table, view, sequences, procedure, factions or package.
A DBA's control of privileges include
1- Providing a user the right to perform a type of operation
2- Grant and revoke access to perform system functions
3- Grant privileges directly to the users and indirectly through the roles
4- Granting privileges to all users (public)
SYSTEM PRIVILEGES:
There are more than 100 system privileges.
Privileges can be classified as
- Privileges that enable system-wide operation
Example: create session, create tablespace
- Privileges that enable user can management objects in its own schema
Example: create table
- Privileges that enable user can management object in any schema
Example create any table
Any keyword signifies that user have the privilege in any schema
Grant command signifies add a privilege to a user or a group of users.
Revoke command delete the privileges.
Revoke and Grand are the DDL commands.
MOST COMMON SYSTEM PRIVILEGES ARE
Index : Create any index
Alter any index
Drop any index
Table : Create table
Create any table
Alter any table
Drop any table
Select any table
Update any table
Delete any table
Session : Create session
` Alter session
Restricted session
Tablespace: Create tablespace
Alter tablespace
Drop tablespace
Unlimited tablespaces
IMPORTANT NOTE:
- There is no create index privilege because
- Create table include the create index and the analyze commands.
- User must have quota on tablespace or must have Grant unlimited tablespace.
- Privilege such as create table, create procedure, create cluster include dropping this objects.
- Unlimited tablespace cannot be granted to role
- The drop any table privilege is necessary to truncate a table in another schema.
HOW TO GRANT SYSTEM PRIVILEGES
PRACTICAL #
SQL > Grant create session to emi;
IF GRANTEE WANT TO FURTHER GRANT THE SYSTEM PRIVILEGE THEN WE USE WITH ADMIN OPTION
PRACTICAL #
SQL > Grant create session to emi with admin option;
With grant option privilege are usually reserved for security administrator and are rarely granted to other users.
Grant any object privilege:
This system privilege allow user to grant and revoke any object privilege on behalf of the object owner.
See more detail in page 16-7
DIFFERENTIATES SYSDBA AND SYSOPER PRIVILEGES
SYSDBA SYSOPER
1- Having SYSOPER privileges with 1- Startup / shutdown
admin option
2- Create database 2- Alter database open | mount
3- Alter tablespace begin/end backup 3- Alter database backup control file to recover database
4- Restricted session 4- Alter database archive log
5- Recover database un till 5- Restricted session
( means past recovery )
RESTRICTIONS ON SYSTEM PRIVILEGES
SQL > Show parameter o7_dictionary_accessibility
name type value
------------------------------------ ----------- -----
o7_dictionary_accessibility boolean false
This parameter control restrictions on system privileges
If set to true
Allow access to objects in sys schema
For example: select any table privilege
System privileges allow accessing the objects of any schema as well as sys/ dictionary schema.
This was oracle 7 behavior.
Now default is false means
Do not allow access to sys schema.
For example: select any table privilege
System privileges allow accessing the objects of any schema but do not allow to access the object in sys/ dictionary schema.
REVOKE SYSTEM PRIVILEGES
Use grant command to remove a system privilege from a user
Only privileges granted with a grand command can be revoked
Revoke create table from emi;
IMPORTANT NOTE:
The revoke command can only revoke privileges that have been granted directly with a gant option.
REVOKING SYSTEM PRIVILEGES WITH ADMIN OPTION
There are no cascading effects when a system privilege is revoked, regardless of wether if was given with admin option
SCENARIO
If DBA grant create session, create table privilege with admin option to UMAR
UMAR create table in his schema
And UMAR give create session, create table privilege to OWAIS
OWAIS create table in his schema
then DBA revoke create session, create table privilege from UMAR
RESULT:
UMAR table still exist but no new table can be create
OWAIS table still exist and have to create further new table as OWAIS want
Therefore no effect on OWAIS because system privileges are independent
NOTE :
DBA having the ability to revoke system privilege from any user
If DBA grant create session with admin option to UMAR
And UMAR give create session to OWAIS
Then DBA having the ability to revoke directly OWAIS privilege.
OBJECT PRIVILEGES
Object privileges table view sequence procedure
1- Alter ok ok ok
2- Delete ok ok
3- Execute ok
4- Index ok ok
5- Insert ok ok
6- References ok
7- Select ok ok ok
8- Update ok ok
An object privilege is a privilege or right to perform a particular action of user on a specific table, view, sequences, procedure, function, or package.
Each object has a particular set of grantable privileges.
This slide does not provide complete object privileges.
GRANTING OBJECT PRIVILEGES
Grant update on scott.emp to aaa;
USE WITH GRANT OPTION
Means user has able to transfer this grant to other
Grant update on scott.emp to aaa with grant option;
REVOKING OBJECT PRIVILEGES
Use the revoke command to revoke object privileges.
User revoking the privileges must be the original granter of the object privilege being revoked
Revoke select on scott.emp from aaa;
SCENARIO
If user aaa grant select any table privilege with grant option to bbb
And bbb grant select any table privilege to ccc
Later user aaa revoke select privilege from bbb
Result:
This revoke also apply on ccc means (cccc do not has the right of select any table)
PRACTICAL #
step 1: create user aaa identified by aaa ;
step 2: grant connect, resource to aaa; or grant create session to aaa; (system privilege)
means that user able to connect database
step 3: grant select on scott.emp to aaa; (object privilege)
step 4: grant update on scott.emp to aaa; (object privilege)
step 5: grant insert on scott.emp to aaa; (object privilege)
step 6: select * from scott.emp ;
step 7: ubdate scott.emp
set=1000; than roll back;
step 8: grant create table ; (system privilege)
step 9: create table abc as select * from scott.emp;
step 10: grant create tablespace to aaa; (system privilege)
step 11: create tablespace data
datafile 'd:\oracle\oradata\orcl\data1.dbf' size 50 m;
step 12: revoke create tablespace from aaa; (revoke system privilige)
step 13: revoke select on scott.emp from aaa; (revoke object privilege)
step 14: drop user aaa; (then show error because user have some object therefore we user cascade option)
step 15: drop user aaa cascade;
OBTAINING PRIVILEGES INFORMATION
DBA_SYS_PRIVS
List of system privileges granted to user and role
SESSION_PRIVS
List the privileges that are currently available to the user
DBA_TAB_PRIVS
List all grants on all objects in the database
DBA_COL_PRIVS
Describe all object_column grant in the database.
-------------------------------- END OF CHAPTER 11 -----------------------------
No comments:
Post a Comment