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 re-enable commands, delete the row containing the restriction.

You can disable the following SQL*Plus commands:

EXECUTE	START
RUN

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 A-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 with AS SYSDBA privileges.

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 with AS SYSDBA privileges and enter:

   
   DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'HR';