SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 28 08:09:33 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba;
Connected.
============================================================================================
DBA_SYS_PRIVS
============================================================================================
SQL> create user moon identified by kumar;
User created.
SQL> grant create session to moon;
Grant succeeded.
SQL> grant create any table to moon;
Grant succeeded.
SQL> grant unlimited tablespace to moon;
Grant succeeded.
SQL> grant create tablespace to moon;
Grant succeeded.
SQL> grant drop tablespace to moon;
Grant succeeded.
SQL> grant alter tablespace to moon;
Grant succeeded.
SQL> grant restricted session to moon;
Grant succeeded.
SQL> grant select any table to moon;
Grant succeeded.
SQL>
SQL> desc dba_sys_privs;
Name Null? Type
----------------------------------------- -------- ---------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select * from dba_sys_privs where grantee='MOON';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MOON CREATE SESSION NO
MOON DROP TABLESPACE NO
MOON ALTER TABLESPACE NO
MOON CREATE ANY TABLE NO
MOON SELECT ANY TABLE NO
MOON CREATE TABLESPACE NO
MOON RESTRICTED SESSION NO
MOON UNLIMITED TABLESPACE NO
8 rows selected.
============================================================================================ DBA_TAB_PRIVS
===========================================================================================
SQL> create user amit identified by kumar;
User created.
SQL> grant select on scott.emp to amit;
Grant succeeded.
SQL> grant create table on scott.emp to amit;
grant create table on scott.emp to amit
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> grant insert on scott.emp to amit;
Grant succeeded.
SQL> grant update on scott.emp to amit;
Grant succeeded.
SQL> grant insert,delete,select,update on scott.emp to amit;
Grant succeeded.
SQL> grant update on scott.DEPT to amit;
Grant succeeded.
SQL> grant insert,delete,select,update on scott.DEPT to amit;
Grant succeeded.
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)
SQL> COLUMN GRANTEE FORMAT A10;
SQL> COLUMN GRANTOR FORMAT A10;
SQL> COLUMN TABLE_NAME FORMAT A10;
SQL> COLUMN PRIVILEGE FORMAT A10;
SQL> select grantee,privilege,table_name,grantor from dba_tab_privs where grantee
e='AMIT';
GRANTEE PRIVILEGE TABLE_NAME GRANTOR
---------- ---------- ---------- ----------
AMIT UPDATE DEPT SCOTT
AMIT DELETE DEPT SCOTT
AMIT INSERT DEPT SCOTT
AMIT SELECT DEPT SCOTT
AMIT SELECT EMP SCOTT
AMIT INSERT EMP SCOTT
AMIT UPDATE EMP SCOTT
AMIT DELETE EMP SCOTT
8 rows selected.
============================================================================================= DBA_ROLE_PRIVS
=========================================================================================
SQL> create user rajan identified by kumar;
User created.
SQL> grant connect to rajan;
Grant succeeded.
SQL> grant resource to rajan;
Grant succeeded.
SQL> grant dba to rajan;
Grant succeeded.
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)
SQL> select * from dba_role_privs where grantee='RAJAN';
GRANTEE GRANTED_ROLE ADM DEF
---------- ------------------------------ --- ---
RAJAN DBA NO YES
RAJAN CONNECT NO YES
RAJAN RESOURCE NO YES
============================================================================================
WITH ADMIN OPTION
============================================================================================
SQL> create user ravi identified by kumar;
User created.
SQL> grant create session to ravi with admin option;
Grant succeeded.
SQL> grant create TABLE to ravi with admin option;
Grant succeeded.
SQL> select * from dba_SYS_privs where grantee='RAVI';
GRANTEE PRIVILEGE ADM
---------- ---------- ---
RAVI CREATE TAB YES
LE
RAVI CREATE SES YES
SION
SQL> CONN RAVI/KUMAR;
Connected.
SQL> GRANT CREATE SESSION TO AMIT;
Grant succeeded.
SQL> GRANT CREATE TABLE TO AMIT;
Grant succeeded.
SQL> CONN /AS SYSDBA;
Connected.
SQL> select * from dba_SYS_privs where grantee='RAVI';
GRANTEE PRIVILEGE ADM
---------- ---------- ---
RAVI CREATE TAB YES
LE
RAVI CREATE SES YES
SION
SQL> select * from dba_SYS_privs where grantee='AMIT';
GRANTEE PRIVILEGE ADM
---------- ---------- ---
AMIT CREATE TAB NO
LE
AMIT CREATE SES NO
SION
SQL> REVOKE CREATE TABLE FROM RAVI;
Revoke succeeded.
SQL> REVOKE CREATE SESSION FROM RAVI;
Revoke succeeded.
SQL> select * from dba_SYS_privs where grantee='RAVI';
no rows selected
SQL> select * from dba_SYS_privs where grantee='AMIT';
GRANTEE PRIVILEGE ADM
---------- ---------- ---
AMIT CREATE TAB NO
LE
AMIT CREATE SES NO
SION
============================================================================================= WITH GRANT OPTION
===========================================================================================
SQL> CREATE USER RAJ IDENTIFIED BY KUMAR;
User created.
SQL> GRANT SELECT ON SCOTT.EMP TO RAJ WITH GRANT OPTION;
Grant succeeded.
SQL> GRANT SELECT ON SCOTT.DEPT TO RAJ WITH GRANT OPTION;
Grant succeeded.
SQL> GRANT INSERT,UPDATE,DELETE ON SCOTT.DEPT TO RAJ WITH GRANT OPTION;
Grant succeeded.
SQL> GRANT INSERT,UPDATE,DELETE ON SCOTT.EMP TO RAJ WITH GRANT OPTION;
Grant succeeded.
SQL> SELECT GRANTEE,PRIVILEGE,TABLE_NAME,GRANTOR FROM DBA_TAB_PRIVS WHERE GRANTE
E='RAJ';
GRANTEE PRIVILEGE TABLE_NAME GRANTOR
---------- ---------- ---------- ----------
RAJ SELECT DEPT SCOTT
RAJ DELETE DEPT SCOTT
RAJ INSERT DEPT SCOTT
RAJ UPDATE DEPT SCOTT
RAJ SELECT EMP SCOTT
RAJ DELETE EMP SCOTT
RAJ INSERT EMP SCOTT
RAJ UPDATE EMP SCOTT
8 rows selected.
SQL>
SQL> CONN RAJ/KUMAR;
Connected.
SQL> GRANT INSERT,UPDATE,DELETE ON SCOTT.EMP TO MOON;
Grant succeeded.
SQL> GRANT INSERT,UPDATE,DELETE ON SCOTT.DEPT TO MOON;
Grant succeeded.
SQL> GRANT SELECT ON SCOTT.EMP TO MOON ;
Grant succeeded.
SQL> GRANT SELECT ON SCOTT.DEPT TO MOON ;
Grant succeeded.
SQL> CONN /AS SYSDBA;
Connected.
SQL> SELECT GRANTEE,PRIVILEGE,TABLE_NAME,GRANTOR FROM DBA_TAB_PRIVS WHERE GRANTE
E='MOON';
GRANTEE PRIVILEGE TABLE_NAME GRANTOR
---------- ---------- ---------- ----------
MOON SELECT DEPT RAJ
MOON DELETE DEPT RAJ
MOON INSERT DEPT RAJ
MOON UPDATE DEPT RAJ
MOON SELECT EMP RAJ
MOON INSERT EMP RAJ
MOON UPDATE EMP RAJ
MOON DELETE EMP RAJ
8 rows selected.
SQL>
SQL> REVOKE SELECT,INSERT,UPDATE,DELETE ON SCOTT.EMP FROM RAJ ;
Revoke succeeded.
SQL> SELECT GRANTEE,PRIVILEGE,TABLE_NAME,GRANTOR FROM DBA_TAB_PRIVS WHERE GRANTE
E='RAJ';
GRANTEE PRIVILEGE TABLE_NAME GRANTOR
---------- ---------- ---------- ----------
RAJ SELECT DEPT SCOTT
RAJ DELETE DEPT SCOTT
RAJ INSERT DEPT SCOTT
RAJ UPDATE DEPT SCOTT
SQL> SELECT GRANTEE,PRIVILEGE,TABLE_NAME,GRANTOR FROM DBA_TAB_PRIVS WHERE GRANTE
E='MOON';
GRANTEE PRIVILEGE TABLE_NAME GRANTOR
---------- ---------- ---------- ----------
MOON SELECT DEPT RAJ
MOON DELETE DEPT RAJ
MOON INSERT DEPT RAJ
MOON UPDATE DEPT RAJ
SQL>
No comments:
Post a Comment