How to restrict user not to run any dml from sql prompt(backend),but allow user to run dml from application?

Today one dba  asked me -
How to restrict user not to run any dml from sql prompt(backend),but allow user to run dml from application?

-
I replyed him below-

If You meant to say that privileges user should not change the data through back end using SQLPLUS BUT the same user can insert update delete through application level entry.

Then you can use PRODUCT_USER_PROFILE table to disable certain SQL PL/SQL and SQL*Plus commands in the SQL*Plus environment on a per user basis except system user.
 This will never going to effect user's privileges at application level.

If this table is not installed then you can create PRODUCT_USER_PROFILE by running the command file named PUPBLD.sql.as SYSTEM USER. The exact format of the file extension and the location of the file are system dependent. Mostly this table are installed by default just check first.

These tables allow SQL*Plus to disable commands per user. The tables
are used only by SQL*Plus and do not affect other client tools that access the database.


example:- login in as System user

SQL> CONN SYSTEM/pwd
Connected.
SQL> insert into product_user_profile
(
PRODUCT ,
USERID ,
ATTRIBUTE ,
SCOPE ,
NUMERIC_VALUE,
CHAR_VALUE ,
DATE_VALUE ,
LONG_VALUE
)
values
('SQL*PLUS','SCOTT','INSERT',NULL,NULL,'DISABLED',NULL,NULL);

1. row created.

NOW CONNECT TO HR/HR

SQL> conn HR/HR
Connected.
SQL> insert into test_table values('testing');
SP2-0544: Command "insert" disabled in Product User Profile.

likewise you can add more entry for update,delete and even begin and end keyword of pl/sql command which totally disallowed using of pl/sql block.