Oracle Useful Information

Product User Profile Table

Updated : 17-January-1999
Version : 1.1

Description

This is a short description of how to use the product user profile table. This table allows you to control access of SQL and SQL*Plus commands to users. The main use of this table is to stop a user changing his/her access rights once in SQL*Plus by issuing a SET ROLE or ALTER command.

Install

Ensure that the product user profile object have been created. You will find the script in the $ORACLE_HOME/sqlplus product directory called v7pup.sql, pupbld.sql or similar. Sometimes it is in $ORACLE_HOME/dbs directory, it depends on which platform and version of Oracle you have installed.

The SYSTEM user is the only user which does not read this table and is therefore immune to it's contents.

Example

rem Disable CONNECT,HOST for user SCOTT

insert into product_profile values ('SQL*Plus','SCOTT','CONNECT',null,null,'DISABLED',null,null);
insert into product_profile values ('SQL*Plus','SCOTT','HOST',null,null,'DISABLED',null,null);

rem Ensure is anyone logs into SQL*Plus they only have read access and that they cannot access any application roles.

insert into product_profile values ('SQL*Plus','%','ALTER',null,null,'DISABLED',null,null);
insert into product_profile values ('SQL*Plus','%','SET ROLE',null,null,'DISABLED',null,null);
insert into product_profile values ('SQL*Plus','%','INSERT',null,null,'DISABLED',null,null);
insert into product_profile values ('SQL*Plus','%','UPDATE',null,null,'DISABLED',null,null);
insert into product_profile values ('SQL*Plus','%','DELETE',null,null,'DISABLED',null,null);

Enabling Commands

There are two ways to enable a command for a particular user. You can simply delete the appropriate rows out of the PRODUCT_PROFILE table, or update the CHAR_VALUE column to ENABLED.

You also have to bear in mind that the PRODUCT_PROFILE table is read once on entering SQL*PLUS so an updated PRODUCT_PROFILE table will not be read until the user exits from SQL*Plus and enters SQL*Plus again. e.g ...

Disable a command for SCOTT (e.g. HOST)
Connect as SCOTT, try SQL> host ls (assuming on a UNIX system) host:invalid command
In another session connect as SYSTEM, update CHAR_VALUE to ENABLED
As SCOTT, try SQL> host ls, command still disabled.
Exit SQL*plus, enter as scott again SQL> host ls should give you a directory listing.

Additional Notes

Wildcards can be used in the username (e.g. B% could be used to disable access for usernames starting with 'B')

You can disable the following SQL*Plus commands

EDIT
EXECUTE
EXIT
GET
HOST (or your operating system's alias for HOST, such as $ on VMS and ! on UNIX)
QUIT
RUN
SAVE
SET
SPOOL
START

You can also disable the following SQL commands:

ALTER
ANALYZE (Oracle7)
AUDIT
CONNECT
CREATE
DELETE
DROP
GRANT
INSERT
LOCK
NOAUDIT
RENAME
REVOKE
SELECT
SET ROLE (Oracle7)
SET TRANSACTION
TRUNCATE (Oracle7)
UPDATE
VALIDATE (only for Oracle V6)

You can also disable the following PL/SQL commands:

BEGIN
DECLARE

Return to Index of SQL Scripts