IT CookBook, SQL Server 로배우는데이터베이스개론과실습 [ 강의교안이용안내 ] 본강의교안의저작권은한빛아카데미 에있습니다. 이자료를무단으로전제하거나배포할경우저작권법 136 조에의거하여최고 5 년이하의징역또는 5 천만원이하의벌금에처할수있고이를병과 ( 倂科 ) 할수도있습니다.
Chapter9. 데이터베이스보안과관리 SQL Server 로배우는데이터베이스개론과실습
1. 데이터베이스관리의개요 2. 보안과권한 3. 백업과복원
데이터베이스관리의중요성과관리를위해필요한업무에는무엇이있는지알아본다. 데이터베이스사용자에게권한을부여하여 DBMS 자체또는특정데이터에접근을차단하는방법을알아본다. 장애시백업데이터를토대로데이터베이스를복원하는방법을알아본다.
01. 데이터베이스관리의개요 데이터베이스관리의중요성 데이터베이스관리업무
1.1 데이터베이스관리의중요성 운영자판매원고객 네트워크 백본, 라우터, 스위치, 허브 데이터베이스서버 소프트웨어 운영체제 (Windows) DBMS(SQL Server) 하드웨어 CPU RAM HDD 그림 9-1 데이터베이스운영환경
1.2 데이터베이스관리업무 서비스관리 DBMS 는동시에여러사용자가접근하여데이터를읽고쓸수있기때문에시스템이다운되는일이없도록많은주 의를필요로한다. 점검및모니터링 업무의특성에따라다르겠지만일반적인데이터베이스시스템은 365 일 24 시간운영되는경우가많다. 이러한시스 템의특성상지속적인모니터링과점검을통해시스템에문제가발생하기전에조치하는것이중요하다. 장애대처 시스템운영중에는불가피하게장애가발생할수있다. 이러한장애가발생하면각경우에맞게신속 정확하게대 처해야한다. 백업과복원 데이터베이스에저장된정보가시스템장애나사용자부주의등으로유실되거나삭제될경우그피해는엄청나다. 이에대비하여 DBMS 의주요데이터파일은주기적으로백업을받아두어야한다. 사용자관리및권한관리 DBMS 는사용자의데이터접근과사용에관한권한을지정할수있다. 로그인사용자관리와권한관리는 DBMS 의 기본기능이다.
1.2 데이터베이스관리업무 시스템데이터베이스관리 DBMS 는 DBMS 의운영정보를담고있는시스템데이터베이스를가지고있다. 시스템데이터베이스가잘못관리 되면 DBMS 가오작동하는등의문제가생길수있다. 사용자데이터베이스관리 사용자데이터베이스는경우에따라하나의 DBMS 에여러개가운영될수있다. 이와관련하여데이터베이스생 성작업및관리가필요하다. 데이터베이스저장공간관리 데이터베이스가꾸준히운영되면당연히데이터의증가에비례하여하드디스크공간역시늘어나야한다. 만약하드디스크가데이터증가에충분히대처하지못한다면데이터베이스시스템은데이터를저장하지못하고중단될것이다. 인덱스관리 인덱스는 B-tree 형태로저장되어균형을유지하려고하는데, 새로운데이터가입력되면새로운노드를만들고분할한다. 이과정에서노드의중간에있는데이터가삭제될경우하드디스크의저장공간에단편화가발생하여인덱스의성능이떨어진다. 따라서일정한기준을넘어서는인덱스의경우재구축작업을수행하여문제를해결해야한다.
02. 보안과권한 로그인사용자관리 권한관리
02. 보안과권한 DBMS 는 1 로그인단계에서 DBMS 접근을제한하는로그인사용자관리와 2 로 그인한사용자별로특정데이터로의접근을제한하는권한관리의기능을제공한 다. DBMS 데이터베이스 사용자 1 허가 테이블 1 사용자 2 거부 거부 테이블 2 1 로그인확인 2 권한확인 그림 9-2 데이터베이스접근권한
2.1 로그인사용자관리 그림 9-3 SQL Server 인증모드 Windows 인증 윈도우운영체제의정보를사용하여계정이름및암호가유효한지확인하는방법이다. SQL Server 인증 SQL Server에서별도로로그인사용자를생성하여관리하는방법이다. 최초설치시에는관리자인 sa(system administrator) 계정이생성된다. sa는최고관리자로모든개체에대한권한을가지고있다.
2.1.1 메뉴를이용하여신규로그인사용자생성하기 새로그인선택 로그인이름, 암호입력 로그인이름 : mdguest 암호 (P) : mdpass
2.1.1 메뉴를이용하여신규로그인사용자생성하기 데이터베이스사용자설정 데이터베이스연결권한설정
2.1.1 메뉴를이용하여신규로그인사용자생성하기 로그인생성확인 사용자생성확인
2.1.2 T-SQL 문을이용하여신규로그인사용자생성하기 CREATE LOGIN 로그인이름을생성할때사용하는명령이다. CREATE LOGIN [ 로그인이름 ] WITH PASSWORD = '[ 비밀번호 ]', default_database=[ 대상데이터베이스 ] CREATE USER 사용자를설정할때사용하는명령이다. CREATE USER [ 사용자이름 ] FOR LOGIN [ 대응되는로그인이름 ] WITH default_schema=[ 기본스키마, dbo]
2.1.2 T-SQL 문을이용하여신규로그인사용자생성하기 질의 9-1 (sa 로그인창 ) 새로운로그인이름 mdguest2 를생성한후 Madang 데이터베이스 에접근할수있도록하시오. USE master; CREATE LOGIN mdguest2 WITH PASSWORD = 'mdpass', default_database=madang; GO USE [Madang] GO CREATE USER mdguest2 FOR LOGIN mdguest2 WITH default_schema=dbo; GO
2.2 권한관리 소유한개체에대한사용권한을관리하기위한명령을 DCL(Data Control Language) 이라고한다. 대표적인 DCL 문에는권한을허가하기위한 GRANT 문과 권한을취소하기위한 REVOKE 문이있다. OWNER mduser GRANT With grant option REVOKE 권한허가 mdguest GRANT REVOKE mdguest2 권한취소 그림 9-11 GRANT 문과 REVOKE 문의관계
2.2.1 권한허가 - GRANT GRANT 문은객체를생성한소유자가대상객체에대한권한을다른사용자에게 허가하는명령이다. GRANT 권한 [( 컬럼 [,...n ])] [,...n ] [ON 객체 ] TO { 사용자 Ι 롤 ΙPUBLIC [,...n ]} [WITH GRANT OPTION] * [,... n ] : 반복가능을의미 권한 : 허가할권한을지정한다. 객체타입별로지정가능한권한은 [ 표 9-1] 과같다. DELETE, INSERT, SELECT, UPDATE, EXECUTE는순서대로삭제, 삽입, 조회, 수정, 실행을할수있는권한을말한다. REFERENCES는외래키제약조건을생성하기위해해당테이블을참조할수있도록허가하는권한이다. REFERENCES( 컬럼이름 ) 의형태로사용한다. 컬럼 : 사용권한을부여할테이블의열이름을지정한다. 꼭괄호 () 안에표시해야한다. 가능한권한은 SELECT, UPDATE, REFERENCES이다. 컬럼을지정하면권한을허가받은사용자만이대상테이블의지정된컬럼을조회하거나수정할수있다. 객체 : 사용권한을부여할객체를지정한다. 테이블이나뷰등의이름이올수있다. TO 사용자 : 권한을부여할사용자를지정한다. 또한권한의묶음인롤 (ROLE) 에권한을추가할수도있다. PUBLIC은모든사용자가사용가능하도록공개적으로권한을부여한다는의미다. WITH GRANT OPTION : 허가받은권한을다른사용자에게다시부여할수있다. 이옵션이없으면권한의재부여는허가하지않는다.
2.2.1 권한허가 - GRANT 표 9-1 객체타입별로지정가능한권한 객체타입테이블뷰스칼라함수저장프로시저 권한 DELETE, INSERT, REFERENCES, SELECT, UPDATE DELETE, INSERT, REFERENCES, SELECT, UPDATE EXECUTE, REFERENCES EXECUTE 질의 9-2 (mduser 로그인창 ) mdguest 에게 Book 테이블의 SELECT 권한을부여하시오. GRANT SELECT ON Book TO mdguest; 질의 9-3 (mduser 로그인창 ) mdguest 에게 Customer 테이블의 SELECT, UPDATE 권한을 WITH GRANT OPTION 과함께부여하시오. GRANT SELECT, UPDATE ON Customer TO mdguest WITH GRANT OPTION;
2.2.1 권한허가 - GRANT 질의 9-4 (mdguest 로그인창 ) Book 테이블과 Customer 테이블의 SELECT 권한을 mdguest2 에부여하시오. GRANT SELECT ON Book TO mdguest2; GRANT SELECT ON Customer TO mdguest2; 질의 9-5 (mduser 로그인창 ) Orders 테이블을모든사용자가 SELECT 할수있도록권한을 부여하시오. GRANT SELECT ON Orders TO PUBLIC;
2.2.2 권한취소 - REVOKE REVOKE 문은 GRANT 문으로허가한권한을취소, 회수하는명령이다. REVOKE 권한 [( 컬럼 [,...n ])] [,...n ] [ON 객체 ] FROM { 사용자 Ι 롤 ΙPUBLIC [,...n ]} [CASCADE] 명령문의주요인수들은 GRANT 문과같은데, 몇가지다른점이있다. GRANT 문이권한부여를위해 TO 사용자 를표기하였다면, REVOKE 문은권한취소를위해 FROM 사용자 를표기한다. 또한권한을재부여하는 WITH GRANT OPTION의회수를위해 CASCADE 옵션을사용한다. CASCADE는사용자가다른사용자에게부여한권한까지연쇄적으로취소하라는의미로, 사전에주의깊게확인하고사용해야한다.
2.2.2 권한취소 - REVOKE 질의 9-6 (mduser 로그인창 ) mdguest 로부터 Book 테이블의 SELECT 권한을취소하시오. REVOKE SELECT ON Book FROM mdguest; 질의 9-7 (mduser 로그인창 ) mdguest로부터 Customer 테이블의 SELECT 권한을취소하시오. 단, 처음에는 CASCADE 옵션을사용하지않고 REVOKE 문을실행해보고, 다음으로 CASCADE 옵션을추가하여실행해본다. REVOKE SELECT ON Customer FROM mdguest; REVOKE SELECT ON Customer FROM mdguest CASCADE;
2.2.3 역할 - ROLE 롤 (ROLE) 은데이터베이스객체에대한권한을모아둔집합니다. 사용자 mduser 테이블 Book mdguest Orders mdguest2 Customer 그림 9-12 마당서점의사용자와테이블간의권한부여 사용자 mduser 역할 테이블 Book mdguest DBA 프로그래머 Orders mdguest2 Customer 그림 9-12 마당서점의사용자와테이블간의권한부여
2.2.3 역할 - ROLE 롤생성 CREATE ROLE 롤이름 롤제거 DROP ROLE 롤이름 롤에사용자추가 EXEC sp_addrolemember 롤이름, 사용자이름 ; 롤에사용자제거 EXEC sp_droprolemember 롤이름, 사용자이름 ;
2.2.3 역할 - ROLE 롤의생성부터사용자추가까지의단계를정리하면다음과같다. CREATE ROLE - 롤의생성 GRANT - 만들어진롤에권한부여 sp_addrolemember - 롤에사용자추가 롤을제거할경우반대로수행한다. sp_droprolemember - 롤의사용자제거 DROP ROLE - 롤삭제 ( 롤에사용자가남아있으면삭제가거부됨 ) 질의 9-8 (sa 로그인창 ) Madang DB 에 programmer 라는롤을생성하시오. USE Madang; GO CREATE ROLE programmer;
2.2.3 역할 - ROLE 질의 9-9 (sa 로그인창 ) programmer 롤에 Book 과 Orders 테이블에대한권한을부여하시 오. 이때 Book 테이블에는 SELECT 와 UPDATE 권한을, Orders 테이블에는 SELECT 와 INSERT 권한을부여한다. GRANT SELECT, UPDATE ON Book TO programmer; GO GRANT SELECT, INSERT ON Orders TO programmer; GO 질의 9-10 (sa 로그인창 ) programmer 에 mdguest 와 mdguest2 사용자를추가하시오. EXEC sp_addrolemember programmer, mdguest; GO EXEC sp_addrolemember programmer, mdguest2; GO
2.2.3 역할 - ROLE 질의 9-11 (mdguest 로그인창 ) Book 테이블에서도서번호 1번을조회 (SELECT) 한후다음과같이데이터를추가 (INSERT) 하시오. SELECT * FROM Book WHERE bookid=1; INSERT INTO Book(bookid, bookname, publisher, price) VALUES (100, ' 좋은책 ', ' 좋은출판사 ', 100);
2.2.3 역할 - ROLE 질의 9-12 (sa 로그인창 ) Madang DB 에 programmer 롤을삭제하시오. USE Madang; GO EXEC sp_droprolemember programmer, mdguest; GO EXEC sp_droprolemember programmer, mdguest2; GO DROP ROLE programmer; 질의 9-13 (mdguest 로그인창 ) Book 테이블에서도서번호 1번을조회 (SELECT) 해보시오. SELECT * FROM Book WHERE bookid=1;
03. 백업과복원 복구모델 백업의종류 백업및복원실습
03. 백업과복원 데이터베이스에서도역시예상하지못한장애에대비하여데이터베이스를복제하여보관하는작업을백업 (backup) 이라고한다. 복원 (recovery) 은장애가발생하여운영중인데이터에손상이발생했을때기존에복사해둔백업파일을사용하여원래대로되돌려놓는작업을말한다. 미디어오류 데이터베이스가저장된매체 ( 일반적으로하드디스크 ) 의고장이나삭제등으로인해데이터베이스파일을사용하지못하게되는오류를말한다. 사용자오류 데이터베이스는업무의특성에따라여러부문의사용자가접속하여사용한다. 이과정에서사용자가데이터를삭제하거나잘못된데이터로업데이트하는등의실수를하여데이터가유실 변경될수있는데, 이를사용자오류라고한다. 하드웨어장애 데이터베이스시스템은일종의소프트웨어로볼수있으며이러한소프트웨어는물리적인하드웨어, 즉서버에의해운영된다. 물리적인하드웨어는자연재해나정전등여러이유로장애가발생할수있으며, 이경우데이터베이스역시사용할수없게된다.
3.1 복구모델 그림 9-14 SQL Server 의복구모델 전체복구모델 (Full) 데이터베이스에장애가발생할경우장애가발생한시점까지복구한다. 이를위해모든작업을로그파일에기록한다. 대량로그복구모델 (Bulk-logged) 전체복구모델과비슷하지만모든작업에대해로그를기록하지는않는다. 로그파일에는 INSERT, UPDATE 작업만기록하며, SELECT INTO나 CREATE INDEX는발생된사실만기록한다. 단순복구모델 (Simple) 전체복구모델과대량로그복구모델과달리로그파일을기록하지않는다. 로그파일을기록하지않으므로마지막에백업을해둔데이터만복구가가능하다.
3.2 백업의종류 시간시작 5 분 10 분 자료입력 10 입력 20 입력 30 입력 트랜잭션 Insert 10.. Insert 20.. Insert 30.. 데이터파일 20 30 20 10 10 10 백업첫번째두번째세번째 그림 9-15 데이터파일의입력순서 ( 시간순 )
3.2 백업의종류 전체백업 데이터베이스개체, 시스템테이블, 데이터등데이터베이스전체를백업한다. 전체복구, 대량로그복구, 단순복구모델에서모두사용가능하다. 전체백업은최초에데이터베이스를생성하였을때나데이터베이스에변경이있을때수행하는것이좋다. 백업을수행할때마다수행시점의모든데이터를백업하기때문에여러번하면각백업파일에데이터가중복저장된다. 또한데이터의양이많을경우백업을수행할때마다많은시간이소요된다. 차등백업 차등백업은전체백업을수행한이후변경된데이터만저장한다. 트랜잭션로그백업 데이터베이스에서수행한작업을기록하고있는로그파일LDF을저장하는방법이다. 로그파일은데이터의입력, 수정, 삭제등에관련된질의를순서대로기록하고있다. 백업은최초에전체백업을수행한후로그백업을수행한다. 로그백업이수행되면로그기록은비워진다. 로그백업은로그만저장하므로빠르게수행할수있으나복구시많은시간이소요된다. 또한대량의데이터작업이발생한경우에는가급적사용하지않는것이좋다.
3.3.1 백업장치설정 백업장치로사용할폴더생성 백업장치설정
3.3.1 백업장치설정 백업장치생성 백업장치확인
3.3.2 백업 백업실행 전체백업수행
3.3.2 백업 백업완료 백업파일확인
3.3.3 복원 Orders 테이블삭제
3.3.3 복원 복원실행
3.3.3 복원 전체복원수행 복원완료
3.3.3 복원 Orders 테이블복원확인
요약 1. DBA 2. DCL 3. GRANT 문 4. WITH GRANT OPTION 5. REVOKE 문 6. 롤 (ROLE, 역할 ) 7. 백업 8. 복원 9. 백업의종류
SQL Server 로배우는데이터베이스개론과실습