SQL*Plus User's Guide and Reference

PRODUCT_USER_PROFILE Table

Various Oracle products use PRODUCT_USER_PROFILE, 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. (SET ROLE requires Oracle7.)

Overview

DBAs can use PRODUCT_USER_PROFILE 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 PRODUCT_USER_PROFILE when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to PRODUCT_USER_PROFILE will only take effect the next time the affected users log in to SQL*Plus.

Creating the Table

You can create PRODUCT_USER_PROFILE 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 SYSTEM will see a warning when connecting to Oracle that the PRODUCT_USER_PROFILE information is not loaded.

Table Structure

The PRODUCT_USER_PROFILE table consists of 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 Columns

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

Product Must contain the product name (in this case "SQL*Plus"). You cannot enter wildcards or NULL in this column. Also notice that the product name SQL*Plus must be specified in mixed case, as shown, in order to be recognized.
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:

SCOTT

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 "Administration" below for a list of SQL and SQL*Plus commands you can disable. See "Roles" below 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 "Roles" 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.

Administration

The DBA username SYSTEM owns and has all privileges on PRODUCT_USER_PROFILE. (When SYSTEM logs in, SQL*Plus does not read PRODUCT_USER_PROFILE. Therefore, no restrictions apply to user SYSTEM.) 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, when run, grants SELECT access on PRODUCT_USER_PROFILE to PUBLIC.

Disabling SQL*Plus, SQL, and PL/SQL Commands

To disable a SQL or SQL*Plus command for a given user, insert a row containing the user's username in the Userid column, the command name in the Attribute column, and DISABLED in the Char_Value column.

The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:

PRODUCT -------- USERID ------ ATTRIBUTE --------- SCOPE ----- NUMERIC VALUE ------- CHAR VALUE ------- DATE VALUE -------
SQL*Plus SCOTT HOST DISABLED
SQL*Plus % INSERT DISABLED
SQL*Plus % UPDATE DISABLED
SQL*Plus % DELETE DISABLED

To re-enable commands, delete the row containing the restriction.

You can disable the following SQL*Plus commands:

EDIT

EXECUTE

EXIT

HOST (or your operating system's alias for HOST, such as $ on VMS and ! on UNIX)

QUIT

SAVE

SET (see note below)

SPOOL

START

Note: Disabling the SQL*Plus SET command will also disable the SQL SET ROLE and SET TRANSACTION commands. Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

You can also disable the following SQL commands:

ALTER

ANALYZE (requires Oracle7)

AUDIT

CONNECT

CREATE

DELETE

DROP

GRANT

INSERT

LOCK

NOAUDIT

RENAME

REVOKE

SELECT

SET ROLE (requires Oracle7)

SET TRANSACTION

TRUNCATE (requires Oracle7)

UPDATE

VALIDATE (only for Oracle V6)

You can also disable the following PL/SQL commands:

BEGIN

DECLARE

Note: Disabling BEGIN and DECLARE does not prevent the use of the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.

Disabling SET ROLE

From SQL*Plus, users can submit any SQL command. In certain situations, this can cause security problems. Unless you take proper precautions, a user could use SET ROLE to access privileges obtained via an application role. With these privileges, they might issue SQL statements from SQL*Plus that could wrongly change database tables.

To prevent application users from accessing application roles in SQL*Plus, you can use PRODUCT_USER_PROFILE to disable the SET ROLE command. This allows a SQL*Plus user only those privileges associated with the roles enabled when they started SQL*Plus. For more information about the creation and usage of user roles, see your Oracle7 Server SQL Language Reference and Oracle7 Server Administrator's Guide.

Disabling Roles

To disable a role for a given user, insert a row in PRODUCT_USER_PROFILE containing the user's username in the Userid column, "ROLES" in the Attribute column, and the role name in the Char_Value column.

Note: When you enter "PUBLIC" or "%" for the Userid column, you disable the role for all users. You should only use "%" or "PUBLIC" for roles which are granted to "PUBLIC". If you try to disable a role that has not been granted to a user, none of the roles for that user are disabled.

The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:

PRODUCT -------- USERID ------ ATTRIBUTE --------- SCOPE ----- NUMERIC VALUE ------- CHAR VALUE ------- DATE VALUE -------
SQL*Plus SCOTT ROLES ROLE1
SQL*Plus PUBLIC ROLES ROLE2

During login, these table rows are translated into the command

SET ROLE ALL EXCEPT ROLE1, ROLE2

To ensure that the user does not use the SET ROLE command to change their roles after login, you can disable the SET ROLE command. See "Disabling SET ROLE" earlier in this appendix.

To re-enable roles, delete the row containing the restriction.

`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`

Product	Must contain the product name (in this case "SQLPlus"). You cannot enter wildcards or NULL in this column. Also notice that the product name SQLPlus must be specified in mixed case, as shown, in order to be recognized.
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:

Attribute	Must contain the name (in uppercase) of the SQL, SQLPlus, 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 "Administration" below for a list of SQL and SQLPlus commands you can disable. See "Roles" below 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 "Roles" 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.

`PRODUCT --------`	`USERID ------`	`ATTRIBUTE ---------`	`SCOPE -----`	`NUMERIC VALUE -------`	`CHAR VALUE -------`	`DATE VALUE -------`
`SQL*Plus`	`SCOTT`	`HOST`			`DISABLED`
`SQL*Plus`	`%`	`INSERT`			`DISABLED`
`SQL*Plus`	`%`	`UPDATE`			`DISABLED`
`SQL*Plus`	`%`	`DELETE`			`DISABLED`