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
+
+--------------------------+

---

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...

---

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

---

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/

---

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

---

---