Re: Analyze schema1.mytable from schema2 in a stored procedure

The key to the whole deal, as it turns out, being that the ANALYZE ANY privilege must be granted explicitly in order to work in a stored procedure. I tested this with a procedure for analyzing a table and not even sys had sufficient privileges. But once I granted 'analyze any' to a user they were able to run the procedure without complaint. Once again the 'explicit grant, not from a role' troll has appeared.

Regards,
Chris Gait

On 10 May 2001, at 8:35, Bill Pribyl wrote:

> 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: 
  INET: cjgait_at_earthlink.net

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).