Have Geek Brain

Limiting SQL Query Results

I am trying to limit the results returned from a query. If a user has access to more objects than only those specified for the current user, then this user should not appear in the list. Using the data below and assuming that User 1 is executing the query because the user with UserId 2 has matches that User 1 does not have, even if they have overlapping values, the user user should be excluded from the query results.

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

How to do it?

0


a source to share


6 answers


You can 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 bottommost query gives us EntityIds belonging to UserId 1. We use this list in the next query to find all user IDs that have an EntityId NOT in this list. Armed with this list of UserIds, which we don't need, the outer query flushes all rows for the rest of the UserIds. These will be the ones that have a subset of the UserId set of 1 EntityIds

+1


a source


It looks like you are trying to restrict users in SQL and not in an application that interacts with the database (like a webapp). If so, you need to restrict access to the table using the built-in database permissions.



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

+3


a source


Is it possible for User 1 to have matches to User 2's flaws and User 2 to have matches to User 1 at the same time? If not, you can just use count

to check how many user rights and if it is higher than the current user, do not return them.

+1


a source


Try the following:

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
)
+1


a source


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

The table below appears to be using UserID, which is just an int, so you will need to create a table that will link the logged in user to the user id. Then just create a view that uses a connection to restrict the viewing results to the currently logged in user.

Here is an example: (I have not tested this code, so some syntax problems may occur)

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 would suggest that you can find similar functionality in other databases. It should also be noted that you can easily achieve this on the client side with a where clause.

+1


a source


In Oracle, you can use Virtual Private Database for this. It is also called fine-grained access control.

You can also define a view that filters out other user rows and you should probably deny direct access to the table. This way, access will be transparent to clients, ensuring that no user can see other custom rows.

+1


a source






More articles:


All Articles