February 11, 2015 at 10:48 am
I have been tasked to STIG a new installation of SQL Server 2008R2 and I am hoping to automate the process. There are many STIG checks that have the Check queries and fix queries. You need to run the check query and the if there are results then run the fix query based on the results. Below is an example.
STEP-1
Obtain the list of accounts that have direct access to the server-level permission 'View any definition' by running the following query:
Select * from sys.server_permissions where permission_name = 'View any definition'
STEP - 2
If any rows are returned, run the following query, substituting the <grantee_principal_id> with those returned in the previous query.
Select * from sys.server_principals where principal_id = <grantee_principal_id>
STEP - 3
Fix Text:
Remove the 'View any definition' permission access from the account that has direct access by running the following script:
USE master
REVOKE VIEW ANY DEFINITION TO <'account name'>
GO
My questions are
A: How can I automate the three steps. For example, running the first query, use the results of that in the next query and then again used the results in the REVOKE query.
B: Has someone already invented a script or process to do this.
There are about 50 of these types of STIGs
I thought of a few possible options but have not yet tried getting them to work
A: I thought to use powershell and store the results of the first and second query in an array and then execute the revoke off the array.
B: I thought to create a script that would generate a temp table – then execute step two to create a new temp table and execute the revoke script off that table and then drop table
C: Using a view for each STIG check
D: Creating a Stored Procedure for each check
D: Somehow use a trigger, but not sure if a trigger would actually do the job.
Please let me know what you think or if you have any ideas
Any help is appreciated.
Jeff
February 11, 2015 at 11:45 am
Been here, sort of done this.
First off, I wouldn't set things up to automatically run the fix query, only because quite a few of the STIGs, if not applied in a reasonable manner, WILL break your SQL install.
But combining up the check queries into one big query, now that's fairly easy. I did it, with some help from one of the denizens here, so that I can run a single, long, multiple result query that tells me the STIG ID, a brief description / STIG check title, and any results.
See this from the SQL2012 STIG:
use [master];
go
declare @stigid varchar(250)
declare @privchecked varchar(150)
set @stigid = 'SQL2-00-017100'
set @privchecked = 'default account sa must be disabled'
select @stigid as [STIGID]
, @privchecked as [PrivChecked]
, s.*
from (
select @stigid as [STIGID]
, @privchecked as [PrivChecked]
) as checked
outer apply (
SELECT name, is_disabled
FROM sys.sql_logins
WHERE principal_id = 1
) as S
Then just repeat for each STIG check, replacing the query in the outer apply section...
February 11, 2015 at 12:51 pm
Run this query, review the result set and execute it:
SELECT 'REVOKE VIEW ANY DEFINITION FROM ' + quotename(name)
FROM sys.server_permissions sp
JOIN sys.server_principals p ON sp.grantee_principal_id = p.principal_id
WHERE sp.permission_name = 'VIEW ANY DEFINITION'
AND p.type IN ('S', 'R', 'U', 'G')
When I run it on my machine it lists one row which is for ##MS_PolicyTsqlExecutionLogin##. I am not sure that I want to revoke permission for this account, which I suspect is used by Policy-Based Management, and which is disabled by default.
Note also that I filter on the login type, so that logins created from certificates and asymmetric keys are involved. If these logins are granted VIEW ANY DEFINITION, it is presumably that there is a stored procedure somewhere signed with the key so that users can see some relevant information they would not be able to.
It would be possible to automate this even further, but in the light of the other post, it seems like a good idea to review before you execute.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
February 11, 2015 at 1:11 pm
Thank you very much for the help
I think that you guys are correct in that I should run the queries one at a time and evaluate before revoking.
Management wants an all-inclusive script to perform the STIGing, so that we can install and STIG and SQL instance in one or two days.
I am still trying to figure out how the first post script will work and I will try the second one
Thanks
Jeff
February 11, 2015 at 3:00 pm
OK, this is good stuff and hoping we could go one step more
I did the following in tempdb
create table temp1 (
ID INT Primary Key IDENTITY(1,1) NOT NULL
,name nvarchar(100))
SELECT
+ quotename(name)
FROM sys.server_permissions sp
JOIN sys.server_principals p ON sp.grantee_principal_id = p.principal_id
WHERE sp.permission_name = 'VIEW ANY DEFINITION'
and was able to create a table in the tempdb that listed all the account names that need to be revoked
the next step is how to I insert the names in the "name" column into the fix query
USE Master
REVOKE VIEW ANY DEFINITION TO <'name'>
name would be the four names that are in the temp1 table
Your help is appreciated.
Jeff
February 11, 2015 at 3:11 pm
If you opt to insert only the names into the table, I suggest that you save quotename for later.
You can execute the table in this way:
SELECT @sql =
(SELECT 'REVOKE VIEW ANY DEFINITION FROM ' +
quotename(name) + char(13) + char(10)
FROM tbl
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
The FOR XML STUFF is funky, but it is guaranteed to produce a concatenated list. But since it is intended to be used as XML, characters that are special to XML are enticised, why need some more XML mumbo-jumbo to get rid of them.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
February 12, 2015 at 5:50 am
jayoub (2/11/2015)
STEP-1Obtain the list of accounts that have direct access to the server-level permission 'View any definition' by running the following query:
Select * from sys.server_permissions where permission_name = 'View any definition'
STEP - 2
If any rows are returned, run the following query, substituting the <grantee_principal_id> with those returned in the previous query.
Select * from sys.server_principals where principal_id = <grantee_principal_id>
One other thing to note about these two steps.
It's nothing more than an INNER JOIN, done by hand.
So you can further clean up the query by re-writing it to use inner joins for these.
So, for example you could replace the two queries above with this:
select ssp.class
, ssp.class_desc
, ssp.grantee_principal_id
, ssp.permission_name
, ssp.state_desc
, SPrin.principal_id
, SPrin.name
, SPrin.sid
, SPrin.type_desc
, SPrin.is_disabled
from sys.server_permissions as ssp
inner join sys.server_principals as SPrin
on ssp.grantee_principal_id = SPrin.principal_id
where ssp.permission_name = 'View any definition'
Bear in mind as well, STIGS get updated. Having just recently had to STIG SQL for work, we had to use the latest SQL2012 STIG, which filters out things like the "##MS_PolicyTsqlExecutionLogin##" login.
You still shouldn't automate the "fixes," though...
February 20, 2015 at 1:21 pm
I would like to thank you for help.
I spoke to the Sr. DBA and we decided that it would be best to perform the STIG one at a time.
Thanks
Jeff
November 18, 2020 at 2:32 am
This was removed by the editor as SPAM
November 18, 2020 at 2:52 am
This was removed by the editor as SPAM
November 18, 2020 at 2:54 am
This was removed by the editor as SPAM
November 18, 2020 at 2:56 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply