insert into tq84_table_user_one values(user);
commit;
User two does an analog thing
connect user_two/passwordGardenSecret
create table tq84_table_user_two(txt varchar2(20));
grant all on tq84_table_user_two to user_one;
insert into tq84_table_user_two values(user);
commit;
If user two wants to select his own table, he does not need to prefix with his username:
select * from tq84_table_user_two;
However, if he wants to query user one's table, he must prefix it (unless a synonym exists):
select * from user_one.tq84_table_user_one;
If user two finds it tedious to always type user_one's schema, he might choose to set user_one as default schema:
alter session set current_schema = user_one;
The select statement now works perfectly:
select * from tq84_table_user_one;
Note: user_two is still user_one. It's not as though he would magically get some extra privileges. The following statement returns USER_ONE, USER_TWO:
select user, sys_context('userenv','current_schema') from dual;
Also, if user_two queries USER_TABLES, he finds his tables:
select * from user_tables where table_name like 'TQ84_TABLE_USER%';
EXECUTE IMMEDIATE
The execute immediate statement is not influenced by the «current schema», rather, it executes in the context of the connected user.
Determine the name of the current schema
The name of the current schema can be determined with sys_context('userenv','current_schema').