Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Sub query base on security table
<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