May 5, 2016 at 11:23 am
Hello, I am implementing DoD STIGS on my DB server. We are on SQL 2008 Enterprise. So, I am using the SQL 2005 STIGS.
I have a question about V-15172 which is the STIG for Public access and restricting the "public" role. I ran the query against my master database and it has like 1,800 permissions. Which permissions do I "NEED" to revoke vs. what would break my system?
Does anybody have a script already that revokes the necessary Public permissions from the master db?
Also for the other databases, should Public have any permissions? I believe there are certain permissions it has to have... but I really don't know what can be revoked vs. must be there.
May 5, 2016 at 1:31 pm
I took a look at the particular STIG check you mentioned, and there is an "out" in it:
If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.
So, you could argue that the permissions granted (presuming you've not explicitly granted any yourself) are required by the SQL engine, get your IAO to authorize them, and document them.
But, you need to discuss it with your IAO...
Another option, although a lot of work, would be to move to SQL2008 R2 or SQL2012 (both of which use the SQL2012 STIG) which has "cleaned up" a lot of the checks like that, to where the majority of the items that used to need to be documented for Public are gone.
(I'm so very, very glad I no longer need to use the SQL2005 STIG...)
May 5, 2016 at 4:49 pm
Haha ok - as long as "SQL needs these" is a valid argument I can put that in the system security document. Cause yea, I really didn't feel like having to wade through that. But, we are using SQL 2008 R2. Where does it say I can use the 2012 STIG for R2? The only thing I saw on DISA's site was that there was no 2008 stig and we had to use 2005.
We are discussing going to 2012 but you know with the government how things are sometimes. 🙂
To your other statement, no I haven't explicitly granted any permissions at all to public and I did notice most of them were sys objects and things but with over 1,800 permissions there I just really didn't want to tackle that. 🙂
Thanks for the feedback!
May 5, 2016 at 5:12 pm
Well I'll be ... oye why don't they have that in the main SQL 2008 FAQ ... THANK YOU! I'm assuming the 2012 STIG is a lot friendlier than the 2005?
May 5, 2016 at 5:14 pm
Way, way more friendly.
May 5, 2016 at 5:14 pm
Oh and can I ask you.... how do you perform the STIG? The download is just XML files and there aren't any real "instructions". I was user stigviewer.com to grab them and do them by the finding ID... but I'm like really lost if there is an easy way to just open it in enterprise manager or something? If I open the XML in there it just quite ugly and they have weird variables in the where clauses.
May 6, 2016 at 7:31 am
Awesome thank you.
May 6, 2016 at 3:44 pm
DISA used to maintain SRR scripts that could do some of the checks in an automated fashion but they stopped supporting it over 3 years ago. Other automated tools like SCAP do not have a benchmark for the TWO SQL checklists you have to go through (DB and instance), so it is sadly a very manual and very long process.
If you have to go through the SQL 2005 STIGs and try and remove permissions from PUBLIC take a look at the Fort SQL blog. It is old and not updated anymore, but had a lot of good tips for approaching the SQL 2005 STIGs.
Remove Public and Guest Permissions
https://blogs.technet.microsoft.com/fort_sql/2010/02/04/remove-public-and-guest-permissions/[/url]
Public Not Granted Server Permissions
https://blogs.technet.microsoft.com/fort_sql/2010/03/19/public-not-granted-server-permissions/[/url]
Connection Error After Removing Public Permissions
https://blogs.technet.microsoft.com/fort_sql/2010/07/06/connection-error-after-removing-public-permissions[/url]
Joie Andrew
"Since 1982"
May 9, 2016 at 6:11 am
Some time back, I took the time to copy / paste all the queryable STIG checks out into one massive set of queries. Comes in handy as you can run the big query and knock through about 1/2 or so of the checks in one go. With a little help from the posters here, each query reports the STIG ID (you could just as easily change it to the V number) and the rule name, making it easy to go over.
The individual queries all look something like this:
declare @stigid varchar(15)
declare @privchecked varchar(150)
set @stigid = 'SQL2-00-002300'
set @privchecked = 'Alter server state'
select @stigid as [STIGID]
, @privchecked as [PrivChecked]
, s.*
from (
select @stigid as [STIGID]
, @privchecked as [PrivChecked]
) as checked
outer apply (
SELECT
who.name AS [Principal Name],
who.type_desc AS [Principal Type],
who.is_disabled AS [Principal Is Disabled],
what.state_desc AS [Permission State],
what.permission_name AS [Permission Name]
FROM
sys.server_permissions what
INNER JOIN sys.server_principals who
ON who.principal_id = what.grantee_principal_id
WHERE
what.permission_name = 'Alter server state'
AND who.type_desc = 'SERVER_ROLE'
) as S
ORDER BY
S.[Principal Name]
There's a few that need to have a sp_msforeachdb used, but it's not too hard to sort out.
May 11, 2016 at 9:53 am
Hey guys I have another question:
Finding V-40949 Talks about implementing a third-party tool or some other sql server method of monitoring security-relevant configuration settings to discover unauthorized changes.
What do you guys use to satisfy that?!?
May 11, 2016 at 12:07 pm
What I do is use a PowerShell that reads a file with directories containing SQL Server binaries (but not directories containing data/log files or any files that change often) and performs a checksum on them that is saved to a file. Once the checksum is created everyday the files are checked again against the values stored in the file. If there is a mismatch an error is logged and an e-mail notification sent.
Two caveats to this process:
- The first file that should be checked is the setup bootstrap log file to determine if an update has been applied. If so new baseline need to be generated.
- Procedures for patching should probably be updated to include manually regenerating the checksum baseline just in case.
Joie Andrew
"Since 1982"
May 11, 2016 at 1:26 pm
Ugh I have no idea how to do that. 🙁
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply