Oracle：根据角色更改架构
有这篇文章描述了设置登录时更改架构的触发器。有没有办法检查用户的角色，看看它是否匹配特定的角色而不是用户名本身？
我尝试在when子句中对user_role_privs进行子查询，但它不允许这样做。有想法吗？
更新
这是我根据 Yahia 建议的解决方案使用的。但它似乎不起作用。当我使用具有该角色的用户登录时，它仍然无法识别前面没有架构名称的表。
CREATE OR REPLACE TRIGGER db_logon
AFTER logon ON DATABASE
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM user_role_privs WHERE granted_role = 'USER_ROLE' and username <> 'DEFAULT_SCHEMA';
IF v_count > 0 THEN
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = DEFAULT_SCHEMA';
END IF;
END;
There is this post that describes setting up a trigger to change the schema on login. Is there a way to check the role of the user to see if it matches a specific role instead of the username itself?
I tried to do a subquery to user_role_privs in the when clause, but it doesn't allow that. Ideas?
UPDATE
This is what I'm using per Yahia's suggested solution. It does not seem to be working though. When I login with a user with the role, it still does not recognize the table without the schema name before it.
CREATE OR REPLACE TRIGGER db_logon
AFTER logon ON DATABASE
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM user_role_privs WHERE granted_role = 'USER_ROLE' and username <> 'DEFAULT_SCHEMA';
IF v_count > 0 THEN
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = DEFAULT_SCHEMA';
END IF;
END;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的 - 放弃 WHEN 部分并在触发器内动态构建 SQL 字符串：
在
USER_ROLE_PRIVS上使用SELECT并从ROLE映射您想要的任何内容到SCHEMA。然后为
EXECUTE IMMEDIATE构建一个 SQL 字符串，包括映射的SCHEMA。Yes - ditch the WHEN part and build the SQL string inside the trigger dynamically:
Use the
SELECTonUSER_ROLE_PRIVSand map whatever you want from theROLEto aSCHEMA.Then build an SQL string for
EXECUTE IMMEDIATEincluding the mappedSCHEMA.