用于创建另一个用户拥有的数据库链接的 Oracle 语法

发布于 2024-07-23 08:45:44 字数 371 浏览 8 评论 0原文

创建数据库链接的典型语法如下：

create database link remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

但我希望我的数据库链接在创建后由另一个帐户拥有。有没有办法做到这一点？

以下不起作用：

create database link anotheruser.remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

原文

The typical syntax for creating a db link is as follows:

create database link remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

But I'd like my DB link owned by another account after it's created. Is there a way to do this?

The following does NOT work:

create database link anotheruser.remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抹茶夏天i‖ 2024-07-30 08:45:44

Sathya 是正确的，因为 CREATE DATABASE LINK 语法不允许在另一个模式中创建数据库链接。但是...

解决方法

只要anotheruser具有CREATE DATABASE LINK，IS就可以在另一个用户的架构中创建数据库链接 权限，并且您连接的用户具有 CREATE ANY PROCEDURE 权限。

这是我使用的解决方法：

    create procedure anotheruser."tmp_doit_200906121431"
    is
    begin
      execute immediate '
        create database link remote_db_link 
        connect to remote_user 
        identified by remote_password 
        using ''remote_db'' ';
    end;
    /
    begin
      anotheruser."tmp_doit_200906121431";
    end;
    /
    drop procedure anotheruser."tmp_doit_200906121431"
    /

让我们放松一下。首先，我在 anotherusers 的架构中创建一个过程；此过程包含我要运行的CREATE DATABASE LINK 语句。

执行该过程时，它以该过程的所有者身份运行，因此 CREATE DATABASE LINK 语句由 anotheruser 执行。

过程的名称并不重要，只是我需要确保它不与任何现有的对象名称冲突。我使用小写字母（将过程名称括在双引号中），使用“tmp”将此对象标记为“临时”，并使用当前的 yyyymmddhh24miss 作为过程名称的一部分。（我通常运行 DBA_OBJECTS 查询来检查匹配的 object_name 是否不存在。）

对于“一次性”类型的管理功能，这是一种可行的解决方法。与其他选择相比，我更喜欢这种方法：保存另一个用户的密码，更改密码，以用户身份连接，然后将另一个用户的密码重置回保存的密码。）

Sathya is correct, in that the CREATE DATABASE LINK syntax does not allow creating a database link in another schema. HOWEVER...

WORKAROUND

It IS possible to create a database link in another user's schema, as long as anotheruser has CREATE DATABASE LINK privilege, and the user you are connected as has CREATE ANY PROCEDURE privilege.

Here's the workaround I use:

    create procedure anotheruser."tmp_doit_200906121431"
    is
    begin
      execute immediate '
        create database link remote_db_link 
        connect to remote_user 
        identified by remote_password 
        using ''remote_db'' ';
    end;
    /
    begin
      anotheruser."tmp_doit_200906121431";
    end;
    /
    drop procedure anotheruser."tmp_doit_200906121431"
    /

Let's unwind that. First, I create a procedure in the anotherusers's schema; this procedure contains the CREATE DATABASE LINK statement that I want to run.

When the procedure is executed, it runs as the owner of the procedure, such that the CREATE DATABASE LINK statement is executed by anotheruser.

The name of the procedure is not important, except that I need to make sure that it doesn't conflict with any existing object name. I use lowercase letters (enclosing the procedure name in double quotes), using "tmp" to mark this object as "temporary", and using the current yyyymmddhh24miss as the part of the procedure name. (I usually run a query of DBA_OBJECTS to check that a matching object_name does not exist.)

For a "one-off" type admin function, this is a viable workaround. I prefer this to the other alternative: saving the anotheruser's password, changing the password, connecting as the user, and resetting anotheruser's password back to the saved.)

回复收藏 0 原文