Advanced

Manage Users, Roles, and Security on Oracle Database 19c

This query returns approximately 162 results, providing a complete overview of rights in the database.

Table of Contents

  1. Manage Oracle Database Users
  1. Module 2 — Manage roles and privileges in an Oracle database
  1. Implement advanced security features
  1. Module 4 — Configure and monitor auditing
  1. Ensuring Compliance with Good Security Practices

1. Manage Oracle Database Users

1.1 Create and manage users in Oracle 19c

Connecting to SQL*Plus as SYSDBA

The first step to administer an Oracle database is to connect via SQL*Plus with the privileged account sysdba. This account has the highest rights in the system.

sqlplus / AS SYSDBA

Check active container (CDB vs PDB)

Oracle 19c uses a multi-tenant (CDB/PDB) architecture. Before any operation, you must check which container you are in. The following command displays the name of the current container:

SHOW CON_NAME;

The result indicates CDB$ROOT, which means that we are in the root container of the CDB (Container Database). To create users in a specific application, you must switch to the associated PDB (Pluggable Database).

Switch to Pluggable Database (PDB)

To change container and connect to the PDB named OraclePDB, we use the ALTER SESSION command:

ALTER SESSION SET CONTAINER = OraclePDB;

This command opens access to the designated PDB. We can check that the PDB is open:

SELECT NAME, OPEN_MODE FROM V$PDBS;

If the PDB is closed, it is possible to open it manually:

ALTER PLUGGABLE DATABASE OraclePDB OPEN;

If the PDB is already open, Oracle returns an error message indicating that it is already in this state.

Create a new user with storage space and quota

Once in the correct PDB, you can create a user. The following command creates the user hr_user with:

  • password authentication (IDENTIFIED BY)
  • a default TABLESPACE named USERS
  • a temporary TABLESPACE named TEMP
  • a quota of 100 MB on the USERS tablespace
CREATE USER hr_user IDENTIFIED BY Password1
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;

Grant essential privileges

After creation, the user cannot do anything yet. It must be granted the minimum necessary privileges.

Allow connection to the base:

GRANT CREATE SESSION TO hr_user;

Allow the creation of tables in its schema:

GRANT CREATE TABLE TO hr_user;

With these two privileges, hr_user can connect to the database and create tables in its own schema.

Check user creation and status

To confirm that the user has been created and know its status (active, locked, etc.):

SELECT username, account_status, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'HR_USER';

Unlock a locked account

If the status of an account is LOCKED, it is necessary to unlock it before you can use it:

ALTER USER hr_user ACCOUNT UNLOCK;

1.2 User authentication, profiles and status management

Create user with password authentication

The most common method of authentication in Oracle is authentication by password stored in the database. The command is as follows:

CREATE USER app_user IDENTIFIED BY Password1;
GRANT CREATE SESSION TO app_user;

The CREATE SESSION privilege allows the user to authenticate and open a session using the password stored in the database.

Create a user with external authentication (OS-based)

Oracle also supports external authentication, that is, the user authenticates via their operating system (OS) credentials. This type of user does not have a password in the database:

CREATE USER ext_user IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO ext_user;

Specifying IDENTIFIED EXTERNALLY indicates that there is no Oracle password for this user and that the connection relies entirely on OS credentials.

Profiles: definition and role

A profile is a set of rules that controls:

  • Password complexity
  • Password expiration
  • System resource limits (CPU time, concurrent sessions, etc.)

To consult the parameters of the DEFAULT profile (applied to any user without a specific profile):

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT';

Visible settings include PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, FAILED_LOGIN_ATTEMPTS, etc. These values ​​can be adjusted to strengthen the security policy.

Create a secure password profile

The following command creates a profile named secure_profile with strict rules:

  • Account locked after 5 failed attempts
  • Account locked for 1 day
  • Mandatory password change every 90 days
  • Unable to reuse a password for 30 days or 5 changes
CREATE PROFILE secure_profile LIMIT
  FAILED_LOGIN_ATTEMPTS  5
  PASSWORD_LOCK_TIME     1
  PASSWORD_LIFE_TIME     90
  PASSWORD_REUSE_TIME    30
  PASSWORD_REUSE_MAX     5;

Apply profile to a user

Once the profile is created, we assign it to a user with ALTER USER:

ALTER USER app_user PROFILE secure_profile;

From then on, the user app_user is subject to the rules of the secure_profile profile.

Manually lock and unlock an account

Lock an account:

ALTER USER app_user ACCOUNT LOCK;

Unlock an account:

ALTER USER app_user ACCOUNT UNLOCK;

Check if an account is locked:

SELECT username, account_status
FROM dba_users
WHERE username = 'APP_USER';

Force password reset at next login

To force a user to change their password the next time they log in:

ALTER USER app_user PASSWORD EXPIRE;

When the password expires, the account may become locked if too many failed attempts have been recorded. In this case, you need to combine unlocking and password reset.

2. Manage roles and privileges in an Oracle database

2.1 Roles and privileges: building effective access control

The two types of privileges in Oracle

Oracle distinguishes two main categories of privileges:

  1. System privileges: Allow a user to perform administrative actions, such as creating tables (CREATE TABLE), managing users (CREATE USER), or administering the database.

  2. Object privileges: Control access to specific objects such as tables, views, or procedures. For example, SELECT ON hr.employees or EXECUTE ON some_procedure.

View all available system privileges

Oracle 19c provides 257 system privileges. To see the full list:

SELECT name FROM system_privilege_map
ORDER BY name;

This query returns 257 rows, each corresponding to a system privilege available in the Oracle database.

Check user privileges

To find out the system privileges currently granted to a specific user (here HR_USER):

SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'HR_USER';

The result shows that hr_user has CREATE TABLE and CREATE SESSION privileges.

Create a role to group privileges

Rather than granting privileges to multiple users one at a time, Oracle recommends creating roles. A role is a named set of privileges that can be assigned to multiple users in a single operation.

Example: create a sales_role role for users in the sales department:

-- Créer le rôle
CREATE ROLE sales_role;

-- Permettre la connexion à la base
GRANT CREATE SESSION TO sales_role;

-- Permettre la création de tables
GRANT CREATE TABLE TO sales_role;

-- Accorder l'accès en lecture à la table des employés HR
GRANT SELECT ON hr.employees TO sales_role;

Assign a role to a user

Instead of granting each privilege separately, we assign the role in one command:

GRANT sales_role TO hr_user;

Check user roles

To confirm that the role has been assigned:

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'HR_USER';

Revoke a privilege from a role

If a privilege is no longer needed in a role, it can be revoked. This revocation takes effect for all users with this role:

REVOKE CREATE TABLE FROM sales_role;

All users with the sales_role role will immediately lose the ability to create tables.

Revoke a role from a user

To completely remove a role (and therefore all privileges it contains) from a user:

REVOKE sales_role FROM hr_user;

Audit role assignments

To obtain an overview of all role assignments in the database:

SELECT grantee, granted_role
FROM dba_role_privs
ORDER BY grantee;

This query returns approximately 161 results. It is useful for auditing privilege assignments and ensuring that users only have access to what they need.

To filter only users with the sales_role role:

SELECT grantee, granted_role
FROM dba_role_privs
WHERE granted_role = 'SALES_ROLE';

The result shows that the only remaining capability of the role is CREATE SESSION (the connection), since CREATE TABLE has been revoked.

2.2 Oracle Predefined Roles and Privilege Auditing

List all predefined roles

Oracle 19c includes many predefined roles. To view them all:

SELECT ROLE FROM dba_roles
ORDER BY ROLE;

This command lists all the roles available in the Oracle 19c database.

Inspect the privileges of a predefined role (example: DBA)

To see what privileges are contained in the DBA role:

SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'DBA';

The DBA role contains a total of 235 system privileges. This is the most powerful role of the database.

Check table assignments for RESOURCE role

To see which tables are linked to the RESOURCE role:

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

In this example, no results are returned because the RESOURCE role does not have privileges on specific objects — it only has system privileges.

Grant CONNECT and RESOURCE roles to a user

These two roles are among the most commonly used for application users:

GRANT CONNECT, RESOURCE TO hr_user;
  • CONNECT: Allows the user to connect to the database.
  • RESOURCE: allows the user to create objects in their schema (tables, sequences, procedures, etc.).

Check the roles granted to a user

To confirm that the roles have been assigned:

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'HR_USER';

The result confirms that the CONNECT and RESOURCE roles have been granted.

Revoke a role that is no longer needed (principle of least privilege)

If a user no longer needs the RESOURCE role, it is revoked:

REVOKE RESOURCE FROM hr_user;

Revoking unnecessary roles is an essential practice — it follows the principle of least privilege and reduces the attack surface in the event of an account compromise.

To verify that the revocation has taken place:

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'HR_USER';

Overview of all role assignments

To get the complete list of all role assignments to all users:

SELECT grantee, granted_role
FROM dba_role_privs
ORDER BY grantee;

This query returns approximately 162 results, providing a complete overview of rights in the database.

Enable auditing of role changes

To record all role-related actions (assignment, revocation) in the audit trail:

AUDIT ROLE;

This command records in the DBA_AUDIT_TRAIL all GRANT ROLE and REVOKE ROLE type operations. This helps track who has granted or revoked roles, which helps prevent privilege escalation attacks.

Review audit trail for role actions

SELECT username, action_name, timestamp
FROM dba_audit_trail
WHERE action_name IN ('GRANT ROLE', 'REVOKE ROLE');

Although no results are returned in the example (no actions have been audited yet), this query forms the basis of any security audit on role management.

3. Implement advanced security features

3.1 Encrypting and hiding data with TDE and Data Redaction

What is TDE (Transparent Data Encryption)?

TDE (Transparent Data Encryption) is an Oracle feature that encrypts data *at rest: database files, backups, and redo logs. Even if someone gains physical access to the database files, they cannot read the data without the encryption key.

Step 1: Check crypto wallet status

Before any configuration, we check the current state of the encryption wallet:

SELECT * FROM v$encryption_wallet;

If no wallet yet exists, the result indicates that there is no encryption available (NOT_AVAILABLE).

Step 2: Configure the sqlnet.ora file

To activate TDE, you must first create a wallet folder in the Oracle administration directory, then configure the sqlnet.ora file to indicate the location of this wallet:

# Entrée à ajouter dans $ORACLE_HOME/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/oracle/admin/ORCL/wallet/)
    )
  )

The folder /opt/oracle/admin/ORCL/wallet/ must be created manually before adding this entry.

Step 3: Create the encrypted keystore

From SQL*Plus as sysdba, we create the keystore on the configured wallet folder:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/oracle/admin/ORCL/wallet/'
IDENTIFIED BY wallet_password;

After execution, the keystore file (ewallet.p12) appears in the wallet folder. You can verify the creation by navigating to the wallet folder from the system terminal.

Step 4: Grant file permissions and restart the instance

The Oracle instance must have access to the keystore file. We grant permissions at the OS level, then we restart the Oracle instance (under Windows):

net stop OracleServiceORCL
net start OracleServiceORCL

Step 5: Verify keystore recognition

After restart, in a new SQL*Plus session as sysdba:

SELECT status FROM v$encryption_wallet;

The result may indicate that the keystore is recognized but no master key is defined yet (OPEN_NO_MASTER_KEY).

Step 6: Set the master key

We define the master key with the WITH BACKUP option to create an automatic backup:

ADMINISTER KEY MANAGEMENT SET KEY
IDENTIFIED BY wallet_password
WITH BACKUP;

The confirmation message indicates that the keystore was successfully modified.

Step 7: Check the complete status of the wallet

SELECT wrl_parameter, status FROM v$encryption_wallet;

Step 8: Create an encrypted tablespace

Before creating encrypted tables, you must create a tablespace with default encryption:

CREATE TABLESPACE encrypted_ts
  DATAFILE '/opt/oracle/oradata/ORCL/encrypted_ts01.dbf' SIZE 100M
  DEFAULT STORAGE(ENCRYPT)
  ENCRYPTION USING AES256;

The data file (*.dbf) is created in the specified location. We can check its presence in the file system.

Step 9: Prepare a user for the encrypted tablespace

Since you cannot create tables encrypted as sysdba (SYS objects), you must create a dedicated user, grant him the necessary rights and switch to the PDB:

-- Ouvrir la PDB
ALTER PLUGGABLE DATABASE OraclePDB OPEN;
ALTER SESSION SET CONTAINER = OraclePDB;

-- Créer l'utilisateur
CREATE USER hr2 IDENTIFIED BY Password1;
GRANT CONNECT, RESOURCE TO hr2;
ALTER USER hr2 QUOTA UNLIMITED ON encrypted_ts;

-- Passer au schéma de cet utilisateur
ALTER SESSION SET CURRENT_SCHEMA = hr2;

Step 10: Open the wallet in the PDB

In the PDB, the wallet must also be explicitly opened:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
IDENTIFIED BY wallet_password
WITH BACKUP USING wallet_password;

Step 11: Create a table with an encrypted column

Once the wallet is opened in the PDB, you can create a table with a specific column encrypted:

CREATE TABLE hr2.customers (
  customer_id  NUMBER,
  name         VARCHAR2(100),
  credit_card  VARCHAR2(20) ENCRYPT USING AES256
) TABLESPACE encrypted_ts;

The credit_card column is individually encrypted with the AES256 algorithm. The table is stored in the encrypted tablespace. The confirmation indicates that the table was created successfully.

3.2 Advanced Access Control (VPD/RLS)

Overview of VPD (Virtual Private Database) and RLS (Row-Level Security)

VPD (Virtual Private Database) / RLS (Row-Level Security) is an Oracle mechanism that allows you to apply a security policy at the row level of a table. Concretely, two users executing the same SELECT query on a table can obtain different results depending on their session context — without modifying the application code.

Step 1: Prepare the environment

We start by opening the PDB and connecting to it:

-- Ouvrir toutes les PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Vérifier le conteneur courant
SHOW CON_NAME;
-- Résultat : CDB$ROOT

-- Basculer vers la PDB
ALTER SESSION SET CONTAINER = OraclePDB;

-- Vérifier
SHOW CON_NAME;
-- Résultat : ORACLEPDB

Step 2: Create XUSER and XMANAGER users

-- Créer XUSER
CREATE USER xuser IDENTIFIED BY Password1;
GRANT CONNECT, RESOURCE TO xuser;
ALTER USER xuser QUOTA UNLIMITED ON users;

-- Créer XMANAGER
CREATE USER xmanager IDENTIFIED BY Password1;
GRANT CONNECT, RESOURCE TO xmanager;
ALTER USER xmanager QUOTA UNLIMITED ON users;

Step 3: Create the employees table as XUSER

We connect as xuser:

CONNECT xuser/Password1@//localhost/OraclePDB

Then we create the table:

CREATE TABLE employees (
  employee_id   NUMBER,
  name          VARCHAR2(100),
  department_id NUMBER
);

Step 4: Insert test data

INSERT INTO employees VALUES (1, 'Alice', 10);
INSERT INTO employees VALUES (2, 'Bob', 20);
INSERT INTO employees VALUES (3, 'Charlie', 10);
COMMIT;

Alice and Charlie belong to department 10, Bob to department 20.

Step 5: Create the VPD security function (dept_security)

We return to sysdba and switch to the PDB. The dept_security function is a PL/SQL function that returns a predicate (an SQL condition). This predicate is automatically added to each query on the protected table.

CREATE OR REPLACE FUNCTION dept_security(
  schema_name IN VARCHAR2,
  table_name  IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  -- USERENV récupère des infos sur la session courante
  -- CLIENT_IDENTIFIER est une valeur définie par DBMS_SESSION.SET_IDENTIFIER
  RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
END dept_security;
/

The function returns the security condition: only lines whose department_id corresponds to the client identifier of the session (CLIENT_IDENTIFIER) will be accessible.

Step 6: Check availability of DBMS_RLS

Before applying the policy, we ensure that DBMS_RLS (the package that manages the RLS) is accessible:

SELECT object_name, object_type
FROM all_objects
WHERE object_name = 'DBMS_RLS';

If DBMS_RLS is inaccessible, Oracle would generate errors when applying fine-grained access control.

Step 7: Grant necessary privileges to XUSER

-- Permettre l'exécution du package RLS
GRANT EXECUTE ON dbms_rls TO xuser;

-- Accorder le privilège d'administration du déclencheur de base de données
GRANT ADMINISTER DATABASE TRIGGER TO xuser;

-- Permettre les opérations DML sur la table employees
GRANT SELECT, INSERT, UPDATE, DELETE ON xuser.employees TO xuser;

Step 8: Apply RLS policy with DBMS_RLS.ADD_POLICY

This PL/SQL block applies the row-level security policy to the employees table of the xuser schema:

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'XUSER',
    object_name     => 'EMPLOYEES',
    policy_name     => 'DEPT_POLICY',
    function_schema => 'XUSER',
    policy_function => 'DEPT_SECURITY'
  );
END;
/
  • object_schema / object_name: identify the table to protect.
  • policy_name: name given to the policy.
  • function_schema / policy_function: indicate the function that generates the security predicate.

Step 9: Compile and validate the security function

After disconnecting and reconnecting, we recompile the function:

ALTER FUNCTION dept_security COMPILE;

We check that it is valid:

SELECT object_name, status
FROM user_objects
WHERE object_name = 'DEPT_SECURITY';
-- Résultat attendu : VALID

Step 10: Test the RLS policy

We define the client identifier of the session on department 10:

EXEC DBMS_SESSION.SET_IDENTIFIER('10');

Then we execute the query:

SELECT * FROM xuser.employees;

The result only shows the rows corresponding to department 10 (Alice and Charlie). Bob, from department 20, is automatically filtered. The security policy is working correctly.

4. Configure and monitor auditing

4.1 Auditing: monitoring database activity

Enable database level auditing

Oracle traditional auditing records user actions in the AUD$ table (aka DBA_AUDIT_TRAIL). To enable system-level auditing:

ALTER SYSTEM SET AUDIT_TRAIL = DB SCOPE = SPFILE;

The DB option indicates that audit records are stored in the database itself. The SCOPE = SPFILE parameter means that the modification will be permanent (taken into account after restart).

Enable auditing on a table’s DML operations

To record each SELECT, INSERT, UPDATE and DELETE operation on the employees table, we use:

AUDIT SELECT, INSERT, UPDATE, DELETE ON employees BY ACCESS;

The BY ACCESS option indicates that each individual instruction is recorded in the audit trail — as opposed to BY SESSION which would only create one entry per session.

Audit user connections (sessions)

To log all login attempts (successful and failed):

AUDIT SESSION;

This audit is particularly useful for detecting unauthorized access attempts.

Audit connection failures only

To log only failed logins (incorrect passwords, locked accounts):

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

This targeted approach can effectively detect brute force attacks or compromised accounts.

Describe the structure of DBA_AUDIT_TRAIL

To find out which fields are available in the audit trail:

DESC dba_audit_trail;

Query Unified Audit Trail

Oracle 19c supports Unified Auditing. The UNIFIED_AUDIT_TRAIL view centralizes all audit events. The following query retrieves operations on the employees table:

SELECT event_timestamp,
       db_username,
       action_name,
       object_name
FROM   unified_audit_trail
WHERE  object_name = 'EMPLOYEES';

The columns returned are:

  • event_timestamp: Timestamp of the action.
  • db_username: database user who executed the action.
  • action_name: operation type (SELECT, INSERT, UPDATE, DELETE).
  • object_name: audited object (here EMPLOYEES).

If no audited action has yet taken place on the table, the query returns no rows selected.

4.2 Manage and secure audit trails

Check audit trail configuration

To confirm that traditional auditing is activated and stored in the database:

SHOW PARAMETER AUDIT_TRAIL;

The result shows DB (audit stored in database), which confirms that the configuration is correct.

View all audit records

The following query returns all audit logs stored in DBA_AUDIT_TRAIL:

SELECT timestamp,
       username,
       action_name,
       obj_name
FROM   dba_audit_trail
ORDER BY timestamp DESC;

The columns returned:

  • timestamp: time of action.
  • username: user who performed the action.
  • action_name: operation type (SELECT, INSERT, UPDATE, DELETE).
  • obj_name: affected object (table or view).

Filter audit logs by user

To see only actions performed by a specific user (TUSER):

SELECT timestamp, username, action_name, obj_name
FROM   dba_audit_trail
WHERE  username = 'TUSER';

Filter audit logs by table

To see only the actions performed on a specific table (EMPLOYEES):

SELECT timestamp, username, action_name, obj_name
FROM   dba_audit_trail
WHERE  obj_name = 'EMPLOYEES';

Count total number of audit records

SELECT COUNT(*) FROM dba_audit_trail;

A result of 0 indicates that no audit log yet exists in DBA_AUDIT_TRAIL.

Why cannot delete audit records directly

A common error is to try to delete records directly from DBA_AUDIT_TRAIL:

-- Ceci va ÉCHOUER :
DELETE FROM dba_audit_trail WHERE timestamp < SYSDATE - 30;

DBA_AUDIT_TRAIL is a *read-only system view. It is impossible to modify your data directly. Oracle returns an explicit error.

Archive old audit records

The best practice for managing the growth of the audit trail is to archive old records in a dedicated table:

CREATE TABLE audit_archive AS
SELECT *
FROM   dba_audit_trail
WHERE  timestamp < SYSDATE - 30;

This command copies all records older than 30 days into AUDIT_ARCHIVE. Moving old records avoids the performance degradation associated with the increasing size of DBA_AUDIT_TRAIL.

Secure the audit trail: check non-admin access

It is crucial to ensure that no non-administrator user has access to the underlying table AUD$:

SELECT grantee, privilege
FROM   dba_tab_privs
WHERE  table_name = 'AUD$';

If non-sysdba users have privileges on AUD$ (such as SELECT, INSERT, UPDATE or DELETE), they could view or even modify the audit logs, which represents a major security risk.

Revoke unauthorized access to audit trail

If unwanted privileges are discovered, they can be revoked. However, it is only possible to revoke privileges that you have granted yourself:

REVOKE SELECT ON sys.aud$ FROM some_user;

5. Ensuring Compliance with Good Security Practices

5.1 Oracle Hardening: Passwords, Networks and Vulnerabilities

View default profile password policies

The DEFAULT profile applies to any user without a custom profile. To view password-related settings only:

SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile        = 'DEFAULT'
AND    resource_type  = 'PASSWORD';

Change default profile security policy

The following command enforces password rules for all users who do not have a dedicated profile:

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME     90    -- Le mot de passe expire après 90 jours
  PASSWORD_REUSE_TIME    365   -- Impossible de réutiliser un mdp pendant 1 an
  PASSWORD_REUSE_MAX     5     -- Impossible de réutiliser les 5 derniers mdps
  PASSWORD_LOCK_TIME     1     -- Compte verrouillé 1 jour si bloqué
  FAILED_LOGIN_ATTEMPTS  5     -- Verrouillage après 5 tentatives échouées
  PASSWORD_GRACE_TIME    7;    -- 7 jours de grâce après expiration

Meaning of each parameter:

  • PASSWORD_LIFE_TIME 90: Password expires after 90 days, forcing a change.
  • PASSWORD_REUSE_TIME 365: a user cannot reuse a password for 1 year.
  • PASSWORD_REUSE_MAX 5: a user cannot reuse their last 5 passwords.
  • PASSWORD_LOCK_TIME 1: if the account is locked, it remains locked for 1 day before automatic unlocking.
  • FAILED_LOGIN_ATTEMPTS 5: Account locks after 5 consecutive failed login attempts.
  • PASSWORD_GRACE_TIME 7: after the password expires, the user has 7 days to change it.

Check updated settings

SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile       = 'DEFAULT'
AND    resource_type = 'PASSWORD';

Multitenancy constraint: common profiles must start with C##

In an Oracle multi-tenant architecture, common profiles (valid throughout the CDB) must have a name starting with C##. Attempting to create a profile with a simple name in CDB$ROOT generates an error.

The correct approach is to create local profiles directly in the PDB:

-- Basculer vers la racine CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Ouvrir explicitement la PDB
ALTER PLUGGABLE DATABASE OraclePDB OPEN;

-- Basculer vers la PDB
ALTER SESSION SET CONTAINER = OraclePDB;

-- Vérifier le conteneur courant
SHOW CON_NAME;
-- Résultat : ORACLEPDB

Create a security profile in the PDB

Once in the PDB, profile creation works without prefix restrictions:

CREATE PROFILE security_profile LIMIT
  FAILED_LOGIN_ATTEMPTS  5
  PASSWORD_LOCK_TIME     1
  PASSWORD_LIFE_TIME     90
  PASSWORD_REUSE_TIME    365
  PASSWORD_REUSE_MAX     5
  PASSWORD_GRACE_TIME    7;

Verify profile creation

SELECT DISTINCT profile
FROM   dba_profiles
WHERE  profile = 'SECURITY_PROFILE';

The query returns 17 lines, one for each parameter of the security_profile that was created.

Assign profile to user

ALTER USER some_user PROFILE security_profile;

Check REMOTE_LOGIN_PASSWORDFILE parameter

This parameter controls how users sysdba and sysoper authenticate remotely to the Oracle database:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

This parameter can take the values ​​NONE, EXCLUSIVE or SHARED and its setting has important implications for the security of administrative connections.

View enabled Oracle features

The V$OPTION view contains indicators for each Oracle feature, indicating whether it is available (TRUE) or not (FALSE) in the current instance:

SELECT parameter, value
FROM   v$option
ORDER BY parameter;

View all users and their status

To have a complete inventory of all users and their current access status:

SELECT username, account_status, profile
FROM   dba_users
ORDER BY account_status, username;

The status determines whether a user can log in (OPEN), or whether their account is restricted (LOCKED, EXPIRED, EXPIRED & LOCKED).

5.2 Security repositories, access reviews and cleanup

Identify users with the DBA role

The first step in an access review is to know which users have the highest rights:

SELECT grantee
FROM   dba_role_privs
WHERE  granted_role = 'DBA';

In a standard system, only SYS and SYSTEM should have the DBA role. Any other user in this list represents a potential risk.

Check the privileges of a specific user

To list the system privileges granted to a particular user (e.g. TUSER):

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'TUSER';

Identify users with high-risk privileges

Certain privileges, such as the ability to modify or delete any table, are particularly sensitive:

SELECT grantee, privilege
FROM   dba_sys_privs
WHERE  privilege IN (
  'DROP ANY TABLE',
  'ALTER ANY TABLE',
  'CREATE ANY TABLE',
  'DELETE ANY TABLE',
  'SELECT ANY TABLE'
)
ORDER BY grantee;

This query returns approximately 168 results, helping to detect high-risk privileges assigned to potentially unauthorized users.

Check privileges granted directly to users (excluding roles)

It is also important to identify privileges granted directly to users and not via roles. This type of grant is less easy to trace and revoke:

SELECT grantee, privilege
FROM   dba_sys_privs
WHERE  grantee NOT IN (SELECT role FROM dba_roles)
ORDER BY grantee;

List all database roles

To have a complete inventory of all the roles defined in the database:

SELECT role
FROM   dba_roles
ORDER BY role;

This query returns 89 roles in the demo environment.

Check which roles are assigned to which users

SELECT grantee, granted_role
FROM   dba_role_privs
ORDER BY grantee;

This query returns 159 results, providing a complete mapping of role assignments.

List all the privileges of a specific role (example: RESOURCE)

To understand exactly what a role allows you to do:

SELECT privilege
FROM   dba_sys_privs
WHERE  grantee = 'RESOURCE';

The RESOURCE role allows you to create tables, types, operators, procedures, sequences and triggers (CREATE TABLE, CREATE TYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, etc.).

View password policies for all profiles

SELECT profile, resource_name, limit
FROM   dba_profiles
WHERE  resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

Identify users with DBA access and their assigned profile

SELECT u.username, u.profile
FROM   dba_users u
WHERE  u.username IN (
  SELECT grantee
  FROM   dba_role_privs
  WHERE  granted_role = 'DBA'
);

Inspect failed login attempts

SELECT username, lcount
FROM   user$
WHERE  lcount > 0
ORDER BY lcount DESC;

Identify users with excessive number of failed attempts

The HAVING COUNT clause allows you to filter users who have accumulated a lot of failures:

SELECT username, COUNT(*) AS failed_attempts
FROM   dba_audit_trail
WHERE  action_name  = 'LOGON'
AND    returncode  != 0
GROUP BY username
HAVING COUNT(*) > 5
ORDER BY failed_attempts DESC;

This query is particularly useful for detecting brute force attacks or compromised accounts.

View audit logs for sensitive operations

To monitor critical operations of type GRANT, REVOKE and DROP:

SELECT username, action_name, timestamp, obj_name
FROM   dba_audit_trail
WHERE  action_name IN ('GRANT', 'REVOKE', 'DROP')
ORDER BY timestamp DESC;

These queries constitute the basis of any approach to auditing user access, roles and privileges in Oracle Database 19c, and make it possible to maintain a security posture consistent with industry best practices.

6. Summary of Key System Tables and Views

View / TableDescription
DBA_USERSList all database users with their status and profile
DBA_SYS_PRIVSSystem privileges granted to users and roles
DBA_TAB_PRIVSPrivileges on objects (tables, views, procedures) granted
DBA_ROLE_PRIVSRoles granted to users and other roles
DBA_ROLESList of all roles defined in the database
DBA_PROFILESSettings for all profiles (resources and passwords)
DBA_AUDIT_TRAILTraditional audit trail (read-only)
UNIFIED_AUDIT_TRAILUnified audit trail (Oracle 12c+)
V$PDBSInformation about Pluggable Databases
V$ENCRYPTION_WALLETTDE Crypto Wallet Status
V$OPTIONOracle Features Enabled in Instance
SYSTEM_PRIVILEGE_MAPAll system privileges available (257 in Oracle 19c)
USER$Internal table containing the failed attempts counter (lcount)
ALL_OBJECTSAll objects accessible to the current user

7. Summary of essential SQL commands

User management

-- Créer un utilisateur
CREATE USER nom_user IDENTIFIED BY mot_de_passe
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;

-- Modifier un utilisateur
ALTER USER nom_user PROFILE mon_profil;
ALTER USER nom_user ACCOUNT LOCK;
ALTER USER nom_user ACCOUNT UNLOCK;
ALTER USER nom_user PASSWORD EXPIRE;

-- Supprimer un utilisateur
DROP USER nom_user CASCADE;

Profile management

-- Créer un profil
CREATE PROFILE mon_profil LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME    1
  PASSWORD_LIFE_TIME    90
  PASSWORD_REUSE_TIME   365
  PASSWORD_REUSE_MAX    5
  PASSWORD_GRACE_TIME   7;

-- Modifier un profil
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;

-- Supprimer un profil
DROP PROFILE mon_profil CASCADE;

Privilege management

-- Accorder des privilèges système
GRANT CREATE SESSION TO nom_user;
GRANT CREATE TABLE TO nom_user;
GRANT DBA TO nom_user;

-- Révoquer des privilèges système
REVOKE CREATE TABLE FROM nom_user;

-- Accorder des privilèges sur objet
GRANT SELECT ON schema.table TO nom_user;
GRANT EXECUTE ON mon_package TO nom_user;

-- Révoquer des privilèges sur objet
REVOKE SELECT ON schema.table FROM nom_user;

Role management

-- Créer un rôle
CREATE ROLE mon_role;

-- Accorder des privilèges à un rôle
GRANT CREATE SESSION TO mon_role;
GRANT SELECT ON hr.employees TO mon_role;

-- Assigner un rôle à un utilisateur
GRANT mon_role TO nom_user;

-- Révoquer un rôle d'un utilisateur
REVOKE mon_role FROM nom_user;

-- Supprimer un rôle
DROP ROLE mon_role;

CDB / PDB navigation

-- Vérifier le conteneur courant
SHOW CON_NAME;

-- Basculer vers une PDB
ALTER SESSION SET CONTAINER = OraclePDB;

-- Basculer vers la racine CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;

-- Ouvrir une PDB
ALTER PLUGGABLE DATABASE OraclePDB OPEN;

-- Ouvrir toutes les PDBs
ALTER PLUGGABLE DATABASE ALL OPEN;

TDE (Transparent Data Encryption)

-- Vérifier le statut du wallet
SELECT * FROM v$encryption_wallet;

-- Créer le keystore
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/chemin/wallet/'
IDENTIFIED BY mot_de_passe_wallet;

-- Définir la master key
ADMINISTER KEY MANAGEMENT SET KEY
IDENTIFIED BY mot_de_passe_wallet WITH BACKUP;

-- Ouvrir le wallet
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
IDENTIFIED BY mot_de_passe_wallet WITH BACKUP
USING mot_de_passe_wallet;

Audit

-- Activer l'audit système
ALTER SYSTEM SET AUDIT_TRAIL = DB SCOPE = SPFILE;

-- Activer l'audit sur une table
AUDIT SELECT, INSERT, UPDATE, DELETE ON schema.table BY ACCESS;

-- Activer l'audit des sessions
AUDIT SESSION;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- Activer l'audit des rôles
AUDIT ROLE;

Search Terms

manage · users · roles · security · oracle · database · 19c · databases · sql · user · audit · check · role · privileges · profile · view · access · auditing · management · password · pdb · status · trail · data

More Oracle Database 19c courses

View all 5

Implement High Availability and Disaster Recovery Solutions on Oracle Database 19c

Oracle Recovery Manager (RMAN) implements the Oracle solution for backup and recovery needs.

Advanced

Install and Configure Oracle Database 19c

This hands-on course teaches you how to install, configure, and validate Oracle Database 19c in a step-by-step manner.

Beginner

Perform Backup and Recovery Operations on Oracle Database 19c

RMAN is a built-in command line tool and Oracle's recommended tool for backup and recovery.

Intermediate

Understand Oracle Database 19c Architecture

Oracle Database 19c is a complex relational database whose architecture is documented in an official 40-page Oracle technical document. Understanding this architecture is essential for an...

Beginner

Interested in this course?

Contact us to book it or get a custom training plan for your team.

Get in Touch Back to Oracle Database 19c