/************************************************************************************************
-- SA 계정의 보안 필요성
************************************************************************************************/

-- 놀랍게도 사용자관리 메뉴에 관리자 그룹에 testuser가 들어가 있다.
xp_cmdshell 'net user testuser /add'
go

xp_cmdshell 'net localgroup administrators testuser /add'
go

xp_cmdshell 'net stop mssqlserver'
go


/************************************************************************************************
-- SQL서버 로그인 성공과 실패 추적
************************************************************************************************/

-- c2 audit mode는 고급 옵션입니다. 
-- sp_configure 시스템 저장 프로시저를 사용하여 설정을 변경하면 show advanced options를 1로 
-- 설정해야만 c2 audit mode를 변경할 수 있습니다.
sp_configure 'show advanced option', 1
reconfigure
go

sp_configure 'c2 audit mode',1
reconfigure

-- 재시작해야된다.
-- sp_configure 도움말의 타입 RR은 재시작해야 적용된다.

-- 파일의 크기가 200MB에 이르면, C2 감사는 새 파일을 시작하고 이전 파일을 닫은 다음 새 감사 
-- 레코드를 새 파일에 씁니다. 이 과정은 SQL Server가 종료되거나 감사가 해제될 때까지 계속됩니다.

-- 추가설명 : 이벤트뷰어에 남기는 방법도 있다.


/************************************************************************************************
-- 객체 사용 감사
************************************************************************************************/
-- 1.SQL에이전트 서비스 가동
exec master..xp_cmdshell 'net start sqlserveragent'

-- 2.객체 삭제상황을 전달받을 운영자 등록(이메일로도 가능하다)
use msdb
go

exec sp_add_operator @name = '관리자',
                     @enabled = 1,
                     @email_address ='sqladmin@mcpworld.com',
                     @netsend_address = 'heatguy'

-- 3.삭제시 발동하는 에러 제작
USE pubs
go

EXEC sp_addmessage @msgnum = 50099, @severity = 16, 
                   @msgtext = 'Customer %s was deleted by %d.', 
                   @lang = 'us_english',@with_log = true
go

EXEC sp_addmessage @msgnum = 50099, @severity = 16, 
                   @msgtext = '사용자 %1!이 고객데이터 %2!를 삭제했다', 
                   @lang = default,@with_log = true
go

-- 4.이벤트뷰어에 나타나는것 확인(raiserror)
raiserror(50099,16, 1, '직원A', 3)

-- 5.에러에 반응하는 경고 만들기
use msdb
go

exec sp_add_alert @name = '고객삭제', 
                  @message_id = 50099, 
                  @severity = 0, 
                  @notification_message = '에러 50099 발생' 
go

exec sp_add_notification @alert_name = '고객삭제'
                       , @operator_name =  '관리자'
                       , @notification_method  = 4
go

raiserror(50099,16, 1, '직원A', '고객') -- 다시 실험

-- 6.실제 상황가정 테이블과 프로시저 제작 테스트
use pubs
go

create table 고객
(
	id int
,	name char(6)
)

insert 고객 values(1,'차주언')
insert 고객 values(2,'이영재')

-- 실험
declare @id int
set @id = 1
raiserror(50099,16, 1, '직원A', @id) -- 다시 실험
--

create proc up_고객데이터삭제
@id int
as
begin
	delete 고객 where id = @id
	raiserror(50099,16, 1, '직원A', @id) -- 다시 실험
end

exec up_고객데이터삭제 2

select * from 고객

----------------------------------------
-- (옵션)
-- 그외에 트리거를 사용하여 다른 테이블에 기록을 남기는 방법도 사용 한다


create table 고객삭제내역
(
	id int identity(1,1)
,	사용자 char(10) default user_name()
,	삭제시간 datetime default getdate()
,	삭제고객번호 int
)

create trigger trg_delete on 고객 for delete
as
	insert 고객삭제내역(삭제고객번호)
	select id from deleted
go

exec up_고객데이터삭제 1

select * from 고객삭제내역

----------------------------------------
-- (옵션)
-- 로긴실패 에러번호 18456


/************************************************************************************************
-- 저장프로시저와 뷰를 사용한 보안(암호화)
************************************************************************************************/
use northwind

select * from employees

-- 1.dbo로 저장프로시저를 만든다.
-- 2.dbo로 뷰를 만든다.
create view  employee_view
with encryption
as
	select LastName,FirstName from employees
go

sp_helptext employee_view
select * from employee_view

alter proc employee_update
	@id int,@whoid int
with encryption
as
	update employees
	set ReportsTo = @whoid
	where employeeid = @id
go

employee_update @id = 1,@whoid = 2

sp_help employees

-- 3.will라는 일반계정을 만든다.
exec sp_addlogin 'will', 'password', 'northwind'

use northwind

exec sp_adduser 'will','will'

-- 4.dbo로 로긴해서 적당한 권한을 준다.
deny select,insert,update,delete
on employees 
to will

grant select
on employee_view
to will

grant execute
on employee_update
to will

-- 5.확인한다.
setuser 'will'
select user_name()

select * from employees

select * from employee_view

exec employee_update @id = 1,@whoid = 2

setuser