Home » SQL & PL/SQL » SQL & PL/SQL » restrict any user not to select more than 100 rows by his select (11g)
restrict any user not to select more than 100 rows by his select [message #399340] Tue, 21 April 2009 13:16 Go to next message
najehas
Messages: 10
Registered: March 2007
Junior Member
hi all
i need ahelp in order to restricct users or tables noe to be accessed all , just i need to permit for a user to select maximum 100 rows at atime, can i do that
Re: restrict any user not to select more than 100 rows by his select [message #399344 is a reply to message #399340] Tue, 21 April 2009 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+select+limit+100+rows


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: restrict any user not to select more than 100 rows by his select [message #399351 is a reply to message #399340] Tue, 21 April 2009 14:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which 100 records? Any?

If so, one way to do that might be this:

  • in the owner's schema create a view which selects 100 records from the original table
  • in other user's schema create a synonym for that view, named as the original table

Here's an example: Table owner:
SQL> connect scott/tiger
Connected.

SQL> create view v_emp as
  2    select * from emp
  3    where rownum <= 5;

View created.

SQL> grant select on v_emp to mike;

Grant succeeded.

Another user:
SQL> connect mike/lion
Connected.

SQL> create synonym emp for scott.v_emp;

Synonym created.

SQL> select empno, ename, job, sal from emp;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7499 ALLEN      SALESMAN        1600
      7521 WARD                       1250
      7566 JONES      MANAGER         2975
      7654 MARTIN     SALESMAN        1250

SQL>
Re: restrict any user not to select more than 100 rows by his select [message #399384 is a reply to message #399351] Tue, 21 April 2009 22:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmmm. Sounds to me like you are trying to stop developers from thrashing a production database.

If you want to restrict the visibility of each table to just 100 rows, then Littlefoots suggestion is the way to go.

However if you want to restrict QUERIES to 100 rows in order limit resource usage, then you might have a problem. The problem is that many queries might do TONS of processing before they return any rows at all. Restricting by an arbitrary number of rows will not stop resource-hungry queries.

To manage resource usage, you should look into PROFILES, with which you can restrict things like CPU_PER_CALL and LOGICAL_READS_PER_CALL. This might be more appropriate.

Ross Leishman
Previous Topic: backing up data (merged 3)
Next Topic: Alter Materialized View log (merged)
Goto Forum:
  


Current Time: Sat Dec 10 20:53:38 CST 2016

Total time taken to generate the page: 0.19461 seconds