0

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)?

enter image description here

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

6
  • If the domain name exists in the authorization table, update it. Could it be something like fiddle
    – omerix
    Oct 19, 2022 at 16:13
  • 1
    Is there a reason regular permissions do not work for you? Oct 19, 2022 at 17:49
  • @SeanGallardy I get error message with "Column Level Permissions". User @role=2 can insert, select, delete dataField1,dataField2 field. But I don't want it to update. I can do this with if like in the link. But as the number of users and domains increases, it becomes difficult for me to manage. fiddle
    – omerix
    Oct 25, 2022 at 13:10
  • That's what groups were made to do along with database level roles. Oct 25, 2022 at 13:23
  • My goal is to make it work without changing the update string in the procedure. It gives the following error when it wants to update the non-permitted fields. My aim update other fields without an error message. "Msg 229, Level 14, State 5, Procedure myTableProcedure, Line 1 [Batch Start Line 4] The EXECUTE permission was denied on the object 'myTableProcedure', database 'test', schema 'dbo'."
    – omerix
    Oct 25, 2022 at 13:52

1 Answer 1

-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.

1
  • I get error message with "Column Level Permissions". User @role=2 can insert, select, delete dataField1,dataField2 field. But I don't want it to update. I can do this with if like in the link. But as the number of users and domains increases, it becomes difficult for me to manage. fiddle
    – omerix
    Oct 25, 2022 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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