Re: Newbie: Select in PL/SQL

"GREGORY KNESER" <gregoryk_at_futures.wharton.upenn.edu> wrote in message news:9rg9pa$7pl$3_at_netnews.upenn.edu...
> Hello,
>
> I'm trying to do a select on tables outside of the schema where the
> pl/sql procs are stored. All users in my DB have DBA role. When I write
> my select col1, col2 from other_schema.table_name I get an error
> "other_schema.table_name must be declared" If I create that exact same
> table in the schema where the proc is located and change the proc to
> select from the local table, it works just fine.
>
> Thanks for any help,
> Greg
>
> PS Sorry if this is a second post, my newsreader isn't working so hot
> tonight.

Sigh, no version again.

First of all: please make sure you *don't* give all your users the DBA role. This means *everyone* can *completely screw up* your database. You should investigate security a little bit more, and try to determine what privileges your users *really* need to have.

The issue you are having is because roles are disabled during compilation of stored procedures and functions, as roles are volatile. Pre-8i the only way to resolve this is either make sure as much as possible runs from one schema (when you grant execute to other users on your procedures that is already sufficient if all your tables are in one schema) or grant select privilege *directly* to the user running the procedure.

In 8i you can define a procedure with definers rights (the default) and invoker rights, which means it runs in the context of the user running the program.

This question has been asked many times, please try to peruse the various archives and FAQs before posting it another time.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Sun Oct 28 2001 - 09:07:05 CST