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
.
精彩评论