254KB 사이즈의 mysql 3.21 Reference Manual PostScript 매뉴얼 정보
그누보드 254KB 사이즈의 mysql 3.21 Reference Manual PostScript 매뉴얼
본문
## 원본 : mysql 3.21 Reference Manual PostScript 매뉴얼
(현재 최신 매뉴얼은 3.22 레퍼런스 매뉴얼이며 3.22.14b-gamma입니다.)
5, 6, 9, 10, 11, 12, 13, 15, 17, 18장 번역 : 문태준(http://www.mysql.com/Manual_split/manual_Todo.html).
이것이 가장 최신 버전의 TODO 목록이다. 부록 F [TODO] 참고.
5.2.1 Sub-selects
다음은 Mysql에서 작동하지 않는다:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
Mysql에서는 오직 INSERT ... SELECT ... and REPLACE ... SELECT ... 만을 지원한다.
독립적인 서브-select 문은 3.23.0에서 아마도 사용할 수 있을 것이다. 그대신 현재 IN() 펑
션을 사용할 수 있다.
5.2.2 SELECT INTO TABLE
Mysql은 아직 SELECT ... INTO TABLE ....을 지원하지 않는다. 현재, Mysql은 오직
SELECT ... INTO OUTFILE ..., 만을 지원하며 기본적으로는 동일하다.
5.2.3 트랜잭션(Transactions)
트랜잭션은 지원되지 않는다. Mysql은 곧 atomic(원자성?) 오퍼레이션을 지원할 것이며
atomic 오퍼레이션은 rollback이 없는 트랜잭션과 비슷하다. atomic 오퍼레이션을 사용하며
insert/select/모든 명령의 그룹을 실행할 수 있으며 어떤 스레드도 충돌하지 않을 수 있도록
보장해준다. 이 문맥에서 일반적으로 롤백(rollback)은 필요없다. 현재 LOCK TABLES 와
UNLOCK TABLES 명령을 이용하여 다른 스레드가 충돌하는 것을 막을 수 있다. 7.23
[Lock Tables] 참고.
5.2.4 저장 프로시저와 트리거
저장 프로시저는 서버에서 컴파일되고 저장될 수 있는 SQL 명령 세트이다. 이런 기능이 수
행되면 클라이언트는 전체 질의를 다시 할 필요가 없고 또한 저장 프로시저를 참조할 수 있
다. 이런 기능이 있으면 질의는 한번만 해석되고 서버와 클라이언트간의 주고받아야 하는
데이터가 줄어들므로 속도가 향상된다. 또한 서버의 펑션 라이브러리를 가짐으로서 개념적
인 단계를 향상시킬 수 있다. (??? You can also raise the conceptual level by having
libraries of functions in the server.)
트리거는 특별한 이벤트가 발생했을 때 생기는 저장 프로시져이다. 예를 들어 트랜잭션 테
이블에서 레코드가 삭제되고 모든 트랜잭션이 지워질 때 상응하는 테이블을 삭제할 수 있는
저장 프로시저를 설치할 수 있다.
앞으로는 저장 프로시저를 지원할 예정이지만 트리거는 아니다. 트리거는 필요하지 않은 경
우에도 사용될 수 있어서 일반적으로 속도가 느려진다.
언제 저장 프로시저를 사용하게 될지는 앞으로 Mysql에 추가할 목록인 부록 F를 참고하
자.(The TODO)
(** 전반적으로 트랜잭션 처리와 트리거 등은 데이터베이스의 속도를 저하시킵니다. Mysql
은 이렇게 속도에 영향을 미칠 수 있는 부분을 제거하여 빠른 속도를 내는 것이지요. 이러
한 부분이 자기가 사용하는 데이터베이스에서 얼마나 중요한가 판단을 해 보아야 할 것입니
다. 보통 소형 DBMS에서는 회복과 병행수행을 지원하지 않는 경우가 많다. 즉 병행수행은
발생하지 않으며, 회복은 사용자의 문제로 생각한다. 그러므로 사용자가 데이터베이스의 예
비 사본을 준비하며, 고장이 발생하면 작업을 다시 해야 한다. 트리거같은 경우는 자료의 무
결성을 보장하기 위해 필요한 것이다. **)
5.2.5 외래키(Foreign Keys)
SQL 문에서 외래키는 테이블을 조인할 때 사용하지 않지만 대부분 참조 무결성을 확인할
때 사용한다. SELECT 문에서 다중 테이블에서 자료를 가져오길 원하면 테이블을 조인해서
처리할 수 있다.
SELECT * from table1,table2 where table1.id = table2.id
7.12 [JOIN] 참고.
Mysql에서 외래키(FOREIGN KEY) 문은 다른 SQL 제품의 CREATE TABLE 명령과의 호
환성 때문에 존재한다: 외래키는 아무것도 하지 않는다. ON DELETE ... 가 없는
FOREIGN KEY 구문은 대부분 문서적인 목적으로 사용한다. 일부 ODBC 애플리케이션은
이것을 자동적인 WHERE 문을 만들 때 사용할 것이다. 그렇지만 이것은 대부분 생략(무시)
하고 넘어가기 쉽다. 외래키는 때로는 제약조건 체크(constraint check)로 사용을 하지만 이
러한 체크는 데이터가 테이블에 정확한 순서로 들어갈때는 불필요하다. Mysql은 일부 애플
리케이션에서 외래키가 존재하는 것을 필요로 하기 때문에(제대로 작동하든 안하든 상관없
이) 지원하는 것일 뿐이다.
Mysql에서 외래키를 가진 테이블의 레코드를 삭제할 때 애플리케이션에 적절한 DELETE
문을 추가하여 ON DELETE ... 가 수행되는 것을 막음으로써 문제를 해결할 수있다. 경험
상 이렇게 하는 것이 외래키를 사용하는 것과 같이 빠르며(어떤 경우에는 더 빠름) 포팅하
기가 더 좋다.
가까운 시일안에 우리는 외래키 기능을 확장할 것이다. 그래서 최소한 mysqldump와 ODBC
에서 정보가 저장되고 검색할 수 있도록 할 것이다.
5.2.5.1 외래키를 사용하지 않는 이유
외래키를 사용할 때 어디에서 출발해야 할지 모르는 많은 문제가 있다:
- Foreign key들은 상황을 매우 복잡하게 만든다. 왜냐하면, foreing key의 정의가 database에
담겨야 하고, foreign key를 구현하는 것은 "자연스런" File 사용법(data file들을 옮기고,
복사하고, 삭제하는 등...)을 제한한다.
(** 문태준님 역주: 번역이 이상한데 외래키가 있으면 참조 무결성 규칙을 위해 여러 가지
보상 연산을 하게 된다. 이것을 뜻하고 있는 듯하다. **)
- INSERT 와 UPDATE 문은 속도에 많은 영향을 끼친다. 그리고 이런 경우 보통 올바른
순서로 올바른 테이블에 레코드를 삽입하기 때문에 대부분 모든 외래키 체크는 사용할 필요
없다.
- 한쪽의 영향이 전체 데이터베이스에 연쇄 작용을 하기 때문에 테이블에서 업데이트를 할
때 매우 많은 테이블에서 락을 사용해야 한다. 한 테이블에서 먼저 레코드를 삭제하고 그후
에 다른 테이블에서 레코드를 삭제하는 것이 훨씬 빠르다.
- Table를 완전히 지우고, (backup이나 새로운 source로부터) 모든 record들을 다시 복구하는
방법으로 Table을 복구할 수 없다.
- Foreign key를 사용한다면, table을 dump(backup)하고 (그 dump한 자료를) restore하는 데
있어 그 일련의 순서를 적절하게 지켜야 한다.
- 각각의 table의 정의가 쓸모있고 적절하더라도, (각 Table들이 상호참조하게 된다면) 단순한
create문으로는 재생성이 불가능한 circular definition(순환정의)가 쉽게 발생한다.
(역자주: A라는 Table이 B의 자료를 참조하는 foreign key를 담고 있고,
B는 C에 대한 foreign key를, C는 A에 대한 foreign key를 담도록 table이 구성된다면
한번에 A,B,C table을 생성할 수 없다. A,B,C를 만든다음 각각의 foreign key를 지정해
주는 방법을 쓰게 된다.)
외래키의 좋은 점은 단지 다음와 같다. ODBC와 특정한 다른 클라이언트 프로그램에서 어
떻게 테이블이 연결되어 있는지를 볼 수 있고 연결 다이어그램을 보는데 사용하며 애플리케
이션을 만드는데 돕는 점이다.
(역자주: 이 글은 foreign key에 대해서 매우 비판적이다. 하지만, 이것은 foreign key의
일부 기능일뿐이다. 무엇보다도 client가 각 Table내 DATA의 연관관계에 대해서
빼먹었는지에 대해서 일일이 신경쓰지 않게 하고, 항상 자료의 무결성(정합성[?])을
보장한다는 것은 매우 중요하다.
특히, Table이 1~20개가 아닌 100단위가 넘어간다면, client에서 일일이 신경쓰며
programing하는 것도 힘들지만, debugging도 예상보다 힘들어진다.
각 table간의 연결관계를 잘 문서화한다면 programmer들이 foreign key로 고통받기
보다는 관련 Table을 check해야 되는 수고를 덜게된다.
foreign key가 기피되는 주된 이유는 table내의 Data를 수정하는 것이 쉽지 않기
때문이다. 현장 실무자의 논리에 어긋나는 요청을 처리하는 데 있어서
foreign key만큼 거추장스런 놈도 없으리라. )
Mysql에서는 곧 외래키 정의를 저장할 수 있도록 해서 클라이언트가 어떻게 원래의 연결이
만들어졌는지에 대해서 질문하고 답을 받을 수 있도록 할 것이다. 현재의 '.frm' 파일 포맷
은 아직 이것을 지원하지 못하고 있다.
5.2.6 뷰
Mysql은 뷰를 지원하지 않는다. 그렇지만 TODO(이후 개선 목록)에 있다.
MySQL doesn't support views, but this is on the TODO.
5.2.7 `--'을 사용한 주석
일부 다른 SQL 데이터베이스는 '--' 로 주석을 시작한다. mysql 명령 라인 도구가 '--'로
시작하는 모든 줄을 제거할 지라도 Mysql은 '#'을 주석 문의 시작으로 사용한다. 사용자는
또한 C 명령 스타일인 /* this is a comment */ 를 mysql에서 사용할 수 있다. 7.28
[Comment] 참고.
Mysql은 '--'를 지원하지 않을 것이다; '--'은 퇴보한 주석문 형태로 자동으로 생성되는
SQL 질의에서 많은 문제를 발생시킨다. 다음의 예제를 보자. 우리는 자동적으로 payment를
!payment! 의 값으로 입력하도록 하고 있다 :
UPDATE tbl_name SET credit=credit-!payment!
payment의 값이 음수일 때 어떤 일이 생길 것이라 생각하는가?
1--1은 합당한 SQL문이기 때문에 '--'가 주석문의 시작을 의미하는 것을 꺼리는 것이다.
'--' 주석을 포함하는 텍스트 파일의 SQL 프로그램을 가졌다면 다음과 같이 사용해야 한다:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
instead of the normal(정상적인 경우 대신???):
shell> mysql database < text-file-with-funny-comments.sql
명령 파일로 '--' 주석을 '#' 주석으로 바꿀 수 있다:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
다음의 명령으로 원래대로 돌려놓자:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
(** 일부 SQL에서 사용하는 -- 주석문에서 문제가 생길 수 있으므로 MYSQL에서는 #을
주석문으로 사용한다는 말이다 **)
5.3 Mysql이 따르고 있는 표준은 무엇인가?
Entry level SQL92. ODBC level 0-2.
5.4 BLOB 와 TEXT 타입의 제한
BLOB 나 TEXT 필드에서 GROUP BY 나 ORDER BY를 사용하길 원하면 그 필드를 고정
길이 객체로 만들어야 한다. 이렇게 하는 표준적인 방법은 SUBSTRING 펑션을 사용하는
것이다. 예를 보자:
mysql> select comment from tbl_name order by SUBSTRING(comment,20);
이렇게 하지 않으면 정렬할 때 오직 첫 번째 max_sort_lengths (기본값=1024)만을 고려된
다.
BLOB 와 TEXT 는 기본값을 가질 수 없으며 또한 언제나 NULL 컬럼일 것이다.
BLOB and TEXT cannot have DEFAULT values and will also always be NULL
columns.
5.5 COMMIT-ROLLBACK 없이 어떻게 대치할 수 있을까?
Mysql은 COMMIT-ROLLBACK 을 지원하지 않는다. 문제는 COMMIT-ROLLBACK을 효
과적으로 다루기 위해서는 Mysql에서 현재 사용하는 것과 완전히 다른 테이블 설계가 필요
하다는 것이다. Mysql은 또한 테이블을 자동 클린업하는 추가적인 스레드와 더 많은 디시
크를 사용할 수 있는 기능이 필요하다. 이러한 기능은 현재보다 mysql을 2-4배 느리게 만
든다. Mysql은 대부분의 다른 SQL 데이터베이스보다 훨씬 더 빠르다. (전형적으로 최소
2-3대 빠름) 이러한 이유는 Mysql에 COMMIT-ROLLBACK이 없기 때문이다.
당분간은 우리는 SQL 서버 언어의 성능을 향상시키는데 더 주력할 것이다. 대부분
COMMIT-ROLLBACK 기능이 정말로 필요한 경우는 드물다. 또한 이렇게 하는 것이 더 좋
은 성능을 낼 수 있다.
일반적으로 트랜잭션이 필요한 루트는 LOCK TABLES를 사용해 코드를 짤 수 있다. 또한
레코드를 업데이트할 때 커서를 사용할 필요가 없다.
우리는 트랜잭션과 커서를 TODO에 넣었지만 우선권이 높은 것은 아니다. 이러한 기능을
수행한다면 CREATE TABLE 의 옵션으로 될 것이다. 이것은 옵션으로 지정한 테이블에서
만 작동하며 그 테이블은 느리게 될 것이라는 것을 의미한다.
우리는 100% 보편적인 데이터보다는 정말로 빠른 데이터베이스가 필요하다.
COMMIT-ROLLBACK 기능을 수행하더라도 속도에 손상이 없다면 우리는 그것을 지원할
것이다. 당분간은 더 중요하게 해야할 일들이 많이 있다. 우리가 어떤 것에 우선권을 두고
있는지는 TODO를 참고하자. 상위 단계의 지원을 받는 고객은 이것을 바꿀 수 있으며 우선
권이 변경될 수도 있다.
현재의 문제는 실제로 ROLLBACK 이다. 롤백없이 LOCK TABLES을 이용하여 여러 종류
의 COMMIT를 사용할 수 있다. 롤백을 지원하기 위해 Mysql은 업데이트가 된 모든 예전
레코드를 저장하고 롤백이 일이났을 때 시작 시점으로 돌아갈 수 있도록 바꾸어야 한다. 예
를 들어 이러한 것은 전혀 어렵지 않다.(현재의 isamlog 는 이런 경우를 위해 사용할 수 있
다) 그러나 ALTER/DROP/CREATE TABLE에서 롤백을 수행하는 것은 무척 어렵다.
롤백 사용을 피하기 위해 다음의 전략을 사용할 수 있다:
1. 접근하기 원하는 모든 테이블에 락을 사용. LOCK TABLES ...
2. 조건 테스트(Test conditions)
3. 모든 것이 제대로 된다면 업데이트를 한다.
4. UNLOCK TABLES
일반적으로 가능한 롤백을 이용해 트랜잭션을 사용하는 것보다는 이러한 방법이 훨씬 더 빠
르다. 그렇지만 항상 사용가능한 것은 아니다. 이러한 방법으로 해결할 수 없는 유일한 상황
은 업데이트중 누군가가 스레드를 죽였을 때이다. 이런 경우 모든 락은 해제가 된다. 그렇지
만 업데이트의 일부는 실행되지 않을 것이다.
물론 단일 오퍼레이션에서 레코드를 업데이트하는 펑션을 사용할 수 있다. 다음의 테크닉을
사용하며 매우 효율적인 애플리케이션을 만들 수 있다:
- 현재 값과 관련되어 있는 필드를 수정
- 실제로 변화가 생겼을때만 필드를 업데이트
예를 들어, 어떤 고객 정보를 업데이트 할 때 오직 바뀐 데이터만 업데이트를 한다. 그리고
For example, when we are doing updates on some customer information, we update only
the customer data that have changed and test only that none of the changed data, or
data that depend on the changed data, have changed compared to the original row.
변화된 데이터의 테스트는 UPDATE 문에서 WHRE 절을 사용하여 할 수 있다. 레코드가
업데이트되지 않았다면 클라이언트에 다음과 같은 메시지를 준다: "당신이 바꾼 데이터 일
부가 다른 사용자에 의해 바뀌었습니다". 그러고나서 우리는 윈도우에서 예전의 레코드와
현재의 레코드를 비교하여 보여준다. 그러면 사용자는 어떤 고객 정보 레코드를 사용할지
결정할 수 있다.
이렇게 하면 "컬럼 라킹"과 비슷하다. 그렇지만 실제로는 더 빠르다. 왜냐하면 현재의 값과
관련되어 있는 값의 컬럼만 업데이트하기 때문이다. 이렇나 전형적인 업데이트문은 다음과
비슷할 것이다:
UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';
지금 보듯이 이렇게 하면 매우 효율적이며 설사 다른 클라이언트가 pay_back 이나
money_he_owes_us 컬럼의 값을 바꾸었을 때라도 제대로 작동한다.
대부분의 경우, 사용자는 테이블에서 유일한 값(identifiers)을 관리하기 위해 롤백과 테이블
락을 사용하고 싶어한다. 이것은 AUTO_INCREMENT 컬럼과 SQL LAST_INSERT_ID()
펑션, 또는 mysql_insert_id() 의 C API 펑션을 사용하여 더욱 효율적으로 사용할 수 있다.
18.4.49 [mysql_insert_id()] 참고.
TcX에서는 언제나 이런 문제를 해결할 수 있기 때문에 결고 low-level 락을 필요로 하지
않는다. 어떤 경우에는 정말로 로우-락이 필요하다. 그렇지만 이런 경우는 극소수이다. 로우
-레벨 락을 원하면 테이블에서 플래그 컬럼을 사용할 수 있다. 다음과 같다:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
만약 row가 발견되고 row_flag가 원래의 row에서 이미 1이 아니라면 영향을 받은 row의
숫자로서 1일 반환한다.
MySQL returns 1 fro the number of affected rows if the row was found and row_flag
wasn't already 1 in the original row.
6. Mysql 접근 권한 시스템
mysql 은 진보적이지만 비표준적인 보안/권한 시스템을 가지고 있다. 이번 장에서는 이것이
어떻게 작동하는지를 설명하고 있다.
6.1 권한 시스템이란 무엇인가?
Mysql 권한 시스템의 주요 기능은 데이터베이스에서 select, insert, update, delete 권한
을 호스트의 사용자 이름과 관련짓는 것이다.
추가적인 기능에는 익명 사용자 기능과 LOAD DATA INFILE 과 관리자 오퍼레이션과 같은 my
sql만의 특수한 권한을 허용하는 부분이 포함되어 있다.
Mysql에서 인증을 목적으로 사용하는 사용자 이름은 유닉스 사용자 이름(로그인 이름)이나
위도우 사용자 이름고는 전혀 관계가 없다는 것!을 기억하자. 대부분 mysql 클라이언트는 m
ysql 사용자 이름으로 현재의 유닉스 사용자 이름을 사용하여 접속하려 할 것이다. 그렇지
만 이건 오직 편의를 위해서이다. 클라이언트 프로그램은 -u 나 --user 옵션으로 지정한 다
른 이름을 허용한다. 이것은 mysql 사용자 이름에 비밀번호를 설정하지 않으면 데이터베이
스의 보안에 문제가 생길 수 있다는 것을 의미한다. 어떤 이름을 사용하여 서버에 접속하려
고 하는 사람은 각 이름에 비밀번호가 설정되어 있지 않다면 접속에 성공할 것이다.
유닉스 사용자 이름이 일반적으로 8글자로 제한되어 있는 것과 다르게 mysql 사용자 이름은
16글자까지 사용할 수 있다.
mysql 비밀번호는 유닉스의 비밀번호와 아무 관련이 없다. 유닉스 머신에 로그인할 때 사용
하는 비밀번호와 데이터베이스에 접속할 때 사용하는 비밀번호는 전혀 관련이 없다. 또한 m
ysql은 유닉스 로그인 프로세스에서 사용하는 것과 다른 알고리즘으로 비밀번호를 암호화한
다.
6.2 mysql 서버에 접속하기
mysql 클라이언트 프로그램은 일반적으로 연결 패러미터(매개 변수)가 필요하다.: 연결할
호스트, 사용자 이름, 비밀번호. 예를 들어 mysql 클라이언트는 다음과 같이 시작할 수 있
다. (선택 인자는 [ ] 로 닫는다)
shell>; mysql [-h host_name] [-u user_name] [-pyour_pass]
-p와 뒤에 붙은 비밀번호 사이에는 공간이 없다는 것을 기억하자.
-h, -u, -p를 대체할 수 있는 형식으로는 --host=host_name, --user=user_name and --passw
ord=your_pass 이 있다.
mysql은 커맨드 라인에서 연결 매개변수가 빠져있을 때는 기본 값을 사용한다. 기본 호스트
이름은 localhost 이고 기본 사용자 이름은 유닉스 로그인 이름이다.(-p 가 빠져있으면 비
밀번호는 사용하지 않는다) 그래서 만약 유닉스 사용자 이름이 joe 라면 다음의 명령은 동
일하다.:
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql
다른 mysql 클라이언트도 비슷하게 작동한다.
유닉스 시스템에서 연결을 할 때 사용할 수 있는 기본 값이 있어서 클라이언트 프로그램을
사용할 때마다 명령행에서 옵션을 사용하지 않아도 된다:
ㅇ 홈 디렉토리의 '.my.cnf' 설정 파일의 [client] 섹션에서 연결 변수를 설정할 수 있다.
파일에서 이와 연관된 섹션은 다음과 같다:
[client]
host=host_name
user=user_name
password=your_pass
4.14.4 [option files] 참고.
ㅇ 환경 변수를 사용하여 연결 변수를 지정할 수 있다. 호스트는 MYSQL_HOST 로 지정할 수
있다. Mysql 사용자 이름은 USER, LOGNAME, 또는 LOGIN을 사용할 수 있다. (이러한 값들은
이미 유닉스 로그인 이름으로 설정되어 있을 것이다. 그러므로 바꾸지 않는게 좋다) 비밀번
호는 MYSQL_PWD로 지정할 수 있다.(그렇지만 이것은 안전하지 않다; 다음 섹션을 참고하자)
연결 변수가 여러 가지 방법을 지정되었다면 명령행에서 지정한 값이 설정 파일과 환경 변
수로 설정한 것보다 우선권을 가진다. 또한 설정 파일의 값이 환경 변수보다 우선권을 가진
다.
6. 2. 1 비밀번호의 보안 유지
다른 사용자가 발견할 수 있게 비밀번호를 지정하는 방법은 권하지 않는다. 클라이언트 프
로그램을 실행할 때 비밀번호를 지정하는 방법은 아래와 같으며 각 방법마다 위험도를 같이
설명하였다:
ㅇ 명령행에서 -pyour_pass 또는 --password=your_pass 옵션 사용. 이 방법은 편리하지만
위험한 방법이다. 비밀번호를 시스템 상황 프로그램(ps 등)을 통해 볼 수 있기 때문에 다른
사용자가 명랭행으로 볼 수 있다.(mysql 클라이언트는 일반적으로 초기화되는 동안 명령행
인자를 0으로 덮어씌운다. 그렇지만 값을 볼 수 있는 짧은 틈이 여전히 있다)
ㅇ -p 또는 --password 옵션 사용(비밀번호 값을 지정하지는 않음). 이런 경우 클라이언트
프로그램은 터미널에서 비밀번호를 물어본다:
shell> mysql -u user_name -p
Enter password: ********
클라이언트는 비밀번호를 칠 때 터미널에서 '*' 문자를 보여준다. 그러므로 다른 사용자가
비밀번호를 볼 수 없다. 다른 사용자가 볼 수 없으므로 명령행에서 비밀번호를 입력하는 것
보다 훨씬 더 안전하다. 그렇지만 이 방법은 비대화식의 스크립트로 클라이언트 프로그램을
사용하면 적절하지 않다.
ㅇ 설정 파일에 비밀번호 저장. 예를 들어 홈 디렉토리의 '.my.cnf' 파일에서 [client] 섹
션에 비밀번호를 지정할 수 있다.
[client]
password=your_pass
비밀번호를 '.my.cnf' 파일에 저장한다면 그 파일은 그룹이나 다른 사용자가 읽기/쓰기를
할 수 없도록 해야 한다. 파일의 퍼미션이 400 이나 600 인지 확인하자.
4.14.4 [옵션 파일] 참고.
ㅇ 비밀번호를 MYSQL_PWD 환경 변수에 저장할 수 있다. 그렇지만 이 방법은 정말로 위험하
며 사용해서는 안된다. 일부 ps 프로그램은 실행 프로세스의 환경변수를 보여주는 옵션이
있다; MYSQL_PWD에 설정을 하면 다른 사람들이 쉽게 비밀번호를 볼 수 있다. 이런 기능의 p
s가 없는 시스템일지라도 프로세스 환경변수를 검색할 수 있는 방법이 없다고 생각하는 것
은 현명하지 못하다.
이중에서 가장 안전한 방법은 클라이언트 프로그램이 비밀번호를 요구하거나 적절하게 보안
이 된 '.my.cnf' 파일에 비밀번호를 지정하는 것이다.
6.3 mysql에서 제공하는 권한
권한과 관련된 정보는 mysql 데이터베이스의(데이터베이스 이름이 mysql 임) user, db, hos
t, table_priv, columns_priv 테이블에 저장된다. mysql 서버는 시작할 때, 그리고 환경을
지정할 때(6.7 [권한 변경] 참고) 이 테이블의 내용을 읽어들인다.
mysql에서 제공하는 권한을 설정할 때 사용하는 이름은 아래와 같다.테이블의 컬럼 이름은
grant tables의 각 권한 및 권한이 적용되는 context와 연관되어 있다.
Privilege Column Context
(권한) (컬럼) (환경)
select Select_priv tables
insert Insert_priv tables
update Update_priv tables
delete Delete_priv tables
index Index_priv tables
alter Alter_priv tables
create Create_priv databases, tables or indexes
drop Drop_priv databases or tables
grant Grant_priv databases or tables
reload Reload_priv server administration
shutdown Shutdown_priv server administration
process Process_priv server administration
file File_priv file access on server
select, insert, update, delete 권한은 데이터베이스의 테이블에서 레코드에 대한 오퍼레
이션을 할 수 있도록 허용한다.
SELECT 문은 오직 실제로 테이블에서 줄(레코드)를 가져올 때만 select 권한이 필요하다.
서버의 데이터베이스에 접근 권한이 없는 경우라고 하더라도 특정한 SELECT 문은 사용할
수 있다. 예를 들면 간단한 계산을 위해 mysql 클라이언트를 사용할 수 있다:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
index(인덱스) 권한은 인덱스를 생성하거나 제거할 수 있다.
alter 권한은 ALTER TABLE 을 사용할 수 있도록 한다.
create 와 drop 권한은 새로운 데이터베이스와 테이블을 생성하거나 존재하는 데이터베이
스와 테이블을 제거할 수 있도록 허용한다.
사용자에게 mysql 데이터베이스의 drop 권한을 허용하면, 그 사용자는 mysql 접근권한 정보
가 저장된 데이터베이스를 없앨 수 있다는것!을 명심하자.
grant 권한은 사용자가 가지고 있는 권한을 다른 사용자가 가질 수 있도록 허용한다.
file 권한은 LOAD DATA INFILE and SELECT ... INTO OUTFILE 문을 이용하여 서버에 파일을
저장하고 읽을 수 있는 권한을 허용한다. 이러한 권한을 가진 사용자는 mysql 서버가 읽고
쓸 수 있는 파일을 읽고 쓸 수 있는 권한이 허용된다.
나머지 권한들은 관리자 오퍼레이션에 사용되며 mysqladmin 프로그램의 기능을 수행한다.
아래의 테이블은 각 관리자 권한에 따라 사용할 수 있는 mysqladmin 명령을 보여준다:
Privilege Commands permitted to privilege holders
(권한) (권한에 따라 허용되는 명령)
reload reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tab
les
shutdown shutdown
process processlist, kill
reload 명령은 서버가 grant 테이블을 다시 읽어 들인다. refresh 명령은 모든 열린 테이블
을 닫으며 로그 파일을 열고 닫는다. flush-privileges 는 reload 명령과 동의어이다. 다른
flush-* 명령은 refresh 와 비슷한 기능을 수행한다. 그러나 범위에 제한이 있으며 어떤 경
우에는 더 선택할 만하다. 예를 들어 로그 파일만 닫고 다시 열고자 한다면 flush-logs 가
refresh보다 더 나은 선택이다.
(** flush의 옵션으로는 호스트, 로그 파일, 권한 설정, 테이블, status variables 설정 변
수가 있다. SQL 문에서 또는 mysqladmin 유틸리티를 사용하면 된다. **)
shutdown 명령은 서버를 셧다운한다. (** 이거 번역 맞아~~?? **)
processlist 명령은 서버에서 실행되고 있는 스레드에 대한 정보를 보여준다. kill 명령은
서버 스레드를 죽인다. 언제나 자신의 스레드는 보거나 죽일 수 있지만 다른 사용자에 의해
시작된 스레드는 프로세스 권한이 있어야 보거나 죽일 수 있다.
몇가지 권한은 조심스럽게 허용해야 한다:y:
ㅇ grant(허용) 권한은 사용자가 다른 사용자의 권한을 설정할 수 있도록 허용한다. 다른
권한과 grant 권한을 가진 두 사용자는 권한을 결합할 수 있다.
ㅇ file 권한은 서버에서 모든 사람이 읽기 가능한 파일을 읽는데 남용 될 수 있.... SELEC
T 문을 이용해 접근할 수 있는 내용...
The file privilege can be abused to read any world-readable file on the server into a
database table, the contents of which can then be accessed using SELECT.
(** 굳이 권한을 주지 않아도 이용할 수 있는 것은 권한을 주지 않는게 낫다는 말이겠지요
**)
ㅇ shutdown 권한은 다른 사용자에가 완전히 서비스를 사용하지 못하도록 남용될 수 있다.
ㅇ process 권한은 비밀번호를 설정하고 바꾸는 질의를 포함해 현재 수행하고 있는 질의를
보는데 사용될 수 있다.
ㅇ mysql 데이터베이스에 대한 권한은 비밀번호와 다른 접근 권한 정보를 바꾸는 데 사용될
수 있다. (비밀번호가 암호화되어 저장되었다고 하더라도, 충분한 권한을 가진 악의있는 사
용자는 다른 비밀번호롤 바꿀 수 있다)
mysql 권한 시스템으로 다룰 수 없는 몇가지가 있다:
ㅇ 접근을 거부할 사용자를 명백하게 지정할 수 없다. 왜냐하면 사용자와 연결을 거부하는
것을 완전하게 연관시킬 수 없기 때문이다.
ㅇ 사용자가 테이터베이스에서 테이블을 만들고 지울 수 있는 권한을 가질 수 있지만 데이
터베이스 자체를 만들고 삭제할 수는 없도록 지정할 수 없다.
(** 그러니까 create 와 drop 권한을 주면 데이터베이스 자체에 대해 제어할 수 있지요. 그
안의 테이블만 만들고 지울 수 있도록 하지는 못한다는 말 **)
{{}}
6.4 권한 시스템 작동 방법
mysql 권한 시스템은 모든 사용자가 허용된 것만큼만 할 수 있도록 보증한다. mysql 서버에
연결할 때, 사용자 확인은 연결한 호스트와 사용자가 지정한 사용자 이름에 의해 결정된다.
시스템은 사용자 확인과 지정한 권한에 따라 권한을 허용한다.
mysql은 사용자를 확인하는데 호스트이름과 사용자 이름 둘다 사용한다. 왜냐면 인터넷에서
이름이 같다고 같은 사용자라고 생각할 수는 없기 때문이다. 예를 들어 whitehouse.gov에서
접속하는 사용자 bill 은 microsoft.com에서 접속하는 사용자 bill 과 같은 사람일 필요는
없다. mysql은 때론 같은 이름을 가지고 있더라도 호스트를 이용해 사용자를 구별한다 : wh
itehouse.gov에서 접속하는 bill에게 특정한 권한을 허용할 수 있고 microsoft.com에서 접
속하는 bill에게 다른 권한을 허용할 수 있다.
mysql의 접근 제어는 두가지 단계가 있다:
단계 1: 서버에서 사용자가 연결할 수 있는지 없는지 판단
단계 2 (서버에 사용자가 연결이 허용되었을 경우) : 사용자가 수행하려는 명령에 대해 충
분한 권한이 있는지 각 요청마다 서버에서 판단.예를 들면, 데이터베이스의 테이블에서 sel
ect rows를 할때, 또는 데이터베이스에서 테이블을 제거할 때 서버에서 테이블에 대한 sele
ct 권한이 있는지 데이터베이스에 대한 제거 권한이 있는지 확인을 한다.
서버는 접근 제어의 각 두 단계에서 mysql 데이터베이스의 user, db, host 테이블을 이용한
다.grant 테이블의 필드는 아래와 같다:
Table name user db host
Scope fields Host Host Host
(필드 범위) User Db Db
Password User
Privilege fields Select_priv Select_priv Select_priv
(권한 필드) Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
접권 제어의 두번째 단계를 위해(요청 인증), 요청이 테이블에 관계된 것이라면 추가적으로
tables_priv 와 columns_priv 테이블을 참고한다. 이 테이블의 필드는 다음과 같다:
Table name tables_priv columns_priv
Scope fields Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege fields Table_priv Type
Column_priv
Other fields Timestamp Timestamp
Grantor
각 승인(grant) 테이블은 필드 범위와 권한 필드로 구성되어 있다.
필드 범위는 테이블에서 각 엔트리의 범위를 결정한다. 다시 말하면 엔트리가 적용되는 con
text(환경, 배경)이다. 예를 들면, Host 와 User 값이 'thomas.loc.gov' 와 'bob' 인 user
테이블 엔트리는 thomas.loc.gov 호스트에서 bob이 연결을 할때 서버에서 인증을 하는데 사
용된다.비슷하게 Host, User, db 필드값이 'thomas.loc.gov', 'bob', 'reports' 인 db 테
이블 엔트리는 thomas.loc.gov 호스트에서 bob 이 reports 데이터베이스에 접근할 때 사용
된다. tables_priv 와 columns_priv 테이블은 테이블이나 각 엔트리가 적용될 수 있는 테이
블/컬럼 조합을 가리키는 범위 필드를 포함하고 있다.
접권 체크를 하기 위해, HOst 값 비교는 대소문자를 구별하지 않는다. User, Password, Db,
Table_name 값은 대소문자를 구별한다. mysql 3.22.12 와 이후 버전에서 Column_name 값은
대소문자를 구별하지 않는다. (3.22.11에서는 대소문자 구별함)
권한 필드는 테이블 엔트리에 승인되는 권한을 가리키며 이는 수행할 수 있는 오페레이션이
다. 서버는 사용자의 권한을 완벽하게 설정하기 위해 다양한 승인(grant) 테이블의 정보를
조합한다. 여기에 사용하는 규칙은 6.6 [Request access]를 참고하자.
범위 필드는 문자열이며 다음과 같이 정의되었다; 기본 값은 빈 문자열이다:
Field name Type
Host CHAR(60)
User CHAR(16)
Password CHAR(16)
Db CHAR(64) (CHAR(60) for the tables_priv and columns_priv tables)
user, db, host 테이블에서 모든 권한 필드는 ENUM('N','Y')로 정의되어 있다. -- 각각은
'N' 나 'Y'의 값을 가지며 기본값은 'N' 이다.
(** ENUM 타입은 목록 값중 오직 하나의 값만 가진다.필드 타입 참조 **)
tables_priv 와 columns_priv 테이블에서 권한 필드는 SET 필드로 정의된다:
(** SET 타입은 목록 값중에 0이나 1개 이상의 값을 가진다 **)
Table name Field name Possible set elements
tables_priv Table_priv 'Select', 'Insert', 'Update',
'Delete', 'Create', 'Drop', 'Grant',
'References', 'Index', 'Alter'
tables_priv Column_priv 'Select', 'Insert', 'Update',
'References'
columns_priv Type 'Select', 'Insert', 'Update',
'References'
간단하게 말해서 서버는 승인(grant) 테이블을 다음과 같이 사용한다:
ㅇ user 테이블의 scope(범위) 필드는 들어오는 연결에 대해 허용할 것인지 거부할 것인지
를 결정한다. 허용된 연결에 대하여, 권한 필드는 사용자의 전체적인 (superuser) 권한을
가리킨다.
ㅇ db 와 host 테이블은 함께 사용된다:
- db 테이블의 범위 필드는 어떤 호스트에서 어떤 데이터베이스에 대해 어떤 사용
자가 접근할 수 있는지 결정한다. 권한 필드는 어떤 오퍼레이션이 허용되었는지를 결정한
다.
- host 테이블은 db 테이블의 엔트리를 여러개의 호스트에 적용하려고 할 때 db 테
이블의 확장을 위해 사용한다.예를 들어, 사용자가 현재 네트웍의 여러 호스트에서 데이터
베이스를 사용할 수 있도록 하려면,사용자의 "db" 테이블 엔트리에 Host 값을 비워두고, "h
ost" 테이블에 각 호스트의 엔트리를 넣으면 된다.이러한 절차는 6,6 [Request access]에
자세하게 나와 있다.
ㅇ tables_priv 와 columns_priv 테이블은 db 테이블과 비슷하다. 그렇지만 더 세부적으로
지정할 수 있다: 이 테이블들은 데이터베이스 단계에서 더 나아가 테이블과 컬럼 단계에 적
용할 수 있다.
관리 권한(reload, shutdown,기타..)은 오직 user 테이블에서만 지정을 할 수 있다는 것을
기억하자! 왜냐면 관리자 오퍼레이션은 서버 자체에 대한 오퍼레이션이며 특정한 데이터베
이스를 지정하는 것이 아니다. 그러므로 이러한 권한은 다른 승인(grant) 테이블에 있을 필
요가 없다.실제로, 오직 user 테이블만이 관리자 오퍼레이션을 수행할 수 있는지 없는지를
결정할 때 참고가 된다.
파일(file) 권한도 마찬가지로 user 테이블에서만 지정한다.위와 같은 관리 권한은 아니다.
그렇지만 서버 호스트에서 파일을 읽거나 쓸 수 있는 권한은 접근하고 있는 데이터베이스와
무관한 것이다.
mysqld 서버는 시작할 때 승인(grant) 테이블을 한번 읽는다. 승인 테이블을 변경하고 효과
를 발휘하려면 6.7 [Privilege changes]를 참고하자.
승인 테이블의 내용을 수정했을 때 원하는대로 권한이 설정되었는지 확인하는 것은 좋은 생
각이다. 유용한 진단 프로그램은 mysqlaccess 스크립트로서 Yves CArlier 가 mysql distrib
ution 으로 제공하고 있다. 어떻게 작동하고 있는지 확인하기 위해 mysqlaccess 에 --help
옵션을 주어 실행해보자. 물론 6.11 [Access denied] 와 6.12 [Security]를 참고하자.
mysqlaccess는 오직 user, db, host 테이블만 점검한다. 테이블이나 컬럼 단계의 권한까지
는 점검하지 않는다는 것을 기억하자.
6.5 접근 제어, 단계 1 : 연결 확인(인증)
mysql 서버에 접속하려고 할 때 서버는 사용자 확인과 비밀번호를 통해 접속을 허용하거나
거부한다. 사용자 확인이 안되면 서버는 접속을 완전히 거부한다. 사용자 확인이 되면 서버
는 연결을 받아들이고 2번째 단계로 들어가며 요청을 기다린다.
사용자 확인은 두가지 정보에 기반하고 있다:
ㅇ 접속하는 호스트
ㅇ mysql 사용자 이름
사용자 확인은 user 테이블의 세가지 범위 필드(Host, User, Password)를 사용하여 수행된
다. 서버는 user 테이블 엔트리의 호스트이름과 사용자 이름이 맞으며, 비밀번호가 정확할
때만 접속을 받아들인다.
아래와 같이 user 테이블의 범위 필드값을 지정할 수 있다:
ㅇ Host 값은 호스트 이름이나 IP 숫자 또는 로컬 호스트를 가리키는 'localhost' 가 될 것
이다.
ㅇ Host 필드에서 '%' 와 '_' 의 와일드카드 문자를 사용할 수 있다.
ㅇ '%'의 Host 값은 모든 호스트 이름을 나타낸다. 공백의 호스트 값은 '%'와 같다. 특정한
호스트에 대한 이러한 값은 당신의 서버에 연결할 수 있다는 것을 참고하자.
ㅇ 와일드카드 문자는 User 필드에는 허용되지 않는다. 그렇지만 모든 유저에 해당하는 공
백으로 둘 수 있다. 연결을 하려는 목록에 공백 사용자 이름이 있다면 클라이언트에서 실제
로 지정한 이름 대신에 그 사용자는 익명 사용자, 이름이 없는 사용자로서 간주된다.
ㅇ Password 필드는 공백으로 될 수 있다.이것은 아무런 비밀번호나 사용할 수 있다는 것을
의미하는 것은 아니며 사용자는 비밀번호를 지정하지 않고 연결을 해야 한다는 의미이다.
아래의 테이블은 연결 요청에 적용하는 "user" 테이블 목록의 Host, User 값이 어떻게 조합
되는지를 보여주는 예제이다:
호스트값/사용자 값 : 목록에 해당하는 연결
'thomas.loc.gov'/'fred' : thomas.loc.gov 에서 연결하는 fred
'thomas.loc.gov'/'' : thomas.loc.gov 에서 연결하는 모든 사용자
'%'/'fred' : 모든 호스트에서 연결하는 fred
'%'/'' : 모든 호스트에서 연결하는 모든 사용자
'%.loc.gov'/'fred' : loc.gov 도메인의 모든 호스트에서 연결하는 fred
'x.y.%'/'fred' : x.y.net, x.y.com, x.y.edu 등에서 접속하는 fred (이것은 아마도 유용하
지 않을 것이다)
'144.155.166.177'/'fred' : 144.155.166.177의 IP 주소에서 접속하는 fred
'144.155.166.%'/'fred' : 144.155.166 클래스 C 서브넷의 모든 호스트에서 접속하는 fred
Host 필드에서 IP에 와일드 카드를 사용할 수 있기 때문에(예를 들어 '144.155.166.%' 는
서브넷의 모든 호스트에 적용된다) 144.155.166.somewhere 와 같은 호스트 이름을 이용하여
부당하게 이용할 가능성이 생길 수 있다. 이러한 것을 막기 위해 mysql은 숫자와 도트(.)으
로 시작하는 호스트이름은 허용하지 않는다. 1.2.foo.com 과 같은 호스트라면 이러한 호스
트이름은 승인(grant) 테이블의 Host 컬럼과 매치되지 않는다. IP 숫자만이 IP 와일드 카드
값과 매치시킬 수 있다.
만약 한개 이상의 user table 목록이 있다면 서버는 어떻게 user table을 선택할까? 이런
경우에는 user table의 정렬 순서에 따라 해결을 하며 , 정열은 서버가 시작할때 수행이 된
다. user table이 다음과 같다고 가정해보자:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
서버가 테이블을 읽을 때, 먼저 특정하게 지정된 값이 있는 호스트부터 목록을 정열한다.
(Host 컬럼에서 '%'는 "모든 호스트"를 의미하여 최소한도로 지정하는 것이다) 목록에서 호
스트값이 같으면 먼저 특정하게 지정된 사용자가 있는 것부터 정열한다.(공백으로 되어 있
는 User 값은 "모든 사용자"를 의미하여 최소한도로 지정하는 것이다.) 이렇게 하면 정열된
user 테이블은 다음과 같다:
+-----------+----------+-
| Host | User | ...
+-----------+----------+
(현재 최신 매뉴얼은 3.22 레퍼런스 매뉴얼이며 3.22.14b-gamma입니다.)
5, 6, 9, 10, 11, 12, 13, 15, 17, 18장 번역 : 문태준(http://www.mysql.com/Manual_split/manual_Todo.html).
이것이 가장 최신 버전의 TODO 목록이다. 부록 F [TODO] 참고.
5.2.1 Sub-selects
다음은 Mysql에서 작동하지 않는다:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
Mysql에서는 오직 INSERT ... SELECT ... and REPLACE ... SELECT ... 만을 지원한다.
독립적인 서브-select 문은 3.23.0에서 아마도 사용할 수 있을 것이다. 그대신 현재 IN() 펑
션을 사용할 수 있다.
5.2.2 SELECT INTO TABLE
Mysql은 아직 SELECT ... INTO TABLE ....을 지원하지 않는다. 현재, Mysql은 오직
SELECT ... INTO OUTFILE ..., 만을 지원하며 기본적으로는 동일하다.
5.2.3 트랜잭션(Transactions)
트랜잭션은 지원되지 않는다. Mysql은 곧 atomic(원자성?) 오퍼레이션을 지원할 것이며
atomic 오퍼레이션은 rollback이 없는 트랜잭션과 비슷하다. atomic 오퍼레이션을 사용하며
insert/select/모든 명령의 그룹을 실행할 수 있으며 어떤 스레드도 충돌하지 않을 수 있도록
보장해준다. 이 문맥에서 일반적으로 롤백(rollback)은 필요없다. 현재 LOCK TABLES 와
UNLOCK TABLES 명령을 이용하여 다른 스레드가 충돌하는 것을 막을 수 있다. 7.23
[Lock Tables] 참고.
5.2.4 저장 프로시저와 트리거
저장 프로시저는 서버에서 컴파일되고 저장될 수 있는 SQL 명령 세트이다. 이런 기능이 수
행되면 클라이언트는 전체 질의를 다시 할 필요가 없고 또한 저장 프로시저를 참조할 수 있
다. 이런 기능이 있으면 질의는 한번만 해석되고 서버와 클라이언트간의 주고받아야 하는
데이터가 줄어들므로 속도가 향상된다. 또한 서버의 펑션 라이브러리를 가짐으로서 개념적
인 단계를 향상시킬 수 있다. (??? You can also raise the conceptual level by having
libraries of functions in the server.)
트리거는 특별한 이벤트가 발생했을 때 생기는 저장 프로시져이다. 예를 들어 트랜잭션 테
이블에서 레코드가 삭제되고 모든 트랜잭션이 지워질 때 상응하는 테이블을 삭제할 수 있는
저장 프로시저를 설치할 수 있다.
앞으로는 저장 프로시저를 지원할 예정이지만 트리거는 아니다. 트리거는 필요하지 않은 경
우에도 사용될 수 있어서 일반적으로 속도가 느려진다.
언제 저장 프로시저를 사용하게 될지는 앞으로 Mysql에 추가할 목록인 부록 F를 참고하
자.(The TODO)
(** 전반적으로 트랜잭션 처리와 트리거 등은 데이터베이스의 속도를 저하시킵니다. Mysql
은 이렇게 속도에 영향을 미칠 수 있는 부분을 제거하여 빠른 속도를 내는 것이지요. 이러
한 부분이 자기가 사용하는 데이터베이스에서 얼마나 중요한가 판단을 해 보아야 할 것입니
다. 보통 소형 DBMS에서는 회복과 병행수행을 지원하지 않는 경우가 많다. 즉 병행수행은
발생하지 않으며, 회복은 사용자의 문제로 생각한다. 그러므로 사용자가 데이터베이스의 예
비 사본을 준비하며, 고장이 발생하면 작업을 다시 해야 한다. 트리거같은 경우는 자료의 무
결성을 보장하기 위해 필요한 것이다. **)
5.2.5 외래키(Foreign Keys)
SQL 문에서 외래키는 테이블을 조인할 때 사용하지 않지만 대부분 참조 무결성을 확인할
때 사용한다. SELECT 문에서 다중 테이블에서 자료를 가져오길 원하면 테이블을 조인해서
처리할 수 있다.
SELECT * from table1,table2 where table1.id = table2.id
7.12 [JOIN] 참고.
Mysql에서 외래키(FOREIGN KEY) 문은 다른 SQL 제품의 CREATE TABLE 명령과의 호
환성 때문에 존재한다: 외래키는 아무것도 하지 않는다. ON DELETE ... 가 없는
FOREIGN KEY 구문은 대부분 문서적인 목적으로 사용한다. 일부 ODBC 애플리케이션은
이것을 자동적인 WHERE 문을 만들 때 사용할 것이다. 그렇지만 이것은 대부분 생략(무시)
하고 넘어가기 쉽다. 외래키는 때로는 제약조건 체크(constraint check)로 사용을 하지만 이
러한 체크는 데이터가 테이블에 정확한 순서로 들어갈때는 불필요하다. Mysql은 일부 애플
리케이션에서 외래키가 존재하는 것을 필요로 하기 때문에(제대로 작동하든 안하든 상관없
이) 지원하는 것일 뿐이다.
Mysql에서 외래키를 가진 테이블의 레코드를 삭제할 때 애플리케이션에 적절한 DELETE
문을 추가하여 ON DELETE ... 가 수행되는 것을 막음으로써 문제를 해결할 수있다. 경험
상 이렇게 하는 것이 외래키를 사용하는 것과 같이 빠르며(어떤 경우에는 더 빠름) 포팅하
기가 더 좋다.
가까운 시일안에 우리는 외래키 기능을 확장할 것이다. 그래서 최소한 mysqldump와 ODBC
에서 정보가 저장되고 검색할 수 있도록 할 것이다.
5.2.5.1 외래키를 사용하지 않는 이유
외래키를 사용할 때 어디에서 출발해야 할지 모르는 많은 문제가 있다:
- Foreign key들은 상황을 매우 복잡하게 만든다. 왜냐하면, foreing key의 정의가 database에
담겨야 하고, foreign key를 구현하는 것은 "자연스런" File 사용법(data file들을 옮기고,
복사하고, 삭제하는 등...)을 제한한다.
(** 문태준님 역주: 번역이 이상한데 외래키가 있으면 참조 무결성 규칙을 위해 여러 가지
보상 연산을 하게 된다. 이것을 뜻하고 있는 듯하다. **)
- INSERT 와 UPDATE 문은 속도에 많은 영향을 끼친다. 그리고 이런 경우 보통 올바른
순서로 올바른 테이블에 레코드를 삽입하기 때문에 대부분 모든 외래키 체크는 사용할 필요
없다.
- 한쪽의 영향이 전체 데이터베이스에 연쇄 작용을 하기 때문에 테이블에서 업데이트를 할
때 매우 많은 테이블에서 락을 사용해야 한다. 한 테이블에서 먼저 레코드를 삭제하고 그후
에 다른 테이블에서 레코드를 삭제하는 것이 훨씬 빠르다.
- Table를 완전히 지우고, (backup이나 새로운 source로부터) 모든 record들을 다시 복구하는
방법으로 Table을 복구할 수 없다.
- Foreign key를 사용한다면, table을 dump(backup)하고 (그 dump한 자료를) restore하는 데
있어 그 일련의 순서를 적절하게 지켜야 한다.
- 각각의 table의 정의가 쓸모있고 적절하더라도, (각 Table들이 상호참조하게 된다면) 단순한
create문으로는 재생성이 불가능한 circular definition(순환정의)가 쉽게 발생한다.
(역자주: A라는 Table이 B의 자료를 참조하는 foreign key를 담고 있고,
B는 C에 대한 foreign key를, C는 A에 대한 foreign key를 담도록 table이 구성된다면
한번에 A,B,C table을 생성할 수 없다. A,B,C를 만든다음 각각의 foreign key를 지정해
주는 방법을 쓰게 된다.)
외래키의 좋은 점은 단지 다음와 같다. ODBC와 특정한 다른 클라이언트 프로그램에서 어
떻게 테이블이 연결되어 있는지를 볼 수 있고 연결 다이어그램을 보는데 사용하며 애플리케
이션을 만드는데 돕는 점이다.
(역자주: 이 글은 foreign key에 대해서 매우 비판적이다. 하지만, 이것은 foreign key의
일부 기능일뿐이다. 무엇보다도 client가 각 Table내 DATA의 연관관계에 대해서
빼먹었는지에 대해서 일일이 신경쓰지 않게 하고, 항상 자료의 무결성(정합성[?])을
보장한다는 것은 매우 중요하다.
특히, Table이 1~20개가 아닌 100단위가 넘어간다면, client에서 일일이 신경쓰며
programing하는 것도 힘들지만, debugging도 예상보다 힘들어진다.
각 table간의 연결관계를 잘 문서화한다면 programmer들이 foreign key로 고통받기
보다는 관련 Table을 check해야 되는 수고를 덜게된다.
foreign key가 기피되는 주된 이유는 table내의 Data를 수정하는 것이 쉽지 않기
때문이다. 현장 실무자의 논리에 어긋나는 요청을 처리하는 데 있어서
foreign key만큼 거추장스런 놈도 없으리라. )
Mysql에서는 곧 외래키 정의를 저장할 수 있도록 해서 클라이언트가 어떻게 원래의 연결이
만들어졌는지에 대해서 질문하고 답을 받을 수 있도록 할 것이다. 현재의 '.frm' 파일 포맷
은 아직 이것을 지원하지 못하고 있다.
5.2.6 뷰
Mysql은 뷰를 지원하지 않는다. 그렇지만 TODO(이후 개선 목록)에 있다.
MySQL doesn't support views, but this is on the TODO.
5.2.7 `--'을 사용한 주석
일부 다른 SQL 데이터베이스는 '--' 로 주석을 시작한다. mysql 명령 라인 도구가 '--'로
시작하는 모든 줄을 제거할 지라도 Mysql은 '#'을 주석 문의 시작으로 사용한다. 사용자는
또한 C 명령 스타일인 /* this is a comment */ 를 mysql에서 사용할 수 있다. 7.28
[Comment] 참고.
Mysql은 '--'를 지원하지 않을 것이다; '--'은 퇴보한 주석문 형태로 자동으로 생성되는
SQL 질의에서 많은 문제를 발생시킨다. 다음의 예제를 보자. 우리는 자동적으로 payment를
!payment! 의 값으로 입력하도록 하고 있다 :
UPDATE tbl_name SET credit=credit-!payment!
payment의 값이 음수일 때 어떤 일이 생길 것이라 생각하는가?
1--1은 합당한 SQL문이기 때문에 '--'가 주석문의 시작을 의미하는 것을 꺼리는 것이다.
'--' 주석을 포함하는 텍스트 파일의 SQL 프로그램을 가졌다면 다음과 같이 사용해야 한다:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
instead of the normal(정상적인 경우 대신???):
shell> mysql database < text-file-with-funny-comments.sql
명령 파일로 '--' 주석을 '#' 주석으로 바꿀 수 있다:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
다음의 명령으로 원래대로 돌려놓자:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
(** 일부 SQL에서 사용하는 -- 주석문에서 문제가 생길 수 있으므로 MYSQL에서는 #을
주석문으로 사용한다는 말이다 **)
5.3 Mysql이 따르고 있는 표준은 무엇인가?
Entry level SQL92. ODBC level 0-2.
5.4 BLOB 와 TEXT 타입의 제한
BLOB 나 TEXT 필드에서 GROUP BY 나 ORDER BY를 사용하길 원하면 그 필드를 고정
길이 객체로 만들어야 한다. 이렇게 하는 표준적인 방법은 SUBSTRING 펑션을 사용하는
것이다. 예를 보자:
mysql> select comment from tbl_name order by SUBSTRING(comment,20);
이렇게 하지 않으면 정렬할 때 오직 첫 번째 max_sort_lengths (기본값=1024)만을 고려된
다.
BLOB 와 TEXT 는 기본값을 가질 수 없으며 또한 언제나 NULL 컬럼일 것이다.
BLOB and TEXT cannot have DEFAULT values and will also always be NULL
columns.
5.5 COMMIT-ROLLBACK 없이 어떻게 대치할 수 있을까?
Mysql은 COMMIT-ROLLBACK 을 지원하지 않는다. 문제는 COMMIT-ROLLBACK을 효
과적으로 다루기 위해서는 Mysql에서 현재 사용하는 것과 완전히 다른 테이블 설계가 필요
하다는 것이다. Mysql은 또한 테이블을 자동 클린업하는 추가적인 스레드와 더 많은 디시
크를 사용할 수 있는 기능이 필요하다. 이러한 기능은 현재보다 mysql을 2-4배 느리게 만
든다. Mysql은 대부분의 다른 SQL 데이터베이스보다 훨씬 더 빠르다. (전형적으로 최소
2-3대 빠름) 이러한 이유는 Mysql에 COMMIT-ROLLBACK이 없기 때문이다.
당분간은 우리는 SQL 서버 언어의 성능을 향상시키는데 더 주력할 것이다. 대부분
COMMIT-ROLLBACK 기능이 정말로 필요한 경우는 드물다. 또한 이렇게 하는 것이 더 좋
은 성능을 낼 수 있다.
일반적으로 트랜잭션이 필요한 루트는 LOCK TABLES를 사용해 코드를 짤 수 있다. 또한
레코드를 업데이트할 때 커서를 사용할 필요가 없다.
우리는 트랜잭션과 커서를 TODO에 넣었지만 우선권이 높은 것은 아니다. 이러한 기능을
수행한다면 CREATE TABLE 의 옵션으로 될 것이다. 이것은 옵션으로 지정한 테이블에서
만 작동하며 그 테이블은 느리게 될 것이라는 것을 의미한다.
우리는 100% 보편적인 데이터보다는 정말로 빠른 데이터베이스가 필요하다.
COMMIT-ROLLBACK 기능을 수행하더라도 속도에 손상이 없다면 우리는 그것을 지원할
것이다. 당분간은 더 중요하게 해야할 일들이 많이 있다. 우리가 어떤 것에 우선권을 두고
있는지는 TODO를 참고하자. 상위 단계의 지원을 받는 고객은 이것을 바꿀 수 있으며 우선
권이 변경될 수도 있다.
현재의 문제는 실제로 ROLLBACK 이다. 롤백없이 LOCK TABLES을 이용하여 여러 종류
의 COMMIT를 사용할 수 있다. 롤백을 지원하기 위해 Mysql은 업데이트가 된 모든 예전
레코드를 저장하고 롤백이 일이났을 때 시작 시점으로 돌아갈 수 있도록 바꾸어야 한다. 예
를 들어 이러한 것은 전혀 어렵지 않다.(현재의 isamlog 는 이런 경우를 위해 사용할 수 있
다) 그러나 ALTER/DROP/CREATE TABLE에서 롤백을 수행하는 것은 무척 어렵다.
롤백 사용을 피하기 위해 다음의 전략을 사용할 수 있다:
1. 접근하기 원하는 모든 테이블에 락을 사용. LOCK TABLES ...
2. 조건 테스트(Test conditions)
3. 모든 것이 제대로 된다면 업데이트를 한다.
4. UNLOCK TABLES
일반적으로 가능한 롤백을 이용해 트랜잭션을 사용하는 것보다는 이러한 방법이 훨씬 더 빠
르다. 그렇지만 항상 사용가능한 것은 아니다. 이러한 방법으로 해결할 수 없는 유일한 상황
은 업데이트중 누군가가 스레드를 죽였을 때이다. 이런 경우 모든 락은 해제가 된다. 그렇지
만 업데이트의 일부는 실행되지 않을 것이다.
물론 단일 오퍼레이션에서 레코드를 업데이트하는 펑션을 사용할 수 있다. 다음의 테크닉을
사용하며 매우 효율적인 애플리케이션을 만들 수 있다:
- 현재 값과 관련되어 있는 필드를 수정
- 실제로 변화가 생겼을때만 필드를 업데이트
예를 들어, 어떤 고객 정보를 업데이트 할 때 오직 바뀐 데이터만 업데이트를 한다. 그리고
For example, when we are doing updates on some customer information, we update only
the customer data that have changed and test only that none of the changed data, or
data that depend on the changed data, have changed compared to the original row.
변화된 데이터의 테스트는 UPDATE 문에서 WHRE 절을 사용하여 할 수 있다. 레코드가
업데이트되지 않았다면 클라이언트에 다음과 같은 메시지를 준다: "당신이 바꾼 데이터 일
부가 다른 사용자에 의해 바뀌었습니다". 그러고나서 우리는 윈도우에서 예전의 레코드와
현재의 레코드를 비교하여 보여준다. 그러면 사용자는 어떤 고객 정보 레코드를 사용할지
결정할 수 있다.
이렇게 하면 "컬럼 라킹"과 비슷하다. 그렇지만 실제로는 더 빠르다. 왜냐하면 현재의 값과
관련되어 있는 값의 컬럼만 업데이트하기 때문이다. 이렇나 전형적인 업데이트문은 다음과
비슷할 것이다:
UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';
지금 보듯이 이렇게 하면 매우 효율적이며 설사 다른 클라이언트가 pay_back 이나
money_he_owes_us 컬럼의 값을 바꾸었을 때라도 제대로 작동한다.
대부분의 경우, 사용자는 테이블에서 유일한 값(identifiers)을 관리하기 위해 롤백과 테이블
락을 사용하고 싶어한다. 이것은 AUTO_INCREMENT 컬럼과 SQL LAST_INSERT_ID()
펑션, 또는 mysql_insert_id() 의 C API 펑션을 사용하여 더욱 효율적으로 사용할 수 있다.
18.4.49 [mysql_insert_id()] 참고.
TcX에서는 언제나 이런 문제를 해결할 수 있기 때문에 결고 low-level 락을 필요로 하지
않는다. 어떤 경우에는 정말로 로우-락이 필요하다. 그렇지만 이런 경우는 극소수이다. 로우
-레벨 락을 원하면 테이블에서 플래그 컬럼을 사용할 수 있다. 다음과 같다:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
만약 row가 발견되고 row_flag가 원래의 row에서 이미 1이 아니라면 영향을 받은 row의
숫자로서 1일 반환한다.
MySQL returns 1 fro the number of affected rows if the row was found and row_flag
wasn't already 1 in the original row.
6. Mysql 접근 권한 시스템
mysql 은 진보적이지만 비표준적인 보안/권한 시스템을 가지고 있다. 이번 장에서는 이것이
어떻게 작동하는지를 설명하고 있다.
6.1 권한 시스템이란 무엇인가?
Mysql 권한 시스템의 주요 기능은 데이터베이스에서 select, insert, update, delete 권한
을 호스트의 사용자 이름과 관련짓는 것이다.
추가적인 기능에는 익명 사용자 기능과 LOAD DATA INFILE 과 관리자 오퍼레이션과 같은 my
sql만의 특수한 권한을 허용하는 부분이 포함되어 있다.
Mysql에서 인증을 목적으로 사용하는 사용자 이름은 유닉스 사용자 이름(로그인 이름)이나
위도우 사용자 이름고는 전혀 관계가 없다는 것!을 기억하자. 대부분 mysql 클라이언트는 m
ysql 사용자 이름으로 현재의 유닉스 사용자 이름을 사용하여 접속하려 할 것이다. 그렇지
만 이건 오직 편의를 위해서이다. 클라이언트 프로그램은 -u 나 --user 옵션으로 지정한 다
른 이름을 허용한다. 이것은 mysql 사용자 이름에 비밀번호를 설정하지 않으면 데이터베이
스의 보안에 문제가 생길 수 있다는 것을 의미한다. 어떤 이름을 사용하여 서버에 접속하려
고 하는 사람은 각 이름에 비밀번호가 설정되어 있지 않다면 접속에 성공할 것이다.
유닉스 사용자 이름이 일반적으로 8글자로 제한되어 있는 것과 다르게 mysql 사용자 이름은
16글자까지 사용할 수 있다.
mysql 비밀번호는 유닉스의 비밀번호와 아무 관련이 없다. 유닉스 머신에 로그인할 때 사용
하는 비밀번호와 데이터베이스에 접속할 때 사용하는 비밀번호는 전혀 관련이 없다. 또한 m
ysql은 유닉스 로그인 프로세스에서 사용하는 것과 다른 알고리즘으로 비밀번호를 암호화한
다.
6.2 mysql 서버에 접속하기
mysql 클라이언트 프로그램은 일반적으로 연결 패러미터(매개 변수)가 필요하다.: 연결할
호스트, 사용자 이름, 비밀번호. 예를 들어 mysql 클라이언트는 다음과 같이 시작할 수 있
다. (선택 인자는 [ ] 로 닫는다)
shell>; mysql [-h host_name] [-u user_name] [-pyour_pass]
-p와 뒤에 붙은 비밀번호 사이에는 공간이 없다는 것을 기억하자.
-h, -u, -p를 대체할 수 있는 형식으로는 --host=host_name, --user=user_name and --passw
ord=your_pass 이 있다.
mysql은 커맨드 라인에서 연결 매개변수가 빠져있을 때는 기본 값을 사용한다. 기본 호스트
이름은 localhost 이고 기본 사용자 이름은 유닉스 로그인 이름이다.(-p 가 빠져있으면 비
밀번호는 사용하지 않는다) 그래서 만약 유닉스 사용자 이름이 joe 라면 다음의 명령은 동
일하다.:
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql
다른 mysql 클라이언트도 비슷하게 작동한다.
유닉스 시스템에서 연결을 할 때 사용할 수 있는 기본 값이 있어서 클라이언트 프로그램을
사용할 때마다 명령행에서 옵션을 사용하지 않아도 된다:
ㅇ 홈 디렉토리의 '.my.cnf' 설정 파일의 [client] 섹션에서 연결 변수를 설정할 수 있다.
파일에서 이와 연관된 섹션은 다음과 같다:
[client]
host=host_name
user=user_name
password=your_pass
4.14.4 [option files] 참고.
ㅇ 환경 변수를 사용하여 연결 변수를 지정할 수 있다. 호스트는 MYSQL_HOST 로 지정할 수
있다. Mysql 사용자 이름은 USER, LOGNAME, 또는 LOGIN을 사용할 수 있다. (이러한 값들은
이미 유닉스 로그인 이름으로 설정되어 있을 것이다. 그러므로 바꾸지 않는게 좋다) 비밀번
호는 MYSQL_PWD로 지정할 수 있다.(그렇지만 이것은 안전하지 않다; 다음 섹션을 참고하자)
연결 변수가 여러 가지 방법을 지정되었다면 명령행에서 지정한 값이 설정 파일과 환경 변
수로 설정한 것보다 우선권을 가진다. 또한 설정 파일의 값이 환경 변수보다 우선권을 가진
다.
6. 2. 1 비밀번호의 보안 유지
다른 사용자가 발견할 수 있게 비밀번호를 지정하는 방법은 권하지 않는다. 클라이언트 프
로그램을 실행할 때 비밀번호를 지정하는 방법은 아래와 같으며 각 방법마다 위험도를 같이
설명하였다:
ㅇ 명령행에서 -pyour_pass 또는 --password=your_pass 옵션 사용. 이 방법은 편리하지만
위험한 방법이다. 비밀번호를 시스템 상황 프로그램(ps 등)을 통해 볼 수 있기 때문에 다른
사용자가 명랭행으로 볼 수 있다.(mysql 클라이언트는 일반적으로 초기화되는 동안 명령행
인자를 0으로 덮어씌운다. 그렇지만 값을 볼 수 있는 짧은 틈이 여전히 있다)
ㅇ -p 또는 --password 옵션 사용(비밀번호 값을 지정하지는 않음). 이런 경우 클라이언트
프로그램은 터미널에서 비밀번호를 물어본다:
shell> mysql -u user_name -p
Enter password: ********
클라이언트는 비밀번호를 칠 때 터미널에서 '*' 문자를 보여준다. 그러므로 다른 사용자가
비밀번호를 볼 수 없다. 다른 사용자가 볼 수 없으므로 명령행에서 비밀번호를 입력하는 것
보다 훨씬 더 안전하다. 그렇지만 이 방법은 비대화식의 스크립트로 클라이언트 프로그램을
사용하면 적절하지 않다.
ㅇ 설정 파일에 비밀번호 저장. 예를 들어 홈 디렉토리의 '.my.cnf' 파일에서 [client] 섹
션에 비밀번호를 지정할 수 있다.
[client]
password=your_pass
비밀번호를 '.my.cnf' 파일에 저장한다면 그 파일은 그룹이나 다른 사용자가 읽기/쓰기를
할 수 없도록 해야 한다. 파일의 퍼미션이 400 이나 600 인지 확인하자.
4.14.4 [옵션 파일] 참고.
ㅇ 비밀번호를 MYSQL_PWD 환경 변수에 저장할 수 있다. 그렇지만 이 방법은 정말로 위험하
며 사용해서는 안된다. 일부 ps 프로그램은 실행 프로세스의 환경변수를 보여주는 옵션이
있다; MYSQL_PWD에 설정을 하면 다른 사람들이 쉽게 비밀번호를 볼 수 있다. 이런 기능의 p
s가 없는 시스템일지라도 프로세스 환경변수를 검색할 수 있는 방법이 없다고 생각하는 것
은 현명하지 못하다.
이중에서 가장 안전한 방법은 클라이언트 프로그램이 비밀번호를 요구하거나 적절하게 보안
이 된 '.my.cnf' 파일에 비밀번호를 지정하는 것이다.
6.3 mysql에서 제공하는 권한
권한과 관련된 정보는 mysql 데이터베이스의(데이터베이스 이름이 mysql 임) user, db, hos
t, table_priv, columns_priv 테이블에 저장된다. mysql 서버는 시작할 때, 그리고 환경을
지정할 때(6.7 [권한 변경] 참고) 이 테이블의 내용을 읽어들인다.
mysql에서 제공하는 권한을 설정할 때 사용하는 이름은 아래와 같다.테이블의 컬럼 이름은
grant tables의 각 권한 및 권한이 적용되는 context와 연관되어 있다.
Privilege Column Context
(권한) (컬럼) (환경)
select Select_priv tables
insert Insert_priv tables
update Update_priv tables
delete Delete_priv tables
index Index_priv tables
alter Alter_priv tables
create Create_priv databases, tables or indexes
drop Drop_priv databases or tables
grant Grant_priv databases or tables
reload Reload_priv server administration
shutdown Shutdown_priv server administration
process Process_priv server administration
file File_priv file access on server
select, insert, update, delete 권한은 데이터베이스의 테이블에서 레코드에 대한 오퍼레
이션을 할 수 있도록 허용한다.
SELECT 문은 오직 실제로 테이블에서 줄(레코드)를 가져올 때만 select 권한이 필요하다.
서버의 데이터베이스에 접근 권한이 없는 경우라고 하더라도 특정한 SELECT 문은 사용할
수 있다. 예를 들면 간단한 계산을 위해 mysql 클라이언트를 사용할 수 있다:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
index(인덱스) 권한은 인덱스를 생성하거나 제거할 수 있다.
alter 권한은 ALTER TABLE 을 사용할 수 있도록 한다.
create 와 drop 권한은 새로운 데이터베이스와 테이블을 생성하거나 존재하는 데이터베이
스와 테이블을 제거할 수 있도록 허용한다.
사용자에게 mysql 데이터베이스의 drop 권한을 허용하면, 그 사용자는 mysql 접근권한 정보
가 저장된 데이터베이스를 없앨 수 있다는것!을 명심하자.
grant 권한은 사용자가 가지고 있는 권한을 다른 사용자가 가질 수 있도록 허용한다.
file 권한은 LOAD DATA INFILE and SELECT ... INTO OUTFILE 문을 이용하여 서버에 파일을
저장하고 읽을 수 있는 권한을 허용한다. 이러한 권한을 가진 사용자는 mysql 서버가 읽고
쓸 수 있는 파일을 읽고 쓸 수 있는 권한이 허용된다.
나머지 권한들은 관리자 오퍼레이션에 사용되며 mysqladmin 프로그램의 기능을 수행한다.
아래의 테이블은 각 관리자 권한에 따라 사용할 수 있는 mysqladmin 명령을 보여준다:
Privilege Commands permitted to privilege holders
(권한) (권한에 따라 허용되는 명령)
reload reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tab
les
shutdown shutdown
process processlist, kill
reload 명령은 서버가 grant 테이블을 다시 읽어 들인다. refresh 명령은 모든 열린 테이블
을 닫으며 로그 파일을 열고 닫는다. flush-privileges 는 reload 명령과 동의어이다. 다른
flush-* 명령은 refresh 와 비슷한 기능을 수행한다. 그러나 범위에 제한이 있으며 어떤 경
우에는 더 선택할 만하다. 예를 들어 로그 파일만 닫고 다시 열고자 한다면 flush-logs 가
refresh보다 더 나은 선택이다.
(** flush의 옵션으로는 호스트, 로그 파일, 권한 설정, 테이블, status variables 설정 변
수가 있다. SQL 문에서 또는 mysqladmin 유틸리티를 사용하면 된다. **)
shutdown 명령은 서버를 셧다운한다. (** 이거 번역 맞아~~?? **)
processlist 명령은 서버에서 실행되고 있는 스레드에 대한 정보를 보여준다. kill 명령은
서버 스레드를 죽인다. 언제나 자신의 스레드는 보거나 죽일 수 있지만 다른 사용자에 의해
시작된 스레드는 프로세스 권한이 있어야 보거나 죽일 수 있다.
몇가지 권한은 조심스럽게 허용해야 한다:y:
ㅇ grant(허용) 권한은 사용자가 다른 사용자의 권한을 설정할 수 있도록 허용한다. 다른
권한과 grant 권한을 가진 두 사용자는 권한을 결합할 수 있다.
ㅇ file 권한은 서버에서 모든 사람이 읽기 가능한 파일을 읽는데 남용 될 수 있.... SELEC
T 문을 이용해 접근할 수 있는 내용...
The file privilege can be abused to read any world-readable file on the server into a
database table, the contents of which can then be accessed using SELECT.
(** 굳이 권한을 주지 않아도 이용할 수 있는 것은 권한을 주지 않는게 낫다는 말이겠지요
**)
ㅇ shutdown 권한은 다른 사용자에가 완전히 서비스를 사용하지 못하도록 남용될 수 있다.
ㅇ process 권한은 비밀번호를 설정하고 바꾸는 질의를 포함해 현재 수행하고 있는 질의를
보는데 사용될 수 있다.
ㅇ mysql 데이터베이스에 대한 권한은 비밀번호와 다른 접근 권한 정보를 바꾸는 데 사용될
수 있다. (비밀번호가 암호화되어 저장되었다고 하더라도, 충분한 권한을 가진 악의있는 사
용자는 다른 비밀번호롤 바꿀 수 있다)
mysql 권한 시스템으로 다룰 수 없는 몇가지가 있다:
ㅇ 접근을 거부할 사용자를 명백하게 지정할 수 없다. 왜냐하면 사용자와 연결을 거부하는
것을 완전하게 연관시킬 수 없기 때문이다.
ㅇ 사용자가 테이터베이스에서 테이블을 만들고 지울 수 있는 권한을 가질 수 있지만 데이
터베이스 자체를 만들고 삭제할 수는 없도록 지정할 수 없다.
(** 그러니까 create 와 drop 권한을 주면 데이터베이스 자체에 대해 제어할 수 있지요. 그
안의 테이블만 만들고 지울 수 있도록 하지는 못한다는 말 **)
{{}}
6.4 권한 시스템 작동 방법
mysql 권한 시스템은 모든 사용자가 허용된 것만큼만 할 수 있도록 보증한다. mysql 서버에
연결할 때, 사용자 확인은 연결한 호스트와 사용자가 지정한 사용자 이름에 의해 결정된다.
시스템은 사용자 확인과 지정한 권한에 따라 권한을 허용한다.
mysql은 사용자를 확인하는데 호스트이름과 사용자 이름 둘다 사용한다. 왜냐면 인터넷에서
이름이 같다고 같은 사용자라고 생각할 수는 없기 때문이다. 예를 들어 whitehouse.gov에서
접속하는 사용자 bill 은 microsoft.com에서 접속하는 사용자 bill 과 같은 사람일 필요는
없다. mysql은 때론 같은 이름을 가지고 있더라도 호스트를 이용해 사용자를 구별한다 : wh
itehouse.gov에서 접속하는 bill에게 특정한 권한을 허용할 수 있고 microsoft.com에서 접
속하는 bill에게 다른 권한을 허용할 수 있다.
mysql의 접근 제어는 두가지 단계가 있다:
단계 1: 서버에서 사용자가 연결할 수 있는지 없는지 판단
단계 2 (서버에 사용자가 연결이 허용되었을 경우) : 사용자가 수행하려는 명령에 대해 충
분한 권한이 있는지 각 요청마다 서버에서 판단.예를 들면, 데이터베이스의 테이블에서 sel
ect rows를 할때, 또는 데이터베이스에서 테이블을 제거할 때 서버에서 테이블에 대한 sele
ct 권한이 있는지 데이터베이스에 대한 제거 권한이 있는지 확인을 한다.
서버는 접근 제어의 각 두 단계에서 mysql 데이터베이스의 user, db, host 테이블을 이용한
다.grant 테이블의 필드는 아래와 같다:
Table name user db host
Scope fields Host Host Host
(필드 범위) User Db Db
Password User
Privilege fields Select_priv Select_priv Select_priv
(권한 필드) Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
접권 제어의 두번째 단계를 위해(요청 인증), 요청이 테이블에 관계된 것이라면 추가적으로
tables_priv 와 columns_priv 테이블을 참고한다. 이 테이블의 필드는 다음과 같다:
Table name tables_priv columns_priv
Scope fields Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege fields Table_priv Type
Column_priv
Other fields Timestamp Timestamp
Grantor
각 승인(grant) 테이블은 필드 범위와 권한 필드로 구성되어 있다.
필드 범위는 테이블에서 각 엔트리의 범위를 결정한다. 다시 말하면 엔트리가 적용되는 con
text(환경, 배경)이다. 예를 들면, Host 와 User 값이 'thomas.loc.gov' 와 'bob' 인 user
테이블 엔트리는 thomas.loc.gov 호스트에서 bob이 연결을 할때 서버에서 인증을 하는데 사
용된다.비슷하게 Host, User, db 필드값이 'thomas.loc.gov', 'bob', 'reports' 인 db 테
이블 엔트리는 thomas.loc.gov 호스트에서 bob 이 reports 데이터베이스에 접근할 때 사용
된다. tables_priv 와 columns_priv 테이블은 테이블이나 각 엔트리가 적용될 수 있는 테이
블/컬럼 조합을 가리키는 범위 필드를 포함하고 있다.
접권 체크를 하기 위해, HOst 값 비교는 대소문자를 구별하지 않는다. User, Password, Db,
Table_name 값은 대소문자를 구별한다. mysql 3.22.12 와 이후 버전에서 Column_name 값은
대소문자를 구별하지 않는다. (3.22.11에서는 대소문자 구별함)
권한 필드는 테이블 엔트리에 승인되는 권한을 가리키며 이는 수행할 수 있는 오페레이션이
다. 서버는 사용자의 권한을 완벽하게 설정하기 위해 다양한 승인(grant) 테이블의 정보를
조합한다. 여기에 사용하는 규칙은 6.6 [Request access]를 참고하자.
범위 필드는 문자열이며 다음과 같이 정의되었다; 기본 값은 빈 문자열이다:
Field name Type
Host CHAR(60)
User CHAR(16)
Password CHAR(16)
Db CHAR(64) (CHAR(60) for the tables_priv and columns_priv tables)
user, db, host 테이블에서 모든 권한 필드는 ENUM('N','Y')로 정의되어 있다. -- 각각은
'N' 나 'Y'의 값을 가지며 기본값은 'N' 이다.
(** ENUM 타입은 목록 값중 오직 하나의 값만 가진다.필드 타입 참조 **)
tables_priv 와 columns_priv 테이블에서 권한 필드는 SET 필드로 정의된다:
(** SET 타입은 목록 값중에 0이나 1개 이상의 값을 가진다 **)
Table name Field name Possible set elements
tables_priv Table_priv 'Select', 'Insert', 'Update',
'Delete', 'Create', 'Drop', 'Grant',
'References', 'Index', 'Alter'
tables_priv Column_priv 'Select', 'Insert', 'Update',
'References'
columns_priv Type 'Select', 'Insert', 'Update',
'References'
간단하게 말해서 서버는 승인(grant) 테이블을 다음과 같이 사용한다:
ㅇ user 테이블의 scope(범위) 필드는 들어오는 연결에 대해 허용할 것인지 거부할 것인지
를 결정한다. 허용된 연결에 대하여, 권한 필드는 사용자의 전체적인 (superuser) 권한을
가리킨다.
ㅇ db 와 host 테이블은 함께 사용된다:
- db 테이블의 범위 필드는 어떤 호스트에서 어떤 데이터베이스에 대해 어떤 사용
자가 접근할 수 있는지 결정한다. 권한 필드는 어떤 오퍼레이션이 허용되었는지를 결정한
다.
- host 테이블은 db 테이블의 엔트리를 여러개의 호스트에 적용하려고 할 때 db 테
이블의 확장을 위해 사용한다.예를 들어, 사용자가 현재 네트웍의 여러 호스트에서 데이터
베이스를 사용할 수 있도록 하려면,사용자의 "db" 테이블 엔트리에 Host 값을 비워두고, "h
ost" 테이블에 각 호스트의 엔트리를 넣으면 된다.이러한 절차는 6,6 [Request access]에
자세하게 나와 있다.
ㅇ tables_priv 와 columns_priv 테이블은 db 테이블과 비슷하다. 그렇지만 더 세부적으로
지정할 수 있다: 이 테이블들은 데이터베이스 단계에서 더 나아가 테이블과 컬럼 단계에 적
용할 수 있다.
관리 권한(reload, shutdown,기타..)은 오직 user 테이블에서만 지정을 할 수 있다는 것을
기억하자! 왜냐면 관리자 오퍼레이션은 서버 자체에 대한 오퍼레이션이며 특정한 데이터베
이스를 지정하는 것이 아니다. 그러므로 이러한 권한은 다른 승인(grant) 테이블에 있을 필
요가 없다.실제로, 오직 user 테이블만이 관리자 오퍼레이션을 수행할 수 있는지 없는지를
결정할 때 참고가 된다.
파일(file) 권한도 마찬가지로 user 테이블에서만 지정한다.위와 같은 관리 권한은 아니다.
그렇지만 서버 호스트에서 파일을 읽거나 쓸 수 있는 권한은 접근하고 있는 데이터베이스와
무관한 것이다.
mysqld 서버는 시작할 때 승인(grant) 테이블을 한번 읽는다. 승인 테이블을 변경하고 효과
를 발휘하려면 6.7 [Privilege changes]를 참고하자.
승인 테이블의 내용을 수정했을 때 원하는대로 권한이 설정되었는지 확인하는 것은 좋은 생
각이다. 유용한 진단 프로그램은 mysqlaccess 스크립트로서 Yves CArlier 가 mysql distrib
ution 으로 제공하고 있다. 어떻게 작동하고 있는지 확인하기 위해 mysqlaccess 에 --help
옵션을 주어 실행해보자. 물론 6.11 [Access denied] 와 6.12 [Security]를 참고하자.
mysqlaccess는 오직 user, db, host 테이블만 점검한다. 테이블이나 컬럼 단계의 권한까지
는 점검하지 않는다는 것을 기억하자.
6.5 접근 제어, 단계 1 : 연결 확인(인증)
mysql 서버에 접속하려고 할 때 서버는 사용자 확인과 비밀번호를 통해 접속을 허용하거나
거부한다. 사용자 확인이 안되면 서버는 접속을 완전히 거부한다. 사용자 확인이 되면 서버
는 연결을 받아들이고 2번째 단계로 들어가며 요청을 기다린다.
사용자 확인은 두가지 정보에 기반하고 있다:
ㅇ 접속하는 호스트
ㅇ mysql 사용자 이름
사용자 확인은 user 테이블의 세가지 범위 필드(Host, User, Password)를 사용하여 수행된
다. 서버는 user 테이블 엔트리의 호스트이름과 사용자 이름이 맞으며, 비밀번호가 정확할
때만 접속을 받아들인다.
아래와 같이 user 테이블의 범위 필드값을 지정할 수 있다:
ㅇ Host 값은 호스트 이름이나 IP 숫자 또는 로컬 호스트를 가리키는 'localhost' 가 될 것
이다.
ㅇ Host 필드에서 '%' 와 '_' 의 와일드카드 문자를 사용할 수 있다.
ㅇ '%'의 Host 값은 모든 호스트 이름을 나타낸다. 공백의 호스트 값은 '%'와 같다. 특정한
호스트에 대한 이러한 값은 당신의 서버에 연결할 수 있다는 것을 참고하자.
ㅇ 와일드카드 문자는 User 필드에는 허용되지 않는다. 그렇지만 모든 유저에 해당하는 공
백으로 둘 수 있다. 연결을 하려는 목록에 공백 사용자 이름이 있다면 클라이언트에서 실제
로 지정한 이름 대신에 그 사용자는 익명 사용자, 이름이 없는 사용자로서 간주된다.
ㅇ Password 필드는 공백으로 될 수 있다.이것은 아무런 비밀번호나 사용할 수 있다는 것을
의미하는 것은 아니며 사용자는 비밀번호를 지정하지 않고 연결을 해야 한다는 의미이다.
아래의 테이블은 연결 요청에 적용하는 "user" 테이블 목록의 Host, User 값이 어떻게 조합
되는지를 보여주는 예제이다:
호스트값/사용자 값 : 목록에 해당하는 연결
'thomas.loc.gov'/'fred' : thomas.loc.gov 에서 연결하는 fred
'thomas.loc.gov'/'' : thomas.loc.gov 에서 연결하는 모든 사용자
'%'/'fred' : 모든 호스트에서 연결하는 fred
'%'/'' : 모든 호스트에서 연결하는 모든 사용자
'%.loc.gov'/'fred' : loc.gov 도메인의 모든 호스트에서 연결하는 fred
'x.y.%'/'fred' : x.y.net, x.y.com, x.y.edu 등에서 접속하는 fred (이것은 아마도 유용하
지 않을 것이다)
'144.155.166.177'/'fred' : 144.155.166.177의 IP 주소에서 접속하는 fred
'144.155.166.%'/'fred' : 144.155.166 클래스 C 서브넷의 모든 호스트에서 접속하는 fred
Host 필드에서 IP에 와일드 카드를 사용할 수 있기 때문에(예를 들어 '144.155.166.%' 는
서브넷의 모든 호스트에 적용된다) 144.155.166.somewhere 와 같은 호스트 이름을 이용하여
부당하게 이용할 가능성이 생길 수 있다. 이러한 것을 막기 위해 mysql은 숫자와 도트(.)으
로 시작하는 호스트이름은 허용하지 않는다. 1.2.foo.com 과 같은 호스트라면 이러한 호스
트이름은 승인(grant) 테이블의 Host 컬럼과 매치되지 않는다. IP 숫자만이 IP 와일드 카드
값과 매치시킬 수 있다.
만약 한개 이상의 user table 목록이 있다면 서버는 어떻게 user table을 선택할까? 이런
경우에는 user table의 정렬 순서에 따라 해결을 하며 , 정열은 서버가 시작할때 수행이 된
다. user table이 다음과 같다고 가정해보자:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
서버가 테이블을 읽을 때, 먼저 특정하게 지정된 값이 있는 호스트부터 목록을 정열한다.
(Host 컬럼에서 '%'는 "모든 호스트"를 의미하여 최소한도로 지정하는 것이다) 목록에서 호
스트값이 같으면 먼저 특정하게 지정된 사용자가 있는 것부터 정열한다.(공백으로 되어 있
는 User 값은 "모든 사용자"를 의미하여 최소한도로 지정하는 것이다.) 이렇게 하면 정열된
user 테이블은 다음과 같다:
+-----------+----------+-
| Host | User | ...
+-----------+----------+
댓글 전체