通过5个数据库实例连接6个表
通过5个数据库实例连接6个表
提问于 2013-06-27 08:00:59
我目前正在重新设计我的公司用户管理，并创建了一个表，其中列出了所有5个数据库实例的所有用户。下一步是，我需要编写一个查询，向我展示用户在所有实例中的所有角色。
我已经使用了UNION ALL，但是输出是非结构化的，您无法判断哪个角色在哪个实例上。因此，我只对3张表尝试了以下操作：
SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
ON (U.USERNAME = W.GRANTEE AND U.DB_INSTANCE = 'DB1')
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
ON (U.USERNAME = V.GRANTEE AND U.DB_INSTANCE = 'DB2')
WHERE U.USERNAME = 'USER'
ORDER BY U.USERNAME ASC;它确实起了作用，但输出并不令人满意：
GRANTED_ROLE_DB1 GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1
ROLE_2
ROLE_3
ROLE_4
ROLE_1
ROLE_2
ROLE_4
ROLE_5
ROLE_6有没有办法做出这样的输出：
GRANTED_ROLE_DB1 GRANTED_ROLE_DB2
--------------------------- --------------------------
ROLE_1 ROLE_1
ROLE_2 ROLE_2
ROLE_3
ROLE_4 ROLE_4
ROLE_5
ROLE_6我试过ON ((U.USERNAME = V.GRANTEE OR W.GRANTED_ROLE = V.GRANTED_ROLE) AND U.DB_INSTANCE = 'DB2')，但是输出更糟糕。
你们有什么建议或有用的想法吗？
回答 1
Database Administration用户
发布于 2013-06-27 16:16:35
问题是您限制了实例字段，这导致角色只出现在某一列或另一列中。
要解决这个问题，请删除对db_instance和full outer join的限制--角色从第二个数据库到第一个数据库中的角色，如下所示：
SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
ON (U.USERNAME = W.GRANTEE)
FULL OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
ON (U.USERNAME = V.GRANTEE AND V.GRANTED_ROLE = W.GRANTED_ROLE)
WHERE nvl(U.USERNAME, v.username) = 'USER'
ORDER BY nvl(W.GRANTED_ROLE, V.GRANTED_ROLE) ASC;这个SQLFiddle有一个有用的例子。在Andriy的评论之后更新小提琴。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/45344
复制相关文章
相似问题
腾讯云开发者
