Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Using the Product User Profile

To use the product user profile, you must create it first. Oracle provides a script for this purpose. Once the product user profile table has been created, you need to know how to do three things:

• Restrict a user, or group of users, from using a specific command.

• Set a role so it will be disabled for a given user or group of users when SQL*Plus first connects.

• Report the restrictions currently in the profile table.

The next few sections show you how to perform each of these tasks.

Creating the Profile Table

Oracle supplies a script named pupbld.sql that creates the table, views, and synonyms shown in Figure 13-1 Figure 13-1. You can generally find the script at the following location:

$ORACLE_HOME/sqlplus/admin/pupbld.sql

You should execute pupbld.sql while logged in as user SYSTEM. Executing it while logged in as some other user will result in the profile table's being created in the wrong schema, and may result in a few privilege violations as the script creates public synonyms. The following example shows the script being executed:

SQL> @c:\orant\dbs\pupbld drop synonym product_user_profile * ERROR at line 1: ORA-01434: private synonym to be dropped does not exist date_value from product_user_profile * ERROR at line 3: ORA-00942: table or view does not exist drop table product_user_profile * ERROR at line 1: ORA-00942: table or view does not exist alter table product_profile add (long_value long) * ERROR at line 1: ORA-00942: table or view does not exist ...