How can I do conditional authorization on a column basis?

Question

I am using Microsoft SQL Server 2016+ There is a need to authorize some fields on the basis of userId.

Users operate on the same record on the screen. I run the same procedure in all cases.

I am getting the userId and roleId of the user. From the value of the roleId I know which records it should actually update.

For example, if role=0, it can update all columns, but if role=2, only dataField1 and dataField2 can be updated. If @role=3 it can update the dataField1,dataField3,dataField4 field.

I thought of making 3 update statements with if, is there a more efficient solution?

I have a table called "rolePrivileges". In this table, I keep the information in 2 columns as roleId, roleFields. (I can modify this table.I can't use it right now.)

If I do it with if in the procedure, I will have to update my procedure when the user changes privileges.

How can I do the most effective column authorization on a per user basis without updating the procedure (without changing the database)?

CREATE TABLE [dbo].[myTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dataField1] [varchar](10) NULL,
    [dataField2] [varchar](10) NULL,
    [dataField3] [varchar](10) NULL,
    [dataField4] [varchar](10) NULL,
    [userId] [int] NULL,
    [recTime] [datetime] NULL,
    [recUniq] [uniqueidentifier] NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recTime]  DEFAULT (getdate()) FOR [recTime]
ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DF_myTable_recUniq]  DEFAULT (newid()) FOR [recUniq]

create PROCEDURE myTableProcedure 
     @id int=0
    ,@dataField1 varchar(10) = '' 
    ,@dataField2 varchar(10) = ''
    ,@dataField3 varchar(10) = ''
    ,@dataField4 varchar(10) = ''
    ,@userId int=0
    ,@role tinyint=0 --1=All Column, 2=dataField1,dataField2, 3=dataField1,dataField3,dataField4 (not=2)
AS
BEGIN
    SET NOCOUNT ON

    --New Record
    if (@id=0)
    Begin
    insert into myTable (dataField1,dataField2,dataField3,dataField4,userId) values (@dataField1,@dataField2,@dataField3,@dataField4,@userId)
    End 
    --Updated
    else
    Begin
        update myTable set
             dataField1=@dataField1
            ,dataField2=@dataField2
            ,dataField3=@dataField3
            ,dataField4=@dataField4
        where id=@id
    End
END

--role=1 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=789
    ,@role=1
--role=2 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=123
    ,@role=2
--role=3 insert
execute myTableProcedure 
     @id=0
    ,@dataField1= 'A' 
    ,@dataField2= 'B'
    ,@dataField3= 'C'
    ,@dataField4= 'D'
    ,@userId=456
    ,@role=3

select * from myTable

id	dataField1	dataField2	dataField3	dataField4	userId	recTime	recUniq
1	A	B	C	D	789	2022-10-19 14:55:25.123	617ab880-6c35-49da-933a-a67f7efd269b
2	A	B	C	D	123	2022-10-19 14:55:25.123	78e9370c-99ee-486c-b816-16ead2b67850
3	A	B	C	D	456	2022-10-19 14:55:25.123	72a9850e-ec27-4950-96ef-cd0cb11694c5

--role=1 insert
execute myTableProcedure 
     @id=1
    ,@dataField1= 'E' 
    ,@dataField2= 'F'
    ,@dataField3= 'G'
    ,@dataField4= 'H'
    ,@userId=789
    ,@role=1
--role=2 insert
execute myTableProcedure 
     @id=2
    ,@dataField1= 'E' 
    ,@dataField2= 'F'
    ,@dataField3= 'G'
    ,@dataField4= 'H'
    ,@userId=123
    ,@role=2
--role=3 insert
execute myTableProcedure 
     @id=3
    ,@dataField1= 'E' 
    ,@dataField2= 'F'
    ,@dataField3= 'G'
    ,@dataField4= 'H'
    ,@userId=456
    ,@role=3

select * from myTable

id	dataField1	dataField2	dataField3	dataField4	userId	recTime	recUniq
1	E	F	G	H	789	2022-10-19 14:55:25.123	617ab880-6c35-49da-933a-a67f7efd269b
2	E	F	G	H	123	2022-10-19 14:55:25.123	78e9370c-99ee-486c-b816-16ead2b67850
3	E	F	G	H	456	2022-10-19 14:55:25.123	72a9850e-ec27-4950-96ef-cd0cb11694c5

fiddle

Answer 1

I think option 1 from this article might help (column level permissions): https://satoricyber.com/sql-server-security/sql-server-column-level-security/

Also, you can think of creating views and granting access to those views instead of the columns.