Using Trigger after logon on database limit IP&USER access your Oracle database

简介:
最近有个项目需要限制某些数据库用户的访问来源IP,在PG中比较好实现,但是ORACLE没有比较简便的操作。
如果不管用户的话,仅仅限制来源IP对监听的访问是比较容易实现的,通过配置数据库服务器的sqlnet.ora文件或者修改数据库服务器的IPTABLES等手段实现。
sqlnet.ora范例:
tcp.validnode_checking=yes
tcp.invited_nodes=(172.16.33.11,172.16.34.89)

iptables范例:
[root@kefu ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
# 允许访问1521的服务器
-A RH-Firewall-1-INPUT -s 172.16.3.68/32 -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

下面来看看如何限制特定用户和特定IP:

1. 创建ACL表 (本例将ACL表建立在dsm用户下,随便建哪里都可以)
create table dsm.tbl_iplimit (logonuser varchar2(32),ip_address varchar2(15),remark varchar2(64),create_time date default sysdate);
insert into dsm.tbl_iplimit values ('DSM','172.16.18.81','digoal''s host.',sysdate);
insert into dsm.tbl_iplimit values ('DSM','local','本地',sysdate);
commit;
这里限制了DSM用户只能从172.16.18.81和ORACLE所在服务器登录.其他用户不受限制.

2. 创建触发器
conn / as sysdba
create or replace trigger "logon_audit" after
logon on database
declare
record_num number;
userip varchar2(15);
isforbidden boolean:=true;
begin
  userip:=nvl(sys_context ('userenv','ip_address'),'local');
  select count(*) into record_num from dsm.tbl_iplimit where logonuser=user;
  if (record_num>0) then
      select count(*) into record_num from dsm.tbl_iplimit where logonuser=user and ip_address=userip;
      if (record_num=0) then
      raise_application_error(-20003,'ip :'||userip||' is forbided');
      end if;
  end if;
exception
 when value_error then
  sys.dbms_output.put_line('exception handed');
 when others then
  raise;
end logon_audit;
/

3. 测试
在本地登录
SQL> conn dsm/pwd
正常
delete from tbl_iplimit where ip_address='local';
commit;
exit;
再在本地登录,已经受阻了.
SQL> conn dsm/pwd
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: ip :local is forbided
ORA-06512: at line 18

换台机器(172.16.3.67)登录:
SQL> conn dsm/pwd@//172.16.3.13:1521/sid
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: ip :local is forbided
ORA-06512: at line 18
受阻

换台机器(172.16.3.81)登录:
SQL> conn dsm/pwd@//172.16.3.13:1521/sid
正常

如果IP范围比较宽,可以写一个IP比较的函数加入到上面的判断中,避免写很多条记录。
相关文章
|
2月前
|
SQL Oracle 关系型数据库
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database (
34 2
|
2月前
|
Oracle 关系型数据库
19c 开启Oracle Database Vault
19c 开启Oracle Database Vault
54 1
|
19天前
|
SQL 监控 NoSQL
db.oplog.rs.find({"ns": "your_database_name.your_collection_name", "o": {$exists: true}}).sort({$natural: -1}).limit(1) 这个SQL什么意思
【6月更文挑战第29天】db.oplog.rs.find({"ns": "your_database_name.your_collection_name", "o": {$exists: true}}).sort({$natural: -1}).limit(1) 这个SQL什么意思
23 8
|
2月前
|
SQL NoSQL MongoDB
db.oplog.rs.find({"ns": "your_database_name.your_collection_name", "o": {$exists: true}}).sort({$natural: -1}).limit(1)
【5月更文挑战第22天】db.oplog.rs.find({"ns": "your_database_name.your_collection_name", "o": {$exists: true}}).sort({$natural: -1}).limit(1) 的作用
33 6
|
1月前
|
Oracle 关系型数据库 Linux
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
Requirements for Installing Oracle Database/Client 19c on OL8 or RHEL8 64-bit (x86-64) (Doc ID 2668780.1)
17 0
|
2月前
|
SQL Oracle 关系型数据库
Connect to Autonomous Database Using Oracle Database Tools
Connect to Autonomous Database Using Oracle Database Tools
29 1
|
2月前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
2月前
|
SQL Oracle 安全
Oracle Database Vault Access Control Components
Oracle Database Vault Access Control Components
21 0
|
2月前
|
Oracle 安全 关系型数据库
What Is Oracle Database Vault?
The Oracle Database Vault security controls protect application data from unauthorized access, and helps you to comply with privacy and regulatory requirements. You can deploy controls to block privileged account access to application data and control sensitive operations inside the database using
17 0
|
2月前
|
Oracle 关系型数据库
Oracle查询优化-分解IP地址
【2月更文挑战第5天】【2月更文挑战第12篇】分解IP地址
40 8

推荐镜像

更多