Friday , July 21 2017
Home / Oracle DBA / USER MANAGEMENT

USER MANAGEMENT

USER MANAGEMENT

  • User creation should be done after clearly understanding requirement from application team.
  • Whenever we create user, we should assign a default permanent tablespace (which allows to create tables) and default temporary tablespace(which allows to do sorting). At any moment of time we can change them.
  • After creating user, don’t grant connect and resource roles (in 9i). In 10g, we can grant connect role as it contains only create session privilege.
  • Resource role internally contains unlimited tablespace privilege and because of this, it will override the quota that is granted initially. So, it should not be granted in real time until it is required.
  • Privileges for a user are of 2 types.
  • System level privs – eg: create table, create view etc.
  • Object level privs – eg: select on A, update on B etc.
  • A role is a set of privileges which will reduce the risk of issuing many commands.
  • To findout roles and privileges assigned to a user, use following views
    1. DBA_SYS_PRIVS
    2. DBA_TAB_PRIVS
    3. DBA_ROLE_PRIVS
    4. ROLE_SYS_PRIVS
    5. ROLE_TAB_PRIVS
    6. ROLE_ROLE_PRIVS
  • System level privileges can be granted with admin option so that the grantee can grant the same privilege to other users. If we revoke the privilege from first grantee, still others will have that privilege.
  • Object level privileges can be granted with grant option so that grantee can grant the same privilege to other users. If we revoke the privilege from first grantee, it will revoked from all the users.
COMMANDS
# To create a user
SQL> create user user1 identified by user1
         default tablespace mytbs
         temporary tablespace temp;
# To grant permissions to user
SQL> grant create session, create table to user1;
# To grant permissions to user with admin option
SQL> grant create table to scott with admin option;
# To grant permissions to user with grant option
SQL> grant update on scott.salary to user1 with grant option;
# Torevoke any permissions from user
SQL> revoke create table from scott;
# To change password of user
SQL> alter user user1 identified by oracle;
# To allocate quota on tablespace
SQL> alter user user1 quota 10m on mydata;
Note: Allocating quota doesn’t represent reserving the space. If 2 or more users are sharing a tablespace, quota will filled up in first come first serve basis.
# To change default tablespace or temporary tablespace
SQL> alter user user1 default tablespace test;
SQL> alter user user1 default temporary tablespace mytemp;
Note: The objects created in the old tablespace remain unchanged even after changing a default tablespace for a user.
# To check default permanent & temporary tablespace for a user
SQL> select default_tablespace,temporary_tablespace from dba_users where username=’SCOTT’;
# To lock or unlock a user
SQL> alter user scott account lock;
SQL> alter user scott account unlock;
# To check default permanent tablespace and temporary tablespace
SQL> select property_name,property_value from database_properties where property_name like ‘DEFAULT%’;
# To change default permanent tablespace
SQL> alter database default tablespace mydata;
# To change default temporary tablespace
SQL> alter database default temporary tablespace mytemp;
# To check system privileges for a user
SQL> select privilege from dba_sys_privs where grantee=’SCOTT’;
# To check object level privileges
SQL> select owner,table_name,privilege from dba_tab_privs where grantee=’SCOTT’;
# To check roles assigned to a user
SQL> select granted_role from dba_role_privs where grantee=’SCOTT’;
# To check permissions assigned to role
SQL> select privilege from role_sys_privs where role=’MYROLE’;
SQL> select owner,table_name,privilege from role_tab_privs where role=’MYROLE’;
SQL> select granted_role from role_role_privs where role=’MYROLE’;
# To drop a user
SQL> drop user user1;
Or
SQL> drop user user1 cascade;
Read More:
UNDO MANAGEMENT
BIGFILE TABLESPACE
LOCAL Vs DICTIONARY managed tablespaces

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *