Re: Limit UPDATE statements to affect only one row

From: bernard (bernard_at_bosvark.com) <bernard_at_bosvark.com>
Date: 11 Aug 2006 07:57:07 -0700
Message-ID: <1155308227.211113.96430@b28g2000cwb.googlegroups.com>

Maxim Demenko wrote:
> May be not too elegant, but seems to work.

Nice one mate! That is exactly what I was looking for. As the Oracle gurus always say: "There are many ways to solve a problem in Oracle". After careful review of my issue I realised that this is a security issue so I should use a security hammer to drive the nail home. I got an alternative solution using VPD, see below.

I will benchmark both solutions and see which is fastest with least amount of latches. I suspect the trigger will be since it will stop execution after it touched 2 rows, but latching might be slightly higher due to contention on the package counter.

Anyway, thank you all for your help.

Regards
Bernard

And the VPD solution is:

create or replace function vpdpolicy3_fnc(p_schema in varchar2,

                                          p_object in varchar2)

  return varchar2 is

• This function will manage the amount of rows affected on an update on the EMP tables to
• prevent bulk updates result varchar2(100); v_current_sql varchar2(4000); v_where_str varchar2(4000); v_test_sql varchar2(4000); n_tab DBMS_SQL.NUMBER_TABLE; indx NUMBER := 1; i_where_pos number; c number; r number; n number; bulk_updata_not_allowed exception; begin
• null; v_current_sql := nvl(sys_context('userenv', 'current_sql'), '') || nvl(sys_context('userenv', 'current_sql1'), '') || nvl(sys_context('userenv', 'current_sql2'), '') || nvl(sys_context('userenv', 'current_sql3'), '') || nvl(sys_context('userenv', 'current_sql4'), '') || nvl(sys_context('userenv', 'current_sql5'), '') || nvl(sys_context('userenv', 'current_sql6'), '') || nvl(sys_context('userenv', 'current_sql7'), ''); i_where_pos := instr(upper(v_current_sql), 'WHERE');

  --dbms_output.put_line('DEBUG ------------------------------');

  --dbms_output.put_line('DEBUG v_current_sql: ' || v_current_sql);   --dbms_output.put_line('DEBUG i_where_pos: ' || i_where_pos);

  if i_where_pos <> 0 then
    v_where_str := substr(v_current_sql, i_where_pos);     --dbms_output.put_line('DEBUG v_where_str: ' || v_where_str);

    v_test_sql := 'select count(*) total from emp ' || v_where_str;     --dbms_output.put_line('DEBUG v_test_sql : ' || v_test_sql);

    c := dbms_sql.open_cursor;
    --dbms_output.put_line('DEBUG c : ' || c);

    dbms_sql.parse (c,v_test_sql,dbms_sql.native);

    DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);

    n := dbms_sql.execute(c);
    n := dbms_sql.fetch_rows(c);
    dbms_sql.column_value (c,1,n_tab);
    r := n_tab (1);

    --dbms_output.put_line('DEBUG n: ' || n);     --dbms_output.put_line('DEBUG r: ' || r);

    dbms_sql.close_cursor(c);

    if r > 1 then
      return '1=0';
    else
      return '1=1';
    end if;

  else
    return '1=0';
  end if;

  return '1=1';
exception
  when others then
    begin

      dbms_output.put_line(sqlerrm);
      dbms_sql.close_cursor(c);

    end;
end vpdpolicy3_fnc;
/

begin
  dbms_rls.drop_policy (object_schema => 'SCOTT',

                        object_name => 'EMP',
                        policy_name => 'VPDPOLICY3');

end;
/
begin

  dbms_rls.add_policy  (object_schema => 'SCOTT',
                        object_name => 'EMP',
                        policy_name => 'VPDPOLICY3',
                        function_schema => 'SCOTT',
                        policy_function => 'VPDPOLICY3_FNC',
                        statement_types => 'UPDATE');

end;
/

set serveroutput on
set echo on
clear
select count(*) from emp;
update emp set sal = 0;
select count(*) from emp where emp.ename = 'SMITH'; update emp set sal = 0 where emp.ename = 'SMITH'; select count(*) from emp where emp.job = 'MANAGER'; update emp set sal = 0 where emp.job = 'MANAGER'; rollback; Received on Fri Aug 11 2006 - 09:57:07 CDT