STIG Automating

  • 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

  • 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...

  • 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]

  • 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

  • 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

  • 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]

  • jayoub (2/11/2015)


    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>

    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...

  • 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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply