Password Sharing - How to identify who what and recommendations
Password Sharing is a common problem in any organization. The DBA is responsible for security and integrity of the database and intervene when problem is rampant. The first step is identify scope and magnitude and this session will cover steps to
1. Identify which account is shared
2. Users sharing the account password
3. Log DML and DDL to a history table
CREATE TABLE "SYS"."DBA_AUDIT_DML_SERVICE_ACCOUNT" (
"ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 108 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"SESSION_USER" VARCHAR2(256 BYTE),
"OS_USER" VARCHAR2(256 BYTE),
"LINE_NUMBER" NUMBER,
"PAYLOAD" CLOB,
"TIMESTAMP" TIMESTAMP (3) WITH TIME ZONE,
"TRACE_FILENAME" VARCHAR2(256 BYTE)
);
CREATE INDEX "SYS"."NU_DBA_AUDIT_DML_SERVICE_ACCOUNT_TIMESTAMP" ON "SYS"."DBA_AUDIT_DML_SERVICE_ACCOUNT" (CAST("TIMESTAMP" AS DATE));
create or replace NONEDITIONABLE TRIGGER SYS.TRIG_SERVICE_ACCOUNT_LOGON
AFTER LOGON ON DATABASE
DECLARE
-- trigger to monitor users connecting using different login like service account or schema owner
BEGIN
IF(UPPER(SYS_CONTEXT('USERENV','OS_USER')) NOT IN ('ORACLE','DBSNMP','EPETL''JBOSSADMIN','STRIIM') AND (UPPER(SYS_CONTEXT('USERENV','OS_USER')) <> UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))))
THEN
execute immediate 'alter session set max_dump_file_size = ''1024M''';
EXECUTE IMMEDIATE 'alter session set tracefile_identifier='''||TRIM(user)||'_'||TO_CHAR(SYSDATE, 'mmddyyyy_hhmmss')||'''';
execute immediate 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 1'''; --Default SQL Trace for DML I/U/D --https://antognini.ch/2012/08/event-10...
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30000 - SESSION TRACE ON '||UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))||' CONNECTED FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
END IF;
EXCEPTION
--NEVER THROW EXCEPTION
WHEN OTHERS THEN
NULL;
END;
create or replace NONEDITIONABLE TRIGGER SYS.TRIG_SERVICE_ACCOUNT_LOGOFF
BEFORE LOGOFF ON DATABASE
DECLARE
-- trigger to monitor users connecting using different login like service account or schema owner
CURSOR c_tracefile IS
SELECT DISTINCT trace_filename
FROM v$diag_trace_file
WHERE trace_filename like '%'||UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))||'%'
AND change_time > CURRENT_TIMESTAMP - interval '15' minute
ORDER BY change_time DESC
FETCH FIRST 6 ROWS ONLY; --limit to avoid logoff delay
BEGIN
IF(UPPER(SYS_CONTEXT('USERENV','OS_USER')) NOT IN ('ORACLE','DBSNMP','EPETL','JBOSSADMIN','STRIIM') AND (UPPER(SYS_CONTEXT('USERENV','OS_USER')) <> UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))))
THEN
execute immediate 'alter session set sql_trace=false';
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30001 - SESSION TRACE OFF '||UPPER(SYS_CONTEXT('USERENV','SESSION_USER'))||' CONNECTED FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
/*
SELECT distinct session_user, os_user, translate(DBMS_LOB.SUBSTR(payload,100,1), chr(10)||chr(11)||chr(13),' ') SHORT_STMT, CAST(timestamp AS DATE) DML_DT, sysdate, trace_filename
FROM DBA_AUDIT_DML_SERVICE_ACCOUNT
WHERE CAST(timestamp AS DATE) > SYSDATE - interval '15' minute
AND DBMS_LOB.SUBSTR(payload,20,1) NOT LIKE 'alter session set%'
ORDER BY CAST(timestamp AS DATE) DESC;
*/
FOR r_tracefile IN c_tracefile
LOOP
--dbms_output.put_line(r_tracefile.trace_filename);
INSERT INTO DBA_AUDIT_DML_SERVICE_ACCOUNT(session_user, os_user, line_number, payload, timestamp, trace_filename)
SELECT UPPER(SYS_CONTEXT('USERENV','SESSION_USER')), UPPER(SYS_CONTEXT('USERENV','OS_USER')), line_number, payload, timestamp, trace_filename
FROM v$diag_trace_file_contents
WHERE trace_filename = r_tracefile.trace_filename
AND (lower(payload) like 'insert%' OR lower(payload) like 'update%' OR lower(payload) like 'delete%' OR lower(payload) like 'create%' OR lower(payload) like 'drop%' OR lower(payload) like 'alter%')
MINUS
SELECT UPPER(SYS_CONTEXT('USERENV','SESSION_USER')), UPPER(SYS_CONTEXT('USERENV','OS_USER')), line_number, payload, timestamp, trace_filename
FROM v$diag_trace_file_contents
WHERE trace_filename = r_tracefile.trace_filename
AND (payload like 'INSERT INTO DBA_%' OR lower(payload) like '%$%')
ORDER BY line_number;
END LOOP;
END IF;
EXCEPTION
--NEVER THROW EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Смотрите видео Password Sharing - How to identify who what and recommendations онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 17 Август 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 19 раз и оно понравилось 1 людям.