보미네

-- --------------------------------------------------------------------------------------------------------------
-- job disable enable automation script
-- Use RAISERROR instead of print.
-- --------------------------------------------------------------------------------------------------------------
select @@servername 

use dba 
go

if object_id('AgentJobDisable') is not null
begin 
	drop table AgentJobDisable
end 

create table AgentJobDisable
(
 idx								int identity(1,1)
,workId								int
,job_id								varchar(100)
,name								nvarchar(1000)
,backup_run_requested_date			datetime
,backup_start_execution_date		datetime
,backup_job_history_id				int
,backup_next_scheduled_run_date		datetime 
,insTime							datetime
,disableStartTime					datetime
,disableStopTime					datetime
,isManualStartedYN					varchar(1)
,isManualStartedTime				datetime
,isEnabledYN						varchar(1)
,isEnabledTime						datetime
)
go

declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId 


-- --------------------------------------------------------------------------------------------------------------
-- step 1 start
-- WorkId and time must be set.
-- Change workId at once with ctrl - h -> @workId int = 6
-- Get the disable task list.
-- Puts only the task list that needs to run at the specified time into the list.
-- --------------------------------------------------------------------------------------------------------------

use dba 
go
select @@servername 
declare @workId int = 6
declare @disableStartTime datetime = '2023-04-14 12:00:00.000'
declare @disableStopTime datetime  = '2023-04-14 22:50:00.000'
declare @insTime datetime = getdate() 
declare @message nvarchar(3000) = N''

begin try 
	if exists (select * from dba.dbo.AgentJobDisable where workId = @workId)
	begin 
		set @message = @@servername + 'workId ' + cast(@workId as varchar(100)) +' exist.' ;
		throw 51000, @message, 1;  
	end 

	insert into AgentJobDisable
		(
		 workId							
		,job_id							
		,name							
		,backup_run_requested_date		
		,backup_start_execution_date
		,backup_job_history_id			
		,backup_next_scheduled_run_date	
		,insTime						
		,disableStartTime				
		,disableStopTime				
		)
	select @workId, a.job_id, a.name, b.run_requested_date, b.start_execution_date, b.job_history_id, b.next_scheduled_run_date, @insTime, @disableStartTime, @disableStopTime
	from msdb.dbo.sysjobs a 
		cross apply (
			select top 1 * 
			from msdb.dbo.sysjobactivity
			where job_id = a.job_id
			and next_scheduled_run_date >= @disableStartTime
			and next_scheduled_run_date <= @disableStopTime
			order by next_scheduled_run_date desc 
		) b
	where a.enabled = 1
		and name not in ('[hourly]Transaction Log Backup', 'syspolicy_purge_history')
	order by next_scheduled_run_date desc 
end try
begin catch 
	throw; 
end catch 

select @workId workId 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 1 end 
-- --------------------------------------------------------------------------------------------------------------

-- disable job target list 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

-- --------------------------------------------------------------------------------------------------------------
-- step 2 start 
-- disable job 
-- Disable all tasks found in the list.
-- --------------------------------------------------------------------------------------------------------------

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @cmd nvarchar(max)
declare @message nvarchar(3000)

begin try 
	select @minIdx = min (idx) from dba.dbo.AgentJobDisable where workId = @workId
	while (1=1)
	begin 
		select @jobName = name from dba.dbo.AgentJobDisable where idx = @minIdx 
		if @@rowcount > 0 
		begin 
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobDisableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 0'
			
			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 

			update dba.dbo.AgentJobDisable 
				set isEnabledYN = 'N'
				, isManualStartedYN ='N' 
			where idx = @minIdx

			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobDisableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
		end 
		else 
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 

		set @message = '*****************************************************************' 
		RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 2 end 
-- --------------------------------------------------------------------------------------------------------------

-- just check 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go
select * from msdb.dbo.sysjobs where enabled = 0

-- --------------------------------------------------------------------------------------------------------------
-- step 3 start 
-- After checking
-- Returns the job that needs to be run.
-- If the checking is finished early, only enable do not run.
-- If the script that was supposed to run didn't run, run and enable it.
-- --------------------------------------------------------------------------------------------------------------

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @job_id nvarchar(100)
declare @cmd nvarchar(max)
declare @backup_next_scheduled_run_date datetime 
declare @disableStartTime datetime
declare @run_requested_date datetime
declare @message nvarchar(3000)

begin try 
	select @minIdx = min (idx) 
	from dba.dbo.AgentJobDisable 
	where workId = @workId

	while (1=1)
	begin 

		set @jobName = ''
		set @job_id = ''

		select top 1 @jobName = name
			, @job_id = job_id
			, @disableStartTime = disableStartTime
		from dba.dbo.AgentJobDisable 
		where idx = @minIdx 
			and isManualStartedYN = 'N'
			and isEnabledYN = 'N'

		if @@rowcount > 0 
		begin 
			select top 1 @backup_next_scheduled_run_date = backup_next_scheduled_run_date from dba.dbo.AgentJobDisable where idx = @minIdx and job_id = @job_id
			if (@backup_next_scheduled_run_date >= @disableStartTime and @backup_next_scheduled_run_date < getdate()) 
			begin 
				if (@jobName = '[daily]cafe_Stat') -- manual job
				begin 
					set @message = 'skipped job : [daily]cafe_Stat'
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
				end 
				else 
				begin 
					set @message = @@servername + ' jobName : ' + @jobName +', ** RUN ** commnet : isManualStartedYN N,  backup_next_scheduled_run_date : ' + convert(varchar(max), @backup_next_scheduled_run_date, 121) + ' current time : ' +convert(varchar(max), getdate(), 121)
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

					set @cmd = 'EXEC msdb.dbo.sp_start_job @job_name = N'''+@jobName+ ''''
					set @message = @cmd
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

					exec (@cmd)

					update dba.dbo.AgentJobDisable set isManualStartedYN = 'Y', isManualStartedTime =getdate() where idx = @minIdx

					set @message = @@servername + ' jobName : ' + @jobName +', commnet : sp_start_job executed'
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
				end 				
			end
			else -- does not match time
			begin 
				set @message = @@servername + ' jobName : ' + @jobName +', ** SKIP **, commnet : isManualStartedYN N,  backup_next_scheduled_run_date : ' + convert(varchar(max), @backup_next_scheduled_run_date, 121) + ' current time : ' +convert(varchar(max), getdate(), 121)
				RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
			end 

			-- common job enable 
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 1'
			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 
			update dba.dbo.AgentJobDisable set isEnabledYN = 'Y', isEnabledTime = getdate() where idx = @minIdx

			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
			
			set @message = '*****************************************************************' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
		end 
		else -- no more job
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 3 end 
-- --------------------------------------------------------------------------------------------------------------

-- just check 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

sp_readerrorlog 
select * from msdb.dbo.sysjobs where enabled = 0

-- --------------------------------------------------------------------------------------------------------------
-- job enable script start
-- --------------------------------------------------------------------------------------------------------------
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @job_id nvarchar(100)
declare @cmd nvarchar(max)
declare @next_scheduled_run_date datetime 
declare @disableStartTime datetime
declare @run_requested_date datetime
declare @message nvarchar(3000)

begin try 
	update dba.dbo.AgentJobDisable 
	set isEnabledYN = 'N' 
	where workId = @workId 

	select @minIdx = min (idx) 
	from dba.dbo.AgentJobDisable 
	where workId = @workId

	while (1=1)
	begin 

		set @jobName = ''
		set @job_id = ''


		select top 1 @jobName = name
			, @job_id = job_id
			, @disableStartTime = disableStartTime
		from dba.dbo.AgentJobDisable 
		where idx = @minIdx 
			and isEnabledYN = 'N'

		if @@rowcount > 0 
		begin 
			-- common job enable (항상 활성화 시킨다.)
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 1'

			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 
			update dba.dbo.AgentJobDisable set isEnabledYN = 'Y', isEnabledTime = getdate() where idx = @minIdx
			
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

		end 
		else 
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- -------------------------------------------
-- job enable script end
-- -------------------------------------------

• 서버에서 caching_sha2_password 가 설정되면 connection string에  SslMode=none;ServerRsaPublicKeyFile=file.pem 옵션이 적용 가능함
• 해당 file.pem 을 얻기 위해 아래 명령은 mysql 서버에서 실행 

SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key';

• 결과중 아래 형식만 file.pem으로 저장 후 메일로 전달 (아래는 일부러 비정상 파일을 만들었음 키 정합성이 맞지 않음)
  -----BEGIN PUBLIC KEY-----
  MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAnbV9QkygOGEzBsUBU2Ac
  Bnm/jBPs21lE7BxJRLudQx22s/0uXQAkG8e+O/sjGHLQlntUCYPMNQ4W3DNXLhXP
  v/W4KHmbYsVcWbMn3kZeCiuy7QTR4BUmDq2+QUYB1bk9twzToD7lNUny5cP+88Lf
  MjLF7bSYQCL3w+D53FKbnsT9uQSmS/+VhpNPWzrOZTJsiaSG/dDoCdx9fYhdUxWY
  bYo1UEA+nGAtEF7eYJrTPeAFlWlgDmzj5cyWO1CpEfLnazWl/RhuHRxb8gI/T+nL
  9qr0wLVZXt/ssEt74nolV5sKesX+QPQ8cJS3aWw4JDphqSMQaR2ZyAwX2nhYJlg8
  jwIDAQAB
  -----END PUBLIC KEY-----
• 위 옵션은 .net 의 경우, Oracle의 MySQL.Data에서는 지원하지 않고 MySqlConnector 에서만 지원 가능함(23년 3월 13일 까지는 지원 안함)
• MySQL.Data와 MySqlConnector에서 SslMode=none;AllowPublicKeyRetrieval=true 옵션을 사용해 접근할 수 있고, 이 옵션은 MITM 공격에 취약함 (중간자 공격)
• 클라이언트에서 위 두 가지 방법중 아무것도 적용을 하지 않으면, 최초 접근이 실패하고 서버가 시작되고 한번이라도 성공한 커넥션이 있으면 접속 가능

https://mysqlconnector.net/connection-options/

https://mysqlconnector.net/troubleshooting/retrieval-public-key/

sha256_password_auto_generate_rsa_keys

caching_sha2_password_auto_generate_rsa_keys

default_authentication_plugin = caching_sha2_password

-- recursive cte example

if object_id ('emp') is not null
drop table emp 
go

create table emp
(empId int
, empName nvarchar(100) 
, managerId int
) 
go

insert into emp (empId, empName, managerId)
select empId, empName, managerId 
from 
	( 
	  values 
	  (1 , 'name1', 3 )    -- level2
	, (2 , 'name2', 3 )    -- level2
	, (3 , 'name3', null ) -- level1
	, (4 , 'name4', 2 )    -- level3  
	, (5 , 'name5', 2 )    -- level3  
	, (6 , 'name6', 2 )    -- level3  
	, (7 , 'name7', 1 )    -- level3  
	, (8 , 'name8', 1 )    -- level3  
	, (9 , 'name9', 1 )    -- level3  
	, (10, 'name10', 9 )   -- level4
	) a (empId, empName, managerId)
go

; with empCte
as 
(
-- recursive anchor query
select empId, empName, managerId, 1 memLevel
from emp 
where empId = 3

union all 

-- recursive target query 
select b.empId, b.empName, b.managerId, a.memLevel + 1 memLevel
from empCte a -- anchor 
	join emp b 
	on b.managerId = a.empId 
)
-- cooking 
select a.empId, a.empName, isnull(b.empName, 'no parent') managerName, a.memLevel
from empCte a
	left join emp b
	on b.empId = a.managerId
order by 1