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';

Related Topics
Consumer Groups
Profiles
Roles
Users

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved