Check user permissions from a table before running query

Question

question

SQLNewbie2010 asked Oct 19, '11

Check user permissions from a table before running query

I have a stored procedure that is accepting a bunch of input parameters, and returning a result set accordingly. The query is working fine, but now I need to integrate user permissions into the query. The Userpermissions table consists of Username, Branch, Department, Account and SubAccount. Every username can and most likely will have more than one record, as they can have permissions to All Branches, departments, Accounts, and subaccounts, or specific ones.


UserPermissions Table
UserName Branch  Department  Account  SubAccount
John	   1	    A	        ALL	    ALL
John	   2	   ALL	      12345	    ALL
John 	  2	      ALL	      36524	    25
Alex	  ALL	   ALL	        ALL	     ALL

The stored procedure accepts a bunch of parameters, but for clarity, I will simplify it to include the ones relevant to the problem: There is a single entry for Branch,department, and subaccount. However, there is a startaccount parameter, and a Endaccount parameter, where I will have to check for permissions for branch , department and subaccounts and also every account in the range of accounts supplied, in order to determine if the user has access to all of the accounts in the range. If yes, then run the query, if not, then return an empty set. There is a list of accounts with other data in an Accounts table.


ALTER  PROCEDURE [dbo].[MyProc]
	(
		@Branch varchar(4) = null,
		@Department varchar(4) = null,
		@SubAccount varchar(8) = null,
		@Start_Account varchar(6) = Null,
		@End_Account varchar(6) = Null,
		@UserName
	)

	As 
	BEGIN

My approach was: trying to check if the user has the permission to run the query as below. And then if count(*) is greater than zero then run the query. But I am having a problem comparing the range of accounts….New to Sql,and STUCK here…and don’t know if what I am doing is right!!!


SELECT  COUNT(*)
	From UserPermissions 
	WHERE (Branch = 'All' or Branch = @Branch)
	AND (Department = 'All' or Department = @Department)
	AND (SubAccount = 'All' or SubAccount = @SubAccount) 
	AND (U.Account = 'All')
	And username = @username 


--This is the final query 	
	SELECT
		Account,
		SubAccount,
		Branch,
		Department,
		Amount,	
	FROM
		Table1
	Where
		(Branch = ISNULL(@Branch,Branch)) AND
		(Department = ISNULL(@Department,Department)) AND
		(SubAccount = ISNULL(@SubAccount, SubAccount)) AND
		(Account BETWEEN ISNULL(@Start_Account,Account) AND ISNULL(@End_Account,Account)) 		 
		END
GO

procedure stored

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-10-19T14:08:41Z

Oleg answered Oct 19, '11

It looks like what you have done is correct, you only need to actually check whether your first query returns any records. Here is one way: declare @count int; SELECT @count = COUNT(*) From UserPermissions WHERE Branch = 'All' or Branch = @Branch) AND (Department = 'All' or Department = @Department) AND (SubAccount = 'All' or SubAccount = @SubAccount) AND (U.Account = 'All') And username = @username -- The only way that the @count variable is greater than zero is -- if there is a record in the persmissions table with the blanket -- "All" permissions for the accounts. However, if the user has -- some accounts listed but not "All" then instead of giving up, -- it would take to now check whether the user has permissions -- for every account in the specified range, passed into the proc -- as parameters. A left join between AccountNumbers and the list -- of accounts from the permissions table should at this time be -- one to one, so if there is any record in the results which has -- a value of null (not matched but still included due to the left -- join) on the right side then the user does not have permissions -- to view data if @count = 0 begin select @count = count(*) from AccountNumbers acct left join ( select Account from UserPermissions where Branch = 'All' or Branch = @Branch) and (Department = 'All' or Department = @Department) and (SubAccount = 'All' or SubAccount = @SubAccount) and (U.Account 'All') -- please note the change here and username = @username ) perm on acct.Account = perm.Account where -- almost forgot about the most important part :) acct.Account between @Start_Account and @End_Account and perm.Account is null; -- if the query above determined that the user does not have -- permissions to every account in the range then this user -- cannot view the data, so lets set the @count to zero at this -- time so the logic below does not have to change :) if @count > 0 set @count = 0 end; if @count > 0 SELECT Account, SubAccount, Branch, Department, Amount, FROM Table1 Where (Branch = ISNULL(@Branch,Branch)) AND (Department = ISNULL(@Department,Department)) AND (SubAccount = ISNULL(@SubAccount, SubAccount)) AND (Account BETWEEN ISNULL(@Start_Account, Account) AND ISNULL(@End_Account, Account)); else SELECT NULL Account, NULL SubAccount, NULL Branch, NULL Department, NULL Amount; Oleg

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLNewbie2010 commented · Oct 20, 2011 at 11:56 AM

Thanks Oleg, but how do I check if a user has permission for the range of accounts? I am passing @start_Account and @end_Account as input parameters, so this query should give me all accounts that fall in the range: SELECT Account INTO #TEMP1 FROM AccountNumbers Where Account between @Start_Account and @End_Account How do i check that the user has access to all accounts in the range. i can only run the query of the user has permissions to all accounts in the range. I tried the following: SELECT @count = COUNT(*) From UserPermissions S LEFT OUTER JOIN #TEMP1 T1 on S.Account = T1.Account WHERE (S.Branch = 'All' or S.Branch = @Branch) AND (S.Department = 'All' or S.Department = @Department) AND (S.SubAccount = 'All' or S.SubAccount = @SubAccount) AND (S.Account = 'All' or S.Account = T1.Account) And (S.DomainUserName = @User_Name) but it does not work. I used a left outer join since account can equal 'All' in the permissions table. Any ideas???

0 ·

Oleg commented · Oct 20, 2011 at 03:32 PM

@SQLNewbie2010 I edited my answer and removed the second sample as it is now useless. Please let me know if this works.

0 ·

Answer 2 · 2011-10-20T11:54:10Z

SQLNewbie2010 answered Oct 20, '11

Thanks Oleg, but how do I check if a user has permission for the range of accounts? I am passing @start_Account and @end_Account as input parameters, so this query should give me all accounts that fall in the range:


SELECT Account
	INTO #TEMP1
	FROM AccountNumbers
	Where Account between @Start_Account and @End_Account

How do i check that the user has access to all accounts in the range. i can only run the query of the user has permissions to all accounts in the range. I tried the following:


SELECT @count = COUNT(*)
    From UserPermissions S LEFT OUTER JOIN #TEMP1 T1 on S.Account = T1.Account   
    WHERE 
        (S.Branch = 'All' or S.Branch = @Branch)
        AND (S.Department = 'All' or S.Department = @Department)
        AND (S.SubAccount = 'All' or S.SubAccount = @SubAccount) 
        AND (S.Account = 'All' or S.Account = T1.Account)
        And (S.DomainUserName = @User_Name)

but it does not work. I used a left outer join since account can equal 'All' in the permissions table. Any ideas???

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question