Re: Running SQR on the Client not as owner

[Arun Kumar <arunkumar13@YAHOO.COM>]

Larry,

you dont have to manually do every table if you have
to give access to every table of the owner. Then you
can generate sql and execute them.
You can use many views and tables (e.g dba_all_tables)
available in oracle to accomplish.
you can spool the result of the following sql and run
it as a sql statements.
select 'CREATE SYNONYM ' || Table_name || ' FOR ' ||
owner || '.' || Table_name from dba_all_tables
where owner = 'SYSADM'

The result of the above sql you can run as a sql
statement. You can actually the edit the result if you
want to exclude access to some of the table too.

Similarly built sql for grant statement and run them
too.
Actually its rather very simple

Hope this helps

Arun




>Thanks for the info.  It really distresses me that we
>need to do this
>table by table....(Sybase has a definite leg up
>here)....but you gotta
>do what you gotta do.  Thanks again, group!!!!


--- Raemil Billings <Gene.R.Billings@JCI.COM> wrote:
> Try a Public Synonym.
>
> Here is the syntax.  Run it as the owner of the
> table.
>
> CREATE [PUBLIC]  SYNONYM [<user>.]<tablename> FOR
> [<owner>.]<tablename>
> [@database_link];
>
> For example
>
> CREATE PUBLIC SYNONYM usertable_x for
> fred.usertable_x;
>
> Hope this helps,
> Raemil Billings
>
> ====================================
> Raemil Billings
> Systems Engineer
> Johnson Controls
> 321-784-7796
>
>
>
>                     LRoux@SYR.EDU
>                     Sent by:             To:
> SQR-USERS@list.iex.net
>                     SQR-USERS@lis        cc:
>                     t.iex.net            Subject:
>  Running SQR on the Client not as owner
>
>
>                     02/20/2002
>                     01:23 PM
>                     Please
>                     respond to
>                     sqr-users
>
>
>
>
>
>
> We are currently converting from Sybase to Oracle.
>
> In Sybase we were able to alias an id to dbo in our
> Development Database so
> programmers could run SQRs off the desktop under
> specific IDs.  In Oracle
> we are having trouble creating IDs that the
> programmers can use to run SQRs
> off their desktop.  We do NOT want to give them the
> owner id/password, and
> they cannot customize all SQRs to include the
> owner.tablename.  Is there a
> method in Oracle to "alias" to another owner similar
> to what we did in
> Sybase?
>
>
> ******************************
> Larry Roux
> Syracuse University
> lroux@syr.edu
> *******************************


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com