oracle dba ------CREATE USER Database User Management

CREATE USER user
   IDENTIFIED { BY password
              | EXTERNALLY [ AS 'certificate_DN' ]
              | GLOBALLY [ AS '[ directory_DN ]' ]
              }
   [ DEFAULT TABLESPACE tablespace
   | TEMPORARY TABLESPACE
        { tablespace | tablespace_group_name }
   | { QUOTA { size_clause | UNLIMITED } ON tablespace }...
EDO   | PROFILE profile
   | PASSWORD EXPIRE
   | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
          { tablespace | tablespace_group_name }
     | { QUOTA { size_clause | UNLIMITED } ON tablespace }...
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK }
     ]...
  ] ;



CREATE AND DROP USER:
create user shaan identified by moon
default tablespace rtbs
temporary tablespace temp
quota 30m on rtbs;
Above command creates a user shaan with password moon. Consider the tablespace
you have in which shaan will store his data is "RTBS". The tablespace used for
storing temporary segments will be "TEMP" and the amount of space which the user
shaan can use on "RTBS" tablespace is 30M.
SQL> DROP USER SHAAN CASCADE;

ALTERING/UNLOCKING ACCOUNT:
SQL> alter user shaan identified by moon account unlock;
The above command alters or unlocks the "SHAAN" user with password "moon".
SQL> select username, account_status, default_tablespace,
temporary_tablespace, profile from dba_users
where username = 'HRMS';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
------ -------------- ------------------ -------------------- ---------
HRMS      OPEN           MUJ_HRMS_DBF      TEMP                 DEFAULT
The above query shows the account information related to "HRMS".

ALTERING TABLEPSACE QUOTA:
SQL> select * from dba_ts_quotas where username = 'HRMS';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
--------------- -------- ----- --------- ------ ----------
MUJ_HRMS_DBF    HRMS     198901760 -1    24280  -1
SQL> alter user shaan quota 40m on RTBS;‎

GRANTING AND REVOKING PRIVILEGES:
SQL> GRANT create table to SHAAN;‎
SQL> GRANT create session to SHAAN;
SQL> GRANT create any table, create tablespace to SHAAN;
SQL> REVOKE create any table from SHAAN;
SQL> REVOKE create tablespace from SHAAN;
SQL> GRANT select, insert, update, delete on HRSM.PAY_PAYMENT_MASTER to HRMS;
SQL> REVOKE update,delete on HRMS.PAY_PAYMENT_MASTER from HRMS;

ROLES:
SQL>create role MY_ROLE;
SQL> GRANT create any table, alter any table, drop any table, select any table, update any table,
delete any table to MY_ROLE;
SQL> GRANT create any index, alter any index, drop any index to MY_ROLE
SQL> GRANT alter session, restricted session to MY_ROLE;
SQL> GRANT create tablespace, alter tablespace, drop tablespace, unlimited tablespace
to MY_ROLE;
SQL> GRANT select, insert, update, delete on HRMS.PAY_PAYMENT_MASTER to MY_ROLE;
SQL> GRANT MY_ROLE to SHAAN;
SQL> select * from dba_sys_privs where grantee = 'SHAAN';
GRANTEE                        PRIVILEGE            ADM
----------------------         -------------------- ---
SHAAN                          CREATE TABLE         NO
SHAAN                          CREATE SESSION       NO
SQL> select * from dba_sys_privs where grantee = 'MY_ROLE'‎
GRANTEE                        PRIVILEGE                   ADM
----------------------         --------------------------- ---
MY_ROLE                        DELETE ANY TABLE            NO
MY_ROLE                        CREATE ANY TABLE            NO
MY_ROLE                        DROP TABLESPACE             NO
MY_ROLE                        ALTER TABLESPACE            NO
MY_ROLE                        ALTER ANY INDEX             NO
MY_ROLE                        DROP ANY TABLE              NO
MY_ROLE                        DROP ANY INDEX              NO
MY_ROLE                        UPDATE ANY TABLE            NO
MY_ROLE                        ALTER SESSION               NO
MY_ROLE                        SELECT ANY TABLE            NO
MY_ROLE                        RESTRICTED SESSION          NO
MY_ROLE                        CREATE ANY INDEX            NO
MY_ROLE                        ALTER ANY TABLE             NO
MY_ROLE                        UNLIMITED TABLESPACE        NO
MY_ROLE                        CREATE TABLESPACE           NO
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
‎where grantee = 'SHAAN';‎
GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
-------- ------ ------------ ---------  ---------
HRMS     HRMS  PAY_PAYMENT_MASTER HRMS  SELECT
HRMS     HRMS  PAY_PAYMENT_MASTER HRMS  INSERT
SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
where grantee = 'MY_ROLE'‎;
GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
-------- ------ ------------ ---------  ---------
MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  UPDATE
MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  SELECT
MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  INSERT
MY_ROLE  HRMS  PAY_PAYMENT_MASTER HRMS  DELETE
SQL> select * from dba_roles where role = 'MY_ROLE';‎
ROLE                  PASSWORD AUTHENTICAT
--------------------- -------- -----------
MY_ROLE               NO       NONE
SQL> select * from dba_role_privs where grantee = 'SHAAN';‎
GRANTEE                        GRANTED_ROLE    ADM DEF
------------------------       --------------- --- ---
SHAAN                          MY_ROLE         NO  YES
SQL> select * from role_sys_privs where role = 'MY_ROLE';‎
ROLE                  PRIVILEGE                       ADM
--------------------- ------------------------------- ---
MY_ROLE               DROP TABLESPACE                 NO
MY_ROLE               CREATE ANY TABLE                NO
MY_ROLE               DELETE ANY TABLE                NO
MY_ROLE               ALTER TABLESPACE                NO
MY_ROLE               DROP ANY TABLE                  NO
MY_ROLE               ALTER ANY INDEX                 NO
MY_ROLE               UPDATE ANY TABLE                NO
MY_ROLE               DROP ANY INDEX                  NO
MY_ROLE               ALTER SESSION                   NO
MY_ROLE               RESTRICTED SESSION              NO
MY_ROLE               SELECT ANY TABLE                NO
MY_ROLE               CREATE TABLESPACE               NO
MY_ROLE               UNLIMITED TABLESPACE            NO
MY_ROLE               ALTER ANY TABLE                 NO
MY_ROLE               CREATE ANY INDEX                NO
SQL> select * from role_tab_privs where role = 'MY_ROLE';‎Code:
ROLE     OWNER   TABLE_NAME   COLUMN_NAME   PRIVILEGE   GRA
-------- ------- ------------ ------------- ----------- ---
MY_ROLE  HRMS   PAY_PAYMENT_MASTER          DELETE       NO
MY_ROLE  HRMS   PAY_PAYMENT_MASTER          UPDATE       NO
MY_ROLE  HRMS   PAY_PAYMENT_MASTER          SELECT       NO
MY_ROLE  HRMS   PAY_PAYMENT_MASTER          INSERT       NO
SQL> revoke MY_ROLE from SHAAN;

PROFILES:
SQL> create profile DEVELOPER limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_grace_time 5
idle_time 30;

SQL> alter user SHAAN profile developer;
SQL> select * from dba_profiles where profile = 'DEVELOPER';
PROFILE     RESOURCE_NAME                    RESOURCE LIMIT
----------- --------------                   -------- -----
DEVELOPER   COMPOSITE_LIMIT                  KERNEL   DEFAULT
DEVELOPER   SESSIONS_PER_USER                KERNEL   DEFAULT
DEVELOPER   CPU_PER_SESSION                  KERNEL   DEFAULT
DEVELOPER   CPU_PER_CALL                     KERNEL   DEFAULT
DEVELOPER   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
DEVELOPER   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
DEVELOPER   IDLE_TIME                        KERNEL   30
DEVELOPER   CONNECT_TIME                     KERNEL   DEFAULT
DEVELOPER   PRIVATE_SGA                      KERNEL   DEFAULT
DEVELOPER   FAILED_LOGIN_ATTEMPTS            PASSWORD 3
DEVELOPER   PASSWORD_LIFE_TIME               PASSWORD 30
DEVELOPER   PASSWORD_REUSE_TIME              PASSWORD 30
DEVELOPER   PASSWORD_REUSE_MAX               PASSWORD DEFAULT
DEVELOPER   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
DEVELOPER   PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEVELOPER   PASSWORD_GRACE_TIME              PASSWORD 5
SQL> drop profile developer cascade;