CODEWITHSUNDEEP
sql-servert-sqlexists
Bestter
Bestter

Reputation: 878

SQL: If exists, limit user. If not exists show everything

I'm trying to found the best way to this requirements:

@fkStaffID INT = Current user.

If @fkStaffID got resource BLABLA only show rows of table X where is StaffID is here. If he DON'T have resource BLABLA, show everything.

SORRY I cannot paste full SQL, for employer's security policy. (I wish I show enough for help, not too much for security...)

What I do:

SELECT * FROM X
WHERE ((EXISTS
(SELECT 1 FROM STAFF WHERE pkStaff=@fkStaffID
AND STAFF.PkStaff IN (SELECT fkStaff FROM SECURITYSUBQUERY WHERE ResourceName='BLABLA')) AND X.fkStaff=@fkStaffID)
OR ((NOT EXISTS (SELECT 1 FROM STAFF WHERE pkStaff=@fkStaffID
AND STAFF.PkStaff IN (SELECT fkStaff FROM SECURITYSUBQUERY WHERE ResourceName='BLABLA')) )

PROBLEM: It's really slow. Can I do a more efficient way? Can I do another way? Thank you for your help!

Upvotes: 0

Views: 161

Answers (3)

Shawn
Shawn

Reputation: 4786

The below query will give you only the data for people in X who are in the STAFF table with a corresponding record in SECURITYSUBQUERY table ('BlaBla' records).

First, build test data.

IF OBJECT_ID(N'tempdb..#x') IS NOT NULL
     DROP TABLE #x

CREATE TABLE #X ( fkStaff int, myStuff varchar(20) )
INSERT INTO #X ( fkStaff, myStuff )
VALUES 
      (1,'not me')
    , (2,'not me')
    , (3,'show me')
    , (4,'not me')
    , (5,'show me too')

IF OBJECT_ID(N'tempdb..#STAFF') IS NOT NULL
     DROP TABLE #STAFF

CREATE TABLE #STAFF ( pkStaff int, name varchar(20) )
INSERT INTO #STAFF ( pkStaff, name )
VALUES 
      (1, 'Joe')
    , (2, 'Jim')
    , (3, 'Bill')
    , (4, 'Ted')
    , (5, 'Rufus')

IF OBJECT_ID(N'tempdb..#SECURITYSUBQUERY') IS NOT NULL
     DROP TABLE #SECURITYSUBQUERY

CREATE TABLE #SECURITYSUBQUERY ( fkStaff int, ResourceName varchar(20) )
INSERT INTO #SECURITYSUBQUERY ( fkStaff, ResourceName )
VALUES 
      ( 1, 'NotAuth' )
    , ( 2, 'NotAuth' )
    , ( 3, 'BlaBla' )
    , ( 3, 'Extra Perm' )
    , ( 4, 'NotAuth' )
    , ( 5, 'BlaBla' )

Now for the query. 

DECLARE @fkStaffID int ; /* Only 3 or 5 will return records. */

SELECT x.* 
FROM #x x 
LEFT OUTER JOIN (
    SELECT s.pkStaff
    FROM #STAFF s
    INNER JOIN #SECURITYSUBQUERY ss ON s.pkStaff = ss.fkStaff
        AND ss.ResourceName = 'BlaBla'
    WHERE s.pkStaff = @fkStaffID
) t ON t.pkStaff = x.fkStaff
WHERE t.pkStaff IS NOT NULL
    AND x.fkStaff = @fkStaffID

This will only give results if users Bill or Rufus are logged in (and passed as @fkStaffID).

I don't know how well this will scale, but the optimizer should work faster than EXISTS or NOT IN subqueries. Try it with your data.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

I think you should be able to qrite the query thus:

SELECT * FROM x
WHERE @fkStaffID NOT IN (SELECT fkStaff FROM SecuritySubquery WHERE ResourceName= 'BLABLA')
   OR @fkStaffID = fkStaff;

So either the @fkStaffID isn't 'BLABLA' or it matches the record's staff ID.

This NOT IN / OR still won't be very fast. Anyway, you should have the following indexes:

CREATE INDEX idx1 ON SecuritySubquery (ResourceName, fkStaff);
CREATE INDEX idx2 ON x (fkStaff);

Upvotes: 1

Rigerta
Rigerta

Reputation: 4039

I would try this:

if exists(select 1 from staff where pkstaff=@fkstaffid)
    begin
        select * from X where ResourceName = 'Blabla' and fkStaff = @fkStaffId
    end
else
    begin
        select * from X where ResourceName = 'Blabla'
    end

If we have a matching record, then we filter by that @fkStaffId, otherwise we select everything.

Upvotes: 0

Related Questions