4 Replies Latest reply: Jan 11, 2014 7:11 PM by ABOracle RSS

Restrict user to select data from plssql Developer.

a84b7ae4-6e15-48f1-bb6e-7446903a8ebd Newbie
Currently Being Moderated

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.

  • 1. Re: Restrict user to select data from plssql Developer.
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    You can put trigger on insert/update/delete on table emp and check condition

     

    SQL> select sys_context('USERENV', 'MODULE')  s from dual;

    S

    --------------------------------------------------------------------------------

    PL/SQL Developer

     

     

    SQL>

     

    but it is not 100% safety.

     

    -----

    Ramin Hashimzade

  • 2. Re: Restrict user to select data from plssql Developer.
    BluShadow Guru Moderator
    Currently Being Moderated

    a84b7ae4-6e15-48f1-bb6e-7446903a8ebd wrote:

     

    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.

  • 3. Re: Restrict user to select data from plssql Developer.
    Vijetha Explorer
    Currently Being Moderated

    You have to create a role and grant options depending on the user.

    I'm definitely not sure, but this might be a solution for what you are looking for.

  • 4. Re: Restrict user to select data from plssql Developer.
    ABOracle Explorer
    Currently Being Moderated
    Restrict user to select data from plssql Developer.

     

    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.

     

    Solution :

     

    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
      declare
    v_module varchar2(30);

      v_user varchar2(30);

      v_role_name varchar2(32);
      begin


    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

      dbms_output.put_line('ok');

      end if
      raise_application_error
      (-20001,'TOAD Access for non DBA users restricted',true);
      end if;
      end;
    /

    You need to make above code operational.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points