December 3, 2009 at 4:42 am
:w00t::w00t:Hi all, I have a problem:w00t: and it is rather urgent. I have auditors auditing my database and they want a security policy from me. It must come out of sql server and telling them what I do won't work. I need a script (I think) to get the security policy as setup in the database. Gee, not sure I make sense but I think there should be a sytem table that has the policy in it.:hehe::hehe::hehe::w00t:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
December 3, 2009 at 6:48 am
When you say policy.... Do you mean permissions?
December 3, 2009 at 7:49 am
Generally a security policy would be defined as who has access to what information and how do you insure that this is being enforced. So you need to pieces of information, what the business says the security should be, and settings from the database showing what the security is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2009 at 10:59 pm
By security policy I mean:
Per example
Password expiry period = 3 months
Password lock out = 3 failures
Strong password required = true
Now I know that by default SQL Server uses the same as windows when you have Windows Server 2003 plus but you can change it to fit your needs as far as I know. Now, although I have not been changing the policy 'cause I do not know how, the auditor would like to get out of SQL Server what this policy is.Can anyone help me please!!!!!!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
December 4, 2009 at 1:45 am
If you're using Windows 2003 or later, you can rely on the operating system password policy.
For the SQL logins, see the CHECK_EXPIRATION and CHECK_POLICY options of the CREATE LOGIN statement.
December 4, 2009 at 5:52 am
Manie Verster (12/3/2009)
By security policy I mean:Per example
Password expiry period = 3 months
Password lock out = 3 failures
Strong password required = true
Now I know that by default SQL Server uses the same as windows when you have Windows Server 2003 plus but you can change it to fit your needs as far as I know. Now, although I have not been changing the policy 'cause I do not know how, the auditor would like to get out of SQL Server what this policy is.Can anyone help me please!!!!!!
There's no one place to get this information. I'll assume your shop looks a bit like ours. The vast majority of our in-house developed apps use windows authentication but a couple of the more retarded apps use sql authentication. Then we have the third party tools that are usually retarted or down right idiotic, sometimes requiring, not simply sysadmin rights, but the 'sa' login itself. So, to get a full picture of security, you need to pull documentation out of the windows OS, not sure how to do that, and then hit all the logins on your SQL systems.
Some of the security settings are the server level and some are at the database level. You'll need to collect data from both. Just a few places to look, sys.server_audits will show any auditing that you have set up on the server. sys.server_permissions will show server level permissions. sys.sql_logins will show the logins and settings there, such as password expiration, etc. Then you have to drill down into the database and look at sys.database_permissions, sys.ldatabase_role_members and put together what's happening at that level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply