Re: Retrieving data without specifying table owner

Jeff:
You wrote:
> that didn't. If I can avoid rewriting each SQL script, it would make
> me happy. Is there anyway to make Oracle find the tables without
> specifying the owner name.

Yes. I'm not sure what "GROUP"s are, but it looks like they convey priviledges as well as name resolution. In Oracle, there's something similar called ROLEs, which allow you to group privs together and then grant the role to specific users. That's what you've done when you grant DBA to your user. That takes care of getting select priviledge. But ROLEs don't take care of the name resolution. If you don't specify an object's owner (a perfectly legit way to do it, I might add), it assumes the object to be owned by the current user.

To get around this, you can create synonyms (either on a user-basis or public) that will resolve to specific objects. You have to have DBA privs to do it. The syntax is:
CREATE [PUBLIC] SYNONYM [user.]synonym_name FOR [user.]table [@database_link];

So, applied to your example:

> GRANT CONNECT TO SystemOwner IDENTIFIED BY password;
> GRANT DBA TO SystemOwner;
> CREATE TABLE SystemOwner.Student
> (
> Name CHAR(30)
> );

You add in this statement:
CREATE PUBLIC SYNONYM student
FOR systemowner.student;

>
> GRANT CONNECT TO SampleUser IDENTIFIED BY password;
> GRANT DBA TO SampleUser;
>
> CONNECT SampleUser/password;
> SELECT * FROM Student;
>

You can have synonyms of synonyms, as well as of views.

Also, a little tidbit for you: SQL is case-insensitive unless you use quotes in your SQL (Not that you can't use different cases to improve readability). Good luck!

Dave Purrington

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 11:42:17 CST