January 14, 2010 at 1:16 pm
Been reading about implementing row level security in SQL 2005. Haven't used the XML data type and am trying to read up on it. Has anyone used XML data type to describe a single record for row level security? What are the pros/ cons, I suspect performance could be an issue.
For example
Table1 (stores the record that needs security)
ID int
Data varbinary(max)
Table 2 (has one XML row to describe the permissons on the row in Table 1)
ID int
Permission (xml) -- structured by permission type listing the group with access
Obviously a solution is to implement tagging, or single row for each permission but want to look at XML to see if that's an option to cut down on the amount of rows.
January 14, 2010 at 1:19 pm
You probably could use XML, but it'll almost certainly end up with worse performance than a purely relational table.
Why not a table that lists permissions and rows? Could be set up with a relatively simple many-to-many relationship.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2010 at 1:22 pm
Your correct, that was the first choice. Looking at the fact we would have millions of records we were trying to think out of the box to see if there was a way to simplify it and have a smaller data set.
I totally understand how indexes store and retreive data and how that impacts queries. I don't have a good enough knowledge on how the indexes on XML work. I'll do more reading on that so I can picture how the queries would run. For example what if I need to query a value in every one of the rows in that XML table if I didn't have the row id.
January 15, 2010 at 7:35 am
XML indexes work by creating a relational model of the data and storing that in the index.
In other words, they do a behind-the-scenes transformation into relational structure. Because they can't understand the purpose of the data, they won't generally do as good a job of this as a trained DBA. So, you're usually better off just building a relational model and storing it that way. Does a better job of the same thing.
The places where that's not true are generally ones where you'll have things like different rows having different columns. Or where the data is used as XML by the applications accessing the database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply