USER MANAGEMENT-03

 Let us we discuss about user management another type managing privilege  

what is privilege / User privilege?

- Privilege is nothing but to give users  access rights  for that particular objects  in oracle

- Privileges are  granted or revoked either by the instance administrator user

- A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object

 

Types of Privileges

privilege types

*System privilege or System level privilege *Object Privilege or object level privilege  

Object Privilege ( with grant option )

-The listed of objects are assigned to roles or users

Privilege  Description

SELECT               Privilege to perform SELECT statements on the table.

INSERT                Privilege to perform INSERT statements on the table.

 

UPDATE               Privilege to perform UPDATE statements on the table.

DELETE                Privilege to perform DELETE statements on the table.

REFERENCES      Privilege to create a constraint that refers to the table.

ALTER                  Privilege to perform ALTER TABLE statements to change the table definition.

INDEX                  Privilege to create an index on the table with the create index statement.

ALL                       All privileges on table.    

Views of object privs

1)USER_TAB_PRIVS

2)ALL_TAB_PRIVS

3)ROLE_TAB_PRIVS

4)DBA_TAB_PRIVS

With grant option and cascading revoke 

S-1 connect  our database as sys  db user

[oracle@localhost ~]$ sqlplus / as sysdba

S-2 create sample schemas

SQL> create user u01 identified by u01;

User created.

Grant  rights to them

SQL> grant create session, create table to u01;

Grant succeeded.

create schema 'u02'

SQL> create user u01 identified by u02;

User created.

grant object rights to u02

SQL> grant create session to u02;

Grant succeeded.

create schema 'u03'

SQL> create user u01 identified by u03;

User created.

grant  rights to u03

SQL> grant create session to u03;

Grant succeeded.

S-3 connect u01

SQL>conn u01/u01

connected.

create table t0001 from u01

SQL> create table t0001 (col1 number, col2 varchar2(100));

Table created

to give  with grant option to u02 user

SQL>grant select on t0001 to u02  with grant option;

Grant succeeded.

Check user_tab_privs

SQL> column grantee format a7

SQL> column owner format a5

SQL> column table_name format a10

SQL> column grantor format a7

SQL> column privilege format a9

SQL> column grantable format a9

SQL> select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE

------- ----- ---------- ------- --------- --------- ---

U02       U01 T0001      U01       SELECT    YES       NO

S-4 connect u02

SQL>conn u02/u02

connected.

grant privilege  select u01.t0001 to u02

SQL> grant select on u01.t0001 to u03;

Grant succeeded.

S-5 connect user u03

SQL>conn u03/u03

connected.

check the privilege given table

SQL>select * from u01.t0001;

no rows selected.

s-6 connect the user session  u01 and check the user _tab_privs

SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE

------- ----- ---------- ------- --------- --------- ---

U03      U01    T0001      U02   SELECT    NO        NO

U02      U01    T0001      U01   SELECT    YES       NO

s-7 revoke 'With  grant  option ' from user u01

SQL> revoke select toool from u02;

Revoke succeeded.