Trusted (MLS) SQL Server?

Question

Trusted (MLS) SQL Server?

lionfan91

SSCrazy Eights

Points: 8794
More actions
March 26, 2004 at 9:30 am

#82480

I have a feeling I already know the answer is NO, but does anyone know if Microsoft offers an equivalent of Trusted Oracle (multi-level security)? Maybe if not in SQL 2K then in Yukon? For those not familiar with Trusted Oracle this is a good page for info -- http://cma.zdnet.com/book/oracle/chapters/0-672-22794-0/ch35/ch35.htm. I have a rather large IIS/SQL2K web app written for a USAF security program but may be forced to port it to a "trusted" solution so the powers-that-be will accredit it for a more wide-scale use. If I have to go to Trusted Oracle on a Sun Solaris platform, that basically spells complete re-write in another language and DB and a lot of headaches! TIA... Steve
March 29, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

This hour of the night my brain is only picking up about half the info. What is it you specifically are concerned with. SQL supports various security such most handled by Grant, Revoke, or Deny but you have to have be granted access to the Database itself first. What are you wanting to know or are you questioning generally (which mean I will have to read earlier in the day :crazy.

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 2

The concept of "trusted" or multi-level security deals with discretionary access controls (DAC) which is the grant, revoke, etc-level permissions users get on data objects as well as mandatory access controls (MAC) in which every piece of data is labeled with the level of permissions of users who can see it. The DB then in turn prevents users from seeing data they can't get to. For example, within the same table you might have some data labeled GENERAL VIEW which everyone can see and some labeled MANAGEMENT ONLY. The DB would allow users with management permissions to view it but to general users it would be like the data doesn't exist. My link to an Oracle document in the original post explains this a lot further (I'm just skimming the surface). This "trusted" concept is used a lot within government and probably will be a lot more given today's security situation. From what I know, Oracle is the only player right now. MS will be losing out on a growing market if they don't offer some sort of equivalent. BTW -- Sun also offers "trusted Solaris" where data at the OS-level is labeled and the OS prevents users from accessing data they don't have the right labels for. Windows will probably lose out to this market too...

Gary Andrews SSCommitted Points: 1639 More actions · Answer 3

I am not sure I understand your concerns, but please allow me to make the following comments.

I believe the following are true.

First, within SQL Server, what is not specifically allowed is denyed.

Having said that.

All logins are member of a role called "public". You have the ability to not allow "public" to do anything.

There is a "guest user account". This can be deleted and thus only allow "granted" access to a database.

You can create roles with specific security authorizations and assign logins to them.

There are fixed server roles.

There are fixed database roles.

There is an application role.

Where one login participates in multiple roles, if you "deny" a permission, etc., that "deny" applies to all roles that login participates in.

There are other ways to control security. i.e. Creating a view with selected columns and then allowing access to the view but not the table. And a number of other ways.

There are a number of other security issues this reply does not address. i.e. NT grouping of Logins, domain groups, etc.

While it seems security is something that can always be improved, I must admit that the ease of administration and flexibility that SQL Server offers seem strong to me.

GaryA

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

Out of the box, no, SQL Server doesn't have the trusted concept. You can, however, build one using row-based security models.

SQL Server MVP Vyas Kondreddi has written up a good article on how to implement row-level security within the database. Basically the idea is fairly straight-forward.

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

You have a table with the different levels of access control. You have another table which has the users and the access control levels they have. And you'll need to create additional tables or expand existing tables to tie the data to the levels. Force everything through views since views can contain the SQL to properly segment your data. If you use integer or numeric identifiers to tie your access controls to the other tables (say GENERAL VIEW = 1 and MANAGEMENT ONLY = 10), you could have the views show data based on numeric comparisons (User's access control label is >= access control label of the data).

Hopefully all that made sense.

K. Brian Kelley
@kbriankelley