472,141 Members | 1,020 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,141 software developers and data experts.

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 8936
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 leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.