securing query authorization in linq-to-sql

Question

I have 3 tables: the User table, the Records table and the UserRecords table.

The columns are so:

UserTable
UserID | OtherUserDataFields

RecordsTable
RecordID | OtherRecordDataFields

UserRecords
UserID | RecordID

The UserRecords table tells me which users have authorization on which record. I have a function that updates the RecordsTable by receiving 2 paramaters: a record TheRecord (which contains a RecordID field) and a UserID.

I'm writing a query in the data context MyDC to fetch the record with the RecordID supplied in the parameter and test if the user is authorized on that record like this:

var RecordToUpdate = (
    from r in MyDC.RecordsTable
    from u in MyDC.UserRecords
    where r.RecordID == TheRecord.RecordID && TheRecord.RecordID == u.RecordID
    where u.UserID == TheUserID
    select r).SingleOrDefault();

Will this ensure me that only records the user is authorized will be fetched? I want to avoid cases where users maliciously send a record they're not authorized on and make changes to these unauthorized records.

Thanks for your advice.

Answer 1

I agree with Anand, you will need a linq query:

var filterUserRecord = from u in MyDC.UserRecords
                       where u.UserID  == TheUserID
                       select u;

var q1 = from r in  MyDC.RecordsTable
          where r.RecordID = TheRecordID
          where filterUserRecord.Any(f => f.RecordID == r.RecordID)
          select r;

This will be converted to the SQL query like the following:

SELECT * FROM RecordsTable rt WHERE rt.RecordID = TheRecordID AND EXISTS
   (SELECT recordId FROM UserRecords ur WHERE ur.userId = TheUserID AND ur.recordID = rt.recordID)

Note that those are IQueryable<T>s and linq queries on it will produce another IQueryable<T> which will contain expressions to be wholly translated to SQL (or whatever the backend is) instead of naively evaluating it at client side.

Answer 2

Well I think your problem could be solved by subquery

sql:

select * from RecordsTable where recordId in 
(select recordId from UserRecords where userId = @someUserId)

It could be represented in Linq as following

var filterUserRecord = from u in MyDC.UserRecords
                       where u.UserID  == TheUserID
                       select u

var q1 = from r in  MyDC.RecordsTable
         where filterUserRecord.Any(f => f.RecordID == r.RecordID)

Details for subquery in Linq - Read from here