User Management

In Oracle an user and schema are similar.

User creation:

• User/schema can be created with a simple create user command as below.
          SQL> CREATE USER username identified by password;

But this create user has many other options like:

1. default tablespace
2. temporary tablespace
3. quota
4. profile
5. password 

• The above create user options can be used as below
          SQL> CREATE USER username
                           IDENTIFIED BY password
                           DEFAULT TABLESPACE default_tabs
                           QUOTA 10M ON default_tabs
                           TEMPORARY TABLESPACE temp_tbs
                           QUOTA 5M ON system     
                           PROFILE profile_name 
                           PASSWORD EXPIRE;

There are some default roles to be given to the user when they are created they are,

• Grant connect role only then user can connect to the database,
  and also resource to use the objects inside the schema using the below query.
          SQL> grant connect, resource to username;

Note: When you grant resource role to a user revoke unlimited tablespace privilege from the user and grant the required quota to that user on a tablespace.

Drop User:

• User can be drop using the below query if its has no objects
          SQL> DROP USER username ;

• if the user/schema has some objects need to drop the user, then can use the below query to drop the user along with the objects.
          SQL> DROP USER username CASCADE;

Some of the alter user commands used for user management tasks:

• To change the password and changing the default tablespace.
          SQL> ALTER USER username
                            IDENTIFIED BY another_password
                            DEFAULT TABLESPACE default_tabs; 

• To change the profile of a user.
          SQL> ALTER USER username PROFILE new_profile; 

• To grant the default roles to a user.
          SQL> ALTER USER username DEFAULT ROLE ALL EXCEPT dw_manager; 

• To make the password expire
          SQL> ALTER USER username PASSWORD EXPIRE;

• To change the temporary tablespace.
          SQL> ALTER USER username TEMPORARY TABLESPACE temp_tbs ;

• To grant quota for a user on a tablespace.
          SQL> ALTER USER username quota 10M on default_tabs;

Granting role/privileges to a user:

There are some default roles to be granted to a user when they are create they are connect and resource

• Grant connect role only then user can connect to the database,
  and also resource to use the objects inside the schema using the below query.
          SQL> grant connect, resource to username;

Note: When you grant resource role to a user revoke unlimited tablespace privilege from the user and grant the required quota to that user on a tablespace, as resource role include unlimited tablespace privilege by default so revoke it.

• Grant syntax is
           SQL> grant privilage1, privilege2, role1, role2  ... on schema.tablename to username;
  The above command grant the list of permissions on the tablename which is in schema  to username.  
  In the above command privileges can be like select, insert, update ...
  roles are nothing but bunch of privileges, instead of assigning 100s of privileges to 100 users 
• Create a role with that privileges and assign that role to 100 users which is an easy task.
          SQL> CREATE ROLE rolename;
          SQL> grant privilage1, privilege2, privilage3, privilege4 to rolename;

• Revoke Syntax is
          SQL> revoke privilage1, privilege2, role1, role2  ... on schema.tablename from username;

• ROLE_ROLE_PRIVS --- describes the roles granted to other roles.
• ROLE_SYS_PRIVS --- describes system privileges granted to roles.

Checking the roles/privileges granted to a user:

If you want to check the current user details like roles and privileges granted use the below queries;

Let Suppose,
        In this session you are connected to user called charan

• To check the privilages granted to charan use the below query;
         SQL> select * from SESSION_PRIVS;

• To check the roles granted to charan use the below query;
         SQL> select * from SESSION_ROLES;
  
  

One can also check the roles and privilages granted to user using some tables mentioned below

• Tables that show the details of current connected user privilages granted and roles granted to it respectively;

1. user_sys_privs --- what privilages are granted to the current user.
2. user_role_privs --- what roles are granted to current user.
3. user_tab_privs --- which user has what access to the tables present in current user/schema. 

• Tables that show /describes all object grants in the database.

1. dba_sys_privs --- what privilages are granted to the users in database.
2. dba_role_privs --- what roles are granted to the users in database.
3. dba_tab_privs --- which user has what access to the tables present in database.
   
   

• Columns in ***_sys_privs are grantee, privilege, admin_option(yes/no)
              grantee - to whom the the privilege/ role is granted to.
              privilege - list of privileges granted to the user/grantee.

• Columns in ***_role_privs are grantee, granted_role, admin_option(yes/no), default_role(yes/no)
              grantee/username - to whom the the  role/privilege is granted to.
              granted_role - what role is granted to the user /grantee is seen here.

• Columns in ***_tab_privs are grantee, owner, table_name, grantor, privilege, grantable(yes/no), hierarchy(yes/no)
               grantee - to whom the the  role/privilege is granted to.
               owner - is the owner of the object/table
               grantor - name of the user who performed grant.
               privilege - what type of privilege is given