Oracle SQL*Plus Security and Product User Profiles

Oracle Product User Profile Version 11.2.0.3

Create Profile - Disable Commands

Source

-- must be run as SYSTEM: Not as SYS

{$ORACLE_HOME}\sqlplus\admin\pupbld.sql

Data Dictionary Objects

PRODUCT_PROFILE

PRODUCT_USER_PROFILE

SQL*Plus commands that can be disabled

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

SQL commands that can be disabled

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

Create Product User Profile

To disable 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.

INSERT INTO product_user_profile
VALUES
(<product_name>, <schema_name>, <SQL Command>, NULL, NULL,
'DISABLED', NULL, NULL);

INSERT INTO system.product_user_profile
(product, userid, attribute, scope, numeric_value, char_value, date_value, long_value)
VALUES
('SQL*Plus', 'UWCLASS', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);

COMMIT;

To disable a role for all users

When you enter "PUBLIC" or "%" for the USERID column, you disable the role for all users. During login, these table rows are translated into the command:

SET ROLE ALL EXCEPT ROLE1, ROLE2

INSERT INTO system.product_user_profile
(product,userid, ...)
VALUES
('SQL*PLUS', PUBLIC, ...)

or

('SQL*PLUS', '%', ...)

COMMIT;

Disable Host Command

To prevent shelling out to the operating system

INSERT INTO system.product_user_profile
(product,userid,attribute,scope,numeric_value,char_value)
VALUES
('SQL*Plus', '%', 'HOST', NULL, NULL, 'DISABLED');

COMMIT;

Drop Profile - Re-enable Commands

To re-enable commands

Delete the row containing the restriction

DELETE FROM product_user_profile WHERE userid = 'UWCLASS';