Oracle PL/SQL Programming, 5th Edition

Recommendations for NDS

By now, you should have a solid understanding of how native dynamic SQL works in PL/SQL. This section covers some topics you should be aware of as you start to build production applications with this PL/SQL feature.

Use Invoker Rights for Shared Programs

I have created a number of useful generic programs in my presentation of NDS, including functions and procedures that do the following:

• Execute any DDL statement

• Return the count of rows in any table

• Return the count for each grouping by specified column

These are pretty darn useful utilities, and I want to let everyone on my development team use them. So I compile them into the COMMON schema and grant EXECUTE authority on the programs to PUBLIC.

However, there is a problem with this strategy. When Sandra connects to her SANDRA schema and executes this command:

SQL> EXEC COMMON.exec_DDL ('create table temp (x date)');

she will inadvertently create a table in the COMMON schema—unless I take advantage of the invoker rights model, which is described in detail in Chapter 24. The invoker rights model means that you define your stored programs so that they execute under the authority of and the privileges of the invoking schema rather than the defining schema (which is the default starting with Oracle8i Database and the only option prior to that release).

Fortunately, it’s easy to take advantage of this new feature. Here is a version of my exec_ddl procedure that executes any DDL statement, but always has an impact on the ...