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

The function of forbidding the designated IP host from accessing the database can be achieved by using the sqlnet.ora file on the database server, which is of great help to improve the security of the database. At the same time, this technology provides an effective means for us to manage and restrict database access control.

Here are the specific steps to achieve this goal for reference only:

1. Default contents of server-side sqlnet.ora files

# sqlnet.ora Network Configuration File: d:\ Server\ Oracle\ Product\ 11.2.0\ dbhome_1\ network\ admin\ sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

two。 Confirm the IP address of the client:

C:\ Documents and Settings\ Administrator > ipconfig

3. Use the tnsping command and the sqlplus command on the client to verify database connectivity, respectively:

C:\ Documents and Settings\ Administrator > tnsping irmdb

C:\ Documents and Settings\ Administrator > sqlplus / nolog

Come here to illustrate the reachability of the database that has been proved on the client in both ways.

4. Restrict client IP address 9.123.112.16 access to the current irmdb database:

We just need to add the following to the server-side sqlnet.ora file.

# sqlnet.ora Network Configuration File: d:\ Server\ Oracle\ Product\ 11.2.0\ dbhome_1\ network\ admin\ sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

Tcp.validnode_checking=yes

Tcp.invited_nodes= (9.123.112.34)

Tcp.excluded_nodes= (9.123.112.16)

Meaning of the first line: enable the IP restriction function

The meaning of the second line: the list of IP addresses that allow access to the database. Multiple IP addresses are separated by commas. In this case, we write to the IP address of the database server.

The meaning of the third line: disable access to the IP address list of the database, multiple IP addresses are separated by commas, here we write the IP address 9.123.112.16 that we want to restrict.

5. It takes effect after the server-side listener is restarted (this can also be achieved through lsnrctl reload):

C:\ Documents and Settings\ Administrator > lsnrctl stop

1) what really works in 9i is the sqlnet.ora file, and we modify sqlnet.ora is actually the best and fastest way.

Add the following section to soracle\ product\ 10.2.0\ db_1\ network\ ADMIN\ qlnet.ora

Tcp.validnode_checking=yes

# IP allowed to be accessed

Tcp.invited_nodes= (ip1,ip2...)

# forbidden IP

Tcp.excluded_nodes= (ip1,ip2...)

Then restart the listener.

Points to pay attention to:

1. Both tcp.invited_nodes and tcp.excluded_nodes exist, mainly tcp.invited_nodes.

2. Be sure to license or not disable the native IP address of the server, otherwise you will not be able to start or stop listening through lsnrctl, because the process listener will access the listener through the local IP, and the IP is prohibited, but starting or shutting down through the service does not affect it.

3. After modification, listening must be restarted to take effect, and there is no need to restart the database.

4. Any platform is fine, but it is only applicable to TCP/IP protocol

(2) the second method is implemented with triggers.

1. This trigger implements the function that IP cannot access test users since 192.168.137.

Create or replace trigger chk_ip

After logon on test.schema

Declare

Ipaddr VARCHAR2 (30)

Begin

Select sys_context ('userenv',' ip_address') into ipaddr from dual

If ipaddr like ('192.168.137.%') then

Raise_application_error ('- 2000, 'you can not logon by test')

End if

End

/

By setting the oracle9i parameter file, you can control the ip address of the access computer.

Add to the configuration file $ORACLE_HOME/network/sqlnet.ora on this machine 172.28.65.13:

# enable checking of ip addresses

Tcp.validnode_checking=yes

# ip allowed to be accessed

Tcp.invited_nodes= (172.28.65.13)

# forbidden ip

Ip.excluded_nodes= (172.27.65.15)

Restart listening!

$lsnrctl reload

LSNRCTL for Solaris: Version 9.2.0.4.0-Production on 14-DEC-2005 16:59:19

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC0)

The command completed successfully.

Edit the $ORACLE_HOME/network/admin/tnsnames.ora file on the 172.28.65.15 machine:

You can add a new service (dsf) here:

Dsf =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (Host= 172.28.65.13) (Port = 1521))

(CONNECT_DATA = (SID = ORCL))

)

Do a tnsping test on 15:

$tnsping dsf

TNS Ping Utility for Solaris: Version 9.2.0.4.0-Production on 14-DEC-2005 17:04:02

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host= 172.28.65.13) (Port = 1521) (CONNECT_DATA = (SID = ORCL)

TNS-12537: TNS:connection closed

Connectivity testing:

$sqlplus wacos/oss@dsf

SQL*Plus: Release 9.2.0.4.0-Production on Wed Dec 14 17:04:24 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:

ORA-12537: TNS:connection closed

-

TCP.VALIDNODE_CHECKING, this parameter must be set, and the value must be YES, otherwise it will not be enabled.

TCP.VALIDNODE_CHECKING=YES

The setting parameter of the whitelist. This address list must contain the address of the local machine, otherwise the listening may fail to start.

TCP.INVITED_NODES= (10.10.2.100, 10.10.2.101)

Setting parameters of the blacklist:

TCP.EXCLUDED_NODES= (10.10.1.100)

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.