开发者

Oracle: Change Schema based on role

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 workin开发者_高级运维g 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;


Yes - ditch the WHEN part and build the SQL string inside the trigger dynamically:

CREATE OR REPLACE TRIGGER db_logon
AFTER logon ON DATABASE 

Use the SELECT on USER_ROLE_PRIVS and map whatever you want from the ROLE to a SCHEMA.

Then build an SQL string for EXECUTE IMMEDIATE including the mapped SCHEMA.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜