logon_trigger

使用logong trigger限制登陆。
相关：
Profile Parameters (sqlnet.ora):
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES:Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

1，创建触发器
create or replace trigger logon_control_trigger
after logon on database
declare
  user_name STRING(30);
begin
  SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user_name from dual;
  IF user_name='T' THEN
      raise_application_error(-20001,'Login not allowed!');
  END IF;
end;
/

2，测试：
2.1测试用户
create user t identified by t;
grant connect,resource to t;

C:\Documents and Settings\mh0575>sqlplus t/t@dev95

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 12月 3 10:07:01 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Login not allowed!
ORA-06512: at line 7

请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误

2.2“ADMINISTER DATABASE TRIGGER”权限对logon trigger的“免疫”：
grant ADMINISTER DATABASE TRIGGER to t;

C:\Documents and Settings\mh0575>sqlplus t/t@dev95

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 12月 3 10:07:26 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

 

SQL> SELECT grantee
  2  FROM dba_sys_privs
  3  WHERE PRIVILEGE = 'ADMINISTER DATABASE TRIGGER';
 
GRANTEE
------------------------------
DBA
SYS
IMP_FULL_DATABASE
T
 
3,创建使用schema触发器
create or replace trigger logon_control_trigger
after logon on t.schema
declare
  ip_address varchar2(100);
begin
  SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO ip_address FROM DUAL;
  IF ip_address='22.11.99.100' THEN
      raise_application_error(-20001,'Login not allowed!');
  END IF;
end;
/

效果与on database相同.

4，kill my session trigger？
--create as sysdba
create or replace trigger logon_control_trigger
after logon on t.schema
declare
  ip_address varchar2(100);
  l_sid varchar2(100);
begin
  SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO ip_address FROM DUAL;
  IF ip_address='22.11.99.99' THEN
      SELECT sid||','||serial# into l_sid
        FROM v$session
      WHERE sid = (SELECT sid FROM v$mystat WHERE rownum < 2);
      execute immediate 'alter system disconnect session '''||l_sid||''' immediate';
      raise_application_error(-20001,'Login not allowed!');
  END IF;
end;
/

C:\Documents and Settings\mh0575>sqlplus t/t@dev95

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 12月 3 10:38:31 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00027: cannot kill current session
ORA-06512: at line 10

drop trigger logon_control_trigger;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-750439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-750439/