Oracle User Management: Understanding Users, Roles, and Privileges

Introduction to User Management in Oracle Database

User management is one of the core responsibilities of an Oracle Database Administrator. Every user who connects to an Oracle Database does so through a user account, making user management critical for database security and access control.

In this article, we will understand what a user is in an Oracle Database and how DBAs create, manage, and control users in daily operations. This guide is designed to help beginners build a strong foundation while also reflecting real-world DBA practices.

What Is a User in an Oracle Database?

In an Oracle Database, a user is a database account that allows access to the database. Every user connects to Oracle using a username and password and operates within defined permissions and limits. Each user is automatically linked to a schema that stores the database objects owned by that user, such as tables, views, and procedures. Proper user management helps DBAs control security, access, and resource usage in an Oracle Database.

Relationship Between Users and Schemas in Oracle

In an Oracle Database, every user is automatically associated with a schema. When a user is created, Oracle creates a schema with the same name as the user. This schema acts as a logical container that stores all database objects owned by the user, such as tables, views, indexes, and procedures. Although users and schemas share the same name, they serve different purposes. The user is used to connect to the database, while the schema is used to organize and manage database objects. Understanding this relationship helps DBAs manage database objects, access control, and ownership more effectively.

Types of Users in Oracle Database

• Administrative Users: High-privilege accounts used to manage and maintain the Oracle Database.
• Application Users: Users created for applications to access and operate on database data.
• Schema Owners: Users that own database objects such as tables, views, and procedures.
• Read-Only Users: Users with access limited to viewing data, commonly used for reporting.

Creating a User in Oracle Database

SQL> CREATE USER TEST IDENTIFIED BY TEST$123;

SQL> CREATE USER TEST IDENTIFIED BY TEST$123 DEFAULT TABLESPACE USERS QUOTA 100M ON USERS;

Locking and Unlocking User Accounts

SQL> ALTER USER TEST ACCOUNT LOCK;

SQL> ALTER USER TEST ACCOUNT UNLOCK;

Assigning Default Tablespaces

SQL> ALTER USER TEST DEFAULT TABLESPACE USERS02;

Assigning Temporary Tablespaces

SQL> ALTER USER TEST DEFAULT TEMPORARY TABLESPACE TEMP02;

Managing Tablespace Quotas for Users

SQL> ALTER USER TEST QUOTA 200M ON USERS;

Dropping Users in Oracle Database

Drops the user only if the user does not own any database objects.

SQL> DROP USER TEST;

Drops the user along with all database objects owned by that user.

SQL> DROP USER TEST CASCADE;

How to Reset the Same Password for an Expired User in Oracle Database

1. Check Account Status and Expiry Date

SQL> SELECT USERNAME, ACCOUNT_STATUS, EXPIRY_DATE FROM DBA_USERS WHERE USERNAME='TEST';

2. Get Password Values from the USER$ Table

SQL> SELECT PASSWORD FROM SYS.USER$ WHERE NAME='TEST';

3. Change a User Password Using Above Password Values

SQL> ALTER USER TEST IDENTIFIED BY VALUES 'XXXXXXXXXXXX';

Note: This method should be used carefully and only by authorized DBAs.

What is Privilege?

A privilege is a permission given to a user or role that defines what actions can be performed in an Oracle Database. Privileges control access to database objects, such as tables and views, as well as system-level operations. By managing privileges properly, DBAs can ensure secure and controlled access to database resources.

Types of Privileges:

• System Privileges
• Object Privileges

1. System Privilege: System privileges are permissions that allow users to perform actions at the database level rather than on specific objects. These privileges control tasks such as creating users, creating tables, and managing database objects. System privileges are typically granted to DBAs or trusted users who need to perform administrative or management operations.

Example:

SQL> GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO TEST;

Views to check system privileges:

• DBA_SYS_PRIVS - Displays system privileges granted directly to users
• ROLE_SYS_PRIVS - Displays system privileges granted to roles

2. Object Privilege: Object privileges are permissions that allow users to perform specific actions on database objects such as tables, views, sequences, and procedures. These privileges control what a user can do with a particular object, either in their own schema or in another user’s schema. Object privileges help DBAs manage access to data while maintaining security and proper ownership.

Example: 

SQL> GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO TEST;

Views to check object privileges:

• DBA_TAB_PRIVS - Displays object privileges granted to users or roles
• DBA_COL_PRIVS - Displays column-level privileges
• DBA_TAB_PRIVS_ROLE - Displays object privileges granted through roles

What Is a Role in Oracle Database?

A role is a collection of privileges that can be granted to users or other roles. Roles simplify user management by grouping related privileges together and assigning them as a single unit. This approach reduces redundancy and makes it easier for DBAs to manage access control and maintain security in an Oracle Database.

Example of Creating and Granting Roles

1. Create a role:

SQL> CREATE ROLE app_role;

2. Grant privileges to the role:

SQL> GRANT CREATE SESSION, CREATE TABLE TO app_role;

3. Grant the role to a user:

SQL> GRANT app_role TO TEST;

This approach makes privilege management easier and more organized.

Views to Check Role Privileges

Oracle provides data dictionary views to verify roles and their privileges.

• DBA_ROLES - Lists all roles in the database
• DBA_ROLE_PRIVS - Shows roles granted to users or other roles
• DBA_TAB_PRIVS - Displays object privileges granted through roles
• ROLE_SYS_PRIVS - Displays system privileges granted to a role

Common User Management Errors and Solutions

• Granting excessive privileges to users can create security risks; always grant only the required privileges.
• Assigning incorrect or default tablespaces may cause storage issues; users should be assigned proper tablespaces based on usage.
• Not setting tablespace quotas can lead to uncontrolled space consumption; quotas help limit storage usage.
• Leaving unused or inactive accounts unlocked increases security threats; such accounts should be locked or dropped.
• Using SYSTEM or SYSAUX tablespaces for user objects can affect database performance; user data should be stored in dedicated tablespaces.

Best Practices for User Management in Oracle

• Grant only the minimum privileges required to each user
• Lock or drop unused user accounts regularly
• Use strong password policies to improve database security
• Assign proper default and temporary tablespaces
• Set tablespace quotas to control storage usage
• Avoid using SYSTEM and SYSAUX tablespaces for user data
• Review user accounts and privileges periodically

Conclusion: User management is a fundamental responsibility of every Oracle DBA. Understanding how users, schemas, privileges, and roles work together helps maintain database security, performance, and proper access control. By following correct user management practices, using appropriate privileges and roles, and regularly reviewing user accounts, DBAs can ensure a secure and well-managed Oracle Database. Mastering these basics builds a strong foundation for handling real-world DBA tasks confidently.