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)
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',
INSERT INTO product_profile (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT',
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;