Download Managing Oracle DB Privileges: Defining, Granting, & Controlling Privileges and more Study notes Business Administration in PDF only on Docsity!
Page 1
DATABASE PRIVILEGES
Today we will cover defining database privileges, grant and control system privileges and grant and control object privileges.
You can grant users privileges to access the database and objects within the database, and allow them specific system privileges.
The DBA’s control of privileges includes: Providing a user the right to perform a type of operation Enabling and restrict access and change to data. Enabling and restricting the ability to perform system functions and to change database structures. Granting to individual users and roles. Granting to all users (PUBLIC)
Privilege Type Description SYSTEM Each system privilege allows a user to perform a particular database operation or class of database operations. OBJECT Each object privilege allows a user to perform a particular action on a specific table, view., sequence, procedure, function or package
Maintain and control privileges by creating roles that consist of a named group of related privileges.
Role properties include :
Reduced granting of privileges Dynamic privilege management Selective availability of privileges Application awareness
System Privileges :
Allow users to perform a particular database operation or class of operations by granting them system privileges. A system privilege is a right to execute a type of command.
Types of System Privileges : In one’s own schema The privilege to create a table in one’s own schema The privilege to create a sequence in one’s own schema On all objects of a specified type The privilege to create a table in any schema The privilege to update rows in any table or view in any schema On the system or a user The privilege to create a user The privilege to create a session (connect to the database)
System Privileges are not specific to a named schema object or structure. They are specific to a particular operation or class of operations on a type of object or structure.
E.g. The system privilege SELECT ANY TABLE gives the user the right to query any table in the database. An object privilege gives a user the right to query a specific table, such as in BIS4.REGION.
There are over 80 distinct system privileges.
Page 2
Granting System Privileges :
A role is a named group of related objects that are granted to users and other roles. command.
Syntax :
GRANT system_priv TO user
role role
PUBLIC
WITH ADMIN OPTION
Where :
Role : is a role name to be granted. PUBLIC : grants system privileges or roles to all users. WITH ADMIN OPTION : allows the grantee to grant the system privilege or role to
other users or roles. If you grant a role with ADMIN OPTION, the grantee can also alter or drop the role.
e.g. grant create session to jack; grant create session to jack WITH ADMIN option;
grant create table, create table to ROLE_NAME; grant create table to ROLE_NAME with ADMIN OPTION;
Displaying System Privileges:
List the system privileges that have been granted by querying the view DBA_SYS_PRIVS.
E.G. SELECT *
FROM SYS.DBA_SYS_PRIVS;
Revoking System Privileges
Syntax :
REVOKE system_priv FROM user
role role
PUBLIC
Where : Role is a role to be revoked FROM identifies users and roles from which the system privileges
Page 4
TO identifies users or roles to which the object privilege is granted PUBLIC grants object privileges to all users. WITH GRANT allows the grantee to grant object privileges to other users and roles.
Examples :
Grant select on bis4.weather to role_name; Grant update on bis4.region to role_name;
If the grant includes the WITH GRANT OPTION, the grantee can further grant the object privileges to other users.
Grant select on bis4.weather to role_name with GRANT OPTION;
Displaying Object Privileges : by querying the following
DBA_TAB_PRIVS
DBA_COL_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
TABLE_PRIVILEGES
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
COLUMN_PRIVILEGES
Example : Display all column specific object privileges granted to all users by querying SYS.DBA_COL_PRIVS SELECT table_name, column_name, privilege, grantable FROM dba_col_privs;
Also, query the DICT tables to find comments on all tables/views mentioned above.
Revoking Object Privileges
Syntax :
REVOKE object_priv ON object Schema.
FROM user Role CASCADE CONSTRAINTS
Public
Where : Object_priv is an object privilege to be revoked.
Page 5
ON identifies the object on which the privileges are revoked FROM identifies users or roles from which the object privilege are revoked. PUBLIC revokes object privileges from all users. CASCADE drops any referential integrity constraints
Note : Grantors can revoke privileges from only those users to whom they have granted privileges.
Examples : Revoke all privileges jack has been granted on bis4.region table
REVOKE ALL ON bis4.student FROM jack;
