Restricting results from a SQL query

Question

I am trying to restrict the results returned from a query. If a user has access to more Entities than just those listed for the current user, then that user should not show up on the list. Using the data below, and assuming User 1 is running the query, since the user with UserId 2 has matches that User 1 does not have, even though they have overlapping values, user 2 should be excluded from the results of the query.

Table1
UserId   EntityId
1        100
1        101
1        102
2        100
2        101
2        102
2        200
2        201

How do I do this?

Answer 1

It looks like you're trying to limit the users in SQL rather than in an application that interfaces with the database (e.g. webapp). If that is the case, you need to restrict access to the table using the built-in database permissions.

You could create a view that filters the results based on the User, deny the users the ability to edit the view, and deny the users access to the table. The only way to get results is to use the view, which will filter their results.

Answer 2

Is it possible for user 1 to have matches user 2 lacks and user 2 to have matches user 1 lacks at the same time? If not, you could just use count to check how many privledges a user has and if it's higher than the current user, don't return them.

Answer 3

You could do this with a series of nested queries:

select A.* from Table1 A where A.UserId NOT IN
   (select B.UserId from Table1 B where B.EntityId NOT IN 
       (select C.EntityId from Table1 C where C.UserId=1));

The bottom, innermost query gives us the EntityIds belonging to UserId 1. We use that list in the next query up to find all the UserIds which have an EntityId NOT in this list. Armed with that list of UserIds we don't want, the outermost query dumps all the rows for the remaining UserIds. These will be ones which have a subset of UserId 1's set of EntityIds

Answer 4

Try this:

select A.UserId, A.EntityId
from Table1 A
where not UserId in (
    select B.UserId
    from Table1 B
    left outer join Table1 C
      on C.UserId=@UserId
      and C.EntityId=B.EntityId
    where B.UserId is null
)

Answer 5

I'm not sure which database you are using, but in MS SQL Server you can determine which user is logged on and limit your results. There is a system function called suser_sname() that will return the user who is currently running the query. For example, if I ran "select suser_sname()" it would return 'jj' (assuming my username is jj).

The table you provide as an example seems to use a UserID that is just an int, so you would have to create a table that would link the logged in user with a user id. Then just create a view that uses a join to limit your view results by the user logged in.

Here's an example: (I did not test this code, so there might be some syntax issues)

UserIDSName Table:
SUser   UserID
jj      1
bob     2

Then create a view:

create view Table1View
as

select userid, entityId 

from Table1 t1

inner join UserIDSName uid on
    t1.userid = uid.userid
    and uid.SUser = suser_sname()

Again, I'm not too sure if you are using SQL Server or not, but I'd imagine you can find similar functionality in the other databases. It also should be noted that you can achieve this on the client side pretty easily with a where clause.

Answer 6

In Oracle you could use Virtual Private Database to accomplish this. It is also referred to as Fine Grained Access Control.

You could also define a view that filters out the other user's rows and you should probably disallow direct access to the table. That way the access will be transparent for the clients, while ensuring that no user can see other user's rows.