data security

Question

data security

ahughesdba

Grasshopper

Points: 24
More actions
May 2, 2003 at 6:02 am

#80012

hi, i'm a devleoper/dba in oracle and have a requirement to convert our oracle data security on to a sql server database. what i mean by data security is this -
user fred logs in and when selecting from table shoes, only sees shoes that his user is allowed to see.
user bert logs in and when selecting from the same table, only sees shoes that his user is allowed to see.
now in oracle we can do this by using policies. this works by setting up a variable (for want of a better word) against the user with their security code, that is defined by a logon trigger. when they access a table with a policy against it, it puts an additional where clause on the end of the select along the lines of "and securitycode = <user security code>"
this method is very nice, becuase it doesn't add to performance, i don't have to make any changes to application code and it works for any program that accesses the database. is there something similar in Sql Server?
thanks for any help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dgermundson Hall of Fame Points: 3175 More actions · Answer 1

Do you have the user name field on each table that you need to filter? If so, you could use a stored procedure that passes in that username when populating data. I'm not sure how to setup security on specific rows. Partitioned views are recommended but that might be tough with alot of users.

Darren

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

It sounds like you're interested in doing something similar to a row-level security solution. If that's the case, check out this article from SQL Server MVP Vyas Kondreddi:

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

ahughesdba Grasshopper Points: 24 More actions · Answer 3

quote:
It sounds like you're interested in doing something similar to a row-level security solution. If that's the case, check out this article from SQL Server MVP Vyas Kondreddi:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

ahughesdba Grasshopper Points: 24 More actions · Answer 4

this solution is not adequate as the main application we use needs to be 99% non-database specific, also we have several other applications which would have to change - this is quite simply not going to happen.

what i need is :

1. user x logs in to the database and they retrieve their security code from a table on the database.

2. an additonal column is held against every table that requires security.

3. when user x accesses one of these tables an additonal where clause is added (by the database, not by addtional views/procedures or code) along the lines of "and SecurityCode = <user x security code>"

I don't think I'm going to get this solution from sql server so i'll have to wait until future releases.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

No, SQL Server will not do this natively. However, such row-level security is only present in Oracle and Sybase (most recent version) so far as I am aware.

You can create a security database in which to store your security tables. Unfortunately, you would have to add columns manually to the tables in your actual databases that need the security checks (basically doing what it sounds like Oracle is doing for you). Then of course, you would have to make the appropriate views, etc. Unfortunately, if you can't do the additional code, SQL Server and DB2, for that matter, are out.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

lambje SSCrazy Points: 2085 More actions · Answer 6

Since you don't want to have to limit access via stored procs you could try this approach (my assumption is that you only have one owner for a record):

1) Add the security column to the tables requiring it (in my example, sysuser)

2) Rename the table

3) Create a view to the renamed table using the old tables name as such:

CREATE VIEW old_table_name AS

SELECT col1, col2... coln

FROM new_table_name

WHERE sysuser = system_user

If more than one user can access a given record then I would set up a security table that maps system_user to a security code and assign that code to the new table column.

Ian Yates SSCoach Points: 19738 More actions · Answer 7

I've got my application making views of the data called (for example)

fred.dataView

joe.dataView

Thus when the user is logged in running ad-hoc queries (mainly for reporting purposes with Crystal) Fred sees only rows he is allowed to see, and similarly for Joe.

The views could be as simple as

Select DV.*

from tblDataView DV

inner join Permissions P on (DV.ID = P.ID)

Where P.UserID = {this part changes}

and P.Granted = 1

That way you can have a permissions table with UserID, DataView Primary Key (ID) and Granted bit field.

The queries in the reports don't need to change as they just use the view DataView, which gets resolved correctly depending on the current user.