ANBOB™

前几天有人问到是否有办法禁用Oracle database link？是的，  有时出于安全、性能、防止SCN传播等原因需要禁用本地创建DBLINK,同时也希望其它库不要DBLINK访问我们的库， 但是只要给其它人提供了本库的用户、密码、监听端口、服务名连接的信息，即使只有create session的权限，对方也可以dblink 从其它库连到本地数据库。

1， 如何控制本地数据库创建DBLINK

本地的好控制，可以通过create database link\ create public database link 权限限制本用户的权限。

2,   如何阻止远程数据库DBLINK 连接本库

阻止某一个数据库与本库有任何连接可以通过sqlnet.ora 文件配置TCP.EXCLUDED_NODES 增加黑名单；

阻止所有数据库与本库有任何DBLINK 连接通过配置open_links  =0;

SQL> @pd open_links
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                           VALUE   DESCRIPTION
---------- ----- ------------------------------ ------- ----------------------- 
      3653   E45 open_links                     4       max # open links per session
      3654   E46 open_links_per_instance        4       max # open links per instance

SQL> alter system set open_links=0 scope=spfile;
System altered.

SQL> shutdown abort   --生产库不要这么干
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 549452656 bytes
Fixed Size 9137008 bytes
Variable Size 306184192 bytes
Database Buffers 230686720 bytes
Redo Buffers 3444736 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual@lookup_dl;
select sysdate from dual@lookup_dl
*
ERROR at line 1:
ORA-02020: too many database links in use

Note: 在19.2多租户环境测试open_links在PDB级修改,重启pdb参数有变，但是没有效果。

阻止个别用户或远程数据库的DBLINK， 使用logon database trigger， 当然对于logon频繁的不建议。

-- 如禁用anbob用户dblink，其它可以增加其它条件 
-- source database
SQL>Create or replace trigger logon_denied_dblink
after  logon on database
begin
if sys_context('USERENV','SESSION_USER')='ANBOB' AND   sys_context('USERENV','module') like 'oracle%' then
raise_application_error(-20001,'Denied!  You are not allowed to logon the database via dblink');
end if;
end;
/

-- target database

anbob@ORCL>select current_scn from v$database;

         CURRENT_SCN
--------------------
             3198315

anbob@ORCL>select sysdate from dual@dl19c;
select sysdate from dual@dl19c
                         *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.LOGON_DENIED_DBLINK'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Denied!  You are not allowed to logon the database via dblink
ORA-06512: at line 3
ORA-02063: preceding 4 lines from DL19C


Elapsed: 00:00:00.65
anbob@ORCL>select current_scn from v$database;
         CURRENT_SCN
--------------------
             3198321

Note: 另外发现logon trigger优先于OPEN_links的校验，和在LOGON trigger拒接连接后SCN也并未同步。

3， 其它脚本

以前的写分享过如何用脚本检查谁正在连接本库
Script: Who’s using a database link?(找出谁在使用dblink)

如何使用sys删除私有Database Link？

database link是一种特殊对象，出于安全考虑，不允许其它用户使用私有的dblink。同时dba也无法直接删除其它用户的私有dblink,  正常需要私有dblink的owner 用户自己删除， 但是也可以使用动态SQL的方式绕过验证。如下:

SQL> alter session set container=pdb1;

Session altered.

SQL> show user
USER is "SYS"
SQL> @dblinks;

OWNER                DB_LINK                                  USERNAME             HOST                                     CREATED
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ---------
SYS                  SYS_HUB                                                       SEEDDATA                                 04-FEB-19
PUBLIC               LOOKUP_DL                                ANBOB                pdb1                                     13-JUL-19
ANBOB                LOOKUP_DL                                ANBOB                pdb1                                     13-JUL-19

SQL> drop public database link lookup_dl;

Database link dropped.

SQL> drop database link anbob.lookup_dl;
drop database link anbob.lookup_dl
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> CREATE PROCEDURE anbob.drop_db_link
AS
BEGIN
EXECUTE IMMEDIATE 'drop database link lookup_dl';
END drop_db_link;
/

Procedure created.

SQL>  exec anbob.drop_db_link
PL/SQL procedure successfully completed.

SQL> @dblinks

OWNER                DB_LINK                                  USERNAME             HOST                                     CREATED
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ---------
SYS                  SYS_HUB                                                       SEEDDATA                                 04-FEB-19

打赏

微信扫一扫，打赏作者吧～