4-13. Creating a Public Name for a Stored Program
Problem
You want to allow for any schema to have the ability to reference a particular stored program that is contained within your schema. For instance, the CALC_EMPLOYEE_PAYCHECK procedure should be executable for any of the administrative users of the database. You want these users to have the ability to simply call the procedure rather than preceding the procedure name with the schema using the dot notation.
Solution
Create a public synonym for the function, procedure, or package. This will allow any user that has EXECUTE privileges on the stored program to call it without specifying the schema name first. Instead, the invoker need only reference the synonym.
In the following example, the user AdminUser does not have direct access to the CALC_EMPLOYEE_PAYCHECK procedure, so they must fully qualify the name of the package using the schema name for which the procedure resides.
SQL> exec application_account.calc_employee_paycheck(200);
Calculating paycheck with taxes
The paycheck total for Whalen is 5200.8
PL/SQL procedure successfully completed.
Next, the database administrator will create a public synonym for the procedure:
SQL> CREATE PUBLIC SYNONYM calc_employee_paycheck
FOR application_user.calc_employee_paycheck;
Now any user with execute privileges on the procedure can invoke it without fully qualifying the name since a public synonym named CALC_EMPLOYEE_PAYCHECK has been created. This is demonstrated in the next lines of code. Again, the user AdminUser is now logged into the system and executes the procedure.
SQL> exec calc_employee_paycheck(206);
Calculating paycheck with taxes
The paycheck total for Gietz is 6640.8
PL/SQL procedure successfully completed.
As you can see, the procedure name no longer requires the schema name to fully qualify it before being invoked.
How It Works
Creating public synonyms is a useful technique for allowing any user to have access to a stored piece of code without knowing which schema the code belongs to. Any user who has EXECUTE privileges on the code can invoke it without fully qualifying the name. Instead, the invoker specifies the synonym name.
An account must be granted the CREATE PUBLIC SYNONYM privilege in order to create a public synonym. It's actually common for database administrators to take care of creating such synonyms.
To create a synonym, execute the following statement, replacing the PUB_SYNONYM_NAME identifier with the name of your choice and replacing SCHEMA.STORED_PROGRAM with the schema name and program that you want to make publically accessible:
CREATE PUBLIC SYNONYM pub_synonym_name FOR schema.stored_program;
The public synonym name does not have to be the same as the actual stored program name, but it is conventional to keep them the same, and it makes things consistent and the names easier to remember. If you begin to have synonym names that differ from the actual program names, then confusion will eventually set in.
Note Creating a synonym does not give execute access. Creating a public synonym provides only a global name that avoids the need for dot notation. Invokers of a procedure or function still must be granted EXECUTE access, as shown in Recipe 4-11.