Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Sub query base on security table

Re: Sub query base on security table

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 25 Oct 2006 07:18:03 +0200
Message-ID: <453ef38a$0$28540$426a74cc@news.free.fr>

<beersa.beersa_at_gmail.com> a écrit dans le message de news: 1161746806.526394.172070_at_h48g2000cwc.googlegroups.com...
|I am using oracle9i.
| I am seeking tips on following:
| I have a table with million rows. I want to restrict the rows by
| user_id.
| Eg.,
| The main table has following structure:
| tbl_a (year,entity,dept,bal)
|
| And the security table has following structure:
| tbl_s(login_id,entity1,entity2,entity3)
|
| The rows in main table is restricted based on the values in security
| table.
| E.g,
| the values in main table are as:
| 2005,A,HR,200
| 2005,B,IT,100
| 2006,A,HR,20
| The values in security table are as:
| abc,A,C,E
| efg,B,D,F
| xyz,C,H,K
|
| The user 'abc' can only allow to see the rows that 'entity' in the main
| table should match with 'entity1' or
| 'entity2' or 'entity3' in the security table
|
| I write query as:
| SELECT * from tbl_a where exists
| (SELECT * from tbl_s where login_id=USER)
|
| but it returns all the rows without restriction.
|
| Your advice and tips are highly appreciated.
|
| Thks.
|

A condition on entity* is missing:

SELECT * from tbl_a where exists
(SELECT 1 from tbl_s where login_id=USER and tbl_a.entity in (tbl_b.entity1,tbl_b.entity2,tbl_b.entity3) )
/

Regards
Michel Cadot Received on Wed Oct 25 2006 - 00:18:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US