Disable SQL and SQL*Plus commands in the SQL*Plus Environment

Disable certain SQL and SQL*Plus commands in the SQL*Plus Environment
=====================================================================

SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.

DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.
When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.

The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.


PUP (SQLPLUS_PRODUCT_PROFILE) Table Script Found:
============================================================

The PUP tables script found in following path:
G:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql

Connect as a system user to run above script:

Pup Table name :- SQLPLUS_PRODUCT_PROFILE

Pup table’s column:-

PRODUCT VARCHAR2 (30) NOT NULL,
USERID VARCHAR2 (30),
ATTRIBUTE VARCHAR2 (240),
SCOPE VARCHAR2 (240),
NUMERIC_VALUE DECIMAL (15,2),
CHAR_VALUE VARCHAR2 (240),
DATE_VALUE DATE,
LONG_VALUE LONG


PUP Table Administration
========================================

The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle Database usernames should have only SELECT access to this table, which enables a view of restrictions for that username and those restrictions assigned to PUBLIC. The script PUPBLD.SQL, when run, grants SELECT access on the PUP table to PUBLIC.

Disabling SQL*Plus, SQL, and PL/SQL 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. The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:

PRODUCT USERID ATTRIBUTE SCOPE NUMBERIC CHAR DATE LONG
VALUE VALUE VALUE VALUE
------- ------ --------- ----- -------- ------ ----- ---
SQL*Plus HR HOST DISABLED
SQL*Plus % INSERT DISABLED
SQL*Plus % UPDATE DISABLED
SQL*Plus % DELETE DISABLED

Note: [To re-enable commands, delete the row containing the restriction.]

================================
A Example of PASSWORD command:
================================

This is an example of how to insert a row into the PUP table to restrict the user SCOTT from using the PASSWORD statement:

1. Log in as SYSTEM with the command

2. SQLPLUS SYSTEM/your_password


3. Insert a row into the PUP table with the command:
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*Plus', 'SCOTT', ' PASSWORD', NULL, NULL, 'DISABLED', NULL, NULL);
4. Connect as SCOTT and try to change by command PASSWORD something:

CONNECT SCOTT/your_password;

SQL> PASSWORD

• This command causes the following error message:
SP2-0544: Command SELECT disabled in Product User Profile

5. To delete this row and remove the restriction from the user HR, CONNECT again as SYSTEM and enter:

DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'SCOTT';


Additional Information
======================

SQL*Plus Commands That Can Be Disabled
---------------------------------------
ACCEPT DEFINE PASSWORD SHUTDOWN
APPEND DEL PAUSE SPOOL
ARCHIVE LOG DESCRIBE PRINT START(@, @@)
ATTRIBUTE DISCONNECT PROMPT STARTUP
BREAK EDIT RECOVER STORE
BTITLE EXECUTE REMARK TIMING
CHANGE EXIT/QUIT REPFOOTER TTITLE
CLEAR GET REPHEADER UNDEFINE
COLUMN HELP (?) RUN VARIABLE
COMPUTE HOST SAVE WHENEVER OSERROR
CONNECT INPUT SET WHENEVER SQLERROR
COPY LIST (;) SHOW


SQL Commands That Can Be Disabled
----------------------------------

ALTER DELETE MERGE SET CONSTRAINTS
ANALYZE DISASSOCIATE NOAUDIT SET ROLE
ASSOCIATE DROP PURGE SET TRANSACTION
AUDIT EXPLAIN RENAME TRUNCATE
CALL FLASHBACK REVOKE UPDATE
COMMENT GRANT ROLLBACK VALIDATE
COMMIT INSERT SAVEPOINT na
CREATE LOCK SELECT na

You can disable the following PL/SQL commands:
--------------------------------------------
BEGIN DECLARE na na


Notes:
===============
•Disabling HOST disables the operating system alias for HOST, such as $ on Windows, and ! on UNIX.
•Disabling LIST disables ; and numbers (numbers entered to go to that line in a script).
•You must disable HELP and ? separately to disable access to command-line help.
•Disabling the SQL*Plus SET command also disables SQL SET CONSTRAINTS, SET ROLE and SET TRANSACTION.
•Disabling SQL*Plus START also disables @ and @@.
•Disabling BEGIN and DECLARE does not prevent the use of SQL*Plus EXECUTE to run PL/SQL. EXECUTE must be disabled separately.
•Disabling EXIT/QUIT is not recommended. If disabled, terminate a command-line session by sending an EOF character such as Ctrl+D in UNIX or Ctrl+Z in Windows. Terminate a Windows GUI session with File > Exit. Otherwise, terminate a session by terminating the SQL*Plus process. If disabled, using EXIT/QUIT to terminate the currently running script in iSQL*Plus is also disabled. If disabled, the EXIT operation in WHENEVER OSERROR and WHENEVER SQLERROR is also disabled.