2

I need to run queries as a "user" which is a record in a table, with permissions based on a record value of that user.

I have a database with a tUsers Table, such as:

ID    Username       Email         Role
1      Pieman       mail.com       Admin
2     Cakedude     mail.co.uk   Receptionist
3      Muffin        gh.com        Other

I need to have it so only "users"/records with "Role" of "Admin" can view and edit the table, and "Receptionist" view it etc.

I know of and use GRANT for permissions, but don't know how to run a query as a user based on a table record and to have the permission only GRANTED if that users' role is "Admin"

So if I have:

USE DB1;
GRANT SELECT ON OBJECT::tUsers TO Admins;
GO 
SELECT * FROM tUsers

How do I make that run as say tUser with ID 1, and the GRANT if the users' role = "Admin"

I'm sure I've seen this done before.

I'm fairly new and still learning the correct terminology, so if this is a duplicate question, or is essentially just describing an sql Function sorry.

Vereonix
  • 1,341
  • 5
  • 27
  • 54
  • I want to see answers on this also. This is a future enhancement to one of my projects. My thoughts are to have another table describing what authorities the various roles have and implementing the restrictions in the application (non-SQL programming language). – Alan Hoover May 08 '15 at 17:59
  • I hope understand your question. You must create inside Database a user and logins in your server and control all permissions based in your table. Refer https://msdn.microsoft.com/en-us/library/aa337545.aspx – Beto May 08 '15 at 18:37
  • I get that, its just this is for a uni question, and the scenario says "Receptionists can’t see medical records", and this is in relation to being implemented in a website for access. So I make a similar User table and roles like shown in my question. Assuming I could do permissions based on them. It doesn't even seem like I can make the grant based on a variable I could post to it based on the users web session. The scenario and criteria seems really disjointed from reality, we don't need a website, just show sql. – Vereonix May 08 '15 at 18:42
  • Ohh. It looks to me like they just want you to create users. grant them specified access. let your website authenticate the users by logging in to the database as the user. Then just run your queries, you are the user in question with only the appropriate authorities. catch your "this user does not have access to that" errors. – Alan Hoover May 08 '15 at 20:18
  • As the system needs to hold a lot of people, would it make sense to just make a user for each "Role", and the permissions accordingly. So to run some logic past you, the user would login to the website, site can retrieve their role, then sign into the Database using the appropriate Role User behind the scenes. Theres no glaring issues with that right? – Vereonix May 08 '15 at 20:39
  • 1
    doing that you will still have to authenticate the users/passwords separately instead of letting the DB handle it. That would probably require hard coding passwords for all the role "users". – Alan Hoover May 08 '15 at 21:42

1 Answers1

1

I don't think you can grant or revoke permissions to users in your own user table. However you can of course restrict queries based on your own user table.

One solution is to do it in your application. Verifier permissions before you do anything for him/her.

Another solution is to use stored procedures which take user id as parameter and do the checking for you in a central place.

The third one is to user parameterized views where you filter out entries one user can't access.

There are other solutions but the basic idea is you need somehow check permissions instead asking dBm server to do it for you.

Tim3880
  • 2,563
  • 1
  • 11
  • 14