<commit_before>#
# Copyright 2012, <NAME>
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

include_recipe 'sqlshell::default'

node['sqlshell']['sql_server']['instances'].each_pair do |key, value|
  server_prefix = "sqlshell.sql_server.instances.#{key}"
  jdbc_url = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.url', String, server_prefix)
  jdbc_driver = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.driver', String, server_prefix)
  extra_classpath = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.extra_classpath', Array, server_prefix)
  jdbc_properties = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.properties', Hash, server_prefix)

  if value['logins']
    value['logins'].each_pair do |login, login_config|
      sqlshell_ms_login login do
        jdbc_url jdbc_url
        jdbc_driver jdbc_driver
        extra_classpath extra_classpath
        jdbc_properties jdbc_properties

        default_database login_config['default_database'] if login_config['default_database']
        default_language login_config['default_language'] if login_config['default_language']
        password login_config['password'] if login_config['password']
      end

      if value['server_roles']
        value['server_roles'].each_pair do |role, config|
          sqlshell_ms_server_role login do
            jdbc_url jdbc_url
            jdbc_driver jdbc_driver
            extra_classpath extra_classpath
            jdbc_properties jdbc_properties
            role role
          end
        end
      end
    end
  end

  if value['databases']
    value['databases'].each_pair do |database_name, database_config|
      database_prefix = "#{server_prefix}.databases.#{database_name}"
      if database_config['users']
        database_config['users'].each_pair do |user, user_config|
          user_prefix = "#{database_prefix}.users.#{user}"

          sqlshell_ms_user user do
            jdbc_url jdbc_url
            jdbc_driver jdbc_driver
            extra_classpath extra_classpath
            jdbc_properties jdbc_properties
            login Chef::AttributeChecker.ensure_attribute(user_config, 'login', String, user_prefix)
            database database_name
          end

          if user_config['permissions']
            user_config['permissions'].each_pair do |permission_key, permission_config|
              permission_prefix = "#{user_prefix}.permissions.#{permission_key}"
              permission = Chef::AttributeChecker.ensure_attribute(permission_config, 'permission', String, permission_prefix)
              securable_type = Chef::AttributeChecker.ensure_attribute(permission_config, 'securable_type', String, permission_prefix)
              securable = permission_config['securable'] ? Chef::AttributeChecker.ensure_attribute(permission_config, 'securable', String, permission_prefix) : nil
              permission_action = permission_config['permission_action'] ? Chef::AttributeChecker.ensure_attribute(permission_config, 'permission_action', String, permission_prefix) : nil

              sqlshell_ms_permission "#{permission_action} #{permission} TO #{user} ON #{securable_type}::#{securable || database_name}" do
                jdbc_url jdbc_url
                jdbc_driver jdbc_driver
                extra_classpath extra_classpath
                jdbc_properties jdbc_properties
                user user
                database database_name
                securable_type securable_type
                securable securable
                permission permission
                action permission_action.to_sym if permission_action
              end
            end
          end
        end
      end

      if value['logins']
        sqlshell_exec "Remove historic Server Roles" do
          jdbc_url jdbc_url
          jdbc_driver jdbc_driver
          extra_classpath extra_classpath
          jdbc_properties jdbc_properties
          command <<-SQL
              SELECT
                P.name, RP.name as role
              FROM
                sys.server_principals P
              JOIN sys.server_role_members SRM ON SRM.member_principal_id = P.principal_id
              JOIN sys.server_principals RP ON RP.principal_id = SRM.role_principal_id AND RP.type_desc = 'SERVER_ROLE'
              WHERE
                P.is_disabled = 0 AND
                P.name NOT LIKE 'NT AUTHORITY\\%' AND
                P.name NOT LIKE 'NT SERVICE\\%' AND
                P.type_desc IN ('SQL_LOGIN', 'WINDOWS_GROUP', 'WINDOWS_LOGIN')
          SQL
          block do
            logins_with_roles = []
            role_map = {}
            value['logins'].each_pair do |login, login_config|
              if login_config['server_roles']
                logins_with_roles << login
                role_map[login] = (role_map[login] || []) + login_config['server_roles'].keys
              end
            end

            delete_unmanaged = !value['delete_unmanaged_server_roles'].is_a?(FalseClass)

            @sql_results.each do |row|
              next if row['name'] == jdbc_properties['user']

              login = row['name']
              role = row['role']

              if !logins_with_roles.include?(login) || !(role_map[login] && role_map[login].include?(role))
                if delete_unmanaged
                  Chef::Log.info "Removing historic server role #{role} from #{login}"
                  sqlshell_ms_server_role login do
                    jdbc_url jdbc_url
                    jdbc_driver jdbc_driver
                    extra_classpath extra_classpath
                    jdbc_properties jdbc_properties
                    role role

                    action :remove
                  end
                else
                  Chef::Log.error "Unmanaged server role #{role} from #{login} found"
                end
              end
            end
          end
        end

        sqlshell_exec "Remove historic logins" do
          jdbc_url jdbc_url
          jdbc_driver jdbc_driver
          extra_classpath extra_classpath
          jdbc_properties jdbc_properties
          command <<-SQL
            SELECT
              SP.name, SP.type_desc
            FROM
              sys.syslogins L
            JOIN sys.server_principals SP ON SP.sid = L.sid
            WHERE
              SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_GROUP', 'WINDOWS_LOGIN') AND
              SP.is_disabled = 0 AND
              SP.name NOT LIKE 'NT AUTHORITY\\%' AND
              SP.name NOT LIKE 'NT SERVICE\\%'
          SQL
          block do
            delete_unmanaged = !value['delete_unmanaged_logins'].is_a?(FalseClass)

            @sql_results.each do |row|
              login = row['name']
              if value['logins'][login].nil? && login != jdbc_properties['user']
                if delete_unmanaged
                  Chef::Log.info "Removing historic login #{login}"
                  sqlshell_ms_login login do
                    jdbc_url jdbc_url
                    jdbc_driver jdbc_driver
                    extra_classpath extra_classpath
                    jdbc_properties jdbc_properties

                    action :drop
                  end
                else
                  Chef::Log.error "Unmanaged login #{login} found"
                end
              end
            end
          end
        end
      end
    end
  end
end<commit_msg>Remove the deletion to the correct level<commit_after>#
# Copyright 2012, <NAME>
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

include_recipe 'sqlshell::default'

node['sqlshell']['sql_server']['instances'].each_pair do |key, value|
  server_prefix = "sqlshell.sql_server.instances.#{key}"
  jdbc_url = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.url', String, server_prefix)
  jdbc_driver = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.driver', String, server_prefix)
  extra_classpath = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.extra_classpath', Array, server_prefix)
  jdbc_properties = Chef::AttributeChecker.ensure_attribute(value, 'jdbc.properties', Hash, server_prefix)

  if value['logins']
    value['logins'].each_pair do |login, login_config|
      sqlshell_ms_login login do
        jdbc_url jdbc_url
        jdbc_driver jdbc_driver
        extra_classpath extra_classpath
        jdbc_properties jdbc_properties

        default_database login_config['default_database'] if login_config['default_database']
        default_language login_config['default_language'] if login_config['default_language']
        password login_config['password'] if login_config['password']
      end

      if value['server_roles']
        value['server_roles'].each_pair do |role, config|
          sqlshell_ms_server_role login do
            jdbc_url jdbc_url
            jdbc_driver jdbc_driver
            extra_classpath extra_classpath
            jdbc_properties jdbc_properties
            role role
          end
        end
      end
    end
  end

  if value['databases']
    value['databases'].each_pair do |database_name, database_config|
      database_prefix = "#{server_prefix}.databases.#{database_name}"
      if database_config['users']
        database_config['users'].each_pair do |user, user_config|
          user_prefix = "#{database_prefix}.users.#{user}"

          sqlshell_ms_user user do
            jdbc_url jdbc_url
            jdbc_driver jdbc_driver
            extra_classpath extra_classpath
            jdbc_properties jdbc_properties
            login Chef::AttributeChecker.ensure_attribute(user_config, 'login', String, user_prefix)
            database database_name
          end

          if user_config['permissions']
            user_config['permissions'].each_pair do |permission_key, permission_config|
              permission_prefix = "#{user_prefix}.permissions.#{permission_key}"
              permission = Chef::AttributeChecker.ensure_attribute(permission_config, 'permission', String, permission_prefix)
              securable_type = Chef::AttributeChecker.ensure_attribute(permission_config, 'securable_type', String, permission_prefix)
              securable = permission_config['securable'] ? Chef::AttributeChecker.ensure_attribute(permission_config, 'securable', String, permission_prefix) : nil
              permission_action = permission_config['permission_action'] ? Chef::AttributeChecker.ensure_attribute(permission_config, 'permission_action', String, permission_prefix) : nil

              sqlshell_ms_permission "#{permission_action} #{permission} TO #{user} ON #{securable_type}::#{securable || database_name}" do
                jdbc_url jdbc_url
                jdbc_driver jdbc_driver
                extra_classpath extra_classpath
                jdbc_properties jdbc_properties
                user user
                database database_name
                securable_type securable_type
                securable securable
                permission permission
                action permission_action.to_sym if permission_action
              end
            end
          end
        end
      end
    end
  end

  if value['logins']
    sqlshell_exec "Remove historic Server Roles" do
      jdbc_url jdbc_url
      jdbc_driver jdbc_driver
      extra_classpath extra_classpath
      jdbc_properties jdbc_properties
      command <<-SQL
        SELECT
          P.name, RP.name as role
        FROM
          sys.server_principals P
        JOIN sys.server_role_members SRM ON SRM.member_principal_id = P.principal_id
        JOIN sys.server_principals RP ON RP.principal_id = SRM.role_principal_id AND RP.type_desc = 'SERVER_ROLE'
        WHERE
          P.is_disabled = 0 AND
          P.name NOT LIKE 'NT AUTHORITY\\%' AND
          P.name NOT LIKE 'NT SERVICE\\%' AND
          P.type_desc IN ('SQL_LOGIN', 'WINDOWS_GROUP', 'WINDOWS_LOGIN')
      SQL
      block do
        logins_with_roles = []
        role_map = {}
        value['logins'].each_pair do |login, login_config|
          if login_config['server_roles']
            logins_with_roles << login
            role_map[login] = (role_map[login] || []) + login_config['server_roles'].keys
          end
        end

        delete_unmanaged = !value['delete_unmanaged_server_roles'].is_a?(FalseClass)

        @sql_results.each do |row|
          next if row['name'] == jdbc_properties['user']

          login = row['name']
          role = row['role']

          if !logins_with_roles.include?(login) || !(role_map[login] && role_map[login].include?(role))
            if delete_unmanaged
              Chef::Log.info "Removing historic server role #{role} from #{login}"
              sqlshell_ms_server_role login do
                jdbc_url jdbc_url
                jdbc_driver jdbc_driver
                extra_classpath extra_classpath
                jdbc_properties jdbc_properties
                role role

                action :remove
              end
            else
              Chef::Log.error "Unmanaged server role #{role} from #{login} found"
            end
          end
        end
      end
    end

    sqlshell_exec "Remove historic logins" do
      jdbc_url jdbc_url
      jdbc_driver jdbc_driver
      extra_classpath extra_classpath
      jdbc_properties jdbc_properties
      command <<-SQL
        SELECT
          SP.name, SP.type_desc
        FROM
          sys.syslogins L
        JOIN sys.server_principals SP ON SP.sid = L.sid
        WHERE
          SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_GROUP', 'WINDOWS_LOGIN') AND
          SP.is_disabled = 0 AND
          SP.name NOT LIKE 'NT AUTHORITY\\%' AND
          SP.name NOT LIKE 'NT SERVICE\\%'
      SQL
      block do
        delete_unmanaged = !value['delete_unmanaged_logins'].is_a?(FalseClass)

        @sql_results.each do |row|
          login = row['name']
          if value['logins'][login].nil? && login != jdbc_properties['user']
            if delete_unmanaged
              Chef::Log.info "Removing historic login #{login}"
              sqlshell_ms_login login do
                jdbc_url jdbc_url
                jdbc_driver jdbc_driver
                extra_classpath extra_classpath
                jdbc_properties jdbc_properties

                action :drop
              end
            else
              Chef::Log.error "Unmanaged login #{login} found"
            end
          end
        end
      end
    end
  end
end
{'fraction_non_alphanumeric': 0.07560674084195458, 'fraction_numerical': 0.001301320840653263, 'mean_word_length': 2.2269577997060677, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2244283', 'n_tokens_mistral': 4212, 'n_tokens_neox': 4048, 'n_words': 1009}