ADVERTISEMENT

Oracle User Management

An Oracle user is an account that can connect to the database and perform operations. Managing users ensures proper access control, security, and organization of database activities.

1. Creating a New User

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

Grant Minimum Privilege to Connect

GRANT CREATE SESSION TO W3BUDDY;

💡 Tip: You may also grant roles like CONNECT, RESOURCE, or custom roles if the user needs access to create tables, procedures, etc.

2. Managing User Passwords

Change Password

ALTER USER W3BUDDY IDENTIFIED BY NewPass12345#;

Expire Password (forces password change on next login)

ALTER USER W3BUDDY ACCOUNT EXPIRE;

💡 Tip: Expiring the password is useful for enforcing first-time password changes.

3. Locking and Unlocking User Accounts

Lock User

ALTER USER W3BUDDY ACCOUNT LOCK;

Unlock User

ALTER USER W3BUDDY ACCOUNT UNLOCK;

💡 Tip: Always lock unused accounts or those under investigation.

4. Managing Default Tablespace

View Default Tablespace

SET LINESIZE 150
COLUMN DEFAULT_TABLESPACE FORMAT A20

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

Change Default Tablespace

ALTER USER W3BUDDY DEFAULT TABLESPACE DATA_TBS;

💡 Tip: Ensure the new tablespace has sufficient quota before assigning.

5. Managing Temporary Tablespace

View Temporary Tablespace

SET LINESIZE 150
COLUMN TEMPORARY_TABLESPACE FORMAT A20

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

Change Temporary Tablespace

ALTER USER W3BUDDY TEMPORARY TABLESPACE TEMP2;

6. Profile Management

Profiles define resource limits and password policies.

View Profile Settings

SET LINESIZE 200
SET PAGESIZE 100
COLUMN RESOURCE_NAME FORMAT A30
COLUMN LIMIT FORMAT A20

SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT 
FROM DBA_PROFILES 
WHERE PROFILE = 'DEFAULT';

💡 Tip: Regularly review profile limits for compliance with security standards.

Create a Custom 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;

Assign Profile to a User

ALTER USER W3BUDDY PROFILE TEST_PROFILE;

💡 Tip: Assign stricter profiles to production users and more relaxed ones to developers/testers.

7. System Privileges

System privileges control operations across the database.

List All System Privileges

SELECT DISTINCT PRIVILEGE FROM DBA_SYS_PRIVS;

Grant System Privileges

GRANT CREATE ANY TABLE, ALTER ANY TABLE TO W3BUDDY;

Revoke System Privileges

REVOKE CREATE ANY TABLE FROM W3BUDDY;

💡 Tip: Avoid granting powerful system privileges directly to users. Use roles when possible.

8. Object Privileges

Object privileges are specific to tables, views, procedures, etc.

List 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;

💡 Tip: Always grant the least privilege necessary for the task.

9. Role Management

Roles group privileges for easier management and auditing.

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;

Assign Role to a User

GRANT TEST_ROLE TO W3BUDDY;

View Roles Assigned to Users

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

Drop a Role

DROP ROLE TEST_ROLE;

💡 Tip: Use roles for environment-based access (e.g., DEV_ROLE, PROD_ROLE) for scalability.

10. Dropping a User

Drop User and All Objects

DROP USER W3BUDDY CASCADE;

💡 Tip: Be cautious with CASCADE — it removes all schema objects owned by the user.

11. Useful Queries for Auditing and Monitoring

List All Users and Their Status

SET LINESIZE 150
COLUMN ACCOUNT_STATUS FORMAT A20
COLUMN DEFAULT_TABLESPACE FORMAT A20

SELECT USERNAME, ACCOUNT_STATUS, PROFILE, DEFAULT_TABLESPACE 
FROM DBA_USERS;

Check User’s Privileges and Roles

-- System Privileges
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'W3BUDDY';

-- Object Privileges
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'W3BUDDY';

-- Roles
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'W3BUDDY';

Best Practices for Oracle DBAs

  • Use roles instead of granting privileges directly to users.
  • Assign custom profiles with appropriate password policies.
  • Regularly audit user access and revoke unused privileges.
  • Lock or drop inactive accounts to reduce attack surface.

Summary

This guide provides a complete reference for Oracle user management, covering:

  • User creation, password changes, tablespaces
  • Profiles and resource control
  • Privileges (system and object)
  • Role-based access control
  • Account and access audits

With these scripts and tips, you can manage Oracle users efficiently and securely in any environment.

ADVERTISEMENT