A number of SQL scripts as well as SQL Developer Reports for various DBA activities are available in this directory. The scripts focus on setup, configuration and analysis of database security topics such as Oracle Unified Audit, Oracle Centrally Managed Users, Advanced Security, Authentication, Authorisation, Encryption and more.. An updated version of the scripts is available via GitHub on oehrli/oradba.
The script names follow the format:
<script_qualifier><privileges_qualifier><topic_qualifier>_<use_case>.sql
The script qualifier is used to determine whether a script is used to read information or to configure, e.g. create, modify, activate, etc.
| Qualifier | Stands For | Comment |
|---|---|---|
| s | Show | Output only on screen |
| d | Delete | Delete any objects, configuration etc |
| i | Initialize | Initializes or enable a configuration |
| c | Create | Create any objects, configuration etc. |
| u | Update | Update any object |
| g | Grant | Grants some objects or system privileges |
The privilege qualifier is used to determine what privileges are required by a script.
| Qualifier | Stands For | Comment |
|---|---|---|
| s | SYS | SYS, SYSDBA, SYSKM, SYSDG, SYSBACKUP or Internal. Depending on use case |
| d | DBA | SYSTEM or any other user with DBA role |
| o | Owner | Object owner |
| p | Create | Needs some special privileges according to the scripts inline comments |
| a | Audit | Audit roles like AUDIT_ADMIN or AUDIT_VIEWER |
Topic Qualifier is used to assign the different scripts to a certain topic and thus to be able to sort them better.
| Qualifier | Stands For | Comment |
|---|---|---|
| ua | Unified Audit | Everything related to Oracle Unified Audit |
| ta | Traditional Audit | Everything related to Oracle traditional Audit |
| sec | Security | Oracle security related stuff |
| enc | Encryption | Oracle Transparent DataEncryption |
| a | Admin | Database Administration |
The following SQL scripts are available.
| Script | Alias | Purpose |
|---|---|---|
| ssa_hip.sql | hip.sql | Show all (hidden and regular) init parameter |
The following SQL scripts are available.
| Script | Alias | Purpose |
|---|---|---|
| cdsec_credbarestrole.sql | Script to create a restricted DBA role including re-grant to existing users. | |
| cssec_pwverify.sql | Create a custom password verify function. The password strength and complexity can be configured by the internal variables at create time | |
| sdsec_sysobj.sql | sysobj.sql | Show respectively create a list of granted SYS object privileges |
| sdsec_syspriv.sql | syspriv.sql | Show respectively create a list of granted system privileges |
| spsec_usrinf.sql | whoami.sql | Script to show session information of current user. |
| sssec_pwverify_test.sql | Script to verify the custom password verify function. List of passwords to be tested have to added to the script / varchar2 array |
not yet available
The following SQL scripts are available.
| Script | Purpose |
|---|---|
| caua_pol.sql | Create custom local audit policies policies |
| cdua_init.sql | Initialize Audit environment (create tablespace, reorganize tables, create jobs) |
| daua_pol.sql | Disable all audit policies and drop all non-Oracle maintained policies |
| iaua_pol.sql | Enable custom local audit policies policies |
| saua_as.sql | Show audit sessions for audit any type |
| saua_asbck.sql | Show audit sessions for audit type RMAN |
| saua_asdbv.sql | Show audit sessions for audit type Database Vault |
| saua_asdet.sql | Show entries of a particular audit session with unified_audit_policies |
| saua_asdetsql.sql | Show entries of a particular audit session with SQL_TEXT |
| saua_asdp.sql | Show audit sessions for audit type Datapump |
| saua_asfga.sql | Show audit sessions for audit type Fine Grained Audit |
| saua_asstd.sql | Show audit sessions for audit type Standard |
| saua_critobj.sql | Show recently accessed critical objects |
| saua_critprivs.sql | Show recently used critical privileges |
| saua_grants.sql | Show recently granted privileges |
| saua_info.sql | Show information about the audit trails |
| saua_logfail.sql | Show failed logins |
| saua_logon.sql | Show all logins |
| saua_pol.sql | Show local audit policies policies. A join of the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES |
| saua_report.sql | Create a simple report by running all show saua_xxxx.sql show scripts |
| saua_tabsize.sql | Show Unified Audit trail table and partition size |
| saua_teact.sql | Show top unified audit events by action for current DBID |
| saua_tecli.sql | Show top unified audit events by client_program_name for current DBID |
| saua_tedbid.sql | Show top unified audit events by DBID |
| saua_tehost.sql | Show top unified audit events by userhost for current DBID |
| saua_teobj.sql | Show top unified audit events by object_name for current DBID |
| saua_teobjusr.sql | Show top unified audit events by Object Name without Oracle maintained schemas for current DBID |
| saua_teosusr.sql | Show top unified audit events by os_username for current DBID |
| saua_teown.sql | Show top unified audit events by object_schema for current DBID |
| saua_tepol.sql | Show top unified audit events by unified_audit_policies for current DBID |
| saua_tepoldet.sql | Show top unified audit events by unified_audit_policies, dbusername, action for current DBID |
| saua_teusr.sql | Show top unified audit events by dbusername for current DBID |
| saua_user.sql | Show recently created users |
| sdua_crpolstm.sql | Generate statements to create all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES |
| sdua_dipolstm.sql | Generate statements to disable all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES |
| sdua_drpolstm.sql | Generate statements to drop all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES |
| sdua_enpolstm.sql | Generate statements to enable all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES |
| sdua_prgstm.sql | Generate Unified Audit trail storage purge statements |
| sdua_stostm.sql | Generate Unified Audit trail storage usage modification statements |
| sdua_usage.sql | Show Unified Audit trail storage usage |
Predefined reports for Unified Audit Assessment available via unified_audit_reports.xml
The scripts are divided into the following categories for easier organisation.
- Generic Audit
- Audit Configuration
- Audit Sessions
- Generate Statements
- Top Audit Events
| Folder | Report | Purpose |
|---|---|---|
| Generic | Audit Events by Day | Chart with number of Audit events by days with a couple of subqueries for history, by hour or DB Info |
| Generic | Audit Events by User | Chart with number of Audit events by user with a couple of subqueries for history, by hour or DB Info |
| Generic | Audit Events by User | Show of Audit Events by Users with a couple of subqueries for audit policies, actions, clients and Policy |
| Audit Configuration | Audit Storage Usage | Information about the Audit storage usage and configuration. |
| Audit Configuration | Clean Up Events | Displays the audit cleanup event history |
| Audit Configuration | Clean Up Jobs | Displays the currently configured audit trail purge jobs |
| Audit Configuration | Configuration | Show current audit configuration parameter |
| Audit Configuration | Last Archive Timestamp | Displays the last archive timestamps set for the audit trails |
| Audit Configuration | Unified Audit Policies | Display overview about unified audit policies based on the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES. |
| Audit Sessions | Proxy Sessions | Show information about proxy sessions for audit type Standard based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Session by Audit Type Standard | Show information about sessions for audit type Standard based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Session Details | Show details of a particular session |
| Audit Sessions | Session Overview | Overview of standard audit session |
| Audit Sessions | Sessions by any Audit Type | Show information about sessions any audit type based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type Database Vault | Show information about sessions for audit type Database Vault based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type Datapump | Show information about sessions for audit type Datapump based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type Direct path API | Show information about sessions for audit type Direct path API based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type Fine Grained Audit | Show information about sessions for audit type Fine Grained Audit based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type Protocol | Show information about sessions for audit type Protocol based on UNIFIED_AUDIT_TRAIL |
| Audit Sessions | Sessions by Audit Type RMAN_AUDIT | Show information about sessions for audit type RMAN_AUDIT based on UNIFIED_AUDIT_TRAIL |
| Generate Statements | Create all Audit Policy | Generate statements to create all audit policies as they are currently set in AUDIT_UNIFIED_ENABLED_POLICIES. Requires DBA privileges. |
| Generate Statements | Disable all Audit Policy | Generate statements to disable all audit policies as they are currently set in AUDIT_UNIFIED_ENABLED_POLICIES. |
| Generate Statements | Drop all Audit Policy | Generate statements to drop all audit policies as they are currently set in AUDIT_UNIFIED_ENABLED_POLICIES. |
| Generate Statements | Enable all Audit Policy | Generate statements to enable all audit policies as they are currently set in AUDIT_UNIFIED_ENABLED_POLICIES. |
| Top Audit Events | Top Audit Events by Action | Show top unified audit events by Action Name |
| Top Audit Events | Top Audit Events by Application Context | Show top unified audit events by Application Context |
| Top Audit Events | Top Audit Events by Audit Type | Show top unified audit events by Audit Type |
| Top Audit Events | Top Audit Events by Client | Show top unified audit events by Client |
| Top Audit Events | Top Audit Events by Client Program name | Show top unified audit events by Client Program |
| Top Audit Events | Top Audit Events by DBID | Show top unified audit events by Database ID |
| Top Audit Events | Top Audit Events by External User ID | Show top unified audit events by External User ID |
| Top Audit Events | Top Audit Events by Global User ID | Show top unified audit events by Global User ID |
| Top Audit Events | Top Audit Events by Object Name | Show top unified audit events by Object Name |
| Top Audit Events | Top Audit Events by none Oracle Object Name | Show top unified audit events by Object Name without Oracle maintained schemas |
| Top Audit Events | Top Audit Events by Object Schema | Show top unified audit events by Object Schema |
| Top Audit Events | Top Audit Events by OS User | Show top unified audit events by OS User |
| Top Audit Events | Top Audit Events by Unified Policy | Show top unified audit events by Unified Audit Policy |
| Top Audit Events | Top Audit Events by SQL Text | Show top unified audit events by SQL Text |
| Top Audit Events | Top Audit Events by User | Show top unified audit events by User |
The following SQL scripts are available.
| Script | Purpose |
|---|---|
| csenc_master.sql | Create master encryption key for TDE configured keystore must be set before hand. Works for CDB as well PDB. |
| csenc_swkeystore.sql | Create TDE software keystore and master encryption key in CDB$ROOT in the WALLET_ROOT directory. |
| ddenc_wroot.sql | Reset init.ora parameter WALLET_ROOT for TDE. This script should run in CDB$ROOT. A manual restart of the database is mandatory to activate WALLET_ROOT |
| dsenc_tde.sql | Remove TDE and software keystore configuration in a single tenant or container database. This scripts does use several other scripts to remove TDE and it also includes restart of the database. |
| idenc_lostkey.sql | Set hidden parameter _db_discard_lost_masterkey to force discard of lost master keys |
| idenc_wroot.sql | Initialize init.ora parameter WALLET_ROOT for TDE with software keystore. This script should run in CDB$ROOT. A manual restart of the database is mandatory to activate WALLET_ROOT |
| isenc_tde_pdbiso_prepare.sql | Prepare TDE in a PDB in isolation mode i.e., with a dedicated wallet in WALLET_ROOT for this pdb. Whereby this just prepare the steps as SYSDBA. The software keystore itself will be created by SYSKM |
| isenc_tde_pdbiso_keyadmin.sql | Create the software keystore in PDB in isolation mode as SYSKM Environment must be prepared before with isenc_tde_pdbiso_prepare.sql |
| isenc_tde_pdbiso.sql | Initialize TDE in a PDB in isolation mode i.e., with a dedicated wallet in WALLET_ROOT for this pdb. The CDB must be configured for TDE beforehand. This scripts does use several other scripts to enable TDE and it also includes restart of the pdb. |
| isenc_tde_pdbuni.sql | Initialize TDE in a PDB in united mode i.e., with a common wallet of the CDB in WALLET_ROOT. The CDB must be configured for TDE beforehand. This scripts does use several other scripts to enable TDE and it also includes restart of the pdb. |
| isenc_tde.sql | Initialize TDE for a single tenant or container database. This scripts does use several other scripts to enable TDE and it also includes restart of the database. |
| ssenc_info.sql | Show information about the TDE Configuration |
| csenc_swkeystore_backup.sql | Create DBMS_SCHEDULER program, schedule and job for TDE software keystore backups. Backup path and directory can be specified. Default is set to WALLET_ROOT/backup |
| dsenc_swkeystore_backup.sql | Delete DBMS_SCHEDULER program, schedule and job for TDE software keystore backups created with csenc_swkeystore_backup.sql |
| ssenc_swkeystore_backup.sql | Show DBMS_SCHEDULER program, schedule and job for TDE software keystore backups |
| sdenc_dbf_off_dec.sql | Generate chunks to offline encrypt datafiles |
| sdenc_dbf_off_enc.sql | Generate chunks to offline decrypt datafiles |
| senc_tde_ops_csv.sql | Show TDE operations from V$SESSION_LONGOPS as CSV |
| senc_tde_ops_run.sql | Show TDE running operations from V$SESSION_LONGOPS |
| senc_tde_ops.sql | Show TDE operations from V$SESSION_LONGOPS |