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
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 :
- 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.
- 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.
- Detecting accurately the correct SYS session is much harder than your average session.
That's why I take the following steps:
- Select from the underlying table i.e. V_$SESSION
- 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.
- 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.
No comments:
Post a Comment