Logon trigger/ auditing method to identify the programs/packages/modules/procedures using sys/system connections

Question

In my database many programs/modules uses sys/system connection to perform operations on data.

eg: sys/ as sysdba

perform dml's,ddl's or any user requirements.

I want to replace this connection string with some other user . Is there any way to identify these programs/modules/procedures/package which are run through sys/system?

My requirement is to find all the programs using sys/system connection string

Answer 1

Absolutely. While the standard auditing would provide you with some info, it is rather limited. You need a logon trigger that captures enriched session information and logs it. Here's an example:

CREATE OR REPLACE TRIGGER SYS.LOG_SYS_CONNECTIONS
AFTER LOGON
ON DATABASE
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF USER != 'SYS' OR SYS_CONTEXT('USERENV','SESSIONID') IN (0,4294967295) -- background processes
  THEN
    NULL;
  ELSE
    EXECUTE IMMEDIATE '
       INSERT INTO sys.sys_connection_log
              (s.username,
               s.client_os_user,
               s.process,
               s.machine,
               s.program,
               s.service_name,
               s.sid,
               s.serial#,
               s.logon_time,
               cix.authentication_type,
               cix.client_charset,
               cix.client_version,
               cix.client_driver,
               SYS_CONTEXT(''USERENV'',''PROXY_USER'') proxy_user,
               SYS_CONTEXT(''USERENV'',''DBLINK_INFO'') dblink_info,
               SYS_CONTEXT(''USERENV'',''AUTHENTICATED_IDENTITY'') authenticated_identity,
               SYS_CONTEXT(''USERENV'',''AUTHENTICATION_METHOD'') authentication_method
          FROM v$session s
               OUTER APPLY (SELECT MAX(NULLIF(authentication_type,''Unknown'')) authentication_type,
                                   MAX(NULLIF(client_charset,''Unknown'')) client_charset,
                                   MAX(NULLIF(client_version,''Unknown'')) client_version,
                                   MAX(NULLIF(client_driver,''Unknown'')) client_driver
                              FROM v$session_connect_info ci
                             WHERE ci.sid = s.sid
                               AND ci.serial# = s.serial#) cix
         WHERE sid = SYS_CONTEXT(''USERENV'',''SID'')
    ';
    
    COMMIT;
  END IF;
EXCEPTION 
  WHEN OTHERS THEN
    NULL;
END;
/

Obviously you'd need to create the logging table (sys_connection_log in my example). A few notes:

1. To prevent any chance of causing problems for background processes we check the audsid (SYS_CONTEXT('USERENV','SESSIONID') returns v$session.audsid which is 0 or 0,4294967295 for background processes) and do nothing if it's a background process. Just being extra cautious. We could have simply queried v$session and looked at TYPE, but this allows us to quit without even a single query.

2. The insert itself is wrapped in a dynamic EXECUTE IMMEDIATE block. While this isn't strictly necessary because there's nothing dynamic about the SQL, this is a safety measure. If anything is wrong with your SQL, or if your logging table gets dropped or anything that could cause the SQL to fail parsing, you don't want the trigger to become invalid and possibly prevent logons from happening. This way, any parsing failure will simply raise an exception within the trigger which we then handle and do nothing (WHEN OTHERS THEN NULL).

3. We use an autonomous transaction so we can commit our insert. That may not be necessary for a logon trigger as Oracle might still have an implicit commit that will follow the trigger since it is a system event, but just to be sure, we want to ensure our record is preserved nonetheless. You cannot do a commit in a trigger without PRAGMA AUTONOMOUS_TRANSACTION.