CODEWITHSUNDEEP
sqlsql-server
Riz
Riz

Reputation: 6686

Define relationship to tables as a whole

I need to define a table which basically will contain an Id for a user, and a second column which will list names of tables to which the user has access. I can't think of anyway to define any relationships here in case the original table names change. All the logic will be at the application level. However, I would like to be able to define some sort of constraints. How can I do this? Also, I am open to advice regarding any other way to do this.

Upvotes: 0

Views: 60

Answers (3)

sallushan
sallushan

Reputation: 1147

Since users are NOT SQL Server Logins, therefore, I guess you can use the DDL trigger to monitor table rename where you can change the table name in your custom security table. But I don't know if you can throw exception within this trigger to prevent table rename (simulating some type of constraint). Also it would be better if you store the table name in each line rather saving comma separated table names in 1 field.

If you can utilize SQL Logins then the Gordan's solution is also applicable, but sometimes you cannot create SQL Logins, in-case if you have different application databases along with hundreds of thousands of users.

Upvotes: 0

Greg Low
Greg Low

Reputation: 1586

Here's a detailed code example of how to achieve this using RLS

USE tempdb;
GO

CREATE TABLE dbo.OKTable
(
    OKTableID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_OKTable PRIMARY KEY,
    SecuredInfo varchar(100)
);
GO

INSERT dbo.OKTable (SecuredInfo)
VALUES ('Very'), ('Secret'), ('Stuff');
GO

CREATE TABLE dbo.NotOKTable
(
    NotOKTableID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY,
    SecuredInfo varchar(100)
);
GO

INSERT dbo.NotOKTable (SecuredInfo)
VALUES ('Other'), ('Important'), ('Things');
GO

CREATE SCHEMA [Security] AUTHORIZATION dbo;
GO

CREATE TABLE [Security].PermittedTableUsers
(
    PermittedTableUsers int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_Security_PermittedTableUsers 
        PRIMARY KEY,
    UserName sysname,
    SchemaName sysname,
    TableName sysname
);
GO

INSERT [Security].PermittedTableUsers (UserName, SchemaName, TableName)
VALUES (N'dbo', N'dbo', 'OKTable');
GO

ALTER FUNCTION [Security].CheckUserAccess
(
    @SchemaName AS sysname,
    @TableName AS sysname
)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS CheckUserAccessOutcome
           WHERE EXISTS (SELECT 1 FROM [Security].PermittedTableUsers AS ptu
                                  WHERE ptu.UserName = USER_NAME()
                                  AND ptu.SchemaName = @SchemaName
                                  AND ptu.TableName = @TableName);
GO

CREATE SECURITY POLICY OKTableAccessFilter 
ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'OKTable')
ON dbo.OKTable  
WITH (STATE = ON); 
GO
CREATE SECURITY POLICY NotOKTableAccessFilter 
ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'NotOKTable')
ON dbo.NotOKTable  
WITH (STATE = ON); 
GO

SELECT * FROM dbo.OKTable;
SELECT * FROM dbo.NotOKTable;
GO

It's described more fully in this link:

https://blog.greglow.com/2019/10/10/sql-how-to-control-access-to-sql-server-tables-by-entries-in-another-table/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270493

I am really confused. Doesn't the grant command do exactly what you want? This assumes that the operations you want are database operations.

If you have a more customized set of operations, then you can keep track of table name changes via DDL triggers.

Upvotes: 1

Related Questions