The user is unable to view tables from another schema even though there is a logon trigger that runs: alter session set current_schema = 'other-user'; which sets the current schema to the schema that owns the objects.
Steps to Replicate:
1. Create 2 users
PSBP06USER1
PSBP06 - owns the objects
2. CREATE OR REPLACE TRIGGER after_logon_psbp06user
AFTER
LOGON
ON DATABASE
begin
if user = 'PSBP06USER1' then
execute immediate 'alter session set current_schema = PSBP06';
end if;
end;
/
3. Create two tables in schema PSBP06
4. Create role example_role
5. Grant select, insert, update, delete on <PSBP06.table1> to example_role
6. Grant select, insert, update, delete on <PSBP06.table2> to example_role
7. Grant example_role to PSBP06USER
8. User PSBP06USER1 connects to the instance.
9. Trigger executes and sets current_schema to PSBP06 for the session.
10. The session list at the top shows that psbp06user1 is connected. The DB Navigator window shows that it is looking at schema PSBP06.
The problem is that the user cannot see any of the table items under PSBP06.
© ALL RIGHTS RESERVED. Feedback 이용 약관 개인정보 보호정책 Cookie Preference Center