Creating Users in Oracle Database with Example

Creating users

/* create profile */

CREATE PROFILE pr01
SESSIONS_PER_USER 1
IDLE_TIME 30
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_VERIFY_FUNCTION SYSUTILS.VERIF_PWD ;

PROFILE = name of the profile
SESSIONS_PER_USER = limits a user to integer concurrent sessions
CPU_PER_SESSION = limits the CPU time for a session, in hundredth of seconds  
CPU_PER_CALL = limits the CPU time for a call
CONNECT_TIME =  limits the total elapsed time of a session, in minutes
IDLE_TIME = limits periods of continuous inactive time during a session, in minutes
LOGICAL_READS_PER_SESSION = number of data blocks read in a session
LOGICAL_READS_PER_CALL = number of data blocks read for a call to process a SQL stmt
FAILED_LOGIN_ATTEMPTS = number of failed attempts to log in, before locking acct
PASSWORD_LIFE_TIME = limits the number of days the same password can be used
PASSWORD_REUSE_TIME = number of days before which a password cannot be reused
PASSWORD_REUSE_MAX = number of password changes required
PASSWORD_LOCK_TIME = number of days an account will be locked
PASSWORD_VERIFY_FUNCTION = a PL/SQL password complexity verification script
DEFAULT = omits a limit for this resource in this profile
COMPOSITE_LIMIT = specifies the total resources cost for a session, in service units
UNLIMITED = a user assigned this profile can use an unlimited amount of this resource


/* create user */

CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE ts01
QUOTA 500M ON ts02
PASSWORD EXPIRE
ACCOUNT UNLOCK
PROFILE pr01 ;


/* user pwd change */

ALTER USER scott
IDENTIFIED BY lion ;

/* add tablespace */
ALTER USER scott
QUOTA 100M ON ts29 ;


/* user drop */

DROP user scott ;