Re: HELP: Stored Procedures

In <44ri2k$na5_at_news.ycc.yale.edu>, Jeffrey Escott <escottjf_at_maspo2.mas.yale.edu> writes:
>We have written the following stored procedure.
>-------------------------------------------------------------------------
>----
>-- CHANGE_PASSWORD
> -- Script that Creates Procedure, SEC_PROD.CHANGE_PASSWORD
>-- which:
>-- 1) Formats a Alter User Statement to Change the User's password
>-- 2) Executes the Alter User Statement by a call to
>-- SEC_PROD.SQL_DDL_PRC
>-------------------------------------------------------------------------
>----
> CREATE OR REPLACE PROCEDURE SEC_PROD.CHANGE_PASSWORD
> (v_user_id IN varchar2,
> v_password IN varchar2)
>IS
> v_alt_user_stmt VARCHAR2(2000) ;
> v_err_msg VARCHAR2(100) ;
>BEGIN
> v_alt_user_stmt := 'ALTER USER ' || v_user_id ||
> ' IDENTIFIED BY ' || v_password ;
> DBMS_OUTPUT.PUT_LINE('v_alt_user_stmt = ' || v_alt_user_stmt) ;
> SEC_PROD.SQL_DDL_PRC(v_alt_user_stmt) ;
>END CHANGE_PASSWORD ;
>/
>
>
> We then created a form in Oracle Forms 4.5 that calls
>CHANGE_PASSWORD. As SEC_PROD we have no problem compiling or executing
>this form. SEC_PROD can execute this procedure in both SQL Studio and in
>Forms4.5 runtime. SEC_PROD is also the owner of this procedure and owner
>of the SQL_DDL_PRC stored procedure. We then granted execute rights on
>CHANGE_PASSWORD to public. We then sign to SQL Studio on as a user (with
>no DBA rights) and can then execute CHANGE_PASSWORD and its call to
>SQL_DDL_PRC. As a user we can neither compile or execute this form.
> We know our procedure works because any user can execute it
>outside of the Forms4.5 environment. We have also tried granting users
>several different rights in the database, all of which had no affect. We
>have narrowed our problem down to the fact that in the Forms4.5
>environment (both designer and runtime) a user can not call or compile a
>procedure they do not own. We consistently get the error ORA-06550,
>which is a compilation error. Why is Forms4.5 always trying to compile
>the procedure? Why should this be if all users have execute rights over
>this procedure and it works outside of Forms4.5? Does anyone know why
>users who do not own a certain procedure, but have execute rights on it,
>can not execute it in Forms4.5, but can execute it outside of Forms4.5.
>
>We would appreciate any help or information on this problem.
>
>Jeff Escott (escottjf_at_maspo2.mas.yale.edu)
>Yale University

I have also had this problem. It seems that when referencing a procedure owned by another user, Forms 4.5 does not understand the normal naming convention: sec_prod.change_password(p1,p2). Specifically, it does not understand "sec_prod.change_password" portion. So you have to hide it.

To fix this problem you will have to:

1. Grant execute on the procedure
2. Create a public synonym for this stored procedure (note: your DBA will have to either grant create public synonym to user SEC_PROD or create the public synonym using a DBA account)
3. Change your Forms call to use the public synonym name instead of the owner.procedure name.

I think this is discussed in the Advanced Techniques guide.

Hope this helps.

M.Landa Received on Wed Oct 04 1995 - 00:00:00 CET