Accessing another user's table without qualifying table name with the owner-name

Accessing another user's table without qualifying table name with the owner-name

Post by Atul Deshmuk » Sat, 21 Mar 1998 04:00:00



How is it posssible to access another user's table without qualifying table
name with the owner-name.

eg.
there's a user "User1" that has the table "Table1".
Now, if there's another user "User2" that needs access to User1.Table1 ...
how can it be accomplished?

One of the ways would be to create a public synonym for User1.Table1 and
then grant access to that synonym for "User2". Using this scheme, "User2"
can issue an SQL statement such as "Select * from public_user1_table". But
in our scenario that solution is not feasible since all other users will
also be able to access this public synonym. Is there any other way to
accomplish the same thing?

Is it posssible to define a user who has a default schema other than the
schema with his own name?

I'm working on Oracle8 Enterprise Edition for WindowsNT.


Thank you.
Atul

 
 
 

Accessing another user's table without qualifying table name with the owner-name

Post by t.. » Sat, 21 Mar 1998 04:00:00


: How is it posssible to access another user's table without qualifying table
: name with the owner-name.

Synonyms. Read up on the difference between private and public synonyms.

: One of the ways would be to create a public synonym for User1.Table1 and
: then grant access to that synonym for "User2". Using this scheme, "User2"
: can issue an SQL statement such as "Select * from public_user1_table". But
: in our scenario that solution is not feasible since all other users will
: also be able to access this public synonym. Is there any other way to
: accomplish the same thing?

Private synonyms. User2 has a (set of) private synonym(s) that resolve to
user1's table(s).

IAP
--
I am using anti-spam measures, please replace 'not.valid' with 'value.net'

 
 
 

Accessing another user's table without qualifying table name with the owner-name

Post by Paul Brewe » Tue, 24 Mar 1998 04:00:00




>: How is it posssible to access another user's table without qualifying table
>: name with the owner-name.

>Synonyms. Read up on the difference between private and public synonyms.

>: One of the ways would be to create a public synonym for User1.Table1 and
>: then grant access to that synonym for "User2". Using this scheme, "User2"
>: can issue an SQL statement such as "Select * from public_user1_table". But
>: in our scenario that solution is not feasible since all other users will
>: also be able to access this public synonym. Is there any other way to
>: accomplish the same thing?

>Private synonyms. User2 has a (set of) private synonym(s) that resolve to
>user1's table(s).

Alternatively (undocumented AFAIK):

alter session set current_schema = <other_user>;

Hope this helps.
--
Paul Brewer

 
 
 

Accessing another user's table without qualifying table name with the owner-name

Post by Brett Neumeie » Wed, 25 Mar 1998 04:00:00



> How is it posssible to access another user's table without qualifying table
> name with the owner-name.
> eg.
> there's a user "User1" that has the table "Table1".
> Now, if there's another user "User2" that needs access to User1.Table1 ...
> how can it be accomplished?
> One of the ways would be to create a public synonym for User1.Table1 and
> then grant access to that synonym for "User2". [..]

Well, actually you would be granting access to the /table/ to User2.
The /synonym/ is accessible to everyone automatically, but the /table/
can only be seen by users with access to it.

Quote:> in our scenario that solution is not feasible since all other users will
> also be able to access this public synonym. Is there any other way to
> accomplish the same thing?

I am not sure why the solution will not work.  Simply grant privileges
on the table to the users and/or roles which should be able to access
it; the existence of the public synonym will not matter.

If you /really/ care about this for some reason, you can certainly
create /private/ synonyms for User1's tables, in User2's schema. But
you will still need to grant privileges on User1's tables to User2!

--
-brett neumeier

 
 
 

1. Refer to table without qualifying with owner name in SQL 7

In my company, we have many users with different permissions.
Obviously, we do not want to sp_addalias all users to "dbo".

When developer A creates table tblA, other users must access the table
with syntax like "select * from developerA.tblA".  We can end up with
many queries like "select * from developerB.tblB", "select * from
developerC.tblC", etc.

This sort of syntax is nightmare when there comes a need to change
codes querying tables.

We would like all different users (with appropriate permissions)to be
able to do "select * from tblA", regardless of who created the table.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Help - Storing large text fields within SQL Server????

3. naming tables without including owner name

4. Adding a value to a lookup table

5. syslogins table, name column and domain qualified login names

6. SOS! (Single User Problem)

7. how to access a table without knowing it's name

8. copy database

9. SQL Server forcing the owner name to be entered all of a sudden in table name

10. Getting table names without actually knowing their names...

11. owner name when accessing tables in SPs and Triggers