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:

Minimum privilege required to connect to a database is create session

2. Change password of a user:

3. Lock/unlock a user

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.

5. Changing default tablespace of a user:

6. Changing default TEMP tablespace of a user:

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:

*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:

9. Alter a profile:

10. Change profile of an user:

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.

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:

13.Grant a system privilege to a user:

14. Revoke a system privilege from a user:

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:

16.Grant object privilege:

17.Revoke object privilege:

ROLE:

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

17.Create a role:

18.Grant privileges to a role:

19. Grant role to a User:

20. Drop a user:

Dropping a user will drop all the objects it owns.

21. Drop a Role: