| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Analyze schema1.mytable from schema2 in a stored procedure
Sherrie.Kubis_at_swfwmd.state.fl.us 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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pribyl INET: bill_at_datacraft.com 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: ListGuru_at_fatcity.com (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).Received on Thu May 10 2001 - 11:16:19 CDT
 |
 |