10

I have a table structured as so (Simplified)

Name, EMail, LastLoggedInAt

I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null.

It looks like I can do this? Is it possible?

32

The SQL Server security model allows you to grant access to a view without granting access to the underlying tables.

Since example code is a great way to show a concept, consider the following, with a LoginDetails table, and corresponding view:

CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE VIEW dbo.LoginDetailsView
AS
SELECT ld.Username
    , ld.EmailAddress
    , ld.LastLoggedInAt
FROM dbo.LoginDetails ld
WHERE ld.LastLoggedInAt IS NOT NULL;
GO

We'll create a login, and a user, then assign that user the rights to select rows from the view, without having any rights to view the table itself.

CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetailsView TO RemoteUser;

Now, we'll insert two test rows:

INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

This tests the security model. The first SELECT statement succeeds, since it is selecting from the view, whereas the second SELECT statement fails because the user does not have direct access to the table.

EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetailsView;
╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 07:36:54.490 ║
╚══════════╩══════════════╩═════════════════════════╝
SELECT *
FROM dbo.LoginDetails;

REVERT

Note the results from the view exclude the row where the LastLoggedInAt value is NULL, as required in your question.

The second SELECT statement against the underlying table returns an error:

Msg 229, Level 14, State 5, Line 28
The SELECT permission was denied on the object 'LoginDetails', database 'tempdb', schema 'dbo'.

Cleanup:

DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP VIEW dbo.LoginDetailsView;
DROP TABLE dbo.LoginDetails;

Alternately, if you have SQL Server 2016 or newer, you could use a row-level-security-predicate to prevent certain users seeing rows with a NULL LastLoggedInAt value.

First, we create the table, a login, a user for that login, and we grant access to the table:

CREATE TABLE dbo.LoginDetails
(
    Username nvarchar(100) NOT NULL
    , EmailAddress nvarchar(256) NOT NULL
    , LastLoggedInAt datetime NULL
);
GO

CREATE LOGIN RemoteUser 
WITH PASSWORD = '2q1345lkjsadfgsa0(*';

CREATE USER RemoteUser
FOR LOGIN RemoteUser
WITH DEFAULT_SCHEMA = dbo;

GRANT SELECT ON dbo.LoginDetails TO RemoteUser;

Next, we insert a couple of sample rows. One row with a null LastLoggedInAt, and one with a non-null value for that column.

INSERT INTO dbo.LoginDetails(Username, EmailAddress, LastLoggedInAt)
VALUES ('user x', 'x@y.com', NULL)
    , ('user y', 'y@y.com', GETDATE());

Here, we're creating a schema-bound table-valued-function that returns a row with either 0 or 1 depending on the value of the @LastLoggedInAt and @username variables that are passed into the function. This function will be used by a filter-predicate to eliminate the rows we want to hide from certain users.

CREATE FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate
(
    @LastLoggedInAt datetime
    , @username sysname
)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
    WHERE (@username = N'RemoteUser' AND @LastLoggedInAt IS NOT NULL)
        OR @username <> N'RemoteUser';  
GO

This is the security filter that eliminates rows from SELECT statements ran against the dbo.LoginDetails table:

CREATE SECURITY POLICY LoginDetailsRemoteUserPolicy
ADD FILTER PREDICATE dbo.fn_LoginDetailsRemoteUserPredicate(LastLoggedInAt, USER_NAME())
ON dbo.LoginDetails
WITH (STATE=ON);

The filter above uses the dbo.fn_LoginDetailsRemoteUserPredicate function by passing in the name of the current user, along with the values from each row for the LastLoggedInAt column from the dbo.LoginDetails table.

If we query the table as a normal user:

SELECT *
FROM dbo.LoginDetails

we see all rows:

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user x   ║ x@y.com      ║ NULL                    ║
║ user y   ║ y@y.com      ║ 2018-02-15 13:53:42.577 ║
╚══════════╩══════════════╩═════════════════════════╝

However, if we test as the RemoteUser:

EXECUTE AS LOGIN = 'RemoteUser';

SELECT *
FROM dbo.LoginDetails

REVERT

we only see "valid" rows:

╔══════════╦══════════════╦═════════════════════════╗
║ Username ║ EmailAddress ║     LastLoggedInAt      ║
╠══════════╬══════════════╬═════════════════════════╣
║ user y   ║ y@y.com      ║ 2018-02-15 13:42:02.023 ║
╚══════════╩══════════════╩═════════════════════════╝

And, we clean up:

DROP SECURITY POLICY LoginDetailsRemoteUserPolicy;
DROP FUNCTION dbo.fn_LoginDetailsRemoteUserPredicate;
DROP USER RemoteUser;
DROP LOGIN RemoteUser;
DROP TABLE dbo.LoginDetails;

Be aware that schema-binding a function to the table in this way does make it impossible to modify the definition of the table without first dropping the filter predicate, and the dbo.fn_LoginDetailsRemoteUserPredicate function.

| improve this answer | |
  • Brilliant answer - thanks! Whats the performance implication of these 2 methods. We've found that our web app is up to 5 times slower when we use the function. Need to look at the View Method. – LiamB Mar 13 '18 at 16:35
  • The row-level-security function is evaluated for each and every row read from the table; I'd expect it to significantly slow access to that table. The view, on the other hand, should have negligible impact on performance assuming you create a useful index on the LastLoggedInAt column. – Max Vernon Mar 13 '18 at 16:41
  • That makes sense - I'll look at the view now, seems to be working well! If we wanted the user to be only able to edit user data for these rows that match the criteria would that be possible with the view? – LiamB Mar 14 '18 at 10:53
  • Its beautiful, all working - thanks for the help with this – LiamB Mar 14 '18 at 11:09
  • Yes, you can edit rows via the view – Max Vernon Mar 14 '18 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.