This blog is subject the DISCLAIMER below.

Sunday, November 30, 2008

How to restrict some users to update some tables under some criteria?

Q. How to restrict some users to update some tables under some criteria?

A. If you administer SQL server database, you know that you can grant\deny user updating some tables, but what if you wish to grant\deny user updating some tables when just column equal some value, scenario like that

Table Course (ID, CourseName)

Table Student (ID, Name, CourseID, ….)

Sure, the most common example we give when we teach someone the introduction of databases especially relationships, so if you don’t know it; in brief Student has right to have some courses and the relationship is one-to-many; course-took by- many students.

1

Going on our scenario, data entry1 responsible for insert\update\delete all students take C# course, data entry2 responsible for insert\update\delete all students take Java course

2 3

Clear, isn’t it?

What you should do is to create 2 users dataentry1, and dataentry2 and to create 2 database roles CSharpController, and JavaController.

To create user using SQL Server Management Studio [URL]

To create Database Role using SQL Server Management Studio [URL]

Assign dataentry1 to CSharpController role, and dataentry2 to JavaController, you can assign more users to proper role as you see.

Next step; when user does insert\update\delete operation on Student table, users belong to CSharpController have right if and only if CourseID = 1, and the same for JavaController if and only if CourseID = 2

So, let’s write some code or script; yes, trigger to do those validations

create trigger ManageDML_Student
on student
for insert, delete, update
AS

--here to get column value and assign it variable to check its value later

IF IS_MEMBER ('CSharpController') = 1 && --value == 1--
  commit transaction
else
rollback transaction

Thanks :)


Acknowledge
I'd like to thank Mohamed Abd ALLAH Hewedy, he helped me through this problem

3 comments:

mhewedy said...

Very greate R,
Thank you man
;)

that remember me with a friend of mine that lack in problem similar to it.
he was a DBA in my organization

;)

Ramy Mahrous said...

loooooooo walahy nsyt aktb acknowledge 2nk helped my a lot, I'll do right now :)

mhewedy said...

Looooooooooooool R,
ha ha ha,
I never post my comment to ask you to do what you have done, R.


" I am just an analyist, i don't bother myself in coding details, i just give Ideas " by: H hewedy :)