User is basically used to connect to database. All db objects like table,index,view etc can be created under that user.In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

1. Create a user:

create user DEV_CLASS identified by DEV_CLASS#1234
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

Minimum privilege required to connect to a database is create session

grant create session to DEV_CLASS;

2. Change password of a user:

alter user DEV_CLASS identified by DEV_CLASS#91234;

3. Lock/unlock a user

alter user dev_class account lock;

alter user dev_class account unlock;

4. Make a user password expiry:

When we make a user id expiry, then when the user does login, it will prompt him to set a new password.

alter user dev_class account expire;

5. Changing default tablespace of a user:

select username,default_tablespace from dba_users where username='DEV_CLASS';

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
DEV_CLASS                   USERS

alter user  DEV_CLASS default tablespace DATATS;

select username,default_tablespace from dba_users where username='DEV_CLASS';

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
DEV_CLASS                   DATATS

6. Changing default TEMP tablespace of a user:

SQL>  select username,TEMPORARY_TABLESPACE from dba_users where username='DEV_CLASS';
USERNAME TEMPORARY_TABLESPACE
----------------------- ------------------------------
DEV_CLASS TEMP

alter user DEV_CLASS temporary tablespace TEMP2;

SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='DEV_CLASS';

USERNAME TEMPORARY_TABLESPACE
----------------------- ------------------------------
DEV_CLASS TEMP2

PROFILE:

A profile enforces set of password security rules and resource usage limit.
While creating a user if no profile is mentioned, then DEFAULT profile will be assigned.

DEFAULT PROFILE SETTING:

col limit for a12
col profile for a14
set lines 200
set pagesize 200
select profile,resource_name,RESOURCE_TYPE,limit from dba_profiles where profile='DEFAULT';

PROFILE        RESOURCE_NAME                    RESOURCE LIMIT
-------------- -------------------------------- -------- ------------
DEFAULT        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT        PASSWORD_GRACE_TIME              PASSWORD 7

*SESSION_PER_USER – No. of allowed concurrent sessions for a user
*CPU_PER_SESSION – CPU time limit for a session, expressed in hundredth of seconds.
*CPU_PER_CALL – Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
*CONNECT_TIME – Specify the total elapsed time limit for a session, expressed in minutes.
*IDLE_TIME – Specify the permitted periods of continuous inactive time during a session, expressed in minutes.
*LOGICAL_READS_PER_SESSION – Specify the permitted number of data blocks read in a session, including blocks read from memory and disk
*LOGICAL_READS_PER_CALL –permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
*PRIVATE_SGA – SGA a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

*FAILED_LOGIN_ATTEMPTS – No. of failed attempts to log in to the user account before the account is locked
*PASSWORD_LIFE_TIME: No. of days the account will be open. after that it will expiry.
*PASSWORD_REUSE_TIME: number of days before which a password cannot be reused
*PASSWORD_REUSE_MAX: number of days before which a password can be reused
*PASSWORD_LOCK_TIME: Number of days the user account remains locked after failed login
*PASSWORD_GRACE_TIME: Number of grace days for user to change password
*PASSWORD_VERIFY_FUNCTION: PL/SQL that can be used for password verification

8. Create a new profile:

CREATE PROFILE "APP_PROFILE"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME 90
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 180
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION NULL
         PASSWORD_LOCK_TIME UNLIMITED
         PASSWORD_GRACE_TIME UNLIMITED;

9. Alter a profile:

ALTER PROFILE APP_PROFILE LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;

10. Change profile of an user:

SQL> select username,profile from dba_users where username='DEV_CLASS';
USERNAME PROFILE
----------------------- ------------------------------
DEV_CLASS DEFAULT

ALTER USER SCOTT PROFILE APP_PROFILE;

SQL> select username,profile from dba_users where username='DEV_CLASS';

USERNAME PROFILE
----------------------- ------------------------------
DEV_CLASS APP_PROFILE

11. How to make a user non-expiry:

Usually application users we need to set non-expiry. I.e it will never expire. To set it, we need to either create a profile with PASSWORD_LIFE_TIME UNLIMITED or alter the profile of that user.

SQL> select username,profile,EXPIRY_DATE from dba_users where username='DEV_CLASS';
USERNAME PROFILE EXPIRY_DATE
----------------------- ----------------------- ---------
DEV_CLASS APP_PROFILE 16-AUG-17

ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

SQL> select username,profile,EXPIRY_DATE from dba_users where username='DEV_CLASS';

USERNAME PROFILE EXPIRY_DATE
----------------------- ----------------------- ---------
DEV_CLASS APP_PROFILE

PRIVILEGES:

A privilege is a permission to execute either a particular type of sql statements or to perform particular action on database objects.

Two type of privilege:
1. SYSTEM PRIVILEGE
2. OBJECT PRIVILEGE

SYSTEM PRIVILEGE

A system privilege is the right to perform a particular action or to perform an action on any object of a particular type.

12.List of all system privileges:

SQL>select distinct privilege from dba_sys_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE OPERATOR
CREATE VIEW
CREATE ANY PROCEDURE
CREATE DATABASE LINK
DEQUEUE ANY QUEUE
DEBUG ANY PROCEDURE
CREATE PUBLIC SYNONYM
SELECT ANY TRANSACTION
READ ANY TABLE
CREATE ASSEMBLY
EXECUTE ANY INDEXTYPE
CREATE ANY TYPE
ANALYZE ANY
DROP PUBLIC SYNONYM
AUDIT SYSTEM
EXECUTE ANY ASSEMBLY
CREATE ANY EDITION
ADMINISTER ANY SQL TUNING SET
DROP ANY RULE SET
CREATE ANY EVALUATION CONTEXT
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
GRANT ANY PRIVILEGE
ALTER RESOURCE COST
ALTER ANY TRIGGER
DROP ANY SYNONYM
CREATE USER
CREATE SQL TRANSLATION PROFILE
EM EXPRESS CONNECT
CREATE ANY TRIGGER
EXEMPT REDACTION POLICY
CREATE DIMENSION
CREATE RULE SET
EXECUTE ANY EVALUATION CONTEXT
ALTER ANY OUTLINE
UNDER ANY TYPE
ALTER ANY ROLE
CREATE ANY MINING MODEL
DROP ANY OUTLINE
ALTER ANY INDEX
UPDATE ANY TABLE
CREATE TABLESPACE
USE ANY SQL TRANSLATION PROFILE
DROP ANY VIEW
CREATE ANY SQL TRANSLATION PROFILE
BECOME USER
DROP ANY MEASURE FOLDER
CREATE ANY CUBE
CREATE ANY OUTLINE
COMMENT ANY MINING MODEL
ALTER ANY INDEXTYPE
DROP PROFILE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE JOB
EXEMPT ACCESS POLICY
QUERY REWRITE
EXECUTE ANY RULE SET
CREATE PLUGGABLE DATABASE
ALTER ANY CUBE
ALTER ANY RULE SET
UNDER ANY VIEW
DROP ANY PROCEDURE
CREATE ROLE
CREATE ANY TABLE
RESTRICTED SESSION
ALTER ANY MEASURE FOLDER
ADVISOR
IMPORT FULL DATABASE
DROP ANY TRIGGER
ALTER ANY PROCEDURE
SELECT ANY SEQUENCE
CREATE ANY CONTEXT
UNDER ANY TABLE
ALTER PROFILE
FORCE TRANSACTION
DROP ANY MINING MODEL
CREATE ANY OPERATOR
CREATE PUBLIC DATABASE LINK
MANAGE ANY FILE GROUP
MANAGE TABLESPACE
CREATE CUBE DIMENSION
UNLIMITED TABLESPACE
SELECT ANY TABLE
CREATE EVALUATION CONTEXT
ON COMMIT REFRESH
CREATE ANY INDEX
EXECUTE ANY PROGRAM
ALTER ANY CUBE BUILD PROCESS
CREATE ANY MEASURE FOLDER
EXECUTE ASSEMBLY
CREATE ANY SQL PROFILE
ALTER ANY TYPE
CREATE PROFILE
EXECUTE ANY PROCEDURE
CREATE ANY CLUSTER
CREATE ANY ASSEMBLY
CREATE ANY RULE
EXECUTE ANY TYPE
ALTER ANY CLUSTER
DROP ANY CUBE
DROP PUBLIC DATABASE LINK
SELECT ANY MEASURE FOLDER
REDEFINE ANY TABLE
SELECT ANY CUBE
CREATE ANY INDEXTYPE
CREATE ANY CUBE DIMENSION
EXEMPT DDL REDACTION POLICY
MANAGE SCHEDULER
ALTER SESSION
CREATE TRIGGER
CREATE MATERIALIZED VIEW
ALTER ANY SEQUENCE
EXEMPT IDENTITY POLICY
CREATE ANY CREDENTIAL
SET CONTAINER
GLOBAL QUERY REWRITE
ALTER ANY LIBRARY
GRANT ANY ROLE
ALTER USER
CREATE MEASURE FOLDER
UPDATE ANY CUBE
READ ANY FILE GROUP
GRANT ANY OBJECT PRIVILEGE
DROP ANY OPERATOR
CREATE CREDENTIAL
CHANGE NOTIFICATION
CREATE ANY SYNONYM
INSERT ANY TABLE
EXEMPT DML REDACTION POLICY
EXECUTE ANY RULE
INSERT ANY MEASURE FOLDER
DROP ANY CUBE DIMENSION
ALTER ANY ASSEMBLY
LOGMINING
CREATE ANY VIEW
CREATE TYPE
FLASHBACK ARCHIVE ADMINISTER
ADMINISTER SQL MANAGEMENT OBJECT
ALTER ANY MINING MODEL
SELECT ANY MINING MODEL
CREATE EXTERNAL JOB
DROP ANY EVALUATION CONTEXT
CREATE LIBRARY
DROP ANY SQL TRANSLATION PROFILE
CREATE MINING MODEL
DROP ANY CONTEXT
MANAGE ANY QUEUE
DROP ANY DIMENSION
CREATE ANY DIMENSION
CREATE ANY LIBRARY
DROP ANY MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER DATABASE
DROP ANY ROLE
LOCK ANY TABLE
DROP USER
DROP TABLESPACE
MERGE ANY VIEW
DROP ANY TYPE
COMMENT ANY TABLE
ALTER TABLESPACE
CREATE CUBE
ALTER ANY SQL PROFILE
DROP ANY INDEXTYPE
ALTER ROLLBACK SEGMENT
DROP ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DELETE ANY CUBE DIMENSION
ANALYZE ANY DICTIONARY
CREATE TABLE
ALTER ANY TABLE
SELECT ANY DICTIONARY
CREATE CLUSTER
DEBUG CONNECT SESSION
CREATE INDEXTYPE
INHERIT ANY PRIVILEGES
DROP ANY SQL PROFILE
CREATE ANY DIRECTORY
DROP ANY INDEX
ENQUEUE ANY QUEUE
DROP ANY CLUSTER
SELECT ANY CUBE BUILD PROCESS
ADMINISTER KEY MANAGEMENT
ALTER ANY SQL TRANSLATION PROFILE
DROP ANY EDITION
CREATE ROLLBACK SEGMENT
SELECT ANY CUBE DIMENSION
ALTER ANY EVALUATION CONTEXT
FORCE ANY TRANSACTION
INSERT ANY CUBE DIMENSION
ALTER ANY OPERATOR
EXECUTE ANY LIBRARY
ALTER ANY MATERIALIZED VIEW
ALTER ANY CUBE DIMENSION
CREATE SYNONYM
FLASHBACK ANY TABLE
CREATE RULE
EXECUTE ANY CLASS
CREATE ANY SEQUENCE
ALTER SYSTEM
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS
CREATE CUBE BUILD PROCESS
DROP ANY ASSEMBLY
ADMINISTER SQL TUNING SET
EXECUTE ANY OPERATOR
DROP ANY LIBRARY
AUDIT ANY
DELETE ANY TABLE
RESUMABLE
DROP ANY TABLE
ALTER ANY EDITION
EXPORT FULL DATABASE
DROP ANY DIRECTORY
DROP ANY SEQUENCE
DROP ROLLBACK SEGMENT
CREATE ANY JOB
BACKUP ANY TABLE
DELETE ANY MEASURE FOLDER
MANAGE FILE GROUP
DROP ANY RULE
ALTER ANY DIMENSION
CREATE ANY RULE SET
ALTER ANY RULE

13.Grant a system privilege to a user:

Grant create any table,alter any table to DEV_CLASS;

SQL>  select privilege,grantee from dba_sys_privs where grantee='DEV_CLASS';

PRIVILEGE                                GRANTEE
---------------------------------------- ---------
CREATE ANY TABLE                         DEV_CLASS
ALTER ANY TABLE                          DEV_CLASS

14. Revoke a system privilege from a user:

REVOKE create any table from dev_class;

OBJECT PRIVILEGE:

An object privilege is the right to perform a particular action on an object or to access another user’s object.

15.list of object privileges:

SQL> select distinct privilege from DBA_TAB_PRIVS;

PRIVILEGE
----------------------------------------
EXECUTE
SELECT
INSERT
INDEX
DEQUEUE
USE
QUERY REWRITE
READ
ON COMMIT REFRESH
REFERENCES
INHERIT PRIVILEGES
DEBUG
ALTER
UPDATE
WRITE
FLASHBACK
DELETE

16.Grant object privilege:

grant insert,update,delete on SIEBEL.TEST2 to DEV_CLASS;

-- grant execute on a procedure

grant execute on SIEBLE.DAILYPROC to DEV_CLASS;

-- View the granted object privilege:

select grantee,owner,table_name,privilege from dba_tab_privs where grantee='DEV_CLASS';

17.Revoke object privilege:

revoke update on siebel.test2 from DEV_CLASS;

ROLE:

A role is a collection of privileges. It allows easier management of privileges.

17.Create a role:

create role DEV_ROLE;

18.Grant privileges to a role:

grant create session to dev_role;
grant select any table to dev_role;
grant insert on siebel.test2 to dev_role;

-- List of  SYSTEM privileges granted to a ROLE

SQL>  select role,privilege from role_sys_privs where role='DEV_ROLE';

ROLE         PRIVILEGE
------------ ----------------------------------------
DEV_ROLE     CREATE SESSION
DEV_ROLE     SELECT ANY TABLE

-- List of OBJECT privileges granted to ROLE;

SQL> select role,owner,table_name,privilege from role_tab_privs where  role='DEV_ROLE';

ROLE         OWNER        TABLE_NAME   PRIVILEGE
------------ ------------ ------------ ----------------------------------------
DEV_ROLE     SIEBEL       TEST2          INSERT

19. Grant role to a User:

grant dev_role to dev_class;

-- List of the user and granted role:

SQL> select grantee,GRANTED_ROLE from dba_role_privs where granted_role='DEV_ROLE';

GRANTEE      GRANTED_ROLE
------------ -----------------------
SYS          DEV_ROLE
DEV_CLASS    DEV_ROLE

20. Drop a user:

Dropping a user will drop all the objects it owns.

drop user DEV_CLASS cascade;

21. Drop a Role:

Drop role DEV_ROLE;