Re: PL-SQL Privilege Problem?

Dave, as a senior DBA, I've been getting these kinds of problems / questions for years and the previous answers given are quite correct. They drive programmers crazy. (And sometimes in a cascading effect, the problems drive us crazy, no fault of the programmers.) As an anonymous block, the role privileges are inherited. Once stored as a procedure or function or package or whatever, then the actual privileges of the owner of the stored object pertain (i.e., directly granted ones to the user). Topping it off is the lack of "GRANT TRUNCATE to..." but that is a side issue.

Some hints I've found helpful: have a separate user created to own this stuff. Say you're doing, uh, I don't, inventory.

Have all the inventory-related tables, sequences, and other objects that contain data, and indices and so forth (everything that doesn't execute) owned by say, INVENTORY; have the executable stored things like stored procedures, functions, packages owned by somebody like INVLIB. Have INVLIB granted directly as a user the object privileges needed, or all possible, to it by the user INVENTORY and have INVLIB also be the owner for views and materialized views.

Then have a role created called INVUSER and GRANT EXECUTE to it for the executable objects INVLIB owns, as INVLIB and grant that as a role to your inventory users; or for finer granularity in security separate it into perhaps INV_QUERY, INV_UPDATE, INV_MGR and GRANT EXECUTE appropriately to those roles, and assign them to the end user ID's, as roles. (And GRANT SELECT for the views owned by INVLIB.)

Further, password the role and make it a non-default role, and embed the (Is it "ALTER SESSION set role? or set role, too lazy to look it up) in your app, or if you want to be really snooty, make the role and password table driven so your apps can look it up that way.

Why? To guard against ad-hoc query tools. That's why I don't like direct privilege grants to objects to users, or system privileges, either.

What you end up with is creating users with nothing more than CREATE SESSION, and a bunch of non-default, pass roles that only the apps can make active.

And a DBA can go and easily see with simple queries, what applications and level of privilege each Oracle username might have, instead of worrying about object-level grants on perhaps thousands of tables.

Hope this helps.

And don't forget MLS features if these aren't good enough!

RSH. "Dave Smith" <james.strickland_at_dfas.mil> wrote in message news:8d36c9e1.0204051332.63234a15_at_posting.google.com...
> I'm trying to use a view name in the from clause of a select statement
> of a cursor definition in a stored procedure and it's not working. I
> can do it in an anonymous block just fine but when I try to compile it
> to a stored procedure I get the dreaded "PLS-00201: identifier
> 'schema_owner.view1' must be declared" message. I'm using the same
> Oracle account when doing either the anonymous block or compile
> attempt. The view has a public synonym and select privilege granted to
> a role that my account is part of.
>
> Here is the anonymous block that works fine...
>
> set serveroutput on
> declare
> cursor lcur_test is
> select col1, col2, col3 from view1;
>
> lrec_test lcur_test%ROWTYPE;
>
> begin
> DBMS_OUTPUT.ENABLE(10000);
> open lcur_test;
>
> LOOP
> FETCH lcur_test INTO lrec_test;
> EXIT WHEN lcur_test%NOTFOUND;
> dbms_output.put_line('col1: ' || lrec_test.col1 || ' col2: ' ||
> lrec_test.col2);
> END LOOP;
>
> close lcur_test;
> end;
> /
>
> Here is the procedure that won't compile...
> create or replace procedure my_test as
> cursor lcur_test is
> select col1, col2, col3 from view1;
>
> lrec_test lcur_test%ROWTYPE;
>
> begin
> DBMS_OUTPUT.ENABLE(10000);
> open lcur_test;
>
> LOOP
> FETCH lcur_test INTO lrec_test;
> EXIT WHEN lcur_test%NOTFOUND;
> dbms_output.put_line('col1: ' || lrec_test.col1 || ' col2: ' ||
> lrec_test.col2);
> END LOOP;
>
> close lcur_test;
> end my_test;
>
> Now why would the anonymous block run fine but the procedure fail to
> compile? Any ideas? We are using Oracle 8.1.6.2.0. I'd sure appreciate
> some help on this!
>
> Dave
Received on Sat Apr 06 2002 - 10:31:03 CST