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
                                      VALUE        VALUE      VALUE
-------    ------  ---------  -----   --------     ------     -----
SQL*Plus   HR      HOST                            DISABLED
SQL*Plus   %       INSERT                          DISABLED
SQL*Plus   %       UPDATE                          DISABLED
SQL*Plus   %       DELETE                          DISABLED

To reenable commands, delete the row containing the restriction.

You can disable the following SQL*Plus commands:

EDIT	QUIT
EXECUTE	RUN
EXIT	SAVE
GET	SET (see note below)
HOST (or your operating system's alias for HOST, such as $ on VMS, and ! on UNIX)	SPOOL
	START

Note: Disabling the SQL*Plus SET command will also disable the SQL SET ROLE and SET TRANSACTION commands. Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

You can also disable the following SQL commands:

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

You can also disable the following PL/SQL commands:

BEGIN

DECLARE

Note: Disabling BEGIN and DECLARE does not prevent the use of the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.

Example E-1 Setting Restrictions in the PUP Table

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

1. Log in as SYSTEM with the command

   
   SQLPLUS SYSTEM/MANAGER
   

2. Insert a row into the PUP table with the command:

   
   INSERT INTO PRODUCT_USER_PROFILE
   VALUES ('SQL*PLUS', 'HR', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
   

3. Connect as HR/HR and try to SELECT something:

   
   CONNECT HR/HR; 
   SELECT * FROM EMP_DETAILS_VIEW; 
   
   

   This command causes the following error message:

   
   SP2-0544: INVALID COMMAND: SELECT 
   

4. 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 = 'HR';