Shulou(Shulou.com)06/03 Report--

Trigger restricts specified IP access to oracle database

-BLOG from Dege, find it very useful, record it.

Recently, there is a project that needs to restrict the access of some database users to the source IP, which is easier to implement in PG, but ORACLE does not have an easy operation.

If you ignore the users, it is relatively easy to restrict the access of the source IP to the monitoring, which is achieved by configuring the sqlnet.ora file of the database server or modifying the IPTABLES of the database server.

Sqlnet.ora example:

Tcp.validnode_checking=yes

Tcp.invited_nodes= (172.16.33.11172.16.34.89)

Iptables example:

[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]

-An INPUT-j RH-Firewall-1-INPUT

-A FORWARD-j RH-Firewall-1-INPUT

# allow access to 1521 of the server

-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

Let's look at how to restrict specific users and specific IP:

1. Create an ACL table (in this example, the ACL table can be built anywhere under the dsm user)

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',' Local', sysdate)

Commit

This restricts DSM users to login from 172.16.18.81 and ORACLE servers. Other users are not restricted.

two。 Create trigger

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 recording 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

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.