PRODUCT_USER_PROFILE Table

Various Oracle products use the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.

DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus--not Oracle--enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.

When SYSTEM, SYS, or a user authenticating with AS SYSDBA or AS SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.

The PUP table applies only to the local database. If accessing objects on a remote database via a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.

Creating the PUP Table

You can create the PUP table by running the command file named PUPBLD with the extension SQL as SYSTEM. The exact format of the file extension and the location of the file are system dependent. See the Oracle installation and user's manual(s) provided for your operating system or your DBA for more information.

Note: If the table is created incorrectly, all users other than privileged users will see a warning when connecting to Oracle that the PUP table information is not loaded.

PUP Table Structure

The PUP table has the following columns:

PRODUCT                 NOT NULL CHAR (30)
USERID                  CHAR(30)
ATTRIBUTE               CHAR(240)
SCOPE                   CHAR(240)
NUMERIC_VALUE           NUMBER(15,2)
CHAR_VALUE              CHAR(240)
DATE_VALUE              DATE
LONG_VALUE              LONG

Description and Use of PUP Columns

Refer to the following list for the descriptions and use of each column in the PUP table:

Product	Must contain the product name (in this case "SQL*PLUS"). You cannot enter wildcards or NULL in this column.
Userid	Must contain the username (in uppercase) of the user for whom you wish to disable the command. To disable the command for more than one user, use SQL wild cards (%) or make multiple entries. Thus, all of the following entries are valid: HR CLASS1 CLASS% (all users whose names start with CLASS) % (all users)
Attribute	Must contain the name (in uppercase) of the SQL, SQL*Plus, or PL/SQL command you wish to disable (for example, GET). If you are disabling a role, it must contain the character string "ROLES". You cannot enter a wildcard. See the section "Administration" later in this chapter for a list of SQL and SQL*Plus commands you can disable. See the section "Disabling Commands with SQLPLUS -RESTRICT" in this chapter for information on how to disable a role.
Scope	SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store specific file restrictions or other data in this column.
Numeric_Value	SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store numeric values in this column.
Char_Value	Must contain the character string "DISABLED" to disable a SQL, SQL*Plus, or PL/SQL command. If you are disabling a role, it must contain the name of the role you wish to disable. You cannot use a wildcard. See "Disabling Commands with SQLPLUS -RESTRICT" below for information on how to disable a role.
Date_Value	SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store DATE values in this column.
Long_Value	SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store LONG values in this column.

PUP Table Administration

The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle usernames should have only SELECT access to this table, which allows a view of restrictions of that username and those restrictions assigned to PUBLIC. The command file PUPBLD.SQL, when run, grants SELECT access on the PUP table to PUBLIC.