473,729 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

111 New Member

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 9273
8,127 Recognized Expert Expert
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 New Member
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 New Member
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

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

Similar topics

by: TJ | last post by:
Anyone know how to display process id's (normally found by going to Component Services and switching to applications to 'Status View') in a web page? I'm trying to keep remote users from having to terminal server or use com+ to view the process id's they need for asp debugging (via attaching to a process (remote debugging via dcom and machine debug manager)) Thx in advance,
by: krystoffff | last post by:
Hi all For speed reasons, I would like to rewrite the following query without the subquery : SELECT * FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2 where field2=X); I thought something like this would work fine : SELECT table1.* FROM table1 LEFT JOIN table2 ON
by: python | last post by:
hello and thanks for reading this, i have been a dos/windows user using some form of the basic language for 30 years now. i own and run a small programming company and there is one feature that keeps me in the windows/basic world. while i will agree that it has not evolved well, it does have one awesome feature that i have yet to see replicated in any linux product that i know about so far. i am a long time windows user and have had...
by: Dieter Vanderelst | last post by:
Dear all, Could anybody tell me whether there are ways to use an existing DLL file in Python without having access to the source code? I'm trying to find a way to use the image filters available in the Filters-project (http://filters.sourceforge.net/) without having to compile or build the DLL myself, which is according to the authors very hard.
by: Johnny BeGood | last post by:
Hi All, Is there a way to post the contents of a form without having to click on the Submit button, i.e <form method="post" action="http://www.whereever.com/ProcessTheData.php"> <INPUT name="Field1" type="text"> <INPUT name="Field2" type="text"> <INPUT name="Field3" type="text"> <INPUT name="EsendexRecipient" type="text">
by: DR | last post by:
I heard there is some trick to referencing statics in C# CLR stored procedure without having to mark the assembly as unsafe. Does anyone know this? This is usefull as the case of needing a little static shared variable here and there without having to compromise safety in the Sql Server 2005.
by: DR | last post by:
I heard there is some trick to referencing statics in VB.NET CLR stored procedure without having to mark the assembly as unsafe. Does anyone know this? This is usefull as the case of needing a little static shared variable here and there without having to compromise safety in the Sql Server 2005.
by: resmi318 | last post by:
I am running a query similar to given below. CREATE TABLE ABC_08 AS (SELECT x.col1, trim(x.col2), x.col3, x.col4, x.col5 FROM Table1 x
by: TriAdmin | last post by:
I am working with a system that allow me to add custom fields but I can not add OnChange() language to the custom fields. So I want to have a function in the header that recognizes when fieldx is changed, do a calculation with the value of fieldx and change fieldy. Can that be done without having to add an OnCHange() event to fieldx? If so, can you give a quick example or link?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.