oracle限制特定ip访问，被限制用户不能有dba及imp_full_database角色

出于数据安全性，公司要求DBA实现控制拒绝特定的用户登录，由于公司不同的工作楼层分属于不同的vlan，因此单从linux主机层次依赖ACL访问列表控制登录数据库服务器，已经不能实现。
因此，只能考虑从数据库内部加以限制登录数据库，也就是借助oracle Trigger实现登录验证，实现方法就是捕获到拒绝登录的用户就抛应用异常强制用户退出登录，也就是使用Raise_Application_Error
实现，但是 需要注意的是，被限制的用户不能有DBA权限，也不能有imp_full_database权限。其实，想想也明白，如果不小心限制了dba权限的用户登录，那么sys就不能登录数据库，也就全完蛋了。
/**
功能：实现限制特定用户登录数据库
注意：被限制用户不能有dba及imp_full_database权限，否则该触发器不能起作用
日期：2017-1-4
**/
Create Or Replace Trigger DENY_LOGIN
After Logon On Database
Declare
v_Program Varchar2(48);
v_Message Varchar2(1000);
v_deny_Client Exception;
v_deny_User Exception;
v_deny_Ip Exception;
Begin
Select Program Into v_Program From V$session Where Audsid = Sys_Context(‘USERENV’, ‘SESSIONID’) And Rownum < 2;
If Lower(v_Program) = ‘plsqldev.exe’ Then
Raise v_deny_Client;
End If;
If User In (‘SCOTT’,‘scott’) Then
Raise v_deny_User;
End If;
If Sys_Context(‘USERENV’, ‘ip_address’) = ‘10.117.196.52’ Then
Raise v_deny_Ip;
End If;
Exception
When v_deny_Client Then
v_Message := ‘Sorry！You cannot access database using this software client!’;
Raise_Application_Error(-20001, v_Message);
When v_deny_User Then
v_Message := ‘Sorry！Database deny you(’||User||’) access,Contact DBAs please!’;
Raise_Application_Error(-20002, v_Message);
When v_deny_Ip Then
v_Message := ‘Sorry！Database deny you(’||Sys_Context(‘USERENV’, ‘SESSIONID’) ||’) access,Contact DBAs please!’;
Raise_Application_Error(-20003, v_Message);
When Others Then
v_Message := ‘ERROR – NOT_LOGON TRIGGER- Please Contact Your DBA!!’ || Sqlerrm;
Raise_Application_Error(-20004, v_Message);
End;
部分测试结果如下：
C:\Users\localadmin>sqlplus / as sysdba
SQLPlus: Release 11.2.0.1.0 Production on 星期三 1月 4 09:32:27 2017
Copyright © 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> revoke dba from scott;
撤销成功。
SQL> quit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
C:\Users\localadmin>sqlplus scott/oracle@oradb
SQLPlus: Release 11.2.0.1.0 Production on 星期三 1月 4 09:32:39 2017
Copyright © 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20002: Sorry！Database deny you(SCOTT) access,Contact DBAs please!
ORA-06512: 在 line 24
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQLPlus
C:\Users\localadmin>
C:\Users\localadmin>sqlplus scott/oracle
SQLPlus: Release 11.2.0.1.0 Production on 星期三 1月 4 09:48:42 2017
Copyright © 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20002: Sorry！Database deny you(SCOTT) access,Contact DBAs please!
ORA-06512: 在 line 24
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus
C:\Users\localadmin>

C:\Users\localadmin>sqlplus zhul/oracle
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 4 09:51:43 2017
Copyright © 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>