Disabling Commands Using Product_user_profile Table

Disabling Commands Using the Table: PRODUCT_USER_PROFILE by Jeff Hunter, Sr. Database Administrator Introduction This article explains how to use product and user level security by disabling and reenabling commands such as SPOOL or ALTER using the table PRODUCT_USER_PROFILE. PRODUCT_USER_PROFILE A table that resides in the SYSTEM account. It provides product level security that supplements the user level security provided by SQL commands GRANT and REVOKE, and can be used with one's own applications as well as with other ORACLE products. The table is created by running the command file PUPBLD.SQL under the schema SYSTEM. NOTE: • •

•

•

DBAs can disable certain SQL and SQL*Plus commands on a per user basis. Restrictions are applied from PRODUCT_USER_PROFILE when a user logs onto SQL*Plus and maintains those restrictions for the session. Changes made to PRODUCT_USER_PROFILE will take effect the next time the user logs onto SQL*Plus. If one restricts a user's privileges through Oracle's GRANT and REVOKE commands, but do not restrict those privileges in PRODUCT_USER_PROFILE, the restrictions still apply. If one denies a user access to commands through PRODUCT_USER_PROFILE, but do not place any restrictions with GRANT or REVOKE, access is still denied.

Usage To disable a SQL or SQL*Plus commands for a given user, insert a row in the PRODUCT_USER_PROFILE tables. User names can be appended to with the wildcard '%'. For example 'SC%', which will then disable the relevant command for all users whose name starts with 'SC'. NOTE: The userid, attribute and char_value must be in uppercase. For Example: INSERT INTO product_user_profile (product,userid,attribute,char_value,date_value)

VALUES('SQL*Plus','JHUNTER','SPOOL','DISABLED',NULL); PRODUCT -----------------SQL*PLUS SQL*PLUS

NUMERIC CHAR DATE USERID ATTRIBUTE SCOPE VALUE VALUE VALUE -------- ---------- ---------- ------- ---------SCOTT JHUNTER

HOST SPOOL

DISABLED DISABLED

If for example user JHUNTER attempts a SPOOL, she would receive this error: SP2-0544: invalid command: spool

To re-enable commands, delete the row containing the restriction SQL and SQL*Plus Commands The following are SQL and SQL*Plus commands that one can disable through the PRODUCT_USER_PROFILE table. For a full list please check your SQL*Plus User's Guide. SQL*Plus: CONNECT EDIT EXIT GET HOST (or your operating system's alias for HOST) QUIT RUN SAVE SPOOL START

SQL:

ALTER AUDIT CREATE DELETE DROP GRANT INSERT LOCK NOAUDIT RENAME REVOKE SELECT UPDATE VALIDATE

PL/SQL:

BEGIN DECLARE