1.11 How do I…Enable product user profiles?

Problem

Whenever a user other than SYS or SYSTEM starts SQL*Plus, the Oracle8 server reports that product user profile information is not loaded. How do I eliminate this bothersome message? Also, I’d like to prevent some users from issuing certain SQL*Plus and SQL commands. How do I accomplish this using product user profiles?

Technique

Eliminate the SQL*Plus startup error message by running the Oracle-supplied script called PUPBLD.SQL. In the Windows NT environment, the default location of this script is the \ORANT\DBS subdirectory. Use standard INSERT statements to populate the PRODUCT_USER_PROFILE table and limit user access to various SQL and SQL*Plus commands.

Figure 1.25 shows the structure of the PRODUCT_USER_PROFILE table. Table 1.3 shows the columns in this table that must be populated to impose SQL*Plus and SQL command limits. Tables 1.4 and 1.5 list the SQL*Plus and SQL commands, respectively, which the PRODUCT_USER_PROFILES table can disable.

Table 1.3 PRODUCT_USER_PROFILE columns used to disable SQL*Plus and SQL commands

Column Name Description Column Value
PRODUCT Product name SQL*Plus
USERID User name (uppercase)  
ATTRIBUTE Name of command to disable (See Table 1.4) 
CHAR_VALUEAction to takeDISABLED

Table 1.4 SQL*Plus commands that can be disabled in PRODUCT_USER_PROFILE

COPY EXIT QUIT SAVE START
EDIT GET PASSWORD SET
EXECUTE HOST RUN SPOOL

Table 1.5 SQL commands that can be disabled in PRODUCT_USER_PROFILE

ALTER DELETE NOAUDIT SET TRANSACTION
ANALYZE DROP RENAME TRUNCATE
AUDIT GRANT REVOKE UPDATE
CONNECT INSERT SELECT BEGIN
CREATE LOCK SET ROLE DECLARE

Steps

1. Run SQL*Plus and connect as the SYSTEM user account. Use the START command to load and execute the PUPBLD.SQL script.

2. While connected as SYSTEM, use an INSERT command to populate the PRODUCT_USER_PROFILE table with the appropriate values from Tables 1.3, 1.4, and 1.5. The script in CHP1_8.SQL that appears next, for example, will prevent the WAITE user from creating any objects through the SQL*Plus interface.

INSERT INTO product_user_profile

(product,userid, attribute, char_value)

VALUES (‘SQL*Plus’, ‘WAITE’, ‘CREATE’, ‘DISABLED’);

3. Run SQL*Plus, connect as the WAITE user, and attempt to create any object. Now that product user profiles are enabled and the CREATE command is expressly disabled for the WAITE account, SQL*Plus will answer the CREATE command with the error message INVALID COMMAND; CREATE.

4. Run SQL*Plus, connect as the SYSTEM user, and use the START command to load and execute CHP1_9.SQL. This deletes any product user profile limitations established for the WAITE user so that scripts later in the book will not generate errors.

How It Works

PRODUCT_USER_PROFILE is actually a synonym. If the user is SYSTEM, this synonym resolves to the SQLPLUS_PRODUCT_PROFILE table. If the user is not SYSTEM, the synonym resolves to a view that queries the SQLPLUS_ PRODUCT_PROFILE table. In Step 1, PUPBLD.SQL creates these views as well as the SQLPLUS_PRODUCT_PROFILE table that stores the product user profile data. Step 2 populates this table, and Step 3 displays the error message that Oracle8 displays when a user attempts to issue a disabled SQL*Plus command.

Comments

SQL*Plus reads only product profile information when a user starts the utility. Product user profile changes will not take effect for active SQL*Plus users until they terminate SQL*Plus and restart it. The contents of the PRODUCT_USER_PROFILE table don’t affect the SYSTEM user’s interaction with SQL*Plus; SQL*Plus does not read this table when SYSTEM connects.DBAs should fully document product user profiles and use them with caution. It can be difficult to trace the cause of the INVALID COMMAND SQL*Plus error if the DBA enables and forgets product user profile limits.