CODEWITHSUNDEEP
oracle-databaseconfiguration
Ferran
Ferran

Reputation: 38

Oracle, limit the row number per user

I need to limit the number of rows that a query can return for a specific user. I know that I can limit in the SQL query, but I need to avoid that a specific user can build a query that return a huge amount of rows. for this reason I need to limit in the configuration.

Does anybody know if it is possible?

Upvotes: 1

Views: 417

Answers (3)

Piotr Siekierski
Piotr Siekierski

Reputation: 494

If you have Enterprise Edition you can implement VPD rule using DBMS_RLS package: 

SQL> create or replace package pac1
  2  is
  3    function limit_rows(owner varchar2, tab varchar2) return varchar2;
  4    end;
  5  /

Package created.

SQL> create or replace package body pac1
  2  is
  3    function limit_rows(owner varchar2, tab varchar2) return varchar2
  4    is
  5    begin
  6      return ' rownum <= 3';
  7    end;
  8  end pac1;
  9  /

Package body created.

SQL> begin
  2    dbms_rls.add_policy('HR','EMPLOYEES','RULE1','HR','PAC1.LIMIT_ROWS','SELECT');
  3  end;
  4  /

PL/SQL procedure successfully completed.


SQL> select first_name, last_name from hr.employees;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Ellen                Abel
Sundar               Ande
Mozhe                Atkinson

SQL>

Upvotes: 1

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Amount of data is not necessarily equal to amount of load. A single row query can kill a database if it's complex enough.

Answer to this is really complex.

You can take the SQL and create a SQL PLAN from it, and from that data limit from estimated cost and/or estimated rows.

How To Create and Evolve a SQL Plan Baseline

How do I display and read the execution plans for a SQL statement

Example: I'll create a temp table:

create table tmp_table2 as
   select * from user_objects;

then I use Oracle's plan estimation with out actually running the query

declare
   l_sql   varchar2 (32767);
begin
   delete from plan_table;
   l_sql := 'select * from tmp_table2';
   execute immediate 'explain plan for ' || l_sql;
   for i in (select cardinality,
                    cost,
                    bytes,
                    cpu_cost
               from PLAN_TABLE
              where operation = 'SELECT STATEMENT') loop
      if i.cardinality /* rows */
                      > 500 then
         dbms_output.put_line ('Too many rows');
      elsif i.cpu_cost > 500000 then
         dbms_output.put_line ('Too much CPU');
      else
         dbms_output.put_line ('About right');
      end if;
   end loop;
end;

Result; 

==>
PL/SQL block executed
Too many rows

Or you can use the Resource manager to limit per session:

Using the Database Resource Manager

Upvotes: 0

Piotr Siekierski
Piotr Siekierski

Reputation: 494

You can achieve this with cursors. See example:

SQL> create or replace function exec_query(sql_text varchar2) return sys_refcursor
  2  is
  3    num_rows number := 3;
  4    c1 sys_refcursor;
  5  begin
  6    open c1 for 'with test as (' || sql_text || ') select * from test where rownum <=' || num_rows ;
  7    return c1;
  8  end;
  9  /

Function created.

SQL> variable c1 refcursor;

SQL> exec :c1 := exec_query('select last_name, salary from hr.employees');

PL/SQL procedure successfully completed.


SQL> print :c1;

LAST_NAME                     SALARY
------------------------- ----------
King                           24000
Kochhar                        17000
De Haan                        17000

Upvotes: 0

Related Questions