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 that belong to that account.
1. Create a user:
create user TEST identified by temporary_pass
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
Minimum privilege required to connect to a database is create
session
SQL> grant create session to TEST;
2. Change password of a user:
SQL> alter user TEST identified by TEST#12345;
3. Lock/unlock a user
Lock the user account:
SQL> alter user test account lock;
Unlock the user account:
SQL> alter user test 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.
SQL> alter user test account expire;
5. Changing default tablespace of a user:
SQL> select username,default_tablespace from dba_users where
username='TEST';
USERNAME
DEFAULT_TABLESPACE
----------------------- ------------------------------
TEST
USERS
SQL> alter user TEST default tablespace DATATS;
SQL> select username,default_tablespace from dba_users where
username='TEST';
USERNAME
DEFAULT_TABLESPACE
----------------------- ------------------------------
TEST
DATATS
6. Changing default TEMP tablespace of a user:
SQL> select username,TEMPORARY_TABLESPACE from dba_users
where username='TEST';
USERNAME
TEMPORARY_TABLESPACE
----------------------- ------------------------------
TEST
TEMP
SQL> alter user TEST temporary tablespace TEMP2;
SQL> select username,TEMPORARY_TABLESPACE from dba_users
where username='TEST';
USERNAME
TEMPORARY_TABLESPACE
----------------------- ------------------------------
TEST
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.
7. DEFAULT PROFILE SETTING:
SQL> 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:
SQL> CREATE PROFILE "NEW_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:
SQL> ALTER PROFILE NEW_PROFILE LIMIT FAILED_LOGIN_ATTEMPS
UNLIMITED;
10. Change profile of an user:
SQL> select username,profile from dba_users where
username='TEST';
USERNAME
PROFILE
----------------------- ------------------------------
TEST
DEFAULT
SQL> ALTER USER TEST PROFILE NEW_PROFILE;
SQL> select username,profile from dba_users where
username='TEST';
USERNAME
PROFILE
----------------------- ------------------------------
TEST
NEW_PROFILE
11. How to make a user non-expiry:
Usually application users we need to set non-expiry. means 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='TEST';
USERNAME
PROFILE
EXPIRY_DATE
----------------------- ----------------------- ---------
TEST
NEW_PROFILE
10-NOV-18
SQL> ALTER PROFILE NEW_PROFILE LIMIT PASSWORD_LIFE_TIME
UNLIMITED;
SQL> select username,profile,EXPIRY_DATE from dba_users where
username='TEST';
USERNAME
PROFILE
EXPIRY_DATE
----------------------- ----------------------- ---------
TEST
NEW_PROFILE
12. Drop a user:
Dropping a user will drop all the objects it owns.
SQL> drop user TEST cascade;
