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 |