Granting permission for a certain time of day

Question

Is it possible to provide user rights, for example, company business hours.

GRANT SELECT 
ON client
  <WHERE CONDITION>
TO Emily 

I know that this can be done with MySQL, where you can add a clause WHEREto the grant option so that you can add contextual conditions to it. However, I work with MS SQL Server, can this be done there?

Another solution would be to add an SQL job to add and remove rights at a specific time, but I don’t really like it, I would rather do it at the grant level.

Answer 1

I like @Turo's suggestion to use a view.

It can only consist of something like

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

, . , , .

2016 , , .

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;