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

[nitinpatel1117]

Hi,

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.

select * from customers;

but it does work if I add the schema name in front of the table, so the below works:

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) .

[debasisdas]

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.

[Dave44]

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.

[nitinpatel1117]

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.