HI, I want to restrict user to select data like "select * from emp" from pls/sql Developer but user can select,insert,update,delete data from oracle forms. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi Faizan.
Not enough information.
As Ramin says there's ways to get environment information, but that can be spoofed easily enough.
If your "oracle forms" is a forms application, then does that application connect to the database as a particular database user. If so, you can provide the users with a different database user to use for connecting from 'other tools' which has a more restricted access, without giving them the connection details that forms is using.
The solution depends on the problem, but the problem is not clearly defined; all you have told us is what you're wanting to try and do to resolve this unknown problem.
Requirement : User is allowed to do following
Oracle forms --> allowed to do all DML
SQL/DEVELOPER --> select
Online user doesn't own objects. So for online users are having permission via role or direct permission for all DML of all objects. This permission has to be consistent else if there might be some abnormal behaviour to access objects. Example: if plsql developer logins - role is changed so that it will have only select privilege then what will happen when forms user will login - conflicting each other.
1. create a new role - READ_ONLY
2. create a new user for plsql developer access
3. grant the new role to the new user
4. introduce restriction for forms users to access db by using plsql developer by introducing logon trigger like below
create or replace trigger logon_db after logon on database
SELECT sys_context('USERENV', 'SESSION_USER') into v_user FROM DUAL;
SELECT sys_context('USERENV', 'MODULE') ino v_module FROM DUAL;
select GRANTED_ROLE into v_user_role from user_roles where username= v_user;
if upper(v_module) = 'PL/SQL Developer' and v_user_role='READ_ONLY' then
(-20001,'TOAD Access for non DBA users restricted',true);
You need to make above code operational.