Allow user to fully access a tablespace!! HELP!!!

I want to create all of our database users for the company, and allow them to have full privilages on a certain tablespace "DEV".

How can I create users and have the user function just like the owner of that tablespace?

i.e. select * from inv_detail;

instead of having them do

select * from dev.inv_detail;

Can anyone help me out, does this require me to make public synonyms for everything in the database or can I do it a different way? I have already created a role granting them access to the tables in DEV, but they would still have to fully qualify the tablename.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

pfinchConnect With a Mentor Commented:

I assume you have created a user and schema for "dev".

If you create the users that want to use dev as proxy users this should allow the users to access tables with out the "dev." prefix.

What database?
InternAuthor Commented:
Sorry - I am running the entire 10g suite, so the database is (I think)
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

jlevieConnect With a Mentor Commented:
Okay so it is Oracle. How are the users accessing the database? If they are using the command line tool and they specify DEV as tablespace to open they should be able to use the simple form of the query.
InternAuthor Commented:
Alright let me explain the main problem.  I am developing an app in Forms, and I do not want to fully qualify the name of each table.  The users will open the form from portal via single sign-on.  Then the form fails because all the tables are not fully qualified.

What do you mean by having the users use dev as a proxy user?
A proxy user is a user without a schema.

So you can point the proxy user to the dev schema, and they should not need to use fully qualified names.

Load up oracle enterprise manager and you will see how to add proxy users.

InternAuthor Commented:
Thank you so much for the advice...  It will help me alot.
All Courses

From novice to tech pro — start learning today.