[EMAIL PROTECTED] wrote:
> I have two schemas: schema1 and schema2. I need to analyze
> schema1.mytable from a stored procedure owned by schema2. Schema1 has
> granted ALL on mytable to Schema2.
>From the Administrator's Guide: "To analyze a table, cluster, or index, you
must own the table, cluster, or index or have the ANALYZE ANY system
privilege." So your grant of ALL doesn't really help. Apparently, schema2 has
received ANALYZE ANY..
> Logged into SQLPLUS as Schema2, these
> both work:
>
> ANALYZE TABLE SCHEMA1.MYTABLE COMPUTE STATISTICS;.
> EXECUTE DBMS_DDL.ANALYZE_OBJECT
> ('TABLE','SCHEMA1','MYTABLE','COMPUTE');
>
> However, when schema2 executes a stored procedure with this line
>
> DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE')
>
> I get an insufficient privilege error.
By default, the stored procedure rights model is "owner's rights," in which
case privileges at run time are determined by the privileges of the owner of
the stored procedure. (Actually, the privileges are determined at compile
time, but if privs change, the procedure has to get recompiled before you run
it). In your case, the owner of the stored procedure would need to be able to
execute the procedure successfully in order for others who have execute
privilege on it to succeed. I don't know who owns your procedure, but imagine
it's schema3. Then this should work:
CONNECT system/manager
GRANT ANALYZE ANY TO schema3;
CONNECT schema3/passwd
CREATE PROCEDURE whatever
AS
BEGIN
DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE');
END;
/
GRANT EXECUTE ON whatever TO schema2;
...and schema2 should be good to go.
If the procedure was created with invoker's rights (AUTHID CURRENT_USER,
introduced in 8.1.6 as I recall), then at run time the privileges of the
invoker apply. (Generally, invoker's rights applies only to tables, but I
tested your case and it seems to apply here as well.) Since you said schema2
can run the commands from the command line, recompiling with invoker's rights
ought to work...
CONNECT system/manager
GRANT ANALYZE ANY TO schema2;
CONNECT schema3/passwd
CREATE PROCEDURE whatever
AUTHID CURRENT_USER
AS
BEGIN
DBMS_DDL.ANALYZE_OBJECT('TABLE','SCHEMA1','MYTABLE','COMPUTE');
END;
/
GRANT EXECUTE ON whatever TO schema2;
...again, schema2 should be good to go.
Good luck
Bill
______________________________________________
http://www.datacraft.com/ http://plnet.org/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pribyl
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
