April 27, 2011 at 6:19 am
Hello. For db security, I understand the following:
Currently this is on sql 2005 going to 2008 r2 shortly, so if there are changes that are available only in 2008 r2, I can wait to implement then.
1. Have a database role, which contains a bunch of users
2. Have a database user/login that has select/insert/update/delete on all tables necessary
3. Have all stored procedures run with EXECUTE AS the above user
4. Have all functions with EXECUTE AS the above user
5. Role has Execute/select on all stored procedures/functions
However, this all falls apart with an in-line tvf. What do I do if I want to continue to use the above security plan, but have an in-line tvf? It seems so neat and clean UNTIL..that darn in-line tvf, should/can I use an EXECUTE AS OWNER on the inline tvf?
Also, please if I am completely going the incorrect way, please let me know and send me in the correct direction!
I appreciate any assistance in this. Thank you!
April 27, 2011 at 12:48 pm
Ukon (4/27/2011)
Hello. For db security, I understand the following:Currently this is on sql 2005 going to 2008 r2 shortly, so if there are changes that are available only in 2008 r2, I can wait to implement then.
1. Have a database role, which contains a bunch of users
2. Have a database user/login that has select/insert/update/delete on all tables necessary
3. Have all stored procedures run with EXECUTE AS the above user
4. Have all functions with EXECUTE AS the above user
5. Role has Execute/select on all stored procedures/functions
However, this all falls apart with an in-line tvf. What do I do if I want to continue to use the above security plan, but have an in-line tvf? It seems so neat and clean UNTIL..that darn in-line tvf, should/can I use an EXECUTE AS OWNER on the inline tvf?
Also, please if I am completely going the incorrect way, please let me know and send me in the correct direction!
I appreciate any assistance in this. Thank you!
Using EXECUTE AS in all your stored procedures as a standard technique means you'll be breaking the ownership chain and therefore will take a slight performance hit on every database call when compared to calls with an unbroken ownership chain. Cumulatively, on a busy system, it could amount to a non-trivial performance hit. Microsoft recommends taking advantage of ownership chaining wherever possible and only veering away from it in systems with granular or complex * security requirements.
* I cannot find the article that stated it in specific terms, if I find it I'll post back
From http://msdn.microsoft.com/en-us/library/ms188676.aspx
Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.
Unless you have a compelling reason to use EXECUTE AS on all your procedures consider keeping it simple from the outset: http://weblogs.sqlteam.com/dang/archive/2008/02/03/Keep-Schema-and-Ownership-Simple.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 1:21 pm
Hello again, and thank you for your reply. I see your point, however, there are also recommendations that data should only be accessible to users via parameterized stored procedures and that the users have no direct ability to access to the underlying tables. (And, by "users", here, I mean users accessing this data through an internet application not locally or through SSMS). I will also search for the articles and post links. Therefore, the chain would have to be broken in this case. I am open to any suggestions, however, it makes sense to me that users should not have direct access to the underlying tables and by keeping ownership chaining, I believe it would be possible for a "bad" user to perform unwelcome actions against the data. However, if when using aspnet applications, there is no direct access to the tables, all data access is performed through the data access layer using parameterized stored procedures, it would be more difficult to hack into the tables and/or it would be more difficult for a "bad" user to perform sql injection, etc. Is this incorrect?
I certainly dont want to seem argumentative, but there are so many differing views and if I am incorrect in the above, I would like to understand very clearly.
Again, thank you for the answers/discussion. Security can be very complex and the best direction to take can be difficult to find! I am more concerned about the internet users than any local users I might have.
April 27, 2011 at 1:39 pm
Ukon (4/27/2011)
Hello again, and thank you for your reply. I see your point, however, there are also recommendations that data should only be accessible to users via parameterized stored procedures and that the users have no direct ability to access to the underlying tables.
Ownership chaining provides you what you are describing in terms of allowing data access through procs while at the same time preventing direct table access to the tables accessed by those procs...plus you get the performance benefit afforded by allowing the database engine to skip checking permissions when the ownership chain is unbroken.
For example, let's say you have a table named SchemaA.TableA and that SchemaA is AUTHORIZED BY dbo. Then let's say you create a procedure named SchemaA.SelectSomeStuffFromTableA that accepts an ID and performs a simple SELECT against SchemaA.TableA, and then let's say you GRANT DatabaseUserA EXECUTE permission on the procedure. DatabaseUserA will NOT be able to log into the database and select data directly from SchemaA.TableA, however, they WILL be able to retrieve data from that table via the execution of the stored procedure you provided. That's what you want, correct?
That is the best example I can give without going too far into it and this is not the place to do a deep-dive. I would urge you to really read the two articles I provided links for carefully. You;re describing a very common scenario that can be done using ownership chaining and Dan's blog post that I linked to lays out a road map on how to do it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 6:06 pm
I will do as you suggest! And review the articles and your example and apply. Thank you for your assistance. Hopefully I have it all straight.
April 28, 2011 at 6:30 am
Good Morning:
Ok, I read the articles you posted. I have one issue. In the article: http://www.databasejournal.com/features/mssql/article.php/10894_2246271_2/Managing-Users-Permissions-on-SQL-Server.htm
there was the following statement:
The DENY statement is used to deny a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. You can use the DENY statement to deny both statements and objects permissions.
The following example denies the user Alex SELECT permissions to the authors table:
DENY SELECT ON authors TO Alex
So I did the following in my 2008 R2 database. I DENIED select on TableA to RoleA. I GRANTED SELECT on a VIEW which includes TableA to RoleA. UserA, a member of RoleA can no longer select from the TableA, however, UserA can still select from the view, which includes TableA.
That makes no sense. If a table is denied to a role and/or a user, and that table is used in a view, then the select on the view should fail but does not and that is SQL SERVER doing that, because the assumption is made that if UserA has permissions to select from the view then UserA has permissions on the underlying tables, that is just wrong. That just seems like a huge security hole to me. If permissions are DENIED on an object to a User or Role, then it should not matter where that object is used, User/Role should not be able to retrieve data from it.
I am guessing that the following from http://msdn.microsoft.com/en-us/library/ms188676.aspx is governing the above:
1.Alex executes SELECT * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
2.The July2003 view requires information from the SalesXZ view. SQL Server checks the ownership of the SalesXZ view. Because this view has the same owner (Mary) as the view that calls it, permissions on SalesXZ are not checked. The required information is returned.
I guess, I just disagree fundamentally with MS and SQL Server, object ownership should have nothing to do with who/what can access an object. The permissions of that object should take precedence, not ownership. From the above explanation: Mary might own TableA and ViewA (ViewA includes TableA), however, if Tom is denied permissions to TableA but by accident or on purpose gets permissions to ViewA which includes TableA, then Tom's select on ViewA should fail.
What am I misunderstanding here? I am sure that I am missing something that makes perfect sense at least to someone <lol>
Thanks again for your patience!
April 28, 2011 at 7:21 am
Ukon (4/28/2011)
Good Morning:Ok, I read the articles you posted. I have one issue. In the article: http://www.databasejournal.com/features/mssql/article.php/10894_2246271_2/Managing-Users-Permissions-on-SQL-Server.htm
there was the following statement:
The DENY statement is used to deny a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. You can use the DENY statement to deny both statements and objects permissions.
The following example denies the user Alex SELECT permissions to the authors table:
DENY SELECT ON authors TO Alex
So I did the following in my 2008 R2 database. I DENIED select on TableA to RoleA. I GRANTED SELECT on a VIEW which includes TableA to RoleA. UserA, a member of RoleA can no longer select from the TableA, however, UserA can still select from the view, which includes TableA.
That makes no sense. If a table is denied to a role and/or a user, and that table is used in a view, then the select on the view should fail but does not and that is SQL SERVER doing that, because the assumption is made that if UserA has permissions to select from the view then UserA has permissions on the underlying tables, that is just wrong. That just seems like a huge security hole to me. If permissions are DENIED on an object to a User or Role, then it should not matter where that object is used, User/Role should not be able to retrieve data from it.
I am guessing that the following from http://msdn.microsoft.com/en-us/library/ms188676.aspx is governing the above:
1.Alex executes SELECT * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
2.The July2003 view requires information from the SalesXZ view. SQL Server checks the ownership of the SalesXZ view. Because this view has the same owner (Mary) as the view that calls it, permissions on SalesXZ are not checked. The required information is returned.
I guess, I just disagree fundamentally with MS and SQL Server, object ownership should have nothing to do with who/what can access an object. The permissions of that object should take precedence, not ownership. From the above explanation: Mary might own TableA and ViewA (ViewA includes TableA), however, if Tom is denied permissions to TableA but by accident or on purpose gets permissions to ViewA which includes TableA, then Tom's select on ViewA should fail.
What am I misunderstanding here? I am sure that I am missing something that makes perfect sense at least to someone <lol>
Thanks again for your patience!
The primary performance benefit offered by ownership chaining is precisely why you are perceiving a security hole. It clearly states that "if the ownership chain is unbroken then further permissions checks are skipped". In the case of the view the ownership chain is unbroken so the DENY is never evaluated. IMHO either you know your security scheme and your object permissions or you don't. If you have a table with sensitive information then it should not have the same owner as the schema it exists in for this exact reason...so the ownership chain will always be broken and instead of working backwards with DENY you will always need to issue an explicit GRANT for users to access the table...a much more intuitive way to manage security than using DENY IMO.
If you're using ownership chaining, and you need to DENY specific users access to specific tables, then you may need to rethink your security strategy. Refer to my earlier comment about "granular or complex". The first step is deciding if ownership chaining is for you. I would argue that it would fit most systems quite well...but "most" is not "everyone".
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 11:13 am
Hello, I agree. I will evaluate my needs. Unfortunately I inherited this "thing" and am finally getting to review the non existent security on it and replace it with something that is workable. Now that I know, I can work with it. I come from where no means no <lol>
Again thank you so much for the advice! I really appreciate it and the clarifications and links.
April 28, 2011 at 11:17 am
Ukon (4/28/2011)
Hello, I agree. I will evaluate my needs. Unfortunately I inherited this "thing" and am finally getting to review the non existent security on it and replace it with something that is workable. Now that I know, I can work with it. I come from where no means no <lol>Again thank you so much for the advice! I really appreciate it and the clarifications and links.
Just remember, the answer is always "It Depends", even when you think it's "No". Welcome to SQL Server and good luck 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 11:47 am
Thank you! I am much clearer now on what is going to work. And I will watch for those tables that need more security and move them out of the chain so to speak.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply