March 4, 2012 at 10:55 pm
Comments posted to this topic are about the item Code Scanning
March 5, 2012 at 2:57 am
If I can explain a security problem simply then it will get a hearing, but getting normal testing done is a problem for me, let alone security testing. If I can get anyone to even cast a superficial look through the outer reaches of my application I am suitably grateful. I do mention that it is hard to test code you have written yourself but generally get ignored. I'll attempt to rationalise this as because my team's code is of good enough quality in general.
However we do put code through automated security testing. I guess this is a good thing, but it can easily miss gaping holes, and be overly assiduous with pretty thorough and watertight code (e.g. insisting on encryption for an already encrypted auth cookie). Essentially it is pretty easy to pass such tests without really being much more secure - but I certainly should not rule out that such testing has made our sites more secure.
March 5, 2012 at 6:03 am
Databases have a much smaller surface area for security breaches than do Web applications. For a database, you're basically concerned with how accounts, role membership, or object permissions have changed, and all these things can be easily scripted out and compared to a base line. Even domain group membership can be scripted out from within SQL Server using xp_logininfo.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 8:39 am
Eric M Russell (3/5/2012)
Databases have a much smaller surface area for security breaches than do Web applications. For a database, you're basically concerned with how accounts, role membership, or object permissions have changed, and all these things can be easily scripted out and compared to a base line. Even domain group membership can be scripted out from within SQL Server using xp_logininfo.
That's true, but scripting things out doesn't necessarily find holes. The intentions of what you want to secure, and limited access can be hard to understand over time, especially as access grows and groups/users grow.
March 5, 2012 at 8:57 am
Steve Jones - SSC Editor (3/5/2012)
Eric M Russell (3/5/2012)
Databases have a much smaller surface area for security breaches than do Web applications. For a database, you're basically concerned with how accounts, role membership, or object permissions have changed, and all these things can be easily scripted out and compared to a base line. Even domain group membership can be scripted out from within SQL Server using xp_logininfo.That's true, but scripting things out doesn't necessarily find holes. The intentions of what you want to secure, and limited access can be hard to understand over time, especially as access grows and groups/users grow.
Scripting permissions is one thing, but what I really meant to say was querying permissions for comparison to a baseline. One method I've used, to identify changes in account / role permissions, is to impersonate the account using EXECUTE AS, and then query the result of has_perms_by_name() for each object in the database into a sort of C-R-U-D-E matrix, which can then be compared against a previous baseline run. By using impersonation, I'm picking up both explicit, implicit, and inherited object permissions.
EXECUTE AS user = 'mycorp\approle';
GO
SELECT system_user sys_user_name
,*
FROM (
SELECT schema_name(schema_id) AS schema_name
,type_desc AS obj_type
,NAME AS obj_name
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'ANY') AS has_any
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'VIEW DEFINITION') AS can_view_definition
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'EXEC') AS can_exec
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'SELECT') AS can_select
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'INSERT') AS can_insert
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'UPDATE') AS can_update
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'DELETE') AS can_delete
,has_perms_by_name(schema_name(schema_id) + '.' + NAME, 'OBJECT', 'ALTER') AS can_alter
FROM sys.objects
WHERE parent_object_id = 0
) x
ORDER BY has_any DESC
,can_exec DESC
,can_alter DESC
,can_delete DESC
,can_update DESC
,can_insert DESC
,can_select DESC
,can_view_definition DESC;
GO
REVERT;
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 9:26 am
Our vendor has a very interesting testing process. They give us their applicatoin, and I, as the customer, have spent about 200 testing their product and found numerous bugs the last 6 months, which now makes the project a year late. Back when I was a consultant, our company tested our own application before it went out the door.
The latest fail: Vendor says "The tax feature which is used by all of our customers couldn't possibly be wrong."
The update I received today: "We will be sending out an update to fix the tax issue."
Oh well, since we are not getting charged by the hour, their loss on this project is probably 4 times beyond what they charged us for.
March 5, 2012 at 9:29 am
@eric, not knocking your process in terms of what it does. I'm more concerned with the issue of actually understanding what permissions are granted and potential holes. Comparing against a baseline is part of the issue, but potential problems with collisions of permissions, or potentially the wrong objects assigned.
Too often I see people assign public permissions, assign everyone rights with the datareader/writer roles, or use so many groups that a whole listing of permissions can obscure. Our tools for understanding the actual rights we have assigned v what we want to assign, aren't very good. We also don't have a good way of catching holes when we have multiple groups because the visualization isn't easy to understand.
March 5, 2012 at 9:56 am
Steve Jones - SSC Editor (3/5/2012)
@Eric, not knocking your process in terms of what it does. I'm more concerned with the issue of actually understanding what permissions are granted and potential holes. Comparing against a baseline is part of the issue, but potential problems with collisions of permissions, or potentially the wrong objects assigned.Too often I see people assign public permissions, assign everyone rights with the datareader/writer roles, or use so many groups that a whole listing of permissions can obscure. Our tools for understanding the actual rights we have assigned v what we want to assign, aren't very good. We also don't have a good way of catching holes when we have multiple groups because the visualization isn't easy to understand.
So you're saying that the project requirements should include a document or Excel sheet that explicitly list what accounts / roles should be deployed in production and what level of access to each object should be? That could be used for the baseline.
Perhaps the Policy-Based Management feature in 2008+ could be levered to insure that changes made to securables conform to some set of rules. That is start off with a clean slate, and then place constraints on how securables are modified over time.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 11:47 am
Eric M Russell (3/5/2012)
So you're saying that the project requirements should include a document or Excel sheet that explicitly list what accounts / roles should be deployed in production and what level of access to each object should be? That could be used for the baseline.Perhaps the Policy-Based Management feature in 2008+ could be levered to insure that changes made to securables conform to some set of rules. That is start off with a clean slate, and then place constraints on how securables are modified over time.
There should be something like this that explains what is needed, and then some way to check against it. An Excel sheet that is maintained makes sense. The issues I see are not usually during a deployment, but during alterations made over time as new requirements or access is needed.
Unfortunately permissions are very hard to to track and understand for many people. It's a simple model, but one that becomes complex quickly when we mix multiple roles, Windows groups, etc.
March 5, 2012 at 12:26 pm
Steve Jones - SSC Editor (3/5/2012)
Eric M Russell (3/5/2012)
So you're saying that the project requirements should include a document or Excel sheet that explicitly list what accounts / roles should be deployed in production and what level of access to each object should be? That could be used for the baseline.Perhaps the Policy-Based Management feature in 2008+ could be levered to insure that changes made to securables conform to some set of rules. That is start off with a clean slate, and then place constraints on how securables are modified over time.
There should be something like this that explains what is needed, and then some way to check against it. An Excel sheet that is maintained makes sense. The issues I see are not usually during a deployment, but during alterations made over time as new requirements or access is needed.
Unfortunately permissions are very hard to to track and understand for many people. It's a simple model, but one that becomes complex quickly when we mix multiple roles, Windows groups, etc.
The query I posted earlier should return all permissions for a user, even those resulting from changes in windows domain group membership. One solution would be to implement a process, something like a nightly scheduled job, that compares each account's permission matrix result against a baseline, and then email a differential report (if needed) to the DBA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2012 at 1:46 pm
Good editorial Steve, I especially liked the last sentence. Maybe Red-Gate could write a nice security tool?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply