locked
Example of how to restrict data access to subset of data only RRS feed

  • Question

  • Sorry for asking such a basic question, but I would like to do this the correct way rather than just fudge the solution.

     In my design I have a number of tables which contain a common user ID field. What I want to be able to do is restrict the data displayed to the user ID given.  Think of it like internet banking – once the user logs in they have access to their data – not the whole table just the bits which match their user ID.

     I am assuming there is some cleaver bit of sql design which will limit the views based on user ID which would be held in a ASPNETDB.mdf.

    Thursday, September 22, 2011 1:41 PM

Answers

  • One way is to Create SQL server Views and restrict the access to all the base tables. And create view as you would like the users to see the data.

    Its just one way out of many.

     


    Sandeep Dasam
    Thursday, September 22, 2011 1:55 PM
  • Probably the simplest way is to use stored procedures. Something like:

    create proc dbo.GetUserData(@UserId int)
    as
    	select Col1, Col2, Col3
    	from dbo.Data
    	where UserId = @UserId

     

    I don't think there is the easy way to handle it with the views. Well, if every system user could be the database user and/or server login, you can do something like (example is for database user approach):

    create table T(ID int, UserName nvarchar(30))
    go
    
    insert into T(ID, UserName) values(1,'user1'), (2,'user2') 
    go
    
    create view vT(ID, UserName)
    as
    	select ID, UserName 
    	from T
    	where UserName = USER_NAME()
    go	
    	
    select * from vT	


    It could be quite messy though if you have a lot of users and/or logins...

     


    Thank you!

    My blog: http://aboutsqlserver.com


    Thursday, September 22, 2011 3:34 PM
  • A TABLE has data, a user gets access to the TABLE. If a user can only see a subset of the data, the subset has to be defined, and access restricted from the TABLE itself.

    The subset can be one in two ways: a VIEW or a stored PROCEDURE. A VIEW is by far the better method as it is simply a query and can be merged with the query on the VIEW to generate one query. An SP is more flexible, but the query cannot be merged, the non-optimal solution.

    To define a subset in a VIEW, the logic has to be defined. If the restriction is based on a username, the easiest way is to add a WHERE clause specifying WHERE User = <username>. If <username> is the SQL Server logged in user, the CURRENT_USER() FUNCTION can be used to retrieve the name. (If not, a probably ugly but workable solution would be to have a "current user" TABLE, which would be UPDATEd with the current application user, and the VIEW could join that.)

    If the query is run from an application, a query with bind variables can be used. Simply make the username a bind variable.

    Otherwise, an SP can be used to build the query dynamically.


    Friday, September 23, 2011 1:34 PM
    Answerer

All replies

  • One way is to Create SQL server Views and restrict the access to all the base tables. And create view as you would like the users to see the data.

    Its just one way out of many.

     


    Sandeep Dasam
    Thursday, September 22, 2011 1:55 PM
  • Thanks and yes that was my first try at a solution - the question is how to supply the userID to the view. 

    I am using Silverlight to connect to the database with the userid being generated in the aspnetDB.mdf file.

    I could pass the userid on a query but this is after the view is created not before it.  

     

    Thursday, September 22, 2011 2:19 PM
  • Probably the simplest way is to use stored procedures. Something like:

    create proc dbo.GetUserData(@UserId int)
    as
    	select Col1, Col2, Col3
    	from dbo.Data
    	where UserId = @UserId

     

    I don't think there is the easy way to handle it with the views. Well, if every system user could be the database user and/or server login, you can do something like (example is for database user approach):

    create table T(ID int, UserName nvarchar(30))
    go
    
    insert into T(ID, UserName) values(1,'user1'), (2,'user2') 
    go
    
    create view vT(ID, UserName)
    as
    	select ID, UserName 
    	from T
    	where UserName = USER_NAME()
    go	
    	
    select * from vT	


    It could be quite messy though if you have a lot of users and/or logins...

     


    Thank you!

    My blog: http://aboutsqlserver.com


    Thursday, September 22, 2011 3:34 PM
  • If the user was a SQL Server user, you would have the username available in the CURRENT_USER FUNCTION.
    Friday, September 23, 2011 1:21 PM
    Answerer
  • A TABLE has data, a user gets access to the TABLE. If a user can only see a subset of the data, the subset has to be defined, and access restricted from the TABLE itself.

    The subset can be one in two ways: a VIEW or a stored PROCEDURE. A VIEW is by far the better method as it is simply a query and can be merged with the query on the VIEW to generate one query. An SP is more flexible, but the query cannot be merged, the non-optimal solution.

    To define a subset in a VIEW, the logic has to be defined. If the restriction is based on a username, the easiest way is to add a WHERE clause specifying WHERE User = <username>. If <username> is the SQL Server logged in user, the CURRENT_USER() FUNCTION can be used to retrieve the name. (If not, a probably ugly but workable solution would be to have a "current user" TABLE, which would be UPDATEd with the current application user, and the VIEW could join that.)

    If the query is run from an application, a query with bind variables can be used. Simply make the username a bind variable.

    Otherwise, an SP can be used to build the query dynamically.


    Friday, September 23, 2011 1:34 PM
    Answerer
  • Thanks for all the response, I am out of the office for the next few days but will try testing the solutions when I get back.
    Sunday, September 25, 2011 2:33 PM
  • We can also use inline table-valued function which will be very similar to the view but can accept UserID as a parameter.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, September 26, 2011 1:01 AM
    Answerer