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_VALUE Action to take DISABLED 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.