469,331 Members | 6,372 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

how to execute query without having to declare schema all the time

111 100+

I've got a slight issue which i'm trying to resolve and was wondering if anyone would help.

Basically, I was building a web application, and while it was being built I was connecting to Oracle as a User with Full admin rights.

Now the system is built and ready to be put live, so therefore, I decided to create a new user that can only do insert, select and update queries e.g. they can't run queries to create or drop tables.

Now since creating the new user all my SQL queries in my application do not seem to work. However, I have managed to figure out that if I put the schema name in front of the table name, the queries do work. I've got no ideal why this has happened and need to fix it without modifying every query in my web application, because I have over 400 queries in this web application.

e.g. the following query which worked previously does not seem to work anymore.
Expand|Select|Wrap|Line Numbers
  1. select * from customers;
but it does work if I add the schema name in front of the table, so the below works:
Expand|Select|Wrap|Line Numbers
  1. select * from schema.customers;
Basically, I would like to know how I can execute my queries without having to delare the schema in each query (i.e. the way it worked originally) .
Jul 23 '08 #1
3 8485
8,127 Expert 4TB
You can create a DB LINK to the target schema from the executing schema . Then you need to use the db link name in the query not the schema name.
Jul 23 '08 #2
153 100+
If this is happening within the same oracle database then a DB Link is of no use here.

Objects such as tables are owned by the user they are created in. Did your "admin rights" user actually own the tables that you were using? If there was no prefixed schema name in front of the tables in your queries i will assume that yes that user did own the objects.

Now your new user cannot see those objects as it does not have the privileges to. Your "owner" user needs to grant the necessary privileges to the new user as well as your new user needs to have a synonym (pointer) with the same name as the "admin" owners' object name.

for example:
user A owns table T1. In order for user B to use that table it needs to have select, insert, update and delete granted either directly to him or to a role which he has been granted. user B also needs to have a private synonym called T1 which points to A.T1. that last step is the one the prevents the need to prefix the schema name to the query.
Jul 23 '08 #3
111 100+
Thanks Dave, that was really helpful.

I Googled 'Oracle synonyms' and found the relevant info that i needed.
I have created synonyms for the users and it all works fine now, thanks.
Jul 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by krystoffff | last post: by
17 posts views Thread by Johnny BeGood | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.