User Level Security - Disable SQL*Plus and DDL Commands

Sqlplus_product_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.

Points to remember:

1) DBAs can disable certain SQL and SQL*Plus commands on a per user basis. 2) 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.
3) 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.
4) 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 system.Sqlplus_product_profile (product,userid,attribute,char_value,date_value)
values('SQL*Plus','INAM','SPOOL','DISABLED',NULL);


  Select Product, Userid, Attribute, Scope, Numeric_value, Char_value,  Date_value, Long_value
   From system.Sqlplus_product_profile

 SQL@ homedev> conn inam/inam@homedev
Connected.
SQL@ homedev> spool d:\testspool.txt
SP2-0544: Command "spool" disabled in Product User Profile
SQL@ homedev>
SQL@ homedev> 


The following are SQL and SQL*Plus commands that one can disable through the PRODUCT_USER_PROFILE. 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

Ref: 2181.1