Search code examples
oraclewindows-10oracle-xe

Configure Oracle XE logging running on Windows 10, prevent writing to Windows Event Log

Environment: Oracle XE 18 running on Windows 10 (my laptop!). I run database commands using specific users and sys.

Problem: The windows application event log has 1000's of verbose Oracle database logging, for example every SELECT statement, indeed every command I run! Launch Windows Event Viewer, execute at command prompt: eventvwr

Questions:

  1. How to configure Oracle so that only certain messages are written the to the Windows Event Log. Ideally no commands that I execute are logged.

  2. Does Oracle XE also write to log files (not windows event logs) and if so how to configure the granularity?

(I'm fairly new to Oracle)

Additional Info: Below is an example of a windows application event log

enter image description here

Solution

  • Looking at your screenshot, it seems you are using the SYS user to execute your SQL commands. By default, all operations executed by users using SYSASM, SYSBACKUP, SYSDBA, SYSDG, SYSKM, or SYSOPER privileges are audited and go to the event log. You can disable that behavior with the AUDIT_SYS_OPERATIONS initialization parameter.

    See documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/AUDIT_SYS_OPERATIONS.html#GUID-58176267-238C-40B5-B1F2-BB8BB9518950

    When logged in as SYS, you can change the parameter setting with the following command:

    alter system set audit_sys_operations=false scope=spfile;

    Then restart the database.

    That said, in general you should not be using SYS for day-to-day operations and preferably would leave the default auditing in place. Instead, use a user account with normal privileges (i.e. not SYS as SYSDBA) to do everything that doesn't require those elevated privileges. For the most part you wouldn't need that level of access except for startup/shutdown, backup operations, and installing patches.

    create user myuser identified by mypassword;
grant dba to myuser;
alter user myuser default role all;

    That should give you a user with plenty of elevated privileges that won't constantly trip the built-in auditing.

    Read up on Oracle security here: https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/introduction-to-oracle-database-security.html#GUID-41040F53-D7A6-48FA-A92A-0C23118BC8A0