ora12c_stig_verify_function 密码策略

转载
charlesc 2024-12-16 16:31:35
文章标签 IP SYS User 文章分类 运维
/===============================================================================

||文件名：数据库审计事件记录脚本

||说明：为了对数据库事件进行审计，而进行相关设计

==============================================================================/
/* ========================= 审计记录表 ========================= */
/* -------- 系统会话事件审计表 -------- /

CREATE TABLE SYSTEM.Audit_Login_DB(

/

||名称：审计数据库会话登陆事件记录表

||说明：
/

Session_Id   NUMBER,          / 会话ID /

OS_User      VARCHAR2(200),   / 终端OS用户 /

IP_Address   VARCHAR2(200),   / 终端IP地址 /

Terminal     VARCHAR2(200),   / 终端 /

Host         VARCHAR2(200),   / 终端主机名 /

User_Name    VARCHAR2(30),    / ORACLE 用户名/

LogOn_Date   DATE,            / 登陆时间 /

LogOff_Date  DATE,            / 登离时间 /

Elapsed_Minutes   NUMBER      / 在线时间 */

)

TABLESPACE TOOLS;/* -------- 会话事件审计表索引 --------- */

CREATE INDEX IX_AUDIT_LOGIN_SESSIONID ON SYSTEM.AUDIT_LOGIN_DB(SESSION_ID)

TABLESPACE INDX;

CREATE INDEX IX_AUDIT_LOGIN_LOGONDATE ON SYSTEM.AUDIT_LOGIN_DB(LOGON_DATE)

TABLESPACE INDX;/* -------- DDL事件审计表 -------- /

CREATE TABLE system.Audit_DDL_OBJ(

/

||名称：审计针对数据库对象的DDL记录表

||说明：
/

Opr_Time     DATE,            / 操作时间 /

Session_Id   NUMBER,          / 会话ID /

OS_User      VARCHAR2(200),   / 终端OS用户 /

IP_Address   VARCHAR2(200),   / 终端IP地址 /

Terminal     VARCHAR2(200),   / 终端 /

Host         VARCHAR2(200),   / 终端主机名 /

User_Name    VARCHAR2(30),    / ORACLE 用户名/

DDL_Type     VARCHAR2(30),    / DDL操作类型 /

DDL_Sql      VARCHAR2(2000),  / DDL语句 /

Object_Type  VARCHAR2(18),    / 操作对象类型 /

Owner        VARCHAR2(30),    / 对象拥有者 /

Object_Name  VARCHAR2(128)    / 对象名称 */

)

;/* -------- DDL事件审计表索引 --------- */

CREATE INDEX IX_AUDIT_DDL_SESSIONID ON SYSTEM.AUDIT_DDL_OBJ(SESSION_ID)

TABLESPACE INDX;CREATE INDEX IX_AUDIT_DDL_OPRTIME ON SYSTEM.AUDIT_DDL_OBJ(OPR_TIME)

TABLESPACE INDX;/* ========================= 审计触发器 ========================= */
/* ------- AFTER LOGON ON DATABASE 触发器 -------/

CREATE OR REPLACE TRIGGER SYS.Login_Audit_Trigger

AFTER LOGON ON DATABASE

/

||名称：会话登陆事件审计触发器

||说明：

*/

DECLARESession_Id_Var   NUMBER;          /* 会话ID /

Os_User_Var      VARCHAR2(200);   / 终端OS用户 /

IP_Address_Var   VARCHAR2(200);   / 终端IP /

Terminal_Var     VARCHAR2(200);   / 终端 /

Host_Var         VARCHAR2(200);   / 终端主机名 */
BEGIN
/* 获取登陆用户信息 */

SELECT   SYS_CONTEXT('USERENV','SESSIONID'),

SYS_CONTEXT('USERENV','OS_USER'),

SYS_CONTEXT('USERENV','IP_ADDRESS'),

SYS_CONTEXT('USERENV','TERMINAL'),

SYS_CONTEXT('USERENV','HOST')

INTO     Session_Id_Var,

Os_User_Var,

IP_Address_Var,

Terminal_Var,

Host_Var

FROM     DUAL;/* 记录登陆审计信息 /

INSERT INTO system.Audit_Login_DB(

Session_Id,      / 会话ID /

OS_User,         / 终端OS用户 /

IP_Address,      / 终端IP地址 /

Terminal,        / 终端 /

Host,            / 终端主机名 /

User_Name,       / ORACLE 用户名/

LogOn_Date,      / 登陆时间 /

LogOff_Date,     / 登离时间 /

Elapsed_Minutes  / 在线时间 */

)

VALUES( Session_Id_Var,

Os_User_Var,

IP_Address_Var,

Terminal_Var,

Host_Var,

USER,

SYSDATE,

NULL,

NULL);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

NULL;

END Login_Audit_Trigger;

//* ------- BEFORE LOGOFF ON DATABASE 触发器 -------/

CREATE OR REPLACE TRIGGER SYS.LogOff_Audit_Trigger

BEFORE LOGOFF ON DATABASE

/

||名称：会话登离事件审计触发器

||说明：

*/

DECLARESession_Id_Var   NUMBER;          /* 会话ID */
BEGIN
/* 获取登陆用户信息 */

SELECT   SYS_CONTEXT('USERENV','SESSIONID')

INTO     Session_Id_Var

FROM     DUAL;/* 更新会话审计记录信息 /

UPDATE system.Audit_Login_DB

SET LogOff_Date = SYSDATE,

Elapsed_Minutes = ROUND((SYSDATE - LogOn_Date) 1440)

WHERE  Session_Id = Session_Id_Var;

--WHERE  SYS_CONTEXT('USERENV','SESSIONID') = Session_Id;

COMMIT;EXCEPTION

WHEN OTHERS THEN

NULL;

END LogOff_Audit_Trigger;

//* ------- AFTER DDL ON DATABASE 触发器 -------/

CREATE OR REPLACE TRIGGER SYS.DDL_Audit_Trigger

AFTER DDL ON DATABASE

/

||名称：DDL事件审计触发器

||说明：

*/

DECLARESession_Id_Var   NUMBER;          /* 会话ID /

Os_User_Var      VARCHAR2(200);   / 终端OS用户 /

IP_Address_Var   VARCHAR2(200);   / 终端IP /

Terminal_Var     VARCHAR2(200);   / 终端 /

Host_Var         VARCHAR2(200);   / 终端主机名 /

Cut              NUMBER;          / SQL列表长度 /

Sql_Text         ORA_NAME_LIST_T; / SQL_TEXT 列表 /

L_Trace          NUMBER;          / 循环执行条件 /

DDL_Sql_Var      VARCHAR2(2000);  / DDL语句 */
BEGIN
/* 获取操作用户信息 */

SELECT   SYS_CONTEXT('USERENV','SESSIONID'),

SYS_CONTEXT('USERENV','OS_USER'),

SYS_CONTEXT('USERENV','IP_ADDRESS'),

SYS_CONTEXT('USERENV','TERMINAL'),

SYS_CONTEXT('USERENV','HOST')

INTO     Session_Id_Var,

Os_User_Var,

IP_Address_Var,

Terminal_Var,

Host_Var

FROM     DUAL;/* 获取DDL SQL语句 */

BEGINSELECT COUNT(*) INTO L_Trace FROM DUAL
   WHERE ORA_DICT_OBJ_NAME NOT LIKE 'MLOG%'
     AND ORA_DICT_OBJ_NAME NOT LIKE '%LOG'
     AND UTL_INADDR.GET_HOST_ADDRESS IS NOT NULL
     AND SYS_CONTEXT('USERENV','IP_ADDRESS') IS NOT NULL
     AND SYS_CONTEXT('USERENV','IP_ADDRESS') <> UTL_INADDR.GET_HOST_ADDRESS;

  IF L_Trace > 0 THEN

     Cut := ORA_SQL_TXT(Sql_Text);

     FOR i IN 1..Cut LOOP
        DDL_Sql_Var := SUBSTR(DDL_Sql_Var || Sql_Text(i),1,2000);
     END LOOP;
  END IF;EXCEPTION

WHEN OTHERS THEN

NULL;

END;/* 记录登陆审计信息 /

INSERT INTO system.Audit_DDL_OBJ(

Opr_Time,     / 操作时间 /

Session_Id,   / 会话ID /

OS_User,      / 终端OS用户 /

IP_Address,   / 终端IP地址 /

Terminal,     / 终端 /

Host,         / 终端主机名 /

User_Name,    / ORACLE 用户名/

DDL_Type,     / DDL操作类型 /

DDL_Sql,      / DDL语句 /

Object_Type,  / 操作对象类型 /

Owner,        / 对象拥有者 /

Object_Name   / 对象名称 */

)

VALUES( SYSDATE,

Session_Id_Var,

Os_User_Var,

IP_Address_Var,

Terminal_Var,

Host_Var,

ORA_LOGIN_USER,

ORA_SYSEVENT,

DDL_Sql_Var,

ORA_DICT_OBJ_TYPE,

ORA_DICT_OBJ_OWNER,

ORA_DICT_OBJ_NAME);

COMMIT;EXCEPTION

WHEN OTHERS THEN

NULL;

END DDL_Audit_Trigger;

//* ========================= 审计DML语句 ========================= /

/

||名称：利用DBMS_FGA包的细粒度审计功能实现对DML语句的审计

||说明：
/

/

CREATE SEQUENCE SEQ_SELECT_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE SEQ_INSERT_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE SEQ_UPDATE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE SEQ_DELETE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;

SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''SELECT'',POLICY_NAME => ''CHK_SELECT_'||SEQ_SELECT_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'

UNION

SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''INSERT'',POLICY_NAME => ''CHK_INSERT_'||SEQ_INSERT_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'

UNION

SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''UPDATE'',POLICY_NAME => ''CHK_UPDATE_'||SEQ_UPDATE_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'

UNION

SELECT 'DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => '''||OWNER||''', OBJECT_NAME => '''||TABLE_NAME||''',STATEMENT_TYPES => ''DELETE'',POLICY_NAME => ''CHK_DELETE_'||SEQ_DELETE_NUMBER.NEXTVAL||'''); ' FROM ALL_TABLES WHERE WNER='INXITE'--需要在以上生成的语句前加上BEGIN，END；语句，以补充完全

*/--创建审计策略

BEGIN

DBMS_FGA.ADD_POLICY (

object_schema      =>  'SCOTT',

object_name        =>  'EMP',

policy_name        =>  'mypolicy1',

enable             =>   TRUE,

statement_types    =>  'INSERT, UPDATE, DELETE, SELECT',

audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);

END;

/--批量创建脚本

SELECT 'DBMS_FGA.ADD_POLICY (object_schema => ' || '''' || OWNER || '''' || ', object_name => ' || '''' || TABLE_NAME || ''''

|| ', policy_name => ' || '''' ||'POL_' || TABLE_NAME || '''' || ', enable => TRUE' || ', statement_types => '''

|| 'INSERT, UPDATE, DELETE, SELECT''' || ', audit_column_opts => DBMS_FGA.ANY_COLUMNS);'

FROM DBA_TABLES WHERE OWNER = 'SCOTT';BEGIN

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'SMDP_SUBSCRIPTIONS', policy_name => 'POL_SMDP_SUBSCRIPTIONS', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'T', policy_name => 'POL_T', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'TAB_1', policy_name => 'POL_TAB_1', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'TAB_2', policy_name => 'POL_TAB_2', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'POL_EMP', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

DBMS_FGA.ADD_POLICY (object_schema => 'SCOTT', object_name => 'OSMSR_EUICC_HISTORIES', policy_name => 'POL_OSMSR_EUICC_HISTORIES', enable => TRUE, statement_types => 'INSERT, UPDATE, DELETE, SELECT', audit_column_opts => DBMS_FGA.ANY_COLUMNS);

END;

/--通过视图来查看SQL结果

select * from dba_fga_audit_trail;--开启审计策略

BEGIN

DBMS_FGA.ENABLE_POLICY (

object_schema    =>  'SCOTT',

object_name      =>  'EMP',

policy_name      =>  'mypolicy1',

enable           =>   TRUE);

END;

/--关闭审计策略

BEGIN

DBMS_FGA.DISABLE_POLICY (

object_schema   =>  'scott',

object_name     =>  'emp',

policy_name     =>  'mypolicy1');

END;

/使用后需要删除审计策略

--删除审计策略

BEGIN

DBMS_FGA.DROP_POLICY (

object_schema   =>  'scott',

object_name     =>  'emp',

policy_name     =>  'mypolicy1');

END;

/批量删除语句

SELECT 'DBMS_FGA.DROP_POLICY (object_schema => ' || '''' || OWNER || '''' || ', object_name => ' || '''' || TABLE_NAME || ''''

|| ', policy_name => ' || '''' ||'POL_' || TABLE_NAME || '''' ||  ');'

FROM DBA_TABLES WHERE OWNER = 'SCOTT';BEGIN

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'SMDP_SUBSCRIPTIONS', policy_name => 'POL_SMDP_SUBSCRIPTIONS');

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'T', policy_name => 'POL_T');

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'TAB_1', policy_name => 'POL_TAB_1');

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'TAB_2', policy_name => 'POL_TAB_2');

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'POL_EMP');

DBMS_FGA.DROP_POLICY (object_schema => 'SCOTT', object_name => 'OSMSR_EUICC_HISTORIES', policy_name => 'POL_OSMSR_EUICC_HISTORIES');

END;

/
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。