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

    Restrict user to select data from plssql Developer.

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

      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

          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

            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

              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
                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.