oracle 触发器脚本

oracle 触发器脚本

一、 限定仅能从指定服务器/工具连接

create or replace trigger logon_ip_module_controlafter logon on database
/*限制DATAFIX用户仅允许通过192.168.1.的ip使用工具登录至数据库*/
declarev_ip      Varchar2(50);v_user    Varchar2(50);v_module  Varchar2(50);v_log     Varchar2(00);v_message Varchar2(00);
beginSELECT SYS_COTEXT(	USEREV	, 	SESSIO_USER	),SYS_COTEXT(	USEREV	, 	IP_ADDRESS	),SYS_COTEXT(	USEREV	, 	MODULE	)into v_user, v_ip, v_modulefrom dual;if v_user = 	DATAFIX	 THEIF (v_ip = 	192.168.1.	 and v_module = 		) THE-- Access Gateway null;elsev_log := 	LOGO_IP_MODULE_COTROL_TRIGGER -- User: 	 || v_user ||	, IP: 	 || v_ip || 	, Logon tool:	 || v_module;sys.dbms_system.ksdwrt(2, v_log);v_message := 	User 	 || v_user ||	 is not allowed to connect from this IP address or logon tool.Please contact your DBA to help.	;raise_application_error(-20001, v_message);ED IF;ED IF;
end;

或者

**/
Create Or Replace Trigger DEY_LOGI  After Logon On Database
Declarev_Program Varchar2(48);v_Message Varchar2(1000);v_deny_Client Exception;v_deny_User Exception;v_deny_Ip Exception;
BeginSelect Program Into v_Program From V$session Where Audsid = Sys_Context(	USEREV	, 	SESSIOID	) And Rownum < 2;If  Lower(v_Program) = 		 ThenRaise v_deny_Client;End If;If  User In (	SCOTT	,	scott	) ThenRaise v_deny_User;End If;If Sys_Context(	USEREV	, 	ip_address	) = 	10.117.196.52	 ThenRaise v_deny_Ip;End If;
ExceptionWhen v_deny_Client Thenv_Message := 	Sorry！You cannot access database using this software client!	;Raise_Application_Error(-20001, v_Message);When v_deny_User Thenv_Message := 	Sorry！Database deny you(	||User||	) access,Contact DBAs please!	;Raise_Application_Error(-20002, v_Message);When v_deny_Ip Thenv_Message := 	Sorry！Database deny you(	||Sys_Context(	USEREV	, 	SESSIOID	) ||	) access,Contact DBAs please!	;Raise_Application_Error(-2000, v_Message);When Others Thenv_Message := 	ERROR – OT_LOGO TRIGGER- Please Contact Your DBA!!	 || Sqlerrm;Raise_Application_Error(-20004, v_Message);
End;
/

二、 监控指定用户登录

其实这个可以开登录审计，这里只作为多了解一种方法。

create or replace trigger sysdba_to_alert
after logon on database
declare
message varchar2(256);
IP varchar2(15);
v_os_user varchar2(80);
v_module varchar2(50);
v_action varchar2(50);
v_type varchar2(50);
v_sql_id varchar2(50);
v_pid varchar2(10);
v_sid number;
v_program varchar2(48);
v_client_id VARCHAR2(64);
begin
IF user =	SYS	 THE-- get IP for remote connecti:
if sys_context(	userenv	,	network_protocol	) = 	TCP	 then
IP := sys_context(	userenv	,	ip_address	);
end if;select distinct sid into v_sid from sys.v_$mystat;
SELECT p.SPID, v.PROGRAM,,v.sql_id into v_pid, v_program,v_type,v_sql_id
FROM V$PROCESS p, V$SESSIO v
WHERE p.ADDR = v.PADDR AD v.sid = v_sid;v_os_user := sys_context(	userenv	,	os_user	);
dbms_application_info.READ_MODULE(v_module,v_action);v_client_id := sys_context(	userenv	,	client_identifier	);message:= to_char(sysdate,	Dy Mon dd HH24:MI:SS YYYY	)||
	 SYSDBA logon from 	||nvl(IP,	localhost	)||	 	||v_sid||	 	||v_sql_id||
	 	||v_os_user||	 	||v_client_id||	 	||v_type||
	 with 	||v_program||	 	||v_module||	 	||v_action;sys.dbms_system.ksdwrt(2,message);end if;
end;
/

三、 只允许用户连到从库不允许连到主库

Create Trigger DEY_LOGIAfter Logon On Database
Declarev_db_role Varchar2(20);v_Message Varchar2(1000);v_deny_User Exception;
Beginselect DATABASE_ROLE into v_db_role from v$database;If User In (	TESTERP	, 	testerp	) Thenif v_db_role = 	PRIMARY	 thenRaise v_deny_User;End If;End If;
ExceptionWhen v_deny_User Thenv_Message := 	Sorry! Primary database denies your user (	 || User ||	) to access.Please connect to the standby database.	;Raise_Application_Error(-20002, v_Message);When Others Thenv_Message := 	ERROR – OT_LOGO TRIGGER- Please Contact Your DBA.	 ||Sqlerrm;Raise_Application_Error(-20004, v_Message);
End;
/

四、 限制用户在某时间段内登陆数据库

• 被限制的用户不能拥有dba权限
• 需要通过sys用户创建触发器

CREATE OR REPLACE TRIGGER limit_connection
AFTER LOGO O DATABASE
BEGI
IF USER=	TM1	 THE
IF to_number(TO_CHAR (SYSDATE,	HH24	)) BETWEE 8 AD 22
THE
RAISE_APPLICATIO_ERROR(-20998,	COT LOGI BETWEE 8-20	);
ED IF;
ED IF;
ED limit_connection;
/

五、 dg broker，让应用可以自动切换的trigger（高可用的）

create trigger myapptriggafter startup on database
declarev_role varchar(0);
beginselect database_role into v_role from v$database;if v_role = 	PRIMARY	 thenDBMS_SERVICE.START_SERVICE(	myapp	);elseDBMS_SERVICE.STOP_SERVICE(	myapp	);end if;
end;
/

五、 禁止DROP、TRUCATE操作

CREATE OR REPLACE TRIGGER TR_DISABLE_DDL
BEFORE DDL O DATABASE
BEGIDECLARE L_FLAG IT;L_TEMPORARY VARCHAR2(50);BEGIIF ORA_SYSEVET = 	TRUCATE	 OR ORA_SYSEVET=	DROP	 THE
/*       DBMS_OUTPUT.put_line(	操作类型:	 || ORA_SYSEVET || CHR(1) ||	操作对象:	 || ORA_DICT_OBJ_TYPE || CHR(1) || 	所属用户:	 || ORA_DICT_OBJ_AME || CHR(1) || 	对象名称:	 || ORA_DICT_OBJ_OWER  );*/SELECT COUT(1) ITO L_FLAG FROM USER_TABLES WHERE TABLE_AME=UPPER(ORA_DICT_OBJ_AME)AD TEMPORARY=		 ;IF L_FLAG=1 THERAISE_APPLICATIO_ERROR(-20000,	You cannot TRUCATE or DROP table: 	 || ORA_DICT_OBJ_AME);ED IF;ED IF;ED;
ED;
/

参考

ORACLE 触发器控制用户登录之权限限制_kissweety的博客-CSD博客