其他用户表的 Oracle 连接字符串

发布于 2024-10-13 06:44:26 字数 328 浏览 4 评论 0原文

我们有 2 台具有相同数据库模式的服务器，但是由于我们的基础架构在一台服务器上的设置方式，我们必须为所有表名添加用户名

ex 前缀：

select * from engmon.cmts

但是在另一台服务器上，查询需要如下所示

select * from cmts

这是因为服务器连接到不同架构/用户名中的表。是否可以更改 jdbc url 中的某些内容以使两个查询看起来像#2？这样我们就不需要根据我们连接到的服务器（dev 或 prod）动态更改 sql。

有什么想法吗？

原文

We have 2 servers with the same database schema, however due to the way our infrastructure is setup on one server we must prefix all table names with a username

ex:

select * from engmon.cmts

However on the other server the query would need to look like this

select * from cmts

This is because one server connects to a table in a different schema/username. Is it possible to change something in the jdbc url to make both queries look like #2? That way we do not need to dynamically change the sql depending on which server we are connecting to (dev or prod).

Any ideas?

分享到QQ

分享到微博

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

发布评论

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

柠栀 2024-10-20 06:44:26

我不相信您可以对 JDBC URL 执行任何操作。但是，您可以在连接后立即发出 SQL 语句

ALTER SESSION SET current_schema = ENGMON

（您也可以将其放在 Oracle 中的登录触发器中）。这将具有指示 Oracle 使用 ENGMON 模式而不是当前用户的模式来解析不合格的对象名称的效果。这对权限没有影响——您的会话仍然只具有分配给当前用户的权限，而不是分配给 ENGMON 用户的权限。

或者，您可以为需要访问的各种表创建同义词。本地同义词（仅对您可见）或公共同义词（对所有用户可见）。因此，

CREATE [PUBLIC] SYNONYM cmts
   FOR engmon.cmts

同义词只需创建一次，并且适用于所有会话，无论它们来自哪个应用程序。每次都需要设置 CURRENT_SCHEMA。

I don't believe you can do anything to the JDBC URL. However, you could issue the SQL statement

ALTER SESSION SET current_schema = ENGMON

immediately after connecting (you could also put this in a logon trigger in Oracle). This would have the effect of instructing Oracle to resolve unqualified object names using the ENGMON schema rather than the current user's schema. This has no impact on permissions-- your session still only has the privileges assigned to the current user, not to the ENGMON user.

Alternately, you could create synonyms for the various tables that you need to access. Either local synonyms (visible just to you) or public synonyms (visible to all users). So

CREATE [PUBLIC] SYNONYM cmts
   FOR engmon.cmts

Synonyms would only need to be created once and would apply to all sessions no matter what application they come from. Setting the CURRENT_SCHEMA would need to be done every time.

回复收藏 0 原文

~没有更多了~