2.2.4 Managing Grants and Synonyms for Stored Programs

When you first create a PL/SQL program, normally no one but you or the database administrator can execute it. To give another user the authority to execute your program, issue a GRANT statement:

GRANT EXECUTE ON wordcount TO scott;

To remove the privilege, use REVOKE:

REVOKE EXECUTE ON wordcount FROM scott;

You could also grant the EXECUTE privilege to a role:

GRANT EXECUTE ON wordcount TO all_mis;

Or, if appropriate, you could allow any Oracle user on the current database to run the program:

GRANT EXECUTE ON wordcount TO PUBLIC;

If you grant a privilege to an individual like Scott, and to a role of which the user is a member, and later grant it toPUBLIC, Oracle remembers all three grants. Any one of the grants is sufficient to permit the individual to run the program, so if you ever decide you don't want Scott to run it, you must revoke the privilege from Scott, and revoke it from PUBLIC, and finally revoke it from the role (or revoke the role from Scott).

To view a list of privileges you have granted to other users and roles, you can query the USER_TAB_PRIVS_MADE data dictionary view. Somewhat nonintuitively, PL/SQL program names appear in the table_name column:

SQL> SELECT table_name, grantee, privilege

2 FROM USER_TAB_PRIVS_MADE

3 WHERE table_name = 'WORDCOUNT';

TABLE_NAME GRANTEE PRIVILEGE

------------------------------ ------------------------------ -----------

WORDCOUNT PUBLIC EXECUTE

WORDCOUNT SCOTT EXECUTE

WORDCOUNT MIS_ALL EXECUTE

When Scott does have the EXECUTE privilege on wordcount, he will probably want to create a synonym for the program to avoid having to prefix it with the name of the schema that owns it:

SQL> CONNECT scott/tiger

Connected.

SQL> CREATE OR REPLACE SYNONYM wordcount FOR bill.wordcount;

Now he can execute the program in his programs by referring only to the synonym:

IF wordcount(localvariable) > 100 THEN...

This is a good thing, because if the owner changes, only the synonym (and not any stored program) needs modification.

It's possible to create a synonym for a procedure, function, package, or—in the most recent Oracle versions—user-defined type. Synonyms forprocedures, functions, or packages can hide not only the schema but also the actual database; you can create a synonym for remote programs as easily as local programs. However, a synonym for a procedure or function inside a package or type will not work.

Removing a synonym is easy:

DROP SYNONYM wordcount;

2.2.5 Dropping a Stored Program

If you really, truly don't need a particular stored program any more, you can drop it using SQL's DROP statement:

DROP FUNCTION wordcount;

You candrop packages and types in their entirety:

DROP PACKAGE pkgname;

DROP TYPE typename;

Or you can drop only the body without invalidating the corresponding specification:

DROP PACKAGE BODY pkgname;

DROP TYPE BODY typename;

Any time you drop a program that other programs call, the callers will be marked invalid.