Oracle Built-in Packages

Getting Started with DBMS_SQL

Before you start using DBMS_SQL, you need to make sure that it is installed and that the appropriate users have access to this package. In addition, you should be aware of how privileges are applied to programs that execute dynamic SQL.

Creating the DBMS_SQL Package

The DBMS_SQL package is created when the Oracle database is installed. The dbmssql.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_SQL for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

Given the power, flexibility, and potential impact of dynamic SQL, you may actually want to revoke public access to DBMS_SQL and instead grant EXECUTE privilege to only those users who need to perform dynamic SQL.

To “hide” DBMS_SQL, issue this command from the SYS account:

REVOKE EXECUTE ON DBMS_SQL FROM PUBLIC;

To grant EXECUTE privilege to a specific user, issue this command from SYS:

GRANT EXECUTE ON DBMS_SQL TO whatever_user;

Security and Privilege Issues

Generally, when you run stored code (and all DBMS_* built-in packages are certainly stored in the database!), that code executes under the authority and using the privileges associated with the owner of the code. If this rule were applied ...