RMAN - solid backup

This isn't the Oracle Suggested Backup script, but this is my RMAN script for a good, solid RMAN backup. It will need slight tweaking if you decide to use it.

backup check logical database; 

sql 'alter system archive log current'; 

backup archivelog all delete all input; 

 allocate channel for maintenance device type disk; 

RUN {  

crosscheck backup of database; 

crosscheck backup of controlfile; 

crosscheck archivelog all;} 

delete force noprompt expired backup of database; 

delete force noprompt expired backup of controlfile; 

delete force noprompt expired archivelog all; 

delete force noprompt obsolete redundancy 5; 

sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE"; 

sql "ALTER DATABASE BACKUP CONTROLFILE TO ''D:\FRA\SBY01\SBY01_CONTROLFILE_BACKUP.CTL'' REUSE"; 

sql "CREATE PFILE=''C:\oracle\product\10.2.0\db_1\admin\SBY01\pfile\INITSBY01.INI'' FROM  SPFILE"; 

exit; 

DDL Detection

Being an Oracle DBA is not always a pleasant task. There are unscrupulous third parties out there who steal your employer's customers, and then try to steal their intellectual property.

Here's one script I wrote after encountering the darker side of DBA-ing. When an untrusted third party starts messing about in your employer's databases, then things "get real".

In this set of scripts, we create a little trap for our third-party friends - if they run DDL, you log it and email it to yourself.

This is a pre-cursor, a backup for another, much more advanced defence against unwanted visitors which I'll post later in another blog entry.

This was written for 10g, YMMV for other versions.

Yes, stuff is written to the SYS schema, which is a no-no; I'll re-write it to use another schema for a later blog entry.

-- clear the previous table
DROP TABLE SYS.ddl_log;


CREATE TABLE SYS.ddl_log
    (audit_id                       NUMBER NOT NULL,
    audit_date                     DATE NOT NULL,
    sessionid                      NUMBER,
    username                       VARCHAR2(30),
    osuser                         VARCHAR2(30),
    terminal                       VARCHAR2(16),
    HOST                           VARCHAR2(64),
    ip                             VARCHAR2(15),
    program                        VARCHAR2(64),
    module                         VARCHAR2(48),
    operation                      VARCHAR2(30),
    obj_owner                      VARCHAR2(30),
    object_name                    VARCHAR2(30),
    sql_text                       VARCHAR2(64)
    )
/

-- Indexes for SYS.ddl_log

CREATE UNIQUE INDEX SYS.pk_ddl_log ON SYS.ddl_log
  (
    audit_id                        ASC
  )
/

-- Comments for SYS.ddl_log

COMMENT ON COLUMN SYS.ddl_log.audit_id IS 'Primary Key'
/
-- End of DDL Script for Table SYS.DDL_LOG

-- We need sequences for the trigger
DROP SEQUENCE SYS.sq_ddl_log;
CREATE SEQUENCE SYS.sq_ddl_log
 START WITH  1
 INCREMENT BY  1
 NOCACHE
/


-- Start of DDL Script for Trigger SYS.DDL_DETECTION

create or replace trigger SYS.DDL_DETECTION
 before
  create or alter or drop
 on database
DECLARE
   v_audit_rec       ddl_log%ROWTYPE;
   oper              ddl_log.operation%TYPE;
   sql_text          ora_name_list_t;
   i                 PLS_INTEGER;
   v_db_instance     VARCHAR2 (8);
   v_email_subject   VARCHAR2 (32);
   v_email_body      VARCHAR2 (4000);
   crlf              VARCHAR2 (2)             := CHR (13) || CHR (10);
   PRAGMA AUTONOMOUS_TRANSACTION; /* only way to commit within a trigger */

BEGIN
/* no checking v$session for detection avoidance
no intention to block - only log and email*/
   SELECT ora_sysevent
     INTO oper
     FROM DUAL;

   BEGIN
      SELECT program
        INTO v_audit_rec.program
        FROM SYS.v_$session
       WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID')
         -- AUDSID is the same for all SYS connections
         AND SID = SYS_CONTEXT ('USERENV', 'SID')
         -- so we use SID to differentiate
         AND ROWNUM = 1;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_audit_rec.program := 'SESSIONID_NOT_FOUND';
   END;

-- get all audit info first
   i := sql_txt (sql_text);

   IF oper IN ('CREATE', 'DROP', 'ALTER')
   THEN
      SELECT ora_sysevent, ora_dict_obj_owner,
             ora_dict_obj_name, sql_text (1)
        INTO v_audit_rec.operation, v_audit_rec.obj_owner,
             v_audit_rec.object_name, v_audit_rec.sql_text
        FROM DUAL;
   END IF;

   SELECT SYS_CONTEXT ('USERENV', 'SESSIONID'),
          SYS_CONTEXT ('USERENV', 'SESSION_USER'),
          SYS_CONTEXT ('USERENV', 'OS_USER'),
          SYS_CONTEXT ('USERENV', 'TERMINAL'),
          SYS_CONTEXT ('USERENV', 'HOST'),
          SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
          SYS_CONTEXT ('USERENV', 'MODULE'),
          SYS_CONTEXT ('USERENV', 'DB_UNIQUE_NAME')
     INTO v_audit_rec.sessionid,
          v_audit_rec.username,
          v_audit_rec.osuser,
          v_audit_rec.terminal,
          v_audit_rec.HOST,
          v_audit_rec.ip,
          v_audit_rec.module,
          v_db_instance
     FROM DUAL;

--determine if we need to log or filter out stuff we're not interested in
   IF (    (v_audit_rec.sessionid > 0)
       AND (v_audit_rec.object_name NOT LIKE ('SYS_TEMP%'))

       AND (v_audit_rec.terminal NOT IN ('MYWORKSTATION', 'FRIENDLYWORKSTATION'))

      )

   THEN                                                           -- let's log

      SELECT sq_ddl_log.NEXTVAL

        INTO v_audit_rec.audit_id

        FROM DUAL;

      v_audit_rec.audit_date := SYSDATE;

      INSERT INTO SYS.ddl_log

           VALUES v_audit_rec;

      COMMIT;

      -- and email it

      v_email_subject := v_db_instance || ': DDL Notification';

      v_email_body :=

            'Timestamp   : '

         || TO_CHAR (v_audit_rec.audit_date, 'dd/mm/yyyy hh24:mi:ss')

         || crlf

         || 'Session ID  : '

         || v_audit_rec.sessionid

         || crlf

         || 'Username    : '

         || v_audit_rec.username

         || crlf

         || 'OS User     : '

         || v_audit_rec.osuser

         || crlf

         || 'Host        : '

         || v_audit_rec.terminal

         || crlf

         || 'IP Addreses : '

         || v_audit_rec.ip

         || crlf

         || 'Module      : '

         || v_audit_rec.module

         || crlf

         || 'Program     : '

         || v_audit_rec.program

         || crlf

         || 'Operation   : '

         || v_audit_rec.operation

         || crlf

         || 'Schema Owner: '

         || v_audit_rec.obj_owner

         || crlf

         || 'Object      : '

         || v_audit_rec.object_name

         || crlf

         || 'Statement   : '

         || v_audit_rec.sql_text;

      BEGIN

         mailit.mailusers ('kmohammed', '', v_email_subject, v_email_body);

      EXCEPTION

         --silently fail email errors, dont alert our visitors

         WHEN OTHERS

         THEN

            NULL;

      END;

   END IF;

END ddl_trigger;

/

-- End of DDL Script for Trigger SYS.DDL_DETECTION

If you don't require email-ing of the DDL, then that's easily removed from the script.

Let me explain one of the most important parts of this script:

       SELECT program

        INTO v_audit_rec.program

        FROM SYS.v_$session

       WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID')

         -- AUDSID is the same for all SYS connections

         AND SID = SYS_CONTEXT ('USERENV', 'SID')

         -- so we use SID to differentiate

         AND ROWNUM = 1;

This appears convoluted because :

1. Not all clients populate the MODULE column of V$SESSION. Toad is especially guilty of this, and I suspected the third-party was using Toad. Hence the decision to use the PROGRAM column.
2. People can mess with V$SESSION. Yes, dear reader, there are unscrupuolous people out there who mess with V$SESSION in an effort to avoid simpler defences against their unauthorised intrusions. 
3. Detecting accurately the correct SYS session is much harder than your average session.

That's why I take the following steps:

1. Select from the underlying table i.e.  V_$SESSION
2. Check the client's SESSIONID (  SYS_CONTEXT ('USERENV', 'SESSIONID') ) against the AUDSID's in V_$SESSION. If the client's SESSIONID does not show up in V_$SESSION, now that's an interesting login.
3. Have further SYS-specific checks.

One problem with SYS is that if your SQLNET.ORA has:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

And your account is a member of the local group ORA_DBA, then you can connect using OS Authentication (on Windows) without using a password, and "AS SYSDBA". We have to live with that.

In later posts, I'll update the script to use a non-SYS schema, and I'll post my advanced scripts to defend against those unscrupulous people.

Execute ANY Host command

I had a problem at one customer - I couldn't login to the Windows host. Their MS techie had created a local admin user for me, but the account had been locked out or the password had been changed, and now I had no way to use the account - the desk jockey wasn't responding to emails fast enough.

Anyway, I could still login to the Oracle DB as SYS. This little script allowed me to change the password of the host account:


-- run as SYS, and change the command in the 3rd argument
BEGIN
   DBMS_SCHEDULER.create_job ('t1',
                              job_action               => 'C:\WINDOWS\SYSTEM32\CMD.EXE',
                              number_of_arguments      => 3,
                              job_type                 => 'executable',
                              enabled                  => FALSE
                             );
   DBMS_SCHEDULER.set_job_argument_value ('t1', 1, '/q');
   DBMS_SCHEDULER.set_job_argument_value ('t1', 2, '/c');
   DBMS_SCHEDULER.set_job_argument_value ('t1',
                                          3,
                                          'C:\WINDOWS\SYSTEM32\NET.EXE user localadmin password1234'
                                         );
   DBMS_SCHEDULER.ENABLE ('t1');
END;
/

The third argument is the key - with it, you can run any command as the built-in user LocalSystem. In this example, the command resets the localadmin account with the password password1234. It effectively gives you complete control of the host.

It's not a backdoor. You must know the SYS password, and it works this way because the Oracle services run as LocalSystem (by default) on a Windows host.

This also works for Windows 2008, but I had to specify the full path to the NET.EXE file. You can troubleshoot any failed commands by looking at the output of DBMS_SCHEDULER.