General discussion


Processing access validation

By scantoria ·
I am building a database application. The database contains data for different clients. Depending on the level of access, the user can only view what is allowed. How do I process this access validation? I am also not sure how to construct the user table.

I am looking suggestions before I make the whole process too overly complicated.


Steve C.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

A few ways

by Tony Hopkinson In reply to Processing access validat ...

You can put column level permissions on a table. So if userB doesn't have it, it doesn't see it. ie you could show a staff table but hide salary except for payroll and HR.

Careful with that one in your application code, use select * from ... if you name the column in the code and permission is not granted you'll get an exception.

Row level security usually done via a stored procedure or a view, you could do it with raw sql but that will get painful after a while.

Don't allow access to the tables then write views one for each type of user. If you match the type to the schema.

MyData.Payroll.v_Staff ...
MyData.Manager.v_Staff ...

the generic way is toadd a usermask column to each table, pass the user id into the view or sp and then only return data where userid and mask match up.

Prefer the role based mathods myself.

Related Discussions

Related Forums