stargazer:몽상가가 어제 꾼 꿈

1. (MySQL 에서 password() 처럼 쓸수 있는 방법
물론 복호화는 불가능하다.

/* 암호화 함수 */
SELECT PwdEncrypt('TESTDATA')
-----------------------------------
-- 결과 : 0x0100B10AD5EF78506BAB878F63BE525BBF501A409E2AA4DBFCEC

/*  확인 함수 */
-- SQL :
SELECT PwdCompare('TESTDATA', PwdEncrypt('TESTDATA'))
------------------------------------------------------------
-- 결과 : 1

-- SQL :
SELECT PwdCompare('TESTDATA', PwdEncrypt('TESTDATA1'))
------------------------------------------------------------
-- 결과 : 0

 

2. 마스터 키 생성 -> 비대칭 키 생성 -> 대칭 키 생성

create master key encryption by password='패스워드'
create asymmetric key asym_encryption_test with algorithm = rsa_1024
create symmetric key sym_encryption_test with algorithm = des encryption by asymmetric key asym_encryption_test

 

일반적으로 위와 같이 생성한다...

 

마스터키를 만들고, 비대칭키를 RSA_1024 알고리즘으로 생성하고,
대칭키를 앞에 만들어 둔 비대칭키를 암호로 하여 DES 알고리즘으로 로 생성한다.

그러나... 또라이 사장을 둔 가난한 회사의 불쌍한 개발자에게는 웹 호스팅 등등 여러가지 상황을 염려에 두어야 하므로...

DB 백업이나, 기타 등등 문제가 생겼을 경우, 복원해도 기존 암호를 복호화를 할 수 있는 키를 생성 할 수 없다.

모든 키를 패스워드로 생성하자.
create master key encryption by password='패스워드'
create asymmetric key asym_encryption_test with algorithm = rsa_1024 ENCRYPTION BY PASSWORD = '패스워드'

create symmetric key sym_encryption_test with algorithm = des  encryption by password = '패스워드'

 

하다보니 드는 생각...
대칭키를 패스워드로 생성하면... 비대칭키는 생성 안해도 되는거 아닌가???
모르겠다.. 귀찮다...

키를 생성했으면 열자

open symmetric key sym_encryption_test
decryption by asymmetric key asym_encryption_test

이것은 세션동안 유지 된다.

 

처음 방법대로 했으면 이렇게 열고...

패스워드를 썼으니
open symmetric key sym_encryption_test
decryption by password='패스워드'

암호화 :

EncryptByKey(Key_GUID('sym_encryption_test'), '암호화 할 문자열');

복호화 :
convert(varchar, DecryptByKey('암호화된 문자열))

생성되는 암호화 문자열이 들어갈 컬럼은 varbinary(128) 정도로 해 준다.

 

select *, gayalabor.dec(encrypted) from gayalabor.temp

 

두대의 서버에서 백업 - 복원 후 패스워드로 키를 생성해 준 후 열어 보았다.

동일한 해쉬(?)값을 갖는 키가 생성되었다.

 

퇴근하자!

SQL Server의 DB 복구시 로그인 계정 연결 문제

검색 키워드 : How to troubleshoot orphan users in SQL Server databases?


데이터베이스를 복구 할 때, (특히 새로 세팅한 서버에 기존 DB 를 복구하거나 연결하는 등의 경우)
사용자, 스키마 등등 user 와 login 이 불일치하여 당장 사용할 수 없는 경우가 있다.

그때 마다 사용자를 제거했다가 다시 로그인 계정을 생성하는 방식으로 매칭을 시키거나 하는 방법으로 쓸수는 있었는데
다음과 같은 프로시저가 sql 내장 프로시저에 존재한다.

sp_change_users_login 구문

sp_change_users_login [ @Action = ] 'action' 
    [ , [ @UserNamePattern = ] 'user' ] 
    [ , [ @LoginName = ] 'login' ] 
        [ , [ @Password = ] 'password' ]

다음 명령으로 로그인 계정에 연결되지 않은 사용자를 조회할 수 있다 :
EXEC sp_change_users_login 'Report';
또한 다음 명령으로 로그인 계정에 연결되지 않은 사용자를 사용자를 위한 로그인 계정을 생성하고 할당할 수 있다 :

use [database]

CREATE LOGIN [login_id] WITH PASSWORD = [password];
GO

--sp_change_users_login [ @Action = ] 'action'
--    [ , [ @UserNamePattern = ] 'user' ]
--    [ , [ @LoginName = ] 'login' ]
--        [ , [ @Password = ] 'password' ]

--Update_One
-- 현재 데이터베이스에서 지정된 user를 기존 SQL Server login에 연결합니다.
-- user와 login은 지정해야 합니다. password는 NULL이거나 지정하지 않아야 합니다.
 
EXEC sp_change_users_login 'update_one', '[user_id]', '[login_id]';

--exec sp_change_users_login
--    @Action ='auto_fix'
--    ,@UserNamePattern = '[login_id]'
--    ,@LoginName = null
--    ,@Password = '[passwd]'

exec sp_change_users_login  @Action ='report'

더 자세한 내용은 SQL Server 2005 매뉴얼에서 sp_change_users_login 시스템 프로시저 항목에서 읽어보자.

BACKUP LOG [데이터베이스] WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE ([데이터베이스이름], TRUNCATEONLY)

간혹 두번째 쿼리만 열심히 돌리고서는 `로그파일 용량이 안줄어요` 하는 사람들이 아주 자주 보인다.
백날 해봐야 소용없다.
일단 있는 로그들을 어떻게든 처리해줘야 파일을 줄이던가 말던가 할 것 아닌가?

위의 경우는 log 파일을 백업하지 않고 버리는 예제이다.


출처 : http://nopd.egloos.com/3475586