Oracle module administration

This document covers the administration of the Oracle module.

0. Prerequisites and module configuration

Prerequisites

• PHP extension oci8 + Oracle Instant Client on the web server. Without it, the module is shown but no connection is possible.
• ZipArchive extension if you use Oracle Cloud connections (wallet import).
• The oracle_admin role to administer (the app_admin super-role also works).

Module configuration

The Oracle module has no global configuration page and no activation hook: there is nothing to set before enabling it, and no securite/modules/oracle.json file is created. Activation simply creates the oracle_admin and oracle_user roles.

All configuration is application-level and is done in the module’s pages, in this order:

1. Groups (§2) — organise the databases; the Default group exists out of the box.
2. Databases (§1) — declare the connections (standard or Cloud/wallet).
3. Access (§3) — grant rights per group × subgroup.
4. Internal Oracle roles (§4) — optional, to share rights.

Subgroups are fixed by the application: dev, homol, perf, pre-prod, prod, secours.

Data and backup

Connections live in the application database (oracle_* tables), passwords there are encrypted, and Cloud wallets are extracted under securite/wallets/<database_id>/.

Back up securite/master.key together with the application database: without this key, the stored passwords are unrecoverable.

1. Registering databases

Access: oracle_admin only.

Connection types

graph TD
 A[New database] --> B{Type?}
 B -->|Standard| C["host:port/service_name"]
 B -->|Cloud / Wallet| D["TNS Alias + wallet ZIP file"]
 C --> E[Connection test]
 D --> F[Wallet extraction<br>into securite/wallets/]
 F --> E
 E -->|OK| G[Saved]

Standard connection

Cloud connection (wallet)

The wallet is extracted into securite/wallets/<database_id>/. The sqlnet.ora file is automatically edited to point to the right directory. Cloud databases automatically have the Diagnostic and Tuning Pack licences.

Common fields

Connection test

The Test button performs a real OCI8 connection and shows the Oracle version on success.

Session initialisation

On every connection, the application automatically sets:

• NLS formats (ISO-8601 dates, binary sort)
• Client identifier (dbms_session.set_identifier)
• Module/Action (dbms_application_info.set_module) — visible in V$SESSION for auditing

2. Database groups

Access: oracle_admin only.

Databases are organised into groups (e.g. “Production”, “Dev/Test”, “Cloud”). Each group holds databases split into subgroups (environments).

Groups are the basis for access control (see next section).

3. Oracle access control

Access: oracle_admin only.

Access model

Access to databases is controlled by a principal × group × subgroup × right matrix:

graph TD
 A[User] --> B{Access?}
 B -->|oracle_admin| C[All databases]
 B -->|app_admin| C
 B -->|Direct access| D[Databases of the granted group/subgroup]
 B -->|Via Oracle role| E[Databases of the role's group/subgroup]
 D --> F{Right?}
 E --> F
 F -->|consultation| G[Read only]
 F -->|dba_dev| H[Read + kill session + SQL fix]
 F -->|dba| I[All DBA actions]

Principals

Two kinds of principal:

Rights

Hierarchy: dba > dba_dev > consultation.

The oracle_right_at_least($right, $minLevel) helper centralises level comparisons.

Editing interface

1. List view: a table of principals with the number of accessible groups/subgroups
2. Edit view (click a principal): a groups × subgroups matrix with a selector (—, consultation, dev, dba)

Changes are transactional: the old rights are removed and the new ones inserted.

Special case: group_id = NULL means “all groups (present and future)”. subgroup = NULL means “all subgroups”.

4. Internal Oracle roles

Access: oracle_admin only.

Note: these roles are internal to the Oracle module and distinct from the application roles (oracle_admin, oracle_user). They are an additional mechanism for grouping access permissions.

Structure

Operations

Right resolution is recursive (a role can include other roles, with protection against cycles).

5. Database import/export

Access: oracle_admin only.

Export

Generates a JSON file containing all registered databases with:

• Connection parameters
• Passwords (encrypted)
• Group and subgroup
• Description

Import

Upload a JSON file: the databases are merged into the existing registry. Passwords are re-encrypted if needed. The associated wallets are re-extracted.

6. Oracle licences

The module automatically detects the Diagnostic Pack and Tuning Pack licences of each database.

Detection

Impact on features

When a licence is missing, the affected reports display a warning banner with the value of the CONTROL_MANAGEMENT_PACK_ACCESS parameter.

7. sw$bash_snapper administration

Context

sw$bash_snapper is a PL/SQL package that samples Oracle session activity in an AWR-like fashion, without requiring a Diagnostic Pack licence. It is a free alternative for databases without that licence.

Prerequisites

• DBA right on the target database
• An Oracle account with the CREATE TABLE, CREATE PROCEDURE, MANAGE SCHEDULER privileges

Life cycle

stateDiagram-v2
 [*] --> Not_installed
 Not_installed --> Installed : Install
 Installed --> Running : Start
 Running --> Installed : Stop
 Installed --> Not_installed : Uninstall
 Running --> Running : Purge

Objects created

Administration interface

From the Diagnostics > sw$bash_snapper report:

Information shown:

• Collector status (running / stopped)
• Package version
• Number of active and historical samples
• Number of snapshots
• Date range of the available data

Actions:

Editable parameters:

Log: the last 20 entries of the collector log (SW$BASH_LOG) are shown at the bottom of the page with severity and message.

Automatic fallback

When sw$bash_snapper is installed and the database has no Diagnostic Pack licence, the historical reports (ASH, heatmaps) automatically switch to the SW$BASH_* tables instead of DBA_HIST_*. This switch is transparent to the user.

8. DBA operations

The following operations require the DBA right on the selected database, unless stated otherwise (some are available from dba_dev).

Kill Session (dba_dev+)

Available from the Real time > Active sessions and Real time > Locks reports.

• Runs ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id'
• Confirmation requested before execution
• CSRF protected

Statistics gathering (dba)

Available from the Statistics gathering dashboard:

• A “Gather” button next to each table with stale statistics
• Runs DBMS_STATS.GATHER_TABLE_STATS via DBMS_SCHEDULER (asynchronous execution)

Scheduling system/dictionary stats

The Schedule button opens a modal to launch an asynchronous gather via DBMS_SCHEDULER.CREATE_JOB (auto_drop=TRUE):

Options:

• Mode (system stats only): NOWORKLOAD, INTERVAL, EXADATA
• Flush buffer cache: runs ALTER SYSTEM FLUSH BUFFER_CACHE before gathering

SQL fix (dba_dev+)

Available from the SQL detail page:

Dropping SQL Profiles (SQL Tuning page)

The Config > SQL Tuning page shows the list of existing SQL Profiles. DBAs can drop a profile directly from this page via the Delete button in the Actions column.

Tablespaces & Redo Logs (dba)

Available from the Config > Tablespaces & Redo Logs report:

Resizing datafiles

The name of each data tablespace (underlined, clickable from the first render of the chart) opens a modal that loads the list of datafiles via AJAX, then lets you change the size of each one.

• Runs ALTER DATABASE DATAFILE '<path>' RESIZE <N>M
• Validation: the file path is checked against DBA_DATA_FILES before execution (injection protection)

Managing redo log groups

Resizing all redo logs

The Resize all redo logs button re-creates each group with the new size. The operation proceeds by:

1. Log switch (ALTER SYSTEM SWITCH LOGFILE) + checkpoint
2. Drop of each INACTIVE group
3. Re-creation with the new size
4. ACTIVE/CURRENT groups handled last after a new switch

The result shows the number of groups re-created, skipped and in error.

DBMS_SCHEDULER management (dba)

Available from the Scheduler dashboard:

Dropping asks for confirmation. All actions are CSRF and DBA-right protected.

sw$bash_snapper management (dba)

Install, start, stop, purge and uninstall the collector (see section 7).

Statistics & Auto Tuning (dba)

From the Configuration → Statistics gathering and Configuration → SQL Tuning pages:

All these actions are CSRF protected and require the DBA right.

TDE — switching to auto-login (dba / app_admin)

From the TDE / Wallets dashboard, a “Switch to auto-login” button is offered to users with the DBA right on the database or the app_admin role (site administrator), and only if the wallet is not already in auto-login.

A modal window asks for the keystore password and, optionally, a local auto-login (LOCAL_AUTO_LOGIN, tied to the host — not recommended on RAC since the wallet cannot be copied to another node).

The action runs:

ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE
 FROM KEYSTORE '<location>' IDENTIFIED BY "<password>";

• The keystore location is read on the server (V$ENCRYPTION_WALLET), never supplied by the client.
• The password is inline in the DDL: double quotes and line breaks are rejected (server-side validation). It is not logged by the application.
• The Oracle connection must have the SYSDBA / SYSKM privilege (or ADMINISTER KEY MANAGEMENT); otherwise the ORA- error is returned.
• Deferred effect: the auto-login keystore (cwallet.sso) is created but only becomes active at the next instance restart (or after closing / reopening the keystore).

Appendix: Oracle module schema

erDiagram
 oracle_databases ||--o{ oracle_access : "controlled by"
 oracle_groups ||--o{ oracle_databases : "contains"
 oracle_roles ||--o{ oracle_role_perms : "has permissions"
 oracle_roles ||--o{ oracle_role_grants : "granted via"
 oracle_databases {
 int id PK
 text name
 int group_id FK
 text subgroup
 text connection_type
 text host
 int port
 text service_name
 text tns_alias
 text username
 text password
 int sysdba
 text description
 }
 oracle_groups {
 int id PK
 text name UK
 }
 oracle_access {
 int id PK
 int user_id FK
 text ad_group
 int group_id FK
 text subgroup
 text right
 }
 oracle_roles {
 int id PK
 text name
 text description
 }
 oracle_role_perms {
 int id PK
 int role_id FK
 int group_id FK
 text subgroup
 text right
 }
 oracle_role_grants {
 int id PK
 int role_id FK
 int user_id FK
 text ad_group
 }