Scripts for Oracle DBSecCentral Account Privileges on Targets

Oracle Database Security Central provides scripts for Oracle Database, Microsoft, IBM DB2 for LUW, and MySQL plug-ins.

About Scripts for Setting up Oracle Database Security Central Account Privileges

You can use scripts to set up accounts and privileges for Oracle Database Security Central.

You must create a user account with the appropriate privileges on each target for Oracle Database Security Central can monitor and collect audit data. Oracle Database Security Central provides setup scripts for this purpose so that you can configure your database targets. Depending on the type of target, the scripts set up user privileges that enable Oracle Database Security Central to do the following functions:

• Audit data collection

• Policy management

• Stored procedure auditing

• User Assessment and Security Assessment

• Native Network Encrypted Traffic monitoring

• Audit trail cleanup (for some targets)

• Sensitive Data Discovery

When you deploy the Audit Vault Agent on a host computer (usually the same computer as the target), the setup scripts for creating the user permissions for Oracle Database Security Central are in the following directory (Linux example follows):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.<secured_target_type>/config/

Oracle Database Setup Scripts

Use these scripts to set up user account privileges for Oracle Database Security Central on Oracle Database targets.

You can download the setup scripts from Audit Vault Server console or find them under deployed Agents. These scripts aren’t required for Database Firewall monitoring.

Download setup scripts from Audit Vault Server

1. Log in the Audit Vault Server console as an administrator.

2. Select Targets.

3. Select Target Setup Script.

4. Download and save the scripts.

Locate scripts in Audit Vault Agents

When you deploy the Audit Vault Agent on a host computer (usually the same computer as the target), the setup scripts for creating the user permissions for Oracle Database Security Central are located in the directory (Linux), $AGENT_HOME/av/plugins/com.oracle.av.plugin.secured_target_type/config/.

Set up user privileges on Oracle Database targets

To set up or revoke Oracle Database Security Central user privileges on an Oracle Database target:

1. Create a user account for Oracle Database Security Central on the Oracle Database. For example:

   CREATE USER <username> IDENTIFIED BY <password>

   You will use this user name and password when registering this Oracle Database as a target in the Audit Vault Server.

2. Connect as the SYS user with the SYSDBA privilege. For example:

   CONNECT SYS / AS SYSDBA

3. To set up Oracle Database Security Central user privileges, run the following setup script and then enter the user name and mode at the prompts:

   @oracle_user_setup.sql

   Alternatively, you can enter the script, user name, and mode on one line:

   @oracle_user_setup.sql <username> <mode>

   • username: Enter the name of the user you created in Step 1.

   • mode: Enter one of the following:

     • SETUP: To grant privileges for collecting data and stored procedure auditing from any audit trail type for this database. For example, use this mode for a TABLE audit trail in Oracle Database Security Central.

     • RETRIEVE_DB_POLICIES: To grant the DBSecCentral user the DV_MONITOR and DV_SECANALYST role on the database, allowing DBSecCentral to retrieve Database Vault policies.

     • ASSESSMENT_DISCOVERY: To enable user assessment, security assessment and sensitive data discovery for this database.

     • AUDIT_ADMIN: To grant AUDIT_ADMIN role for managing audit policies.

   Note:

   • For audit collection from CDB, create a user in the CDB and run the oracle_user_setup.sql script for this user.

   • For audit collection from individual PDB, first alter the session to switch to the PDB, create the user on the PDB and then run the oracle_user_setup.sql script for this user.

4. If Database Vault is installed and enabled on the Oracle Database, log in as a user who has been granted the DV_OWNER role do the following:

   1. SETUP mode:

      1. Grant the Oracle Database Security Central user the DV_MONITOR role for this Oracle Database:

         GRANT DV_MONITOR TO <username>;

         Ensure you use the same <username> from Step 1.

         The DV_MONITOR role is used for monitoring Oracle Database Vault.

      2. Starting with Oracle AI Database 26ai, authorize the Oracle Database Security Central user to use Oracle SQL Firewall and unified audit trail collection:

         EXEC DBMS_MACADM.AUTHORIZE_SQL_FIREWALL('<username>');
         EXEC DBMS_MACADM.AUTHORIZE_AUDIT_VIEWER('<username>');

      3. For Oracle Database versions 26ai or later collect audit data from traditional auditing tables (for example, SYS.AUD$,SYS.FGA_LOG$):

         1. Temporarily add the SYS user to the Oracle Audit realm:

            BEGIN
            DBMS_MACADM.ADD_AUTH_TO_REALM(
                'Oracle Audit',
                'SYS',
                NULL,
                DBMS_MACUTL.G_REALM_AUTH_OWNER
            );
            END;
            /

         2. Grant the required permissions to the audit user:

            GRANT SELECT ON SYS.AUD$ TO username;
            GRANT SELECT ON SYS.FGA_LOG$ TO username;

         3. Remove SYS from the Oracle Audit realm:

            BEGIN
            DVSYS.DBMS_MACADM.DELETE_AUTH_FROM_REALM(
                realm_name => 'Oracle Audit',
                grantee    => 'SYS'
            );
            END;
            /

   2. ASSESSMENT_DISCOVERY mode:

      1. Grant the Oracle Database Security Central user the DV_SECANALYST role on this Oracle Database. For example:

         GRANT DV_SECANALYST TO <username>;

         For <username>, enter the user name you created in Step 1.

         The DV_SECANALYST role enables Oracle Database Security Central to monitor and collect audit trail data for Oracle Database Vault, and run Oracle Database Vault reports.

      2. Authorize the Oracle Database Security Central user to access the Oracle Label Security realm as a participant:

         EXEC DBMS_MACADM.ADD_AUTH_TO_REALM('Oracle Label Security', 'username', NULL, DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

         The Oracle Label Security realm protects Oracle Label Security schemas and roles.

      3. Starting with Oracle AI Database 26ai, authorize the Oracle Database Security Central user to perform the auditing tasks that are available to users who have been granted the AUDIT_VIEWER role:

         EXEC DBMS_MACADM.AUTHORIZE_AUDIT_VIEWER('username');

   3. RETRIEVE_DB_POLICIES mode:

      1. Grant the Oracle Database Security Central user the DV_SECANALYST role on this Oracle Database. For example:

         GRANT DV_SECANALYST TO <username>;

         For <username>, enter the user name you created in Step 1.

         The DV_SECANALYST role enables Oracle Database Security Central to monitor and collect audit trail data for Oracle Database Vault, and run Oracle Database Vault reports.

   4. AUDIT_ADMIN mode:

      1. Starting with Oracle AI Database 26ai, authorize the Oracle Database Security Central user to perform the auditing tasks that are available to users who have been granted the AUDIT_ADMIN role.

Revoke Oracle Database Security Central user privileges

To revoke Oracle Database Security Central user privileges, follow these steps:

1. Connect to the database as the SYS user with the SYSDBA privilege.

2. Run the following script and then enter the user name and mode at the prompts:

   @oracle_drop_db_permissions.sql

   Alternatively, you can enter the script, user name, and mode on one line:

   @oracle_drop_db_permissions.sql <username> <mode>

   • username: Enter the name of the user you created in Step 1.

   • mode: Enter one of the following:

     • SETUP: To revoke privileges for managing the Oracle Database audit policy from Oracle Database Security Central, and for collecting data from any audit trail type.

     • RETRIEVE_DB_POLICIES: To revoke privileges for retrieving audit policies, SQL Firewall policies and Database Vault policies.

     • ASSESSMENT_DISCOVERY: To disable user assessment, security assessment and sensitive data discovery for this database.

     • AUDIT_ADMIN:To revoke the target user the AUDIT_ADMIN role on the database.

3. Revoke the privileges granted to user as DV_OWNER as mentioned in Step 4.

If you encounter any issues, drop the user.

Identifying Users with Audit Report Access in Oracle Audit Vault Server

To find out which users are able to check reports in DBSecCentral, you need to connect to the DBSecCentral server using SSH and run the following query:

select * from dba_role_privs where granted_role='AV_AUDITOR';

The output will return the users that have the AUDITOR privilege. Users such as SYS, AVREPORTUSER, OPS$DBFW_LOCAL_REPORT, AVSYS can be excluded since they are predefined (system) users. The rest of the listed users are able to connect as AUDITORS to the DBSecCentral.

See Also:: Configuring Audit Trail Collection for CDBs and PDBs

Microsoft SQL Server Setup Scripts

The Microsoft SQL Server setup scripts manage audit data collection and auditing privileges for Microsoft SQL Server targets.

About the SQL Server Setup Script

Use the Microsoft SQL Server setup script to set up or revoke user privileges for Oracle DBSecCentral. Microsoft SQL Server 2012 was deprecated in Oracle Oracle Database Security Central 20.12, and it will be desupported in one of the future releases.

The Oracle DBSecCentral setup and drop scripts for a Microsoft SQL Server target are mssql_user_setup.sql and mssql_drop_db_permissions.sql for SQL Server 2014 and later (or mssql_user_setup_pre2014.sql and mssql_drop_db_permissions_pre2014.sql for releases prior to 2014).

Starting with Oracle DBSecCentral 20.10, to download the scripts from the Oracle Database Security Central console:

1. Log in to the Oracle Database Security Central console as an administrator.

2. Select Targets.

3. Select Target Setup Script .

You can also access the scripts in the following directory:

<AGENT_HOME>\av\plugins\com.oracle.av.plugin.mssql\config\

These scripts set up or revoke user privileges for Oracle DBSecCentral to perform the following functions for SQL Server:

• Audit data collection

• Stored procedure auditing (SPA)

Related Topics

• Behavior Changes, Deprecated, and Desupported Platforms and Features

Setting Up Audit Data Collection Privileges for SQL Server Targets

You can set up audit data collection privileges for Microsoft SQL Server targets.

Prerequisites

Assign the following required privileges to run the commands in this topic:

Version and Usage	Command
To assign the required privileges in SQL Server 2014 and later	AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_user_setup.sql
To revoke the assigned privileges in SQL Server 2014 and later	AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_drop_db_permissions.sql
To assign the required privileges in SQL Server versions prior to 2014	AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_user_setup_pre2014.sql
To revoke the assigned privileges in SQL Server versions prior to 2014	AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\config\mssql_drop_db_permissions_pre2014.sql

To set up or revoke Oracle DBSecCentral user privileges for audit data collection:

1. Create a user account for Oracle DBSecCentral in SQL Server or use a Windows authenticated user. For example:

   exec sp_executesql N'create login <username> with password = ''<password>'',
   check_policy= off'
   
   exec sp_executesql N'create user <username> for login <username>'

   Use this user name and password when registering this SQL Server database as a target in the Audit Vault Server.

2. Run the mssql_user_setup.sql or mssql_user_setup_pre2014.sql script with one of the following commands:

   For SQL Server authentication (SQL Server 2014 and later):

   sqlcmd -S <server_name> -U <sa> -i mssql_user_setup.sql -v username="<username>" mode="AUDIT_COLL" all_databases="NA" database="NA"

   For Windows authentication (SQL Server 2014 and later):

   sqlcmd -S localhost -U <sa> -i mssql_user_setup.sql -v username="[<domain_name><username>]" mode="AUDIT_COLL" all_databases="NA" database="NA"

   • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you’re running the script locally, then omit the -S *server_name` argument.

   • sa: Enter the system administrator user name.

   • username: Enter the name of the user that you created in step 1.

3. When prompted for a password, enter the system administrator password.

4. To revoke audit data collection privileges, run the mssql_drop_db_permissions.sql or mssql_drop_db_permissions_pre2014.sql script with one of the following commands:

   For SQL Server authentication (SQL Server 2014 and later):

   sqlcmd -S <server_name> -U <sa> -i mssql_drop_db_permissions.sql -v username="<username>" mode="AUDIT_COLL" all_databases="NA" database="NA"

   For Windows authentication (SQL Server 2014 and later):

   sqlcmd -S <server_name> -U <sa> -i mssql_drop_db_permissions.sql -v username="[<domain_name><username>]" mode="AUDIT_COLL" all_databases="NA" database="NA"

   • server_name: Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you’re running the script locally, then omit the `` -S *server_name argument.

   • sa: Enter the system administrator user name.

   • username: Enter the name of the user that you created in step 1.

   When prompted for a password, enter the system administrator password.

Setting Up Stored Procedure Auditing Privileges for SQL Server Targets

You can set up stored procedure auditing privileges for SQL Server targets.

To set up or revoke Oracle DBSecCentral user privileges for stored procedure auditing:

1. If you have not already done so, create a user account for Oracle DBSecCentral in SQL Server. For example:

   exec sp_executesql N'create login <username> with password = '<password>',
   check_policy= off'
   
   exec sp_executesql N'create user <username> for login <username>'

   You will use this user name and password when registering this SQL Server database as a target in the Audit Vault Server.

2. Run the mssql_user_setup.sql script as follows:

   sqlcmd -S <server_name> -U <sa> -i mssql_user_setup.sql -v username="<username>" mode="SPA" all_databases="<Y/N>"
   database="<NA/database_name>"

   • : Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S argument.
   • : Enter the system administrator user name.
   • : Enter the name of the user you created in Step 1.

   • <Y/N>: Enter Y if all databases should be audited for stored procedures. Enter N to specify one database name in the database parameter.

   • <NA/database_name>: If you entered Y for all_databases, enter NA. If you entered N for all_databases, enter the database name that should be audited for stored procedures.

3. When prompted for a password, enter the system administrator password.

4. To revoke SPA privileges run the mssql_drop_db_permissions.sql script as follows:

   mssql_drop_db_permissions.sql

   • : Only use this argument if the database is remote. Enter the name of the remote server or its IP address. If you are running the script locally, then omit the -S argument.
   • : Enter the system administrator user name.
   • : Enter the system administrator password.

   • <Y/N>: Enter Y if SPA privileges for all databases should be revoked. Enter N to specify one database name in the database parameter.

   • <NA/database_name>: If you entered Y for all_databases, enter NA. If you entered N for all_databases, enter the database name for which SPA privileges should be revoked.

   • When prompted for a password, enter the name of the user you created in Step 1.

IBM DB2 for LUW Setup Scripts

The IBM DB2 for LUW setup scripts manage privileges for audit data collection and stored procedure auditing (SPA) privileges for IBM DB2 for LUW targets.

About the IBM DB2 for LUW Setup Scripts

Learn how to use the IBM DB2 for LUW setup scripts.

The Oracle Database Security Central setup scripts for a DB2 target, db2_auditcoll_user_setup.sql and db2_spa_user_setup.sql, are located in the following directory (Linux example below):

$AGENT_HOME/av/plugins/com.oracle.av.plugin.db2/config/

Note: Connect string is not required from release 12.2.0.11.0 and onwards.

These scripts are used to set up or revoke user privileges on the DB2 database for Oracle DBSecCentral to do the following functions:

• Audit data collection

• Stored procedure auditing (SPA)

Setting Up Audit Data Collection Privileges for IBM DB2 for LUW

You can configure audit data collection privileges for IBM DB2 for LUW to control access to the audit data.

To set up or revoke Oracle DBSecCentral user privileges for audit data collection:

1. Create a new user account in DB2 to be used by Oracle DBSecCentral for audit data collection.

   You will use this user name and password when registering this DB2 database as a target in the Audit Vault Server.

2. In the $AGENT_HOME/av/plugins/com.oracle.av.plugin.db2/config/ directory, locate the db2_auditcoll_user_setup.sql script and open it for editing.

3. In the script, put the user name of the account from Step 1 in the grant statement, then save the modified script.

4. Run the modified script as follows:

   $> db2 -tvf db2_auditcoll_user_setup.sql

5. To revoke audit collection privileges:

   1. Modify the db2_auditcoll_drop_db_permissions.sql script as in Step 3 above.

   2. Run the script as follows:

      $> db2 -tvf db2_auditcoll_drop_db_permissions.sql