1.

Introduction to Oracle User Management

User management in Oracle Database is the process of creating, maintaining, and controlling
access for individuals and applications to the database. It is a fundamental aspect of database
security, ensuring that only authorized entities can access and manipulate data.

1.1 What is User Management?

User management encompasses all activities related to defining who can connect to the
database and what actions they can perform once connected. It's crucial for:
● Security: Preventing unauthorized access to sensitive data and operations.
● Data Integrity: Protecting data from accidental or malicious corruption.
● Compliance: Meeting regulatory requirements (e.g., SOX, HIPAA, GDPR) for data
access control and auditing.
● Accountability: Linking specific actions to specific users for auditing and troubleshooting.
Authentication vs. Authorization:
● Authentication: The process of verifying the identity of a user. "Who are you?" (e.g.,
checking username and password).
● Authorization: The process of determining what actions an authenticated user is
permitted to perform. "What are you allowed to do?" (e.g., read a table, create a user,
delete data).

1.2 Key Concepts

Understanding these core concepts is essential for effective Oracle user management:
● Users:
○ Also known as database accounts or schemas.
○ A user is a logical entity that can connect to the database.
○ Each user typically owns a schema, which is a logical collection of database objects
(tables, views, procedures, etc.). The schema name is usually the same as the
username.
○ Users are authenticated to prove their identity.
● Roles:
○ A named collection of related privileges.
○ Roles are a best practice for managing privileges because they simplify
administration. Instead of granting individual privileges to many users, you grant
privileges to a role, and then grant the role to users.
○ This makes it easier to add or remove privileges for groups of users and simplifies
auditing.
○ Examples: DBA, CONNECT, RESOURCE (system-defined roles);
APP_READ_ONLY, APP_DEVELOPER (user-defined roles).
● Privileges:
○ Specific permissions that allow a user or role to perform a certain action.
○ System Privileges: Allow users to perform database-wide actions or actions on
any object of a certain type (e.g., CREATE SESSION to connect, CREATE TABLE
to create tables, SELECT ANY TABLE to query any table in the database).
○ Object Privileges: Allow users to perform actions on specific database objects
(e.g., SELECT on EMPLOYEES table, EXECUTE on CALCULATE_SALARY
procedure, INSERT on ORDERS table).
● Profiles:
 ○ A set of limits on database resources and password management policies for users.
○ They control aspects like:
■ Maximum number of concurrent sessions.
■ CPU usage per session/call.
■ Logical I/O per session/call.
■ Idle time and connect time limits.
■ Password complexity, expiry, reuse, and lockout policies.
○ Every user is assigned a profile (explicitly or implicitly, using the DEFAULT profile).
● Schemas:
○ A logical collection of database objects (tables, views, sequences, procedures, etc.)
owned by a database user.
○ When you create a user, Oracle automatically creates a schema with the same
name as the user.
○ The user is the schema owner and has full control over the objects within their
schema.
● Authentication Methods: Oracle supports various ways to authenticate users:
○ Database Authentication: The most common method, where Oracle stores and
verifies the username and password internally.
○ Operating System Authentication: Users are authenticated by the operating
system, and Oracle trusts the OS authentication. Often used for OS users that need
to administer the database (e.g., sys, system).
○ External Authentication (LDAP, Kerberos, RADIUS): Oracle delegates
authentication to an external service like LDAP (e.g., Microsoft Active Directory),
Kerberos, or RADIUS. This provides centralized user management.
○ Enterprise User Security (EUS): A feature that leverages LDAP (specifically
Oracle Internet Directory or third-party LDAP servers) to manage database users
and their roles centrally across multiple databases.

2. Creating and Managing Users

This section details the fundamental operations for user lifecycle management in Oracle
Database.

2.1 Creating Users

The CREATE USER statement is used to define a new database account.

Syntax:
CREATE USER username
IDENTIFIED { BY password | EXTERNALLY | GLOBALLY AS
'external_name' }
[ DEFAULT TABLESPACE tablespace_name ]
[ TEMPORARY TABLESPACE temp_tablespace_name ]
[ QUOTA { integer [K | M | G] | UNLIMITED } ON tablespace_name [,
QUOTA ... ] ]
[ PROFILE profile_name ]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK } ];

Syntax Breakdown:
● username: The name of the new database user.
● IDENTIFIED BY password: The most common method. Specifies a password for
 database authentication. Passwords are case-sensitive by default from Oracle 11g
onward.
● IDENTIFIED EXTERNALLY: For operating system authenticated users. The database
trusts the operating system to verify the user's identity. (e.g., OPS$OS_USERNAME).
● IDENTIFIED GLOBALLY AS 'external_name': For externally authenticated users (e.g., via
LDAP/EUS). external_name is typically the Distinguished Name (DN) from the directory
service.
● DEFAULT TABLESPACE tablespace_name: Specifies the default tablespace where
objects created by this user (if no tablespace is specified) will be stored. If omitted, the
database's default permanent tablespace is used.
● TEMPORARY TABLESPACE temp_tablespace_name: Specifies the temporary
tablespace for the user's temporary segments (e.g., for sorting operations). If omitted, the
database's default temporary tablespace is used.
● QUOTA { integer [K | M | G] | UNLIMITED } ON tablespace_name: Grants storage quota
to the user in a specific tablespace.
○ integer [K | M | G]: A specific size in Kilobytes, Megabytes, or Gigabytes.
○ UNLIMITED: Allows the user to consume any amount of space in the specified
tablespace. This is generally discouraged for application users.
● PROFILE profile_name: Assigns a specific profile to the user, controlling resource limits
and password policies. If omitted, the DEFAULT profile is assigned.
● PASSWORD EXPIRE: Forces the user to change their password upon their first
successful login.
● ACCOUNT LOCK | UNLOCK: Specifies the initial state of the user's account. LOCK
prevents the user from logging in immediately. UNLOCK (default) allows immediate login.
Examples:
1. Basic User Creation (Database Authenticated):
CREATE USER myappuser IDENTIFIED BY MySecureP@ssw0rd;
Note: This user can log in but has no privileges yet.
2. User with Tablespaces and Profile:
CREATE USER hr_user IDENTIFIED BY HR_P@ssw0rd123
DEFAULT TABLESPACE users_data
TEMPORARY TABLESPACE temp_data
QUOTA 100M ON users_data
PROFILE app_user_profile
PASSWORD EXPIRE;
This user gets 100MB quota on users_data tablespace and must change their password
on first login.
3. OS Authenticated User:
CREATE USER OPS$DBADMIN IDENTIFIED EXTERNALLY;
The OS user DBADMIN (on Linux/Unix) or DOMAIN\DBADMIN (on Windows) can now
connect without a password, provided OS_AUTHENT_PREFIX is set correctly in init.ora
or spfile (default is OPS$).
4. Globally Authenticated User (EUS/LDAP):
CREATE USER myldapuser IDENTIFIED GLOBALLY AS
'CN=myldapuser,OU=Users,DC=example,DC=com'
DEFAULT TABLESPACE users_data;

Using SQL Developer/SQL*Plus:

● SQL*Plus: Simply type the CREATE USER command at the SQL prompt and press
Enter.
● SQL Developer:
 1. Connect as a privileged user (e.g., SYS AS SYSDBA).
2. In the Connections navigator, expand your connection.
3. Right-click on Other Users and select Create User....
4. Fill in the fields in the Create User dialog box, select tablespaces, assign quotas,
and choose a profile. SQL Developer will generate the CREATE USER statement
for you.

2.2 Modifying Users

The ALTER USER statement is used to change attributes of an existing user.

Syntax:
ALTER USER username
[ IDENTIFIED { BY password | EXTERNALLY | GLOBALLY AS
'external_name' } ]
[ DEFAULT TABLESPACE tablespace_name ]
[ TEMPORARY TABLESPACE temp_tablespace_name ]
[ QUOTA { integer [K | M | G] | UNLIMITED } ON tablespace_name [,
QUOTA ... ] ]
[ PROFILE profile_name ]
[ PASSWORD EXPIRE | NO EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK } ];

Syntax Breakdown (changes/additions from CREATE USER):

● PASSWORD EXPIRE | NO EXPIRE: NO EXPIRE removes the password expiry flag.
● All other clauses function similarly to CREATE USER, allowing you to modify the
respective user attributes.
Examples:
1. Change a User's Password:
ALTER USER myappuser IDENTIFIED BY NewSecureP@ssw0rd;

2. Lock an Account:
ALTER USER hr_user ACCOUNT LOCK;
The user hr_user can no longer log in.
3. Unlock an Account:
ALTER USER hr_user ACCOUNT UNLOCK;

4. Assign a Different Profile:

ALTER USER myappuser PROFILE prod_app_profile;

5. Increase Tablespace Quota:

ALTER USER hr_user QUOTA UNLIMITED ON users_data;

6. Force Password Expiry:

ALTER USER myappuser PASSWORD EXPIRE;
Next time myappuser logs in, they will be prompted to change their password.

2.3 Dropping Users

The DROP USER statement removes a user from the database.

Syntax:
DROP USER username [CASCADE];
Syntax Breakdown:
● username: The name of the user to be dropped.
● CASCADE: Crucial option! If the user owns any objects (tables, views, procedures, etc.),
you must specify CASCADE.
○ When CASCADE is used, all schema objects owned by the user are dropped.
○ All referential integrity constraints that refer to objects in the dropped user's schema
are also dropped.
○ All grants of privileges on objects in the dropped user's schema are revoked.
○ If you omit CASCADE and the user owns objects, the command will fail with ORA-
01922: CASCADE must be specified to drop user 'USERNAME'.
Examples:
1. Drop a user with no objects:
DROP USER testuser_no_objects;

2. Drop a user and their schema objects:

DROP USER hr_user CASCADE;
Be extremely cautious with CASCADE, as it permanently deletes data and objects!
Best Practices for Dropping Users:
● Audit and Confirm: Before dropping a user with CASCADE, ensure you have a backup
or understand the full impact of losing the associated schema objects. Identify all
dependencies.
● Revoke Privileges First: As a good practice, sometimes administrators revoke all
privileges and roles from a user first, then lock the account, wait for a period, and only
then drop the user. This ensures no unexpected impacts.
● Backup Data: If the user owns critical data, back it up before dropping the user.

2.4 Viewing User Information

Oracle provides several data dictionary views to query information about users.
Key Data Dictionary Views:
● DBA_USERS: Shows all users in the database, visible only to users with SELECT ANY
DICTIONARY privilege or DBA role.
● ALL_USERS: Shows all users accessible to the current user (typically, all users in the
database).
● USER_USERS: Shows information about the current connected user.
● V$SESSION: Dynamic performance view showing information about active user sessions.
Queries to Retrieve User Details:
1. List all users and their default/temporary tablespaces, profiles, and account status:
SELECT
username,
account_status,
default_tablespace,
temporary_tablespace,
profile,
created
FROM
dba_users
ORDER BY
username;
 2. Check password expiry status for all users:
SELECT
username,
account_status,
expiry_date
FROM
dba_users
WHERE
account_status LIKE '%EXPIRED%' OR expiry_date IS NOT NULL
ORDER BY
username;

3. Find the current logged-in user:

SELECT
user
FROM
dual;
(Note: USER is a pseudo-column returning the current schema name)
4. List currently active sessions:
SELECT
sid,
serial#,
username,
status,
osuser,
program,
machine
FROM
v$session
WHERE
username IS NOT NULL
ORDER BY
username;

3. Managing Roles
Roles are a cornerstone of Oracle's security model, enabling efficient privilege management by
grouping related privileges.

3.1 What are Roles?

A role is a named collection of privileges (system privileges, object privileges, or even other
roles) that can be granted to users or other roles.
Benefits of Using Roles:
● Simplified Administration: Instead of granting the same 10 privileges to 50 users
individually, you grant the 10 privileges to one role, and then grant that role to 50 users.
● Centralized Privilege Management: To change privileges for a group of users, you
modify the role, and the changes automatically apply to all users assigned that role.
● Improved Security: Reduces the chance of errors when assigning privileges.
● Easier Auditing: Easier to see what privileges a user has by examining their assigned
 roles.
System-defined vs. User-defined Roles:
● System-Defined Roles: Oracle provides several built-in roles, such as:
○ CONNECT: Basic connectivity (CREATE SESSION privilege).
○ RESOURCE: Allows creation of schema objects (CREATE TABLE, CREATE
SEQUENCE, etc.).
○ DBA: Extensive system privileges, typically granted to DBAs.
○ SCHEDULER_ADMIN: Privileges for managing the database scheduler.
○ And many more for specific features (e.g., EM_EXPRESS_ALL, PDB_DBA).
● User-Defined Roles: Roles created by database administrators to group privileges
specific to an application or job function (e.g., APP_DEVELOPER, SALES_ANALYST,
HR_CLERK).

3.2 Creating Roles

The CREATE ROLE statement defines a new role.

Syntax:
CREATE ROLE role_name
[ NOT IDENTIFIED | IDENTIFIED { BY password | EXTERNALLY |
GLOBALLY } ];

Syntax Breakdown:
● role_name: The name of the new role.
● NOT IDENTIFIED: The most common type. The role is activated automatically when
granted to a user.
● IDENTIFIED BY password: The role requires a password to be explicitly enabled by the
user using SET ROLE role_name IDENTIFIED BY password;. This is less common for
general application roles.
● IDENTIFIED EXTERNALLY: The role is enabled if the operating system authenticates the
user for that role.
● IDENTIFIED GLOBALLY: The role is enabled if an external directory service (like
LDAP/EUS) authenticates the user for that role.
Examples:
1. Basic Role (Not Identified):
CREATE ROLE app_read_only;

2. Role Requiring a Password (Less Common):

CREATE ROLE hr_secure_role IDENTIFIED BY Secret_HR_P@ss;

3.3 Granting Privileges to Roles

Once a role is created, you grant system or object privileges to it.

Syntax:
GRANT { system_privilege | object_privilege_list } TO role_name;

Examples:
1. Grant System Privileges to a Role:
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_read_only;
This role can now connect, create tables, and create views.
2. Grant Object Privileges to a Role:
 GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO
app_read_only;
GRANT EXECUTE ON app_pkg.calculate_salary TO app_read_only;
The role can now perform DML operations on hr.employees and execute the
calculate_salary procedure.

3.4 Granting Roles to Users (and other Roles)

Roles can be granted to users or even to other roles, allowing for a hierarchical privilege
structure.
Syntax:
GRANT role_name [, role_name, ...] TO { user_name | role_name | PUBLIC
} [WITH ADMIN OPTION];

Syntax Breakdown:
● user_name: The user to whom the role is granted.
● role_name: Another role to which the current role is granted (role nesting).
● PUBLIC: Grants the role to all users in the database. Use with extreme caution as it
grants wide access.
● WITH ADMIN OPTION: If specified, the grantee (user or role) can then:
○ Grant the role to other users or roles.
○ Revoke the role from other users or roles.
○ Alter the role (e.g., change password).
○ Drop the role.
○ This option should be used sparingly and only for trusted administrators.
Examples:
1. Grant a Role to a User:
GRANT app_read_only TO myappuser;
myappuser now inherits all privileges granted to app_read_only.
2. Grant a Role to Another Role (Nesting):
CREATE ROLE app_developer;
GRANT app_read_only TO app_developer;
GRANT CREATE PROCEDURE, CREATE TRIGGER TO app_developer;
GRANT app_developer TO dev_user; -- dev_user gets all privileges
from both roles

3. Grant Role with ADMIN OPTION:

GRANT app_read_only TO hr_admin_user WITH ADMIN OPTION;
hr_admin_user can now grant app_read_only to other users.

3.5 Revoking Privileges from Roles

Removes previously granted privileges from a role.

Syntax:
REVOKE { system_privilege | object_privilege_list } FROM role_name;

Examples:
1. Revoke a System Privilege from a Role:
REVOKE CREATE TABLE FROM app_read_only;

2. Revoke an Object Privilege from a Role:

 REVOKE DELETE ON hr.employees FROM app_read_only;

3.6 Revoking Roles from Users

Removes a role and its associated privileges from a user.

Syntax:
REVOKE role_name [, role_name, ...] FROM { user_name | role_name |
PUBLIC };

Examples:
1. Revoke a Role from a User:
REVOKE app_read_only FROM myappuser;
myappuser loses all privileges associated with app_read_only.
2. Revoke a Role from another Role:
REVOKE app_read_only FROM app_developer;

3.7 Dropping Roles

Removes a role definition from the database.

Syntax:
DROP ROLE role_name;

Impact of Dropping a Role:

● All privileges granted to the role are automatically revoked.
● The role is automatically revoked from any users or other roles to which it was granted.
● No CASCADE option is needed for DROP ROLE as the impact is automatically handled.
Example:
DROP ROLE hr_secure_role;

3.8 Viewing Role Information

Data dictionary views provide details about roles and their assignments.
Key Data Dictionary Views:
● DBA_ROLES: Lists all roles in the database.
● DBA_ROLE_PRIVS: Shows roles granted to users and other roles.
● ROLE_ROLE_PRIVS: Shows roles granted to other roles.
● ROLE_SYS_PRIVS: Shows system privileges granted to roles.
● ROLE_TAB_PRIVS: Shows object privileges granted to roles.
● USER_ROLE_PRIVS: Shows roles granted to the current user.
● USER_SYS_PRIVS: Shows system privileges granted directly to the current user (not via
roles).
● USER_TAB_PRIVS: Shows object privileges granted directly to the current user (not via
roles).
Queries to Inspect Role Assignments and Privileges:
1. List all roles in the database:
SELECT role FROM dba_roles ORDER BY role;

2. Show which roles are granted to a specific user (e.g., MYAPPUSER):

 SELECT
granted_role,
admin_option,
default_role
FROM
dba_role_privs
WHERE
grantee = 'MYAPPUSER'
ORDER BY
granted_role;

3. List system privileges granted to a specific role (e.g., APP_READ_ONLY):

SELECT
privilege,
admin_option
FROM
role_sys_privs
WHERE
role = 'APP_READ_ONLY'
ORDER BY
privilege;

4. List object privileges granted to a specific role (e.g., APP_READ_ONLY):

SELECT
owner,
table_name,
privilege,
grantable
FROM
role_tab_privs
WHERE
role = 'APP_READ_ONLY'
ORDER BY
owner, table_name, privilege;

4. Managing Privileges
Privileges are the specific permissions that allow users or roles to perform actions in the
database. While it's best practice to grant privileges to roles and then grant roles to users, you
can also grant privileges directly to users.

4.1 Types of Privileges

● System Privileges:
○ Allow users to perform specific database operations or actions on any object of a
specific type.
○ There are over 200 system privileges in Oracle.
○ Examples:
■ CREATE SESSION: Allows a user to connect to the database. (Most basic
privilege for any user).
 ■ CREATE TABLE: Allows a user to create tables in their own schema.
■ CREATE ANY TABLE: Allows a user to create tables in any schema.
(Powerful, use with caution).
■ SELECT ANY TABLE: Allows a user to query any table or view in the
database. (Very powerful).
■ UNLIMITED TABLESPACE: Allows a user to use an unlimited amount of
space in any tablespace, bypassing quotas. (Generally granted to DBAs
only).
■ ALTER DATABASE, ALTER SYSTEM, GRANT ANY PRIVILEGE.
● Object Privileges:
○ Allow users to perform actions on specific database objects that they do not own.
○ Examples for a TABLE or VIEW:
■ SELECT: Query data from the table/view.
■ INSERT: Add new rows.
■ UPDATE: Modify existing rows. (Can specify columns:
UPDATE(column_name)).
■ DELETE: Remove rows.
■ REFERENCES: Create foreign key constraints referencing the table.
■ ALTER: Alter the table definition (add/drop columns).
■ INDEX: Create an index on the table.
■ READ: (For 12cR2+ tables/views) A restricted form of SELECT, only for
specific types of data.
○ Examples for a SEQUENCE:
■ SELECT: Query NEXTVAL or CURRVAL.
■ ALTER: Alter the sequence.
○ Examples for a PROCEDURE, FUNCTION, PACKAGE:
■ EXECUTE: Run the procedure/function or package.
○ Examples for a DIRECTORY:
■ READ: Read files in the directory.
■ WRITE: Write files in the directory.

4.2 Granting Privileges Directly to Users

While not recommended for general application users (roles are preferred), you can grant
privileges directly. This is sometimes done for specific administrative accounts or during
development.
Syntax:
GRANT { system_privilege | object_privilege_list }
TO { user_name | PUBLIC } [WITH ADMIN OPTION | WITH GRANT OPTION];

Syntax Breakdown:
● user_name: The user receiving the privilege.
● PUBLIC: Grants the privilege to all users in the database. Use PUBLIC with extreme
caution and only for truly universal access (e.g., SELECT on DUAL).
● WITH ADMIN OPTION (for System Privileges only):
○ Allows the grantee (the user receiving the privilege) to grant this system privilege to
other users or roles.
○ Allows the grantee to revoke this system privilege from other users or roles.
○ Allows the grantee to grant any role that has this system privilege to other users or
roles.
○ Warning: Granting system privileges with ADMIN OPTION should be reserved for
 very trusted administrators, as it can escalate privileges quickly.
● WITH GRANT OPTION (for Object Privileges only):
○ Allows the grantee to grant this object privilege to other users or roles.
○ Cascading Revokes: If a privilege was granted WITH GRANT OPTION and then
later revoked, any grants made by the grantee based on that privilege will also be
automatically revoked (cascading revoke). This can have unintended
consequences.
Examples:
1. Grant CREATE SESSION directly to a user:
GRANT CREATE SESSION TO temp_user;

2. Grant SELECT on a table directly to a user:

GRANT SELECT ON hr.employees TO temp_user;

3. Grant SELECT ANY TABLE with ADMIN OPTION:

GRANT SELECT ANY TABLE TO auditor_user WITH ADMIN OPTION;
auditor_user can now grant SELECT ANY TABLE to other users.
4. Grant SELECT on a table with GRANT OPTION:
GRANT SELECT ON app.orders TO report_user WITH GRANT OPTION;
report_user can now grant SELECT ON app.orders to other users. If SELECT ON
app.orders is later revoked from report_user, any users report_user granted it to will also
lose that privilege.

4.3 Revoking Privileges Directly from Users

Removes privileges that were granted directly to a user.

Syntax:
REVOKE { system_privilege | object_privilege_list } FROM { user_name |
PUBLIC };

Impact of Revoking:
● When a system privilege granted WITH ADMIN OPTION is revoked, any grants made by
the grantee for that specific system privilege are also revoked (cascading revoke).
● When an object privilege granted WITH GRANT OPTION is revoked, any grants made by
the grantee for that specific object privilege are also revoked (cascading revoke).
Examples:
1. Revoke CREATE SESSION from a user:
REVOKE CREATE SESSION FROM temp_user;

2. Revoke SELECT on a table from a user:

REVOKE SELECT ON hr.employees FROM temp_user;

4.4 Viewing Privilege Information

Data dictionary views are essential for auditing and understanding privilege assignments.
Key Data Dictionary Views:
● DBA_SYS_PRIVS: Shows all system privileges granted to users and roles.
● DBA_TAB_PRIVS: Shows all object privileges granted to users and roles.
● ALL_TAB_PRIVS: Shows object privileges on objects accessible to the current user.
● USER_SYS_PRIVS: Shows system privileges granted directly to the current user.
 ● USER_TAB_PRIVS: Shows object privileges granted directly to the current user.
Queries to See Granted System and Object Privileges:
1. List all system privileges granted directly to a specific user (e.g., TEMP_USER):
SELECT
privilege,
admin_option
FROM
dba_sys_privs
WHERE
grantee = 'TEMP_USER'
ORDER BY
privilege;

2. List all object privileges granted directly to a specific user (e.g., TEMP_USER):
SELECT
owner,
table_name,
privilege,
grantor,
grantable
FROM
dba_tab_privs
WHERE
grantee = 'TEMP_USER'
ORDER BY
owner, table_name, privilege;

3. Find who has SELECT ANY TABLE privilege:

SELECT
grantee,
admin_option
FROM
dba_sys_privs
WHERE
privilege = 'SELECT ANY TABLE'
ORDER BY
grantee;

4. Find who has EXECUTE privilege on a specific package (e.g.,

SYS.DBMS_SCHEDULER):
SELECT
grantee,
owner,
table_name AS object_name,
privilege,
grantable
FROM
dba_tab_privs
WHERE
owner = 'SYS'
AND table_name = 'DBMS_SCHEDULER'
 AND privilege = 'EXECUTE'
ORDER BY
grantee;

5. Managing Profiles
Profiles in Oracle Database allow administrators to control resource consumption and enforce
password policies for users.

5.1 What are Profiles?

A profile is a named set of resource limits and password parameters. When a user is assigned a
profile, these limits and policies apply to that user's sessions.
Purpose of Profiles:
● Resource Limits: Prevent a single user or application from monopolizing database
resources (CPU, I/O, memory, connect time). This helps ensure database stability and
performance for all users.
● Password Management: Enforce security policies for user passwords, such as:
○ Password complexity (via verification functions).
○ Password expiration.
○ Minimum password lifetime.
○ Prevention of password reuse.
○ Account locking after too many failed login attempts.
Every database has a DEFAULT profile. If a user is not explicitly assigned a profile during
creation or alteration, they automatically inherit the settings from the DEFAULT profile.

5.2 Creating Profiles

The CREATE PROFILE statement defines a new profile and its parameters.
Syntax:
CREATE PROFILE profile_name LIMIT
{ SESSIONS_PER_USER { integer | UNLIMITED | DEFAULT } }
{ CPU_PER_SESSION { integer | UNLIMITED | DEFAULT } }
{ CPU_PER_CALL { integer | UNLIMITED | DEFAULT } }
{ LOGICAL_READS_PER_SESSION { integer | UNLIMITED | DEFAULT } }
{ LOGICAL_READS_PER_CALL { integer | UNLIMITED | DEFAULT } }
{ IDLE_TIME { integer | UNLIMITED | DEFAULT } }
{ CONNECT_TIME { integer | UNLIMITED | DEFAULT } }
{ PRIVATE_SGA { integer [K | M | G] | UNLIMITED | DEFAULT } } --
12cR2+
{ COMPOSITE_LIMIT { integer | UNLIMITED | DEFAULT } }
{ FAILED_LOGIN_ATTEMPTS { integer | UNLIMITED | DEFAULT } }
{ PASSWORD_LIFE_TIME { integer | UNLIMITED | DEFAULT } }
{ PASSWORD_GRACE_TIME { integer | UNLIMITED | DEFAULT } }
{ PASSWORD_REUSE_TIME { integer | UNLIMITED | DEFAULT } }
{ PASSWORD_REUSE_MAX { integer | UNLIMITED | DEFAULT } }
{ PASSWORD_VERIFY_FUNCTION { function_name | NULL | DEFAULT } }
{ PASSWORD_LOCK_TIME { integer | UNLIMITED | DEFAULT } };

Syntax Breakdown for Parameters:

 ● Resource Parameters:
○ SESSIONS_PER_USER: Maximum number of concurrent sessions a user can
have.
○ CPU_PER_SESSION: Maximum CPU time (in hundredths of seconds) a session
can use.
○ CPU_PER_CALL: Maximum CPU time (in hundredths of seconds) a single SQL
call can use.
○ LOGICAL_READS_PER_SESSION: Maximum number of logical data blocks read
in a session.
○ LOGICAL_READS_PER_CALL: Maximum number of logical data blocks read in a
single SQL call.
○ IDLE_TIME: Maximum time (in minutes) a session can be idle (no database
activity).
○ CONNECT_TIME: Maximum total time (in minutes) a user can be connected.
○ PRIVATE_SGA: Maximum private space (in bytes, K, M, G) a session can allocate
in the Shared Global Area (SGA) for dedicated server connections. (Introduced in
12cR2).
○ COMPOSITE_LIMIT: A weighted sum of CPU_PER_SESSION,
LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA.
Useful for overall resource control.
● Password Parameters:
○ FAILED_LOGIN_ATTEMPTS: Number of incorrect login attempts before the
account is locked.
○ PASSWORD_LOCK_TIME: Number of days the account remains locked after
FAILED_LOGIN_ATTEMPTS is exceeded. Use UNLIMITED for indefinite lock,
DEFAULT for the default value (usually 1 day).
○ PASSWORD_LIFE_TIME: Number of days a password remains valid. After this, the
password expires.
○ PASSWORD_GRACE_TIME: Number of days a user can log in with an expired
password before being forced to change it.
○ PASSWORD_REUSE_TIME: Number of days before a password can be reused. If
set, PASSWORD_REUSE_MAX must be UNLIMITED.
○ PASSWORD_REUSE_MAX: Number of times a password must be changed before
an old password can be reused. If set, PASSWORD_REUSE_TIME must be
UNLIMITED.
○ PASSWORD_VERIFY_FUNCTION: Specifies a PL/SQL function to enforce
password complexity rules.
■ NULL: No password verification.
■ DEFAULT: Uses the database's default password verification function (e.g.,
ORA12C_VERIFY_FUNCTION from 12c onward).
■ function_name: A custom PL/SQL function you create.
Examples:
1. Create a profile for application users with resource limits:
CREATE PROFILE app_user_profile LIMIT
SESSIONS_PER_USER 3
CPU_PER_SESSION 60000 -- 10 minutes CPU time
CONNECT_TIME 480 -- 8 hours connect time
IDLE_TIME 30 -- 30 minutes idle
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 10
 PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;

2. Create a profile for batch jobs (less restrictive):

CREATE PROFILE batch_profile LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME UNLIMITED;

Creating Custom Password Verification Functions:

You can create your own PL/SQL function to define very specific password complexity rules.
This function must be owned by SYS and stored in SYS.STANDARD. It usually takes
user_name, password, and old_password as arguments and returns TRUE if the password is
valid, FALSE otherwise (or raises an error).
Example (Conceptual - simplified):
CREATE OR REPLACE FUNCTION my_password_verify_func (
userid VARCHAR2,
password VARCHAR2,
oldpass VARCHAR2
) RETURN BOOLEAN AS
BEGIN
-- Check for minimum length
IF LENGTH(password) < 10 THEN
RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 10
characters long.');
END IF;

-- Check for mixed case

IF NOT REGEXP_LIKE(password, '[A-Z]') OR NOT REGEXP_LIKE(password,
'[a-z]') THEN
RAISE_APPLICATION_ERROR(-20002, 'Password must contain mixed
case letters.');
END IF;

-- Check for digits

IF NOT REGEXP_LIKE(password, '[0-9]') THEN
RAISE_APPLICATION_ERROR(-20003, 'Password must contain at
least one digit.');
END IF;

-- Check for special characters

IF NOT REGEXP_LIKE(password, '[^a-zA-Z0-9]') THEN
RAISE_APPLICATION_ERROR(-20004, 'Password must contain at
least one special character.');
END IF;

-- Check if new password is too similar to old password

 -- (This logic is more complex and usually involves calculating
similarity or differences)
-- For simplicity, let's just say it cannot be exactly the same
for this example
IF password = oldpass THEN
RAISE_APPLICATION_ERROR(-20005, 'New password cannot be the
same as the old password.');
END IF;

RETURN TRUE;
END;
/

-- Then, assign it to a profile:

ALTER PROFILE my_custom_profile LIMIT PASSWORD_VERIFY_FUNCTION
my_password_verify_func;

5.3 Altering Profiles

The ALTER PROFILE statement modifies the limits and parameters of an existing profile.
Syntax:
ALTER PROFILE profile_name LIMIT
{ SESSIONS_PER_USER { integer | UNLIMITED | DEFAULT } }
... (all other parameters as in CREATE PROFILE) ...
;

Examples:
1. Increase IDLE_TIME for a profile:
ALTER PROFILE app_user_profile LIMIT IDLE_TIME 60; -- 60 minutes
idle

2. Change password life time:

ALTER PROFILE app_user_profile LIMIT PASSWORD_LIFE_TIME 60; -- 60
days

3. Disable password verification for a profile (not recommended):

ALTER PROFILE app_user_profile LIMIT PASSWORD_VERIFY_FUNCTION
NULL;

5.4 Assigning Profiles to Users

Profiles are assigned to users during creation or modification.

● During User Creation:
CREATE USER myappuser IDENTIFIED BY MySecureP@ssw0rd PROFILE
app_user_profile;

● After User Creation:

ALTER USER myappuser PROFILE app_user_profile;
If no profile is specified, the DEFAULT profile is used.
5.5 Dropping Profiles

The DROP PROFILE statement removes a profile.

Syntax:
DROP PROFILE profile_name [CASCADE];

Syntax Breakdown:
● CASCADE: If there are users currently assigned to the profile being dropped, you must
use CASCADE.
○ When CASCADE is specified, all users who were assigned this profile are
automatically reassigned to the DEFAULT profile.
○ If CASCADE is omitted and users are assigned to the profile, the command will fail.
Examples:
1. Drop a profile with no assigned users:
DROP PROFILE old_test_profile;

2. Drop a profile and reassign its users to DEFAULT:

DROP PROFILE app_user_profile CASCADE;

5.6 Viewing Profile Information

The DBA_PROFILES data dictionary view provides details about all profiles and their settings.
Queries to See Profile Settings:
1. List all profiles and their parameters:
SELECT
profile,
resource_name,
resource_type,
limit
FROM
dba_profiles
ORDER BY
profile, resource_name;

2. View specific password-related parameters for a profile (e.g.,

APP_USER_PROFILE):
SELECT
resource_name,
limit
FROM
dba_profiles
WHERE
profile = 'APP_USER_PROFILE'
AND resource_type = 'PASSWORD'
ORDER BY
resource_name;

3. View which profile each user is assigned to:

SELECT
username,
 profile
FROM
dba_users
ORDER BY
username;

6. Authentication Methods (Advanced)

Beyond simple database username/password authentication, Oracle supports various advanced
authentication mechanisms to integrate with existing IT infrastructures and enhance security.

6.1 Operating System Authentication

● Concept: Oracle trusts the operating system to authenticate the user. If the OS
authenticates a user, Oracle automatically allows them to connect to the database without
requiring a separate database password.
● Mechanism: Oracle looks for a specific prefix (default is OPS$) before the OS username.
For example, if your OS username is oracle and OS_AUTHENT_PREFIX is OPS$,
Oracle will look for a database user named OPS$ORACLE.
● Configuration:
○ Set OS_AUTHENT_PREFIX in init.ora or spfile (e.g., OS_AUTHENT_PREFIX =
'OPS$'). If you set it to "" (empty string), then the OS username directly maps to the
database username.
○ Create the user in the database using IDENTIFIED EXTERNALLY.
● Usage: Primarily used for administrative users (e.g., SYS, SYSTEM) to allow them to
connect even if the database is not fully started or if they forget the password, as long as
they are authenticated at the OS level. Less common for regular application users.
● Example:
1. Ensure OS_AUTHENT_PREFIX is set (e.g., OPS$).
2. CREATE USER OPS$OSUSER_JOHN IDENTIFIED EXTERNALLY;
3. GRANT CREATE SESSION TO OPS$OSUSER_JOHN;
4. From the OS account OSUSER_JOHN, you can connect without a password:
sqlplus / (if sqlplus recognizes the OS user) or sqlplus OPS$OSUSER_JOHN/ (if
sqlplus recognizes the user but you need to specify the user).

6.2 External Authentication (LDAP, Active Directory)

● Concept: Oracle delegates user authentication to an external directory service, most

commonly Lightweight Directory Access Protocol (LDAP) servers like Oracle Internet
Directory (OID), Microsoft Active Directory, or OpenLDAP.
● Benefits:
○ Centralized User Management: Manage all user accounts (database, email,
network, applications) from a single directory.
○ Single Sign-On (SSO): Users can authenticate once to the network and gain
access to multiple services, including Oracle databases, without re-entering
credentials.
○ Enhanced Security: Leverage the advanced security features of directory services
(e.g., stronger password policies, multi-factor authentication).
○ Simplified Administration: Reduce the overhead of managing individual user
 accounts in each database.
● Oracle Internet Directory (OID) / LDAP Integration:
○ OID is Oracle's own LDAP v3 compliant directory server, part of Oracle Fusion
Middleware.
○ Oracle databases can be configured to use OID (or other LDAP servers) for user
authentication and authorization.
● Enterprise User Security (EUS):
○ EUS is Oracle's specific implementation that leverages an LDAP directory (primarily
OID, but also AD since 10gR2 with some configuration) for centralized user and
role management across multiple Oracle databases.
○ How EUS Works:
1. Database users and their assigned global roles are defined in the LDAP
directory, not in individual databases.
2. When an enterprise user connects to an EUS-enabled database, the
database queries the LDAP directory to authenticate the user and retrieve
their global role assignments.
3. The database then applies the privileges associated with those global roles to
the user's session.
○ Benefits of EUS:
■ Scalability: Manage thousands of users across hundreds of databases from
one central location.
■ Consistency: Ensure consistent security policies across the entire
enterprise.
■ Reduced Administrative Overhead: No need to create users and assign
roles in each database instance.
○ Setup Considerations:
■ Requires an LDAP directory (e.g., OID, AD).
■ Configuration of sqlnet.ora on the client and database server to point to the
directory.
■ Creation of "enterprise users" in the directory.
■ Creation of "global roles" in the database that map to roles defined in the
directory.
■ Mapping enterprise users to global roles in the directory.

6.3 Kerberos Authentication

● Concept: Kerberos is a network authentication protocol that provides strong

authentication for client/server applications by using secret-key cryptography. It's widely
used in Windows Active Directory environments.
● Integration: Oracle Net Services can be configured to use Kerberos for authentication,
allowing users to connect to Oracle databases using their Kerberos credentials.
● Benefits: Strong mutual authentication (client verifies server, server verifies client), single
sign-on within the Kerberos realm.

6.4 RADIUS Authentication

● Concept: Remote Authentication Dial-In User Service (RADIUS) is a networking protocol

that provides centralized Authentication, Authorization, and Accounting (AAA)
management for users who connect and use a network service.
● Integration: Oracle databases can be configured to use RADIUS for authenticating
users, often used in environments with existing RADIUS servers (e.g., for VPN access,
 network device authentication).

7. Security Best Practices for User Management

Effective user management is paramount for database security. Adhering to best practices
helps minimize vulnerabilities and maintain data integrity.

7.1 Principle of Least Privilege

● Rule: Grant users and roles only the minimum set of privileges required to perform their
tasks.
● Why: Reduces the attack surface. If an account is compromised, the damage is limited to
what that account is authorized to do.
● Implementation:
○ Avoid granting DBA or SYSDBA unless absolutely necessary.
○ Avoid powerful system privileges like ANY (e.g., SELECT ANY TABLE, DROP ANY
TABLE).
○ Use object-specific privileges over schema-wide or database-wide privileges.

7.2 Role-Based Access Control (RBAC)

● Rule: Use roles as the primary method for granting and managing privileges. Grant
privileges to roles, and then grant roles to users.
● Why: Simplifies administration, improves consistency, and makes auditing easier.
● Implementation:
○ Define roles that align with job functions (e.g., SALES_ENTRY,
FINANCE_REPORTING, HR_MANAGER).
○ Grant only the necessary system and object privileges to these roles.
○ Grant roles to users.
○ Avoid granting privileges directly to users unless there's a very specific and justified
reason (and document it).

7.3 Strong Password Policies

● Rule: Enforce robust password requirements through profiles.

● Why: Prevents brute-force attacks and unauthorized access.
● Implementation:
○ Use PASSWORD_LIFE_TIME to force regular password changes.
○ Use FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME to deter brute-
force attacks.
○ Use PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX to prevent users
from reusing old passwords.
○ Implement a PASSWORD_VERIFY_FUNCTION (custom or
ORA12C_VERIFY_FUNCTION) to enforce complexity (length, mixed case,
numbers, special characters).

7.4 Regular Auditing

● Rule: Implement comprehensive auditing to track user activities, privilege changes, and
security-related events.
 ● Why: Provides accountability, helps detect suspicious activity, and aids in forensic
analysis.
● Implementation:
○ Configure AUDIT_TRAIL parameter.
○ Audit critical system privileges (e.g., AUDIT ALL BY user_name BY SESSION
WHENEVER NOT SUCCESSFUL).
○ Audit DML/DDL on sensitive tables (e.g., AUDIT SELECT, INSERT, UPDATE,
DELETE ON hr.employees BY ACCESS).
○ Audit privilege grants/revokes (AUDIT GRANT CONNECT TO SYSTEM).
○ Regularly review audit trails.

7.5 Account Locking and Unlocking Policies

● Rule: Define clear policies for when accounts are locked (e.g., inactive, suspicious
activity) and the procedure for unlocking them.
● Why: Prevents unauthorized access through dormant or compromised accounts.
● Implementation:
○ Use IDLE_TIME in profiles to automatically disconnect inactive sessions.
○ Manually ALTER USER ... ACCOUNT LOCK for inactive or suspicious accounts.
○ Have a documented process for users to request account unlocks, involving identity
verification.

7.6 Reviewing Default Accounts

● Rule: Identify and secure or disable default Oracle accounts.

● Why: Default accounts (like SCOTT, HR, SH, OE, IX, PM) often have well-known default
passwords, making them targets for attackers.
● Implementation:
○ Lock and expire default accounts if they are not used (ALTER USER SCOTT
ACCOUNT LOCK PASSWORD EXPIRE;).
○ If used, change default passwords to strong, unique ones.
○ Remove unnecessary privileges from default accounts.

7.7 Segregation of Duties

● Rule: Separate responsibilities among different users or groups to prevent a single

individual from having complete control over critical operations.
● Why: Reduces the risk of fraud, error, and insider threat.
● Implementation:
○ Separate duties for application administration, database administration, security
administration, and auditing.
○ For example, the person who grants privileges should not be the same person who
audits those grants.

7.8 Secure Configuration of Public Synonyms

● Rule: Avoid granting excessive privileges to PUBLIC.

● Why: Privileges granted to PUBLIC are available to all users, including new users and
accounts you might not even know exist. This creates a huge security hole.
● Implementation:
 ○ Only grant SELECT on DUAL and other truly common, non-sensitive objects to
PUBLIC.
○ Grant object privileges directly to specific roles or users instead of PUBLIC.

7.9 Monitoring Failed Login Attempts

● Rule: Actively monitor for excessive failed login attempts.

● Why: Indicates potential brute-force attacks or unauthorized access attempts.
● Implementation:
○ Use FAILED_LOGIN_ATTEMPTS in profiles.
○ Query DBA_AUDIT_TRAIL or UNIFIED_AUDIT_TRAIL for failed login records.
○ Set up alerts for high numbers of failed logins from a single source.

7.10 Regularly Reviewing User Privileges and Role Assignments

● Rule: Periodically (e.g., quarterly, semi-annually) review all user accounts, their assigned
roles, and directly granted privileges.
● Why: Ensures that privileges are still appropriate, identifies dormant accounts, and
removes unnecessary access. Roles can accumulate privileges over time.
● Implementation:
○ Use data dictionary queries (DBA_USERS, DBA_ROLE_PRIVS,
DBA_SYS_PRIVS, DBA_TAB_PRIVS) to generate reports.
○ Involve business owners to validate if users still require specific access levels.

8. Auditing User Activities

Auditing is the process of tracking and recording specific database actions and events. It's a
critical component of security and compliance, providing an immutable record of "who did what,
when, and how."

8.1 Purpose of Auditing

● Accountability: Establishes a trail for every action, linking it to a specific user.

● Security Incident Detection: Helps identify suspicious or unauthorized activities (e.g.,
excessive failed logins, access to sensitive data by unauthorized users).
● Compliance: Meets regulatory requirements (e.g., PCI DSS, HIPAA, GDPR, SOX) that
mandate logging of access to sensitive data and changes to system configurations.
● Forensic Analysis: Provides data for investigating security breaches or operational
issues.
● Performance Analysis (Limited): Can sometimes reveal frequently accessed objects or
resource-intensive operations.

8.2 Configuring Auditing

Oracle offers different auditing mechanisms: Standard Auditing, Fine-Grained Auditing (FGA),
and from 12c, Unified Auditing.
8.2.1 Standard Auditing (Pre-12c and still available)
● Enable Auditing: Set the AUDIT_TRAIL initialization parameter in the init.ora file or
spfile.
○ AUDIT_TRAIL = NONE: No auditing (default).
 ○ AUDIT_TRAIL = OS: Audit records written to an OS file (XML or text).
○ AUDIT_TRAIL = DB: Audit records written to the AUD$ table in the SYS schema.
○ AUDIT_TRAIL = DB_EXTENDED: Like DB, but also captures SQL_TEXT and
SQL_BIND values.
○ AUDIT_TRAIL = XML: Audit records written to an OS XML file.
○ AUDIT_TRAIL = XML, EXTENDED: XML audit with SQL_TEXT and SQL_BIND.
○ Note: After changing AUDIT_TRAIL, the database instance must be restarted.
● AUDIT Statement: Used to specify what to audit.
○ Syntax (System Privileges):
AUDIT { privilege | ALL PRIVILEGES }
[ BY { SESSION | ACCESS } ]
[ WHENEVER [ NOT ] SUCCESSFUL ];

■ BY SESSION: Audits one record per session for the audited event.
■ BY ACCESS: Audits one record for each access or SQL statement. More
granular, more overhead.
■ WHENEVER SUCCESSFUL: Audits only successful operations.
■ WHENEVER NOT SUCCESSFUL: Audits only failed operations (useful for
detecting attacks).
■ (If WHENEVER clause is omitted, both successful and unsuccessful attempts
are audited).
Examples:AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL; --
Audit failed logins
AUDIT ALTER ANY TABLE BY ACCESS; -- Audit every attempt to
alter any table
AUDIT GRANT ANY PRIVILEGE BY SESSION; -- Audit once per
session when someone grants a privilege

○ Syntax (Object Privileges):

AUDIT { object_privilege | ALL }
ON [ schema. ] object_name
[ BY { SESSION | ACCESS } ]
[ WHENEVER [ NOT ] SUCCESSFUL ];
Examples:
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY
ACCESS;
AUDIT EXECUTE ON app_pkg.calculate_salary BY ACCESS WHENEVER
SUCCESSFUL;

● NOAUDIT Statement: Used to stop auditing. Syntax is similar to AUDIT.

NOAUDIT CREATE SESSION;
NOAUDIT SELECT, INSERT ON hr.employees;

8.2.2 Fine-Grained Auditing (FGA)

● Concept: Allows auditing based on the content of the data being accessed or the specific
columns involved in a query. It's implemented using DBMS_FGA package.
● Purpose: Ideal for compliance (e.g., "who accessed salaries greater than X?" or "who
viewed the social security number column?").
● Mechanism: When a policy is met, an audit event is generated.
● Example (Conceptual):
BEGIN
 DBMS_FGA.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'AUDIT_SENSITIVE_SALARY',
audit_condition => 'salary > 100000',
audit_column => 'salary, email', -- audit only if these
columns are referenced
enable => TRUE,
statement_types => 'SELECT, UPDATE'
);
END;
/

8.2.3 Unified Auditing (Oracle 12c and later - Recommended)

● Concept: Consolidates all audit trails (standard, FGA, RMAN, Data Pump, SQL*Loader
direct path, etc.) into a single, central audit trail stored in a read-only table called
UNIFIED_AUDIT_TRAIL.
● Benefits: Simplified administration, improved security (audit trail cannot be tampered with
easily), better performance.
● Enablement:
1. Ensure the AUDIT_TRAIL parameter is set to DB or DB_EXTENDED (for backward
compatibility if needed, but Unified Auditing overrides much of it).
2. The database must be in AUDIT_MODE or FORCE AUDIT_MODE (default is
usually AUDIT_MODE).
3. SHUTDOWN IMMEDIATE; STARTUP UPGRADE; @?/rdbms/admin/catconso.sql;
SHUTDOWN IMMEDIATE; STARTUP; (This is typically done once after upgrade to
enable it permanently).
● Configuration with AUDIT Policy:
CREATE AUDIT POLICY access_sensitive_data_pol
PRIVILEGES SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY
TABLE
ACTIONS ALL ON hr.employees,
SELECT ON sales.customers (credit_limit, phone_number)
ROLES DBA;

AUDIT POLICY access_sensitive_data_pol;

NOAUDIT POLICY access_sensitive_data_pol; -- To disable

● UNIFIED_AUDIT_TRAIL View: All audit records are collected here.

8.3 Viewing Audit Trails

● For Standard Auditing:

○ DBA_AUDIT_TRAIL: Main view for all audit records.
○ DBA_AUDIT_SESSION: Audit records for connect/disconnect.
○ DBA_AUDIT_STATEMENT: Audit records for DDL/DCL.
○ DBA_AUDIT_OBJECT: Audit records for object access.
○ V$OSSTAT: If auditing to OS, you'd need to access OS files.
Example Query:SELECT
username,
action_name,
 obj_name,
sql_text,
returncode,
timestamp
FROM
dba_audit_trail
WHERE
returncode != 0 -- Failed operations
ORDER BY
timestamp DESC;

● For Fine-Grained Auditing:

○ DBA_FGA_AUDIT_TRAIL: Specific view for FGA records.
● For Unified Auditing (Recommended):
○ UNIFIED_AUDIT_TRAIL: The single, comprehensive view.
Example Query:SELECT
event_timestamp,
dbusername,
action_name,
object_schema,
object_name,
return_code,
system_privilege_used,
sql_text
FROM
unified_audit_trail
WHERE
unified_audit_policies LIKE '%ACCESS_SENSITIVE_DATA_POL%'
ORDER BY
event_timestamp DESC;

8.4 Best Practices for Auditing

● Define a Clear Auditing Strategy: What needs to be audited for security, compliance,
and troubleshooting?
● Audit Failed Logins: Crucial for detecting brute-force attacks.
● Audit Privilege Grants/Revokes: Track changes to access control.
● Audit DDL Statements: Track schema changes.
● Audit Sensitive Data Access: Use FGA or Unified Auditing policies for compliance.
● Separate Audit Trail from Data: If auditing to DB, ensure the audit tables are in a
separate tablespace (not SYSTEM) to prevent SYSTEM tablespace filling up.
● Regularly Purge Audit Trail: Audit trails can grow very large. Implement a strategy for
archiving and purging old audit data to manage space.
● Secure the Audit Trail: Only highly privileged users (e.g., AUDIT_ADMIN role from 12c)
should have access to audit configuration and the audit trail data.
● Alerting: Integrate audit logs with security information and event management (SIEM)
systems for real-time alerting on critical security events.

9. Troubleshooting Common User Management Issues

This section provides guidance on diagnosing and resolving common problems encountered
during Oracle user management.

9.1 User locked/expired

Symptoms:
● ORA-28000: the account is locked.
● ORA-28001: the password has expired.
Diagnosis & Resolution:
1. Check Account Status:
SELECT username, account_status, expiry_date FROM dba_users WHERE
username = 'YOUR_USER';

○ If ACCOUNT_STATUS is LOCKED or LOCKED(TIMED), it's locked due to failed

login attempts or manually locked.
○ If ACCOUNT_STATUS is EXPIRED or EXPIRED(GRACE), the password has
expired.
2. Unlock Account:
ALTER USER YOUR_USER ACCOUNT UNLOCK;

3. Reset Password (if expired or for general reset):

ALTER USER YOUR_USER IDENTIFIED BY new_password;

○ If PASSWORD EXPIRE is set in the profile or was explicitly added, the user will be
prompted to change it on first login.
○ Inform the user of the new password and whether they need to change it
immediately.
4. Check Profile Settings: The locking/expiring behavior is often due to the assigned
profile.
SELECT profile FROM dba_users WHERE username = 'YOUR_USER';
SELECT resource_name, limit FROM dba_profiles WHERE profile =
'YOUR_PROFILE' AND resource_type = 'PASSWORD';

○ Adjust FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME,

PASSWORD_LIFE_TIME, etc., if the policies are too aggressive.

9.2 Incorrect Password

Symptoms:
● ORA-01017: invalid username/password; logon denied.
Diagnosis & Resolution:
1. Verify Username: Ensure the user is typing the correct username. Oracle usernames are
case-insensitive by default unless enclosed in double quotes during creation (e.g.,
"MyUser").
2. Verify Password: Passwords are case-sensitive by default from Oracle 11g onward.
Ensure Caps Lock is off.
3. Password Reset: If forgotten, reset the password as shown above:
ALTER USER YOUR_USER IDENTIFIED BY new_password;

4. Check Authentication Method:

○ If it's an OS-authenticated user, ensure OS_AUTHENT_PREFIX is correct and the
 OS user name matches.
○ If it's an external (LDAP/EUS) user, verify LDAP connectivity and the correct
Distinguished Name (DN) mapping.

9.3 Insufficient Privileges Error (ORA-01031)

Symptoms:
● ORA-01031: insufficient privileges.
○ This is a generic error indicating the user tried to perform an action for which they
do not have the necessary privilege.
Diagnosis & Resolution:
1. Identify the Action: What specific SQL statement or operation was the user trying to
perform?
2. Determine Required Privilege: Consult Oracle documentation or common knowledge for
the privilege needed (e.g., CREATE TABLE, SELECT ON table_name, EXECUTE ON
procedure_name).
3. Check User's Privileges and Roles:
○ Check directly granted system privileges:
SELECT privilege FROM dba_sys_privs WHERE grantee =
'YOUR_USER';

○ Check roles granted to the user:

SELECT granted_role FROM dba_role_privs WHERE grantee =
'YOUR_USER';

○ For each role, check its privileges:

SELECT privilege FROM role_sys_privs WHERE role =
'YOUR_ROLE';
SELECT owner, table_name, privilege FROM role_tab_privs WHERE
role = 'YOUR_ROLE';

○ Check directly granted object privileges:

SELECT owner, table_name, privilege FROM dba_tab_privs WHERE
grantee = 'YOUR_USER';

4. Grant Missing Privilege/Role: Grant the necessary privilege to an appropriate role, then
grant the role to the user. Avoid direct grants where possible.
GRANT SELECT ON hr.employees TO app_read_only;
GRANT app_read_only TO YOUR_USER;

5. Role Not Enabled (if role requires password): If a role was created with IDENTIFIED
BY password, the user must explicitly enable it:
SET ROLE role_name IDENTIFIED BY password;
This is less common for general application roles.

9.4 Tablespace Issues (Quota Exceeded)

Symptoms:
● ORA-01536: space quota exceeded for tablespace 'TABLESPACE_NAME'.
● User cannot create objects or insert data into their schema.
Diagnosis & Resolution:
 1. Check User's Quota:
SELECT username, tablespace_name, bytes/1024/1024 current_mb,
max_bytes/1024/1024 max_mb
FROM dba_ts_quotas
WHERE username = 'YOUR_USER' AND tablespace_name =
'TABLESPACE_NAME';

2. Increase Quota:
ALTER USER YOUR_USER QUOTA NEW_SIZE_IN_M ON TABLESPACE_NAME; --
e.g., QUOTA 500M
-- OR, grant unlimited quota (use with caution):
ALTER USER YOUR_USER QUOTA UNLIMITED ON TABLESPACE_NAME;

3. Check Default Tablespace: Ensure the user's DEFAULT TABLESPACE is appropriate

and has enough free space. If not, consider changing it or resizing the tablespace.

9.5 Profile Limits Reached

Symptoms:
● ORA-02391: exceeded simultaneous logins limit (SESSIONS_PER_USER)
● ORA-02392: exceeded session limit for CPU usage (CPU_PER_SESSION)
● ORA-02393: exceeded call limit for CPU usage (CPU_PER_CALL)
● ORA-02394: exceeded session limit on logical reads
(LOGICAL_READS_PER_SESSION)
● ORA-02395: exceeded call limit on logical reads (LOGICAL_READS_PER_CALL)
● ORA-02396: exceeded maximum idle time, please connect again (IDLE_TIME)
● ORA-02399: exceeded maximum connect time, please connect again (CONNECT_TIME)
Diagnosis & Resolution:
1. Identify the Profile and Limit:
SELECT profile FROM dba_users WHERE username = 'YOUR_USER';
SELECT resource_name, limit FROM dba_profiles WHERE profile =
'YOUR_PROFILE';

2. Analyze User Activity: Determine if the user's activity truly warrants hitting the limit or if
there's an inefficient application design or runaway query.
3. Adjust Profile Limit: If the limit is too restrictive for legitimate activity, alter the profile.
ALTER PROFILE YOUR_PROFILE LIMIT IDLE_TIME UNLIMITED; -- or a
higher number

○ Caution: Increasing limits for resource-intensive users might impact overall

database performance. Consider carefully.

9.6 Connectivity Issues

Symptoms:
● ORA-12154: TNS:could not resolve the connect identifier specified.
● ORA-12541: TNS:no listener.
● ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor.
Diagnosis & Resolution (briefly, as this is more Net Services related):
1. Check TNSNames.ora: Ensure the service name in the connection string matches an
 entry in tnsnames.ora (client side).
2. Check Listener Status: On the database server, run lsnrctl status to confirm the listener
is running and configured for the correct service.
3. Firewall: Check for firewall rules blocking the database port (default 1521).
4. Network Connectivity: Basic network troubleshooting (ping, telnet to port).

10. Conclusion
Oracle User Management is a cornerstone of database security and operational efficiency. By
mastering the concepts and commands presented in this guide, database administrators can
ensure:
● Secure Access: Only authenticated and authorized individuals can interact with the
database.
● Data Integrity: Protection against unauthorized modifications or deletions of critical data.
● Compliance: Adherence to internal policies and external regulations regarding data
access and auditability.
● Operational Stability: Prevention of resource exhaustion by individual users through
proper profile management.
● Simplified Administration: Efficient management of user privileges through the effective
use of roles.
The emphasis on Role-Based Access Control (RBAC), the Principle of Least Privilege, and
Robust Auditing cannot be overstated. These practices form the bedrock of a secure and
manageable Oracle environment. Regular reviews of user accounts, privilege assignments, and
audit trails are essential to adapt to changing requirements and to proactively identify and
mitigate security risks. Investing time in understanding and implementing these user
management principles will significantly enhance the overall security posture and reliability of
your Oracle databases.