Accenture MDR Quick Start Guide for Oracle® Database

This quick start guide will help Accenture MDR customers configure Oracle® Database to allow log collection from the Log collection Platform (LCP).

The document includes the following topics:

Supported Versions

A list of supported versions is available in the Accenture MDR Supported Products List document (Accenture_MDR_Supported_Products_List.xlsx) which can be found in Accenture MDR Portal - https://mss.accenture.com/PortalNextGen/Reports/Documents

NOTE: From version 21c, the Traditional Auditing will be deprecated and only Unified auditing will be supported. Traditional Auditing and XML Auditing are supported till version 19c. From version 21c Unified auditing will be supported.

Port Requirements

Table 1-1: Port requirements for LCP communication.

Configuring Oracle Database 

To Create a read-only database user for Oracle
Collectors that use a database sensor require that you create a read-only database user account so that the collector can query for events.
You can set up Oracle database users in the following ways:

1. By using Oracle 10g or 11g Enterprise Manager

2. By using Oracle 12C Enterprise Manager

3. By using SQLPlus

To create a read-only database user account using Oracle 10g or 11g Enterprise Manager

1. Login to the Oracle Database as a user with permissions to create roles and users with Enterprise Manager.

2. On the Database Instance page, click the Administration tab.

3. Under Users & Privileges, click Roles.

4. On the Roles page, click Create.

5. On the Create Role page, in the Name field, type a new role name. For example, ReadOnlyRole.

6. Click System Privileges, and then click Edit List.

7. In the Available System Privileges list, select the privilege named SELECT.

8. Do one of the following steps:

• If Oracle Auditing is configured to use the Database Audit Trail, select the DBA_AUDIT_TRAIL view.

• If Oracle Auditing is configured to use the XML Audit Trail, select the V_$XML_AUDIT_TRAIL view.

9. Click Move so that the privileges appear in the Selected System Privileges list.

10.Click OK and then click OK again to create the role.

11.Click the link Database Instance:database_name.

12.On the Administration tab, under Users & Privileges, click Users.

13.click Create.

14.On the Create User page, perform the following tasks in the order given:

To create a read-only database user account using Oracle 12c Enterprise Manager

1. Login to the Oracle database as a user with permissions to create roles and users with Enterprise Manager.

2. On the Database Instance page, click the Security tab.

3. Under Security, click Roles.

4. On the Roles page, click Create.

5. On the Create Role page, in the Name field, type a new role name. For example, ReadOnlyRole.

6. Click System Privileges and then click Edit List.

7. In the Available System Privileges list, select the privilege named SELECT.

8. Do one of the following steps:

• If Oracle Auditing is configured to use the Database Audit Trail, select DBA view.

• If Oracle Auditing is configured to use the XML Audit Trail, select Audit_Viewer view.

9.Click Move so that the privileges appear in the Selected System Privileges list.

10.Click OK and then click OK again to create the role.

11.Click the link Database Instance:database_name.

12.On the Security tab, under Users & Privileges, click Users.

13.Click Create.

14.On the Create User page, perform the following tasks in the order given:

 To create a read-only Oracle Database user account using SQLPlus

1. To start SQLPlus without logging in to a database, at the command prompt, type the following: sqlplus /nolog;

2. To connect as a system database administrator, at the SQLPlus prompt, type the following command: connect sys/password@SID as sysdba;

3. To create a read-only user, at the SQLPlus prompt, type the following commands:

4. Do one of the following steps:

• If Oracle Auditing is configured to use Database Audit Trail, type the following command: 

• If Oracle Auditing is configured to use XML Audit Trail, type the following command: 

​ 5. To grant select privileges for the database user, type the following commands:

6. Type the following commands:

• If DBA_AUDIT_TRAIL is enabled, use following command: 

• If XML_Audit_trail is enabled, use the following command: 

 To create a read-only Oracle database user by using SQLPlus for 21c:

• To start SQLPlus without logging in to a database, at a command prompt, type the following: 

• To connect as a system database administrator, at the SQLPlus prompt, type the following command: 

• To create a read-only user, at the SQLPlus prompt, type the following commands:

• To grant select privileges for the database user, type the following commands:

• Type the following commands: 

To enable auditing in an Oracle Database

For Oracle Database to work with the collector, you must complete the following procedures in the order shown:

• Enable auditing in the Oracle Database. See “To enable auditing in the Oracle 10g, 11g, or 12c database”

• Enable auditing in the Oracle Database. See “To Enable unified auditing in the Oracle 21c”

• Configure auditing for a database user account. See “To configure auditing for an Oracle 10g, 11g, or 12c database user account”

• Adding support for 21c and Above

Please consult your Oracle Database administrator before you proceed with any Oracle-related procedures.

To enable auditing in the Oracle 10g, 11g, 12c and 19c database

1. On the Oracle computer, start SQLPlus without connecting to a database by typing the following command

2. To connect to the database as a system administrator, at the SQL prompt, type the following command

1. Do one of the following steps:

• To configure Oracle Auditing to use Database audit trail, at the command prompt type the following command

• To configure Oracle Auditing to use XML audit trail, at the command prompt type the following command

4. ​To shut down the database, type the following command: 

Note: Please consult a Oracle Database administrator before executing the above command. For Windows, restart Oracle services.

5. To restart the database, type the following command: startup

6. For Oracle 10g database, set up the audit events using the examples in the following website: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

7. For Oracle 11g database, set up the audit events using the examples in the following website: http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm

8. For Oracle 12c database, set up the audit events using the examples in the following website: http://docs.oracle.com/database/121/DBSEG/audit_config.htm

To Enable unified auditing in the Oracle 21c

 To enable unified auditing:

1. Log in to SQL*Plus as user SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba Enter password: password

2. Run the following query to find out if your database has been migrated to use unified auditing. Enter Unified Auditing in the case shown here.

   SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

   If the output for the VALUE column is FALSE, then complete the remaining steps in this section to migrate to unified auditing. If the output is TRUE, then unified auditing is enabled by-default.

3. Stop the database.

   For single-instance installations, enter the following commands from SQL*Plus:

   SHUTDOWN IMMEDIATE EXIT

   For Windows systems, stop the Oracle service:

   net stop OracleService%ORACLE_SID%

   For Oracle Real Application Clusters (Oracle RAC) installations, shut down each database instance as follows:

   srvctl stop database -db db_name

4. Stop the listener. (Stopping the listener is not necessary for Oracle RAC and Grid Infrastructure listeners.)

   lsnrctl stop listener_name

   You can find the name of the listener by running the lsnrctl status command. The name is indicated by the Alias setting.

5. Go to the $ORACLE_HOME/rdbms/lib directory.

6. Enable the unified auditing executable.

   • UNIX: Run the following command:

     make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

   • Windows: Rename the %ORACLE_HOME%/bin/orauniaud12.dll.option file to %ORACLE_HOME%/bin/orauniaud12.dll.

7. Restart the listener.

   lsnrctl start listener_name

8. Restart the database. Log in to SQL*Plus and then enter the STARTUP command as follows:

   sqlplus sys as sysoper Enter password: password SQL> STARTUP

   For Windows systems, start the Oracle service again.

   net start OracleService%ORACLE_SID%

   For Oracle RAC installations, from a command line, restart the database as follows:

   srvctl setenv database -db orcl

When using Windows, you can restart Oracle.

1. Restart by typing the following command: startup

2. For the Oracle 10g database, set up the audit events using the examples at the following Web site: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

3. For the Oracle 11g database, set up the audit events using the examples at the following Web site: http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/ auditing.htm#BCGIDBFI

Disable Traditional Auditing and Enable Unified Auditing in Oracle DB.

To Disable Traditional Auditing

1. Log in to the server as the oracle user.

2. Run the following commands:

   > sqlplus / as sysdba SQL> alter system set audit_sys_operations=FALSE scope=spfile; SQL> alter system set audit_trail=NONE scope=spfile;

    

3. Restart the Oracle database.

To Enable Unified Auditing

1. Log in to SQL*Plus as user SYS with the SYSDBA administrative privilege.

   sqlplus sys as sysdba Enter password: password

2. Run the following query to find out if your database has been migrated to use unified auditing. Enter Unified Auditing in the case shown here.

   SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

   If the output for the VALUE column is FALSE, then complete the remaining steps in this section to migrate to unified auditing. If the output is TRUE, then unified auditing is enabled by-default.

3. Stop the database.

   For single-instance installations, enter the following commands from SQL*Plus:

   SHUTDOWN IMMEDIATE EXIT

   For Windows systems, stop the Oracle service:

   net stop OracleService%ORACLE_SID%

   For Oracle Real Application Clusters (Oracle RAC) installations, shut down each database instance as follows:

   srvctl stop database -db db_name

4. Stop the listener. (Stopping the listener is not necessary for Oracle RAC and Grid Infrastructure listeners.)

   lsnrctl stop listener_name

   You can find the name of the listener by running the lsnrctl status command. The name is indicated by the Alias setting.

5. Go to the $ORACLE_HOME/rdbms/lib directory.

6. Enable the unified auditing executable.

   • UNIX: Run the following command:

     make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

   • Windows: Rename the %ORACLE_HOME%/bin/orauniaud12.dll.option file to %ORACLE_HOME%/bin/orauniaud12.dll.

7. Restart the listener.

   lsnrctl start listener_name

8. Restart the database. Log in to SQL*Plus and then enter the STARTUP command as follows:

   sqlplus sys as sysoper Enter password: password SQL> STARTUP

   For Windows systems, start the Oracle service again.

   net start OracleService%ORACLE_SID%

   For Oracle RAC installations, from a command line, restart the database as follows:

   srvctl setenv database -db orcl

When using Windows, you can restart Oracle.

1. Restart by typing the following command: startup

2. For the Oracle 10g database, set up the audit events using the examples at the following Web site: http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

3. For the Oracle 11g database, set up the audit events using the examples at the following Web site: http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/
   auditing.htm#BCGIDBFI

To configure auditing for an Oracle 10g, 11g, or 12c database user account

Oracle Auditing is highly customizable and the design of an auditing policy may vary depending on your needs. The following procedure is one example of Oracle Auditing.

1. At a command prompt, start SQLPlus without connecting to a database by typing the following command

2. To connect to the database as an administrator, at the SQL prompt, type the following command: 

3. To configure auditing for the database user, at the SQL prompt, type the following commands:

Adding support for Unified Auditing From version 11c

1. If the DB version is 21c and above then Traditional Auditing will be deprecated and Unified logging will be enabled.

2. We need to copy the config.xml file to collector folder.

3. Log into LCP

4. Stop Event Translator service:

   systemctl event-translator.service stop

5. Go to collector Folder.

   cd /opt/lcp/collectors/oracledb/

6. Rename the config.xml with 

   mv config.xml config.xml.bak.

7. Go to utils folder.

   cd utils/

8. Under Utils folder there will be a folder named Unified_Auditing_21c.

9. Copy the file and paste it to collector directory. With given command:

   cp config.xml /opt/lcp/collectors/oracledb/config.xml

10. Provide the proper permission and ownership to new config.xml.

    chmod 664 config.xml chown sesuser:ses config.xml

11. Start the event-translator service.

Supported versions

 Managed Security Services monitors the Oracle Database versions that are listed in the Supported Products List document only. For the latest supported versions, visit Symantec Managed Security Services portal, and view the Symantec_MSS_Supported_Products_List_CUSTOMER.xlsx document.

Creating a read-only database user for Oracle

Collectors that use a database sensor require that you create a read-only database user account so that the collector can query for events.

See "Installing collectors that use a database sensor"

You can set up Oracle database users in the following ways:

• By using Oracle 10g or 11g Enterprise Manager See "To create a read-only database user account by using Oracle 10g or 11g Enterprise Manager"

• By using Oracle 12c Enterprise Manager See "To create a read-only database user account by using Oracle 12c Enterprise Manager"

• By using SQLPlus See "To create a read-only Oracle database user by using SQLPlus" See "To create a read-only Oracle database user by using SQLPlus"

To create a read-only database user account by using Oracle 10g or 11g Enterprise Manager

1. Log in to the Oracle database as a user that has permissions to create roles and users with Enterprise Manager.

2. On the Database Instance page, click the Administration tab.

3. Under the Users & Privileges heading, click Roles.

4. On the Roles page, click Create.

5. On the Create Role page, in the Name field, type a new role name. For example, ReadOnlyRole.

6. Click System Privileges, and then click Edit List.

7. In the Available System Privileges list, select the privilege named SELECT.

8. Do one of the following steps:

   • If Oracle Auditing is configured to use the Database Audit Trail, select the DBA_AUDIT_TRAIL view.

   •  If Oracle Auditing is configured to use the XML Audit Trail, select the V_$XML_AUDIT_TRAIL view.

9.  Click Move so that the privileges appear in the Selected System Privileges list.

10. Click OK, and then click OK again to create the role.

11. Click the link Database Instance:database_name.

12. On the Administration tab, under Users & Privileges, click Users.

13. Click Create.

14. On the Create User page, perform the following tasks in the order given:

    1. In the Name field, type a user name. For example, READONLYUSER.

    2. In the Profile field, leave the value as DEFAULT.

    3. In the Authentication field, leave the value as PASSWORD.

    4. In the Enter Password and Confirm Password fields, type a password and confirm it.

    5. In the Default Tablespace field, type a default table space for the user. For example, USERS.

    6. In the Temporary Tablespace field, type a temporary table space for the user. For example, TEMP.

15. Click Roles.

16. On the Roles page, click Edit List.

17. In the Available Role list, select the role that you created in step 1.

18. Click Move so that the role appears in the Selected Roles list.

19. Click OK, and then click OK again, to create the user with read-only access to the database tables.

To create a read-only database user account by using Oracle 12c Enterprise Manager

1. Log in to the Oracle database as a user that has permissions to create roles and users with Enterprise Manager.

2. On the Database Instance page, click the Security tab.

3. Under the Security heading, click Roles.

4. On the Roles page, click Create.

5. On the Create Role page, in the Name field, type a new role name. For example, ReadOnlyRole.

6. Click System Privileges, and then click Edit List.

7. In the Available System Privileges list, select the privilege named SELECT.

Do one of the following steps:

• If Oracle Auditing is configured to use the Database Audit Trail, select the DBA view.

•  If Oracle Auditing is configured to use the XML Audit Trail, select the Audit_Viewer view.

1.  Click Move so that the privileges appear in the Selected System Privileges list.

2. Click OK, and then click OK again to create the role.

3. Click the link Database Instance:database_name.

4. On the Security tab, under Users & Privileges, click Users.

5. Click Create.

6.  On the Create User page, perform the following tasks in the order given:

   1. In the Name field, type a user name. For example, READONLYUSER.

   2. In the Profile field, leave the value as DEFAULT.

   3. In the Authentication field, leave the value as PASSWORD.

   4. In the Enter Password and Confirm Password fields, type a password and confirm it.

   5. In the Default Tablespace field, type a default table space for the user. For example, USERS.

   6. In the Temporary Tablespace field, type a temporary table space for the user. For example, TEMP. 

7. Click Roles.

8. On the Roles page, click Edit List.

9. In the Available Role list, select the role that you created in step 1.

10. Click Move so that the role appears in the Selected Roles list.

11. Click OK, and then click OK again, to create the user with read-only access to the database tables.

To create a read-only Oracle database user by using SQLPlus

1. To start SQLPlus without logging in to a database, at a command prompt, type the following: sqlplus /nolog;

2. To connect as a system database administrator, at the SQLPlus prompt, type the following command: connect sys/password@SID as sysdba;

3. To create a read-only user, at the SQLPlus prompt, type the following commands: create user read_only_user identified by password; grant connect to read_only_user;

4.  Do one of the following steps:

   • If Oracle Auditing is configured to use the Database Audit Trail, type the following command: grant select on DBA_AUDIT_TRAIL to read_only_role;

   •  If Oracle Auditing is configured to use the XML Audit Trail, type the following command: grant select on V_$XML_AUDIT_TRAIL to read_only_user;

5. To grant select privileges for the database user, type the following commands: grant select on sys.aud$ to read_only_user; grant select on sys.system_privilege_map to read_only_user; grant select on sys.stmt_audit_option_map to read_only_user; grant select on sys.audit_actions to read_only_user;

6. Type the following commands: grant create session to read_only_user; grant read_only_user to read;

To Enable auditing in an Oracle database

In order for Oracle Database to work with the collector, you must complete the following procedures in the order shown:

• Enable auditing in the Oracle database See "To enable auditing in the Oracle 10g database" See "To enable auditing in the Oracle 10g or 11g database" See "To enable auditing in the Oracle 10g, 11g, or 12c database"

• Configure auditing for a database user account See "To configure auditing for an Oracle 10g, 11g, or 12c database user account"

Please consult your Oracle database administrator before you proceed with any Oracle-related procedures.

See "Installing collectors that use a database sensor"

To enable auditing in the Oracle 10g, 11g, or 12c database

1. On the Oracle computer, start SQLPlus without connecting to a database. Type the following command: sqlplus /nolog;

2. Connect to the database as a system database administrator. At the SQL prompt, type the following command: connect username/password@SID as sysdba;

3.  Do one of the following steps:

   • To configure Oracle Auditing to use the Database audit trail , at the command prompt, type the following command: ALTER SYSTEM SET audit_trail=db,extended scope=SPFILE;

   •  To configure Oracle Auditing to use the XML audit trail , at the command prompt, type the following command: ALTER SYSTEM SET audit_trail=xml,extended scope=SPFILE;

4. Shut down the database. Type the following command: SHUTDOWN immediate.  When you use Windows, you can restart Oracle.

5. Restart by typing the following command: startup

6. For the Oracle 10g database, set up the audit events using the examples at the following Web site : http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

7. For the Oracle 11g database, set up the audit events using the examples at the following Web site: http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm

8. For the Oracle 12c database, set up the audit events using the examples at the following Web site: http://docs.oracle.com/database/121/DBSEG/audit_config.htm

To configure auditing for an Oracle 10g, 11g, or 12c database user account

1. At a command prompt, start SQLPlus without connecting to a database by typing the following command: sqlplus /nolog;

2. Connect to the database as a system database administrator. At the SQL prompt, type the following command: CONNECT user_name/password@SID as SYSDBA;

3. Configure auditing for the database user. At the SQL prompt, type the following commands: AUDIT ALL BY user_name BY ACCESS; AUDIT SELECT TABLE, CREATE TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY user_name BY ACCESS; AUDIT EXECUTE PROCEDURE BY user_name BY ACCESS;

Sample event log of Oracle® Database device

An event example is as follows:

QUERYID|XMLAUDIT|TIMESTAMP|1256222734937|
ENTRYID|1|SESSIONID|0|STATEMENTID|0|OS_USERNAME|
DMIVA-CCS\Administrator|USERNAME|/|TERMINAL|DMIVA-CCS|
USERHOST|SCOM\DMIVA-CCS|OBJ_NAME|null|OWNER|null|
OS_PRIVILEGE|SYSDBA|OBJ_PRIVILEGE|null|ACTION_NAME|
UNKNOWN|ACTION|0|GRANTEE|null|RETURNCODE|0|PRIV_USED|0|
CLIENT_ID|null|ECONTEXT_ID|null|PROXY_SESSIONID|0|
GLOBAL_UID|null|INSTANCE_NUMBER|0|OS_PROCESS|3728:1528|
TRANSACTIONID|null|SCN|0|SES_ACTIONS|null|DST_IP
|169.254.13.3|DST_HOST|DMIVA-CCS|COMMENT_TEXT|null
|SQL_TEXT|CONNECT|SQL_BIND|null

Creating New Request for Monitoring

Once the device is configured as outlined in the steps above and all network pre-requisites have been made, you are now ready to onboard it for MDR monitoring. To complete this process, submit a New Request via the MDR Portal at https://mss.accenture.com/. This new request should contain the following information:

1. Reporting LCP Hostname/IP Address:

2. Database Hostname/IP Address and Database port:

3. User Name (Read-only):

4. Password:

If you have any questions about this process, please contact your Onboarding Engineer or Service Manager.

LCP Configuration Parameters

Table 1-1: The Oracle Database event collector (DB - 3465) properties to be configured by MDR are shown in the table.