Flowers To India

Flowers To India
Send cakes,flowers to India

Saturday, January 3, 2009

ROLE MANAGEMENT

1) How to create a role

Create role  role_name

            Create role satish_role

2) Create Password Protected Role

             Create role role name identified by  password

3) How to Assign Privilege To A Role

             GRANT privilege_name TO role_name;

             GRANT create session TO role_name

     You can give grant role to role

            Grant  to  

4) How to Assigning Roles To Users

            GRANT  roles_name  TO  user_name;

5) How to Revoking Privileges From Roles

REVOKE FROM ;

6) How to Revoke a role from a user

REVOKE  role_name  FROM  user_name;

7) How Activating A Role

SET ROLE  role_name;

8) How Activating A Password Protected Role

SET  ROLE  role_name  IDENTIFIED BY role_password;

9) How to Activating All Roles

SET ROLE all;

10) How to Activating All Roles Except One

SET ROLE all EXCEPT  role_name;

11) How to Deactivating A Role

Can not be done on an individual basis

12) How Deactivating All Roles

SET ROLE none;

13) How to Dropping A Role

DROP ROLE  role_name;

14) How to Creating And Assigning The PLUSTRACE Role Used By AUTOTRACE

            This role must be created by SYS and grants SELECT on the following v_$ views:

·         V$SESSTAT

·         V$STATNAME

·         V$MYSTAT

SQL> @c:\oracle\product\ora10\sqlplus\admin\plustrce.sql
GRANT plustrace TO uwclass;

15) User$

In this user$ table we can see all roles in our database

16) role_sys_privs

In this role_sys_privs table we can see which Privileges Granted To A Role

17) dba_role_privs

In this dba_role_privs  we can see which Roles Granted To Schemas

18) session_roles

In this session_roles we can see the present session have which roles

19) session_prives

In this session_prives table we can see the present session have which privelags

20) dba_roles

            In this dba_roles table we can see the dba user contain which roles

21) dba_sys_privs

In this dba_sys_privs table we can see all the system privileges

ORA-01045) this error will be come due to insufficient privs

 

all_tab_privs_made  in this table you can see all tables privs

user_tab_privs_made