Oracle SQL∗Plus – The Definitive Guide

Oracle SQL*Plus: The Definitive Guide was a book of two firsts for me: it was the first book I wrote entirely on my own, and it was my first O’Reilly book. I really can’t say enough good things about O’Reilly as a publisher. I promised them this book in six months, and it took me 11 months to deliver it. I was five months late! Through it all, my editor stuck by me and never once threatened to cancel the project or bring in a coauthor. She just let me write at my own pace. Both of us are very pleased with the results.

Ironically, this book has its origins in some poor design work done by inexperienced analysts. While working on a client project, I was assigned the task of generating some “simple” SQL*Plus scripts to perform a series of yearly batch processes. The analysts had thrown together some quick-and-dirty specs, and hadn’t considered how an end user was to run the scripts that I wrote, nor had they fully realized that the database design required that the batch processes be recursive in nature. Let me tell you, recursion is most definitely not a SQL*Plus strong point! In the end, I implemented the heavy-duty lifting, including the recursion, in PL/SQL. The front-end, including menus and some reporting functionality, was done using SQL*Plus. To make it easy for users to run the batch processes, I created a Windows desktop icon that would invoke SQL*Plus to run my top-level menu script.

Those “simple” SQL*Plus scripts actually turned out to be some very complex SQL*Plus scripts that interacted with an even more complex PL/SQL package. Many of the techniques in my book, especially those for branching, looping, and working with user and bind variables, were learned during my work on that one project.

I’m always interested in feedback on the book, suggestions for revisions, new SQL*Plus tips, and so forth. Feel free to contact me at jonathan@gennick.com. I also maintain a corrections page and an errata page on my website.

Jonathan Gennick is an O’Reilly & Associates editor specializing in database and programming titles. Prior to joining O’Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital’s Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it’s set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O’Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O’Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O’Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O’Reilly & Associates, 2002) and editing Practical PostgreSQL (O’Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at jonathan@gennick.com, and you can learn more about him personally by visiting his website at http://gennick.com.

Chapter 9 – The Product User Profile

In this chapter:
What Is the Product User Profile?
Using the Product User Profile

In addition to the standard database security Oracle provides and enforces for all database objects — tables, views, and the like — Oracle also provides an application security scheme for SQL*Plus. This allows you to control the specific commands a SQL*Plus user is allowed to execute. At the core of the SQL*Plus application security scheme is the product user profile.

What Is the Product User Profile?
The product user profile is an Oracle table, owned by the SYSTEM user, that contains a list of SQL*Plus command restrictions by user. The table may contain role restrictions as well. The name of this table used to be PRODUCT_USER_PROFILE. Now it is just PRODUCT_PROFILE, but a synonym named PRODUCT_USER_PROFILE exists to ensure backwards compatibility.

Why Does the Product User Profile Exist?
Primarily, the product user profile enables you to give end users access to SQL*Plus for reporting and ad-hoc query purposes, yet restrict them from using SQL*Plus commands such as INSERT, DELETE, etc., that might damage production data.

Real-world applications typically implement a large number of business rules, edit checks, and even security at the application level rather than within the database. Modifying the data using an ad-hoc tool, such as SQL*Plus, bypasses the rules and puts data integrity at risk. Because of this, it’s usually important to ensure that data is modified through the application, where the rules can be enforced.

If you give people an application that requires a database username and password, and those people also have access to SQL*Plus, it won’t be too long before some curious and adventurous soul will figure out that the same userid and password that works for the application will also work for SQL*Plus. Next thing you know, you will have someone running ad-hoc queries that haven’t been tuned, or, worse yet, you may have someone issuing ad-hoc INSERT, UPDATE, or DELETE commands. The product user profile allows you to defend against this risk.

The PRODUCT_PROFILE Table
The PRODUCT_PROFILE table is owned by SYSTEM

Most users will not have SELECT access on the table itself, so if you aren’t logged in as SYSTEM, you may not be able to DESCRIBE the table. Instead, you should have access to a view on the table named PRODUCT_PRIVS. This view returns all the records from the PRODUCT_PROFILE table that apply to the currently logged-on user — you. Figure 9-1 shows the table, the view, the synonyms that normally exist, and the relationship between them.