SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Restricting users to data in a database.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

siportal
Starting Member

5 Posts

Posted - 04/29/2008 :  09:13:00  Show Profile  Reply with Quote
I have users logging into a sql database using sql accounts. I've created below a simplified example of three tables in my 100+ table database. I have a sql table that holds extra information about the sql users and two data tables. I would like to restrict which records the user has access to in the manufacturer and automobile tables. I know I can add the UserID column to these two tables, and then add the appropriate userID into the UserID column of the manufacturer and automobile tables. Hence records in the automobile table with userid X will not be seen by userid Y. My question is, how can I possibly avoid modifying 1000+ sql queries to reference the UserID column? It would save me a lot of time if I can filter the results a user gets based on thier sql login/userid.

+--------------------------+
+ USERS TABLE
+
+ USERID int
+ Name varchar
+ Telephone varchar
+
+--------------------------+

+--------------------------+
+ MANUFACTURER TABLE
+
+ MANID int
+ Name varchar
+
+--------------------------+

+--------------------------+
+ AUTOMOBILE TABLE
+
+ AUTOID int
+ Name varchar
+
+--------------------------+

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 04/29/2008 :  09:44:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't think you can do record level security with an easy method.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  15:02:57  Show Profile  Visit m_k_s@hotmail.com's Homepage  Reply with Quote
quote:
Originally posted by siportal

I have users logging into a sql database using sql accounts. I've created below a simplified example of three tables in my 100+ table database. I have a sql table that holds extra information about the sql users and two data tables. I would like to restrict which records the user has access to in the manufacturer and automobile tables. I know I can add the UserID column to these two tables, and then add the appropriate userID into the UserID column of the manufacturer and automobile tables. Hence records in the automobile table with userid X will not be seen by userid Y. My question is, how can I possibly avoid modifying 1000+ sql queries to reference the UserID column? It would save me a lot of time if I can filter the results a user gets based on thier sql login/userid.

+--------------------------+
+ USERS TABLE
+
+ USERID int
+ Name varchar
+ Telephone varchar
+
+--------------------------+

+--------------------------+
+ MANUFACTURER TABLE
+
+ MANID int
+ Name varchar
+
+--------------------------+

+--------------------------+
+ AUTOMOBILE TABLE
+
+ AUTOID int
+ Name varchar
+
+--------------------------+




DBMSs are capable of doing what you want, but it would be a nightmare for maintenance (basically not feasible).

If you really need this, you need to build an application around your db and restrict access to your app by user.

Depending on complexity, I think you're looking at a 1-6 month development cycle.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 04/29/2008 :  15:08:32  Show Profile  Visit spirit1's Homepage  Reply with Quote
see if this helps you in any way:
http://weblogs.sqlteam.com/mladenp/archive/2006/08/12/11153.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  15:11:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
This is called "row level" security. It can very easy to do, it depends on the specifics.

Simply deny your users access to the tables directly, and only allow access to Views. In the Views, you would simply join to the table of permissions so that it filters the results based on the current user logged in.

Here are some links for you:

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/29/2008 15:12:52
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  17:20:50  Show Profile  Visit m_k_s@hotmail.com's Homepage  Reply with Quote
row level, column level security is possible, but i've only seen features like that used for protecting data from developers.

i'm not sure how many users you have, but the pain of maintaining these restrictions will directly correspond to your number of users. i don't think that is the intended use of the DBMS feature.

also, i'm not sure that adding user security information to a table follows good relational principals.

i'd build a desktop/web interface for your app.
you need a

function table
id
name
desc

permission table
function_id
user_id

the complex permission requirements you likely have can be granularly managed and audited.

you can even add a role table which gives certain user categories default capabilities.

this architecture will allow you to leave all your queries alone. you will control the access to your queries through an app.

just because it's possible to do row/cell level permissions doesn't mean you should... you could do your taxes in sql server, but it's probably a better idea to user turbotax or taxcut...
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  18:25:29  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by m_k_s@hotmail.com

row level, column level security is possible, but i've only seen features like that used for protecting data from developers.

i'm not sure how many users you have, but the pain of maintaining these restrictions will directly correspond to your number of users. i don't think that is the intended use of the DBMS feature.

also, i'm not sure that adding user security information to a table follows good relational principals.

i'd build a desktop/web interface for your app.
you need a

function table
id
name
desc

permission table
function_id
user_id

the complex permission requirements you likely have can be granularly managed and audited.

you can even add a role table which gives certain user categories default capabilities.

this architecture will allow you to leave all your queries alone. you will control the access to your queries through an app.

just because it's possible to do row/cell level permissions doesn't mean you should... you could do your taxes in sql server, but it's probably a better idea to user turbotax or taxcut...



He doesn't want permission by function. He also doesn't want column level permissions. He wants permissions on the DATA -- this is called row-level permissions. That has nothing to do with functional permissions, or permissions on database objects like columns.

It is very easy to create and to set up and certainly doesn't violate any relational principles to have a table that relates users to entities that they have permissions to view, or edit, or whatever the case may be.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/29/2008 18:26:00
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 04/29/2008 :  18:33:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
I agree with Jeff. We had this implemented in one of our applications that was used by the County of San Diego (government job). Access was provided via views that had the necessary WHERE clause on them. Each organization within CoSD got access to their own data by having their own view setup. Since the number of organizations rarely changed, it was very easy to maintain.

If you are going to provide the access to users, then I'd suggest doing this via roles.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 04/29/2008 18:34:49
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  18:46:51  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Tara -- it actually can be even easier than that; you don't even need sets of views per role or group.

Suppose you have a table of LogIns with a PK of LogInID, and a table of Counties with a PK of CountyID. If you create a table called "CountyLogins" with a pk of (CountyID, LoginID) that indicates the counties that can be accessed by which logins, you can just join to that table in your Views and filter them where LoginUser = SUSER_SNAME(). Then, depending on who logs into the system, if all they can access are the Views, all they ever see are the counties they have permissions for, and they can all use the same set of views.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 04/29/2008 :  18:56:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
This was two jobs ago and over 10 years ago. We were on SQL Server 6.5. I don't remember the exact logistics of how it was handled just the idea that it was with views and WHERE clauses.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  20:08:51  Show Profile  Visit m_k_s@hotmail.com's Homepage  Reply with Quote
Inevitably what happens is you will get a requirement that doesn't fit the simple mold and the whole thing breaks down.

Row A
Row B
Row C

Users 1-100

User 1 can look at Row A.
User 2 can look at Row B.
User 3 can look at Row A and B.
...


Wait, now User 3,7,12 can look at 5, but they can't update it.

Oh wait, User 78 can create Row C, and then User 54 and 52 can't look at it.

Oh wait, if User 44 changes row A, then User 35 can't look at it anymore...

Oh wait, everyone can look at Row E.

Good luck managing all the different permissions and roles you can make.

Yeah - I'm sure row-level permissions work real well for application type functionality...

Yeah - someone should explain to the doofuses at Amazon and Ebay that they shouldn't have applications around their database, they should give each user row-level locks around what each user can do or cannot do...

siportal - beware people who try to fit your requirement into the tool they happen to be good with. app developers often try to own/manage things in their application that should be left to the database. and apparently db people try to do the same...

sometimes the job requires a hammer, sometimes a wrench...


quote:
Originally posted by jsmith8858

quote:
Originally posted by m_k_s@hotmail.com

row level, column level security is possible, but i've only seen features like that used for protecting data from developers.

i'm not sure how many users you have, but the pain of maintaining these restrictions will directly correspond to your number of users. i don't think that is the intended use of the DBMS feature.

also, i'm not sure that adding user security information to a table follows good relational principals.

i'd build a desktop/web interface for your app.
you need a

function table
id
name
desc

permission table
function_id
user_id

the complex permission requirements you likely have can be granularly managed and audited.

you can even add a role table which gives certain user categories default capabilities.

this architecture will allow you to leave all your queries alone. you will control the access to your queries through an app.

just because it's possible to do row/cell level permissions doesn't mean you should... you could do your taxes in sql server, but it's probably a better idea to user turbotax or taxcut...



He doesn't want permission by function. He also doesn't want column level permissions. He wants permissions on the DATA -- this is called row-level permissions. That has nothing to do with functional permissions, or permissions on database objects like columns.

It is very easy to create and to set up and certainly doesn't violate any relational principles to have a table that relates users to entities that they have permissions to view, or edit, or whatever the case may be.

- Jeff
http://weblogs.sqlteam.com/JeffS


Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  21:26:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by tkizer

This was two jobs ago and over 10 years ago. We were on SQL Server 6.5. I don't remember the exact logistics of how it was handled just the idea that it was with views and WHERE clauses.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/



Makes sense, I think this was harder to do back in the old days ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  21:36:19  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
m_k_s -- you do understand that "row level" security doesn't mean that you are securing things by row #, right? We are relating entities in our data. Nothing you wrote has anything to do with what the OP is doing or is trying to do.

I do like your argument though. It is very easy to apply to almost any situation. e.g.,

"Can we put this logo on the home page of the web application?"

Your response:

"No! You can't have a logo. Why not? Sure, you want a simple graphic now. But then, tomorrow, you want text. Or, the next day, you want a logo AND some text. Or a BRAND NEW logo. Or two logos side by side! Or, a bitmap that is 2000x2000 and is 26MB in size! Think of the network traffic!! What's next, an entire MOVIE as your logo! Where does it end?? Who deals with that! Who will update and maintain it? Not you, not me, no one!!! Who would direct this 'logo movie'? Who will star in it? What if it gets bad reviews? Are there copyrights to worry about!!? It's a giant mess. Now maybe we need to buy new servers now to handle all the data and the traffic and the users who will maintain this fancy logo, and suddenly the system is hacked and the logos are lost or changed and then all of our data is stolen and then where are we? So ... NO! You cannot have a logo, you don't want a logo!"

(Oh..by the way: I am an application developer...)

quote:
Originally posted by m_k_s@hotmail.com

Inevitably what happens is you will get a requirement that doesn't fit the simple mold and the whole thing breaks down.

Row A
Row B
Row C

Users 1-100

User 1 can look at Row A.
User 2 can look at Row B.
User 3 can look at Row A and B.
...


Wait, now User 3,7,12 can look at 5, but they can't update it.

Oh wait, User 78 can create Row C, and then User 54 and 52 can't look at it.

Oh wait, if User 44 changes row A, then User 35 can't look at it anymore...

Oh wait, everyone can look at Row E.

Good luck managing all the different permissions and roles you can make.

Yeah - I'm sure row-level permissions work real well for application type functionality...

Yeah - someone should explain to the doofuses at Amazon and Ebay that they shouldn't have applications around their database, they should give each user row-level locks around what each user can do or cannot do...

siportal - beware people who try to fit your requirement into the tool they happen to be good with. app developers often try to own/manage things in their application that should be left to the database. and apparently db people try to do the same...

sometimes the job requires a hammer, sometimes a wrench...


quote:
Originally posted by jsmith8858

quote:
Originally posted by m_k_s@hotmail.com

row level, column level security is possible, but i've only seen features like that used for protecting data from developers.

i'm not sure how many users you have, but the pain of maintaining these restrictions will directly correspond to your number of users. i don't think that is the intended use of the DBMS feature.

also, i'm not sure that adding user security information to a table follows good relational principals.

i'd build a desktop/web interface for your app.
you need a

function table
id
name
desc

permission table
function_id
user_id

the complex permission requirements you likely have can be granularly managed and audited.

you can even add a role table which gives certain user categories default capabilities.

this architecture will allow you to leave all your queries alone. you will control the access to your queries through an app.

just because it's possible to do row/cell level permissions doesn't mean you should... you could do your taxes in sql server, but it's probably a better idea to user turbotax or taxcut...



He doesn't want permission by function. He also doesn't want column level permissions. He wants permissions on the DATA -- this is called row-level permissions. That has nothing to do with functional permissions, or permissions on database objects like columns.

It is very easy to create and to set up and certainly doesn't violate any relational principles to have a table that relates users to entities that they have permissions to view, or edit, or whatever the case may be.

- Jeff
http://weblogs.sqlteam.com/JeffS






- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/29/2008 21:55:09
Go to Top of Page

siportal
Starting Member

5 Posts

Posted - 04/29/2008 :  21:54:53  Show Profile  Reply with Quote
Thanks all. To clarify, UserX will never (EVER) have access to the rows used by UserY.

Based on this, I think I'll do some research on "row level" security.
Go to Top of Page

siportal
Starting Member

5 Posts

Posted - 04/29/2008 :  21:57:02  Show Profile  Reply with Quote
FYI: I found this link based on the info you guys provides. It describes exactly what I am trying to accomplish. Thanks again.

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/29/2008 :  22:02:01  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Ah, yes, I posted that link earlier for you.

Good luck, let us know if you need any help.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000