Grant permissions for specific time of day

Question

Is it possible to give a user rights for, say the business hours of the company.

GRANT SELECT 
ON client
  <WHERE CONDITION>
TO Emily 

I know something like this is possible to do this with MySQL where you can add a WHERE clause to the grant option so you can add context conditions to it. However, I'm working with MS SQL Server, can it be done in there?

Another solution would be to add a SQL Job to add and remove the rights on specific times, but I don't really like that, I'd prefer to do this on the granting level.

Answer 1

I like @Turo's suggestion of using a view.

It could just consist of something like

CREATE VIEW dbo.TimeFilteredClient
AS
  SELECT *
  FROM   dbo.Client
  WHERE  CAST(GETDATE() AS TIME) BETWEEN '09:00' AND '17:00' 

Then grant Emily permissions on the view and not the table. As long as the view and table share the same owner she will be able to select from the view but get no results outside the specified time.

If you are on 2016 you could also use row level security on the table to achieve much the same thing. Example below

CREATE TABLE dbo.Client
  (
     clientId INT IDENTITY PRIMARY KEY,
     Name     VARCHAR(50)
  );

INSERT dbo.Client
VALUES ('client1'),
       ('client2');

CREATE USER Emily WITHOUT LOGIN;

GRANT SELECT ON dbo.Client TO Emily;

GO

CREATE SCHEMA Security;

GO

CREATE FUNCTION Security.EmilyTimeFilterPredicate()
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
      SELECT 1 AS fn_securitypredicate_result
      WHERE  USER_NAME() <> 'Emily'
              OR CAST(GETDATE() AS TIME) BETWEEN '09:00' AND '17:00';

GO

CREATE SECURITY POLICY EmilyTimeFilter  
ADD FILTER PREDICATE Security.EmilyTimeFilterPredicate()   
ON dbo.Client 
WITH (STATE = ON);  

GO

EXECUTE AS USER = 'Emily';

SELECT *
FROM   dbo.Client;

REVERT;

SELECT *
FROM   dbo.Client; 

GO

DROP SECURITY POLICY EmilyTimeFilter ;

DROP TABLE dbo.Client

DROP USER Emily

DROP FUNCTION Security.EmilyTimeFilterPredicate

DROP SCHEMA Security;