Oracle User Management: A Complete Guide

In this blog post, learn how Oracle user management works. A user is primarily used to connect to the database, and all database objects, such as tables, indexes, views, etc., can be created under that user. Users and schemas in Oracle are essentially the same; a user is the account used to connect to the database, while a schema refers to the set of objects (tables, views, etc.) that belong to that user.

1. Create a User:

CREATE USER W3BUDDY IDENTIFIED BY Welcome#123
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

To grant the minimum privilege required to connect to the database:

GRANT CREATE SESSION TO W3BUDDY;

2. Change Password of a User:

ALTER USER W3BUDDY IDENTIFIED BY NewPass12345#;

3. Lock/Unlock a User Account:

ALTER USER W3BUDDY ACCOUNT LOCK;
ALTER USER W3BUDDY ACCOUNT UNLOCK;

4. Set User Password Expiry:

ALTER USER W3BUDDY ACCOUNT EXPIRE;

5. Change Default Tablespace of a User:

Check the current default tablespace:

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='W3BUDDY';

Change the default tablespace:

ALTER USER W3BUDDY DEFAULT TABLESPACE DATA_TBS;

6. Change Default Temporary Tablespace of a User:

Check the current temporary tablespace:

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='W3BUDDY';

Change the temporary tablespace:

ALTER USER W3BUDDY TEMPORARY TABLESPACE TEMP2;

Profile Management

A profile enforces a set of password security rules and resource usage limits. By default, users are assigned the DEFAULT profile unless specified otherwise.

Default Profile Settings:

You can view the default profile settings with:

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
DEFAULT              INACTIVE_ACCOUNT_TIME     PASSWORD UNLIMITED

17 rows selected.

• COMPOSITE_LIMIT: Defines the total resource usage limit for a user, such as CPU time, memory, etc., in a session.
• SESSIONS_PER_USER: Limits the number of concurrent sessions a user can have to prevent overloading the database.
• CPU_PER_SESSION: Sets the maximum CPU time a session can use, expressed in hundredths of seconds, to prevent excessive CPU consumption.
• CPU_PER_CALL: Limits the CPU time per SQL call (a parse, execute, or fetch), ensuring no single SQL operation consumes too much CPU.
• LOGICAL_READS_PER_SESSION: Specifies the maximum number of data blocks a user can read in a session, including both memory and disk reads.
• LOGICAL_READS_PER_CALL: Limits the number of data blocks a user can read per individual SQL call, helping manage resource consumption per query.
• IDLE_TIME: Specifies the maximum period of inactivity allowed in a session before it is terminated, preventing unnecessary open sessions.
• CONNECT_TIME: Limits the total time a session can remain connected to the database to ensure connections do not stay open indefinitely.
• PRIVATE_SGA: Controls the amount of memory a session can allocate in the shared pool of the System Global Area (SGA), preventing excessive memory usage.
• FAILED_LOGIN_ATTEMPTS: Specifies the number of failed login attempts allowed before the user account is locked, enhancing security.
• PASSWORD_LIFE_TIME: Determines the maximum number of days a password remains valid before the user is required to change it, enforcing password rotation.
• PASSWORD_REUSE_TIME: Defines the number of days a user must wait before reusing a previously used password.
• PASSWORD_REUSE_MAX: Sets the maximum number of password changes a user can make before being allowed to reuse an old password.
• PASSWORD_VERIFY_FUNCTION: Specifies a custom PL/SQL function to verify password complexity, ensuring passwords meet security standards.
• PASSWORD_LOCK_TIME: Defines the duration for which a user’s account remains locked after a specified number of failed login attempts.
• PASSWORD_GRACE_TIME: Specifies the grace period (in days) a user has to change their password before their account is locked.
• INACTIVE_ACCOUNT_TIME: Specifies the duration of inactivity after which a user account is automatically locked, helping manage unused accounts.

Create a New Profile:

CREATE PROFILE "TEST_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;

Privileges in Oracle

System Privileges:

Permissions to perform actions on any object of a particular type.

List of System Privileges:

SELECT DISTINCT PRIVILEGE FROM DBA_SYS_PRIVS;

Granting a System Privilege:

GRANT CREATE ANY TABLE, ALTER ANY TABLE TO W3BUDDY;

Revoke a System Privilege:

REVOKE CREATE ANY TABLE FROM W3BUDDY;

Object Privileges:

Rights to perform a particular action on an object (like a table or procedure).

List of Object Privileges:

SELECT DISTINCT PRIVILEGE FROM DBA_TAB_PRIVS;

Grant Object Privileges:

GRANT INSERT, UPDATE, DELETE ON APPUSER.TEST2 TO W3BUDDY;
GRANT EXECUTE ON SIEBEL.DAILYPROC TO W3BUDDY;

Revoke Object Privileges:

REVOKE UPDATE ON APPUSER.TEST2 FROM W3BUDDY;

Roles in Oracle

A role is a collection of privileges, making privilege management easier.

Create a Role:

CREATE ROLE TEST_ROLE;

Grant Privileges to a Role:

GRANT CREATE SESSION TO TEST_ROLE;
GRANT SELECT ANY TABLE TO TEST_ROLE;

Grant Role to a User:

GRANT TEST_ROLE TO W3BUDDY;

List of Roles and Granted Privileges:

SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE='TEST_ROLE';

Dropping a User

DROP USER W3BUDDY CASCADE;

Drop a Role:

DROP ROLE TEST_ROLE;

This covers the basics of user management in Oracle, including user creation, password management, role management, privileges, and much more.