Privilege is a right to execute a particular type of SQL statement or to access another user's object. Example: Connect to the DB(create session), create table.
DBA can grant privileges to user. So that users can accomplish that task required for their jobs. DBA should grant a privilege only to a user who requires that privilege to accomplish the necessary work. Excessive granting of unnecessary privileges can compromiseThere are five major categories of Privileges. They are
- System Privileges
- Object Privileges
- View Privileges
- Procedure Privileges
- Type Privileges
System Privileges :
A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. DBA can grant / revoke system previleges to users and roles.
Who can Grant or Revoke system privilege:
- Users who have been granted a specific system privilege with the ADMIN OPTION
- Users with the system privilege GRANT ANY PRIVILEGE
SQL> desc system_privilege_map
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
Approximately 250 privileges are there under System Privileges
SQL> desc dba_sys_privs (Shows which user has system privileges)
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
COMMON VARCHAR2(3)
SQL> select privilege from dba_sys_privs where grantee='SHYAM';
PRIVILEGE
----------------------------------------
CREATE JOB
CREATE SESSION
SELECT ANY TABLE
SQL> revoke select any table from shyam;
Revoke succeeded.
SQL> select privilege from dba_sys_privs where grantee='SHYAM';
PRIVILEGE
----------------------------------------
CREATE JOB
CREATE SESSION
Schema Object Privileges :
A Schema Object privilege is the permission to perform a particular action on specific schema object.
Who can Grant or Revoke system privilege:
- A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user with or without the GRANT OPTION of the GRANT statement. Otherwise, the grantee can use the privilege, but cannot grant it to other users.
SQL> desc table_privilege_map
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
SQL> grant alter on scott.emp to shyam;
Grant succeeded.
SQL> desc dba_tab_privs; (Shows which user has table/object privileges)
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
GRANTOR VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
COMMON VARCHAR2(3)
TYPE VARCHAR2(24)
SQL> select privilege from dba_tab_privs where grantee='SHYAM';
PRIVILEGE
----------------------------------------
ALTER
Administrative Privileges from System_privilege_map are given below:
SQL> select name,privilege,property from system_privilege_map where name='SYSDBA';
NAME PRIVILEGE PROPERTY
---------------------------------------- ---------- ----------
SYSDBA -83 0
SQL> select name,privilege from system_privilege_map where name='SYSOPER';
NAME PRIVILEGE
---------------------------------------- ----------
SYSOPER -84
SQL> select name,privilege from system_privilege_map where name='SYSDG';
NAME PRIVILEGE
---------------------------------------- ----------
SYSDG -341
SQL> select name,privilege from system_privilege_map where name='SYSBACKUP';
NAME PRIVILEGE
---------------------------------------- ----------
SYSBACKUP -340
SQL> select name,privilege from system_privilege_map where name='SYSKM';
NAME PRIVILEGE
---------------------------------------- ----------
SYSKM -342
SQL> desc system_privilege_map
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
COMMON VARCHAR2(3)
SQL> select privilege,admin_option from dba_sys_privs where grantee='SHIRISHA';
PRIVILEGE ADM
---------------------------------------- ---
CREATE SESSION NO
SQL> select privilege,admin_option from dba_sys_privs where grantee='SHYAM';
PRIVILEGE ADM
---------------------------------------- ---
CREATE JOB NO
CREATE SESSION NO
SQL> desc dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
GRANTOR VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
COMMON VARCHAR2(3)
TYPE VARCHAR2(24)
SQL> column privilege format a10;
SQL> column grantor format a20;
SQL> select privilege,grantable,grantor from dba_tab_privs where grantee='SHYAM';
PRIVILEGE GRA GRANTOR
---------- --- --------------------
ALTER NO SCOTT
SQL> select privilege,grantable,grantor from dba_tab_privs where grantee='SHIRISHA';
no rows selected
Granting System Privilege to a user:
SQL> select name from system_privilege_map where name='CREATE USER';
NAME
------------------------------
CREATE USER
User SQL> SHOW USER
USER is "SHIRISHA"
SQL> create user srikanth identified by srikanth123;
create user srikanth identified by srikanth123
*ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant create user to shirisha;
Grant succeeded.
sys SQL> select privilege from dba_sys_privs where grantee='SHIRISHA';
PRIVILEGE
----------
CREATE SESSION
CREATE USER
User SQL> create user srikanth identified by srikanth123;
User created.
No comments:
Post a Comment