Disabling a Command for a User

Let’s say we have an instance where in there are 100 tables in a schema and that Schema Owner is not supposed to use delete on any of those tables.

Then instead of getting into the mess of Grants / revokes, we can use one of the facilities that are provided by Oracle.

There is a table called as product_profile and it can be created (if not present) by executing the PUBBLD.SQL

The PRODUCT_PROFILE table is owned by SYSTEM and has the following structure:

Name         Null?       Type
------------------------------------

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

To disable a command for a user, insert a row into the PRODUCT_PROFILE table. You should normally log in as SYSTEM, and your INSERT statement should look like this:

INSERT INTO product_profile (product, userid, attribute, char_value) VALUES ('SQL*Plus','USERNAME','COMMAND_NAME','DISABLED');

Example:

INSERT INTO product_profile (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT','DELETE','DISABLED');

Now, SCOTT cannot use the DELETE command on any table. IF you want to allow him to use the delete command then, simply login as system again and drop the record from product_profile.

Example: DELETE FROM product_profile;