In oracle it appears like a NON_OWNER user though granted access(select, insert, delete, update) to tables created by OWNER of the table cannot directly view them without the schema prefix.
This would mean all the queries in the java application will have to have this table owner user name prefix.
The possible options to drop this prefix :
1. ALTER SESSION SET CURRENT_SCHEMA= OWNER_SCHEMA ; (AFTER LOGON trigger as mentioned here )
But this works only for the current session unless we have a logon trigger. This seems like a preferred approach as per various forum threads.
2. Create views/synonyms from the NON_OWNER current user per table. So the tables can be accessed without prefix.
3. It could be something simpler as well (I am no DB expert) like setting the default current schema for the NON_OWNER user in some other way.
Running scripts from SQL developer:
To run this from sqldeveloper :
1. Set a default Path: Tools menu > Preferences > Database > Worksheet > Select default path to look for scripts
2. open run.sql from sqldeveloper File > Open
3. Run script.
This would mean all the queries in the java application will have to have this table owner user name prefix.
The possible options to drop this prefix :
1. ALTER SESSION SET CURRENT_SCHEMA= OWNER_SCHEMA ; (AFTER LOGON trigger as mentioned here )
But this works only for the current session unless we have a logon trigger. This seems like a preferred approach as per various forum threads.
2. Create views/synonyms from the NON_OWNER current user per table. So the tables can be accessed without prefix.
3. It could be something simpler as well (I am no DB expert) like setting the default current schema for the NON_OWNER user in some other way.
Running scripts from SQL developer:
To run this from sqldeveloper :
1. Set a default Path: Tools menu > Preferences > Database > Worksheet > Select default path to look for scripts
2. open run.sql from sqldeveloper File > Open
3. Run script.
No comments:
Post a Comment