Error Messages for No Privileges on Procedure

[aldovalerio]

I would like to pass on Oracle messages to users telling them that they don't have privileges to execute a procedure, but the messages seem very cryptic for users: "PLS-00201: identifier 'schema_name.procedure_name' must be declared". Isn't it possible to get a more descriptive message?

I've got 2 user roles, ROLE_USER and ROLE_READONLY_USER. I granted the former EXECUTE permission on all procedures, and the latter EXECUTE permission only on non-data-modifying procedures. I added the second role recently in an attempt to introduce application-level security without modifying my Visual Basic client app.

 

[D2Kinwindows]

First findout the basic Oracle Database Error Code for the above Forms Error. Then you have to write the following code in the ON-ERROR trigger in the form level.
/*

** Built-in: DBMS_ERROR_CODE,DBMS_ERROR_TEXT
** Example: Reword certain Forms error messages by evaluating
** the DBMS error code that caused them
** Trigger: On-Error
*/
DECLARE
errcode NUMBER := ERROR_CODE;
dbmserrcode NUMBER;
dbmserrtext VARCHAR2(200); A_id Alert;alert_button number;username varchar2(50);
BEGIN
dbmserrcode := DBMS_ERROR_CODE;
IF dbmserrcode = -1031 THEN
/*
** ORA-01031 is "Insufficient Privilages"
*/
select user into username from dual;
a_id := Find_Alert ('ERR');
Set_Alert_Property(a_id,Title,'TEMS (R) Message for '||username);
Set_Alert_Property(a_id,Alert_Message_Text,'You dont have sufficient privilages to do the intented operation. Contact administrator to get more privilages. Click NO when ask for SAVE CHANGES');
Alert_Button := Show_Alert(a_id);
elsif dbmserrcode = -942 THEN
select user into username from dual;
a_id := Find_Alert ('ERR');
Set_Alert_Property(a_id,Title,'TEMS (R) Message for '||username);
Set_Alert_Property(a_id,Alert_Message_Text,'You are trying to access information on which you dont have access.Contact administrator to get more privilages');
Alert_Button := Show_Alert(a_id);
end if;

END;

 

[aldovalerio]

The VB client doesn't have ON ERROR triggers in its forms. I ended up trapping the Oracle 01031 message in my VB app. and translating it to an "intelligible" message for users. Thanks all the same for your detailed reply.