使用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

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

連線到:
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

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;

logon_trigger