Sub query base on security table

From: <beersa.beersa_at_gmail.com>
Date: 24 Oct 2006 20:36:22 -0700
Message-ID: <1161747382.513252.321910@i3g2000cwc.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. Received on Tue Oct 24 2006 - 22:36:22 CDT