Oracle PL/SQL Programming, Third Edition

Building Applications with 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 new PL/SQL feature.

Sharing NDS Programs with Invoker Rights

I have created a number of useful generic programs in my presentation on 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.execDDL ('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 Chapter 20. 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 in Oracle 8.1 and the only option prior to Oracle 8.1).

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