## 원본 : mysql 3.21 Reference Manual PostScript 매뉴얼 ### 5, 6, 9, 10, 11, 12, 13, 15, 17, 18장 번역 : 문태준(taejun@hitel.net) 18장 중 C-API 부분 번역 : 권용찬 (golmong@cre.co.kr) 이메일 : taejun@hitel.net, taejun@taejun.pe.kr 웹 : http://taejun.pe.kr (Mysql 관련 자료가 모아져 있습니다) - 차 례 - 5. mysql의 표준 호환성 5.1 mysql의 ANSI SQL92 확장부분 5.2 MYSQL에서 빠진 기능 5.2.1 Sub-selects 5.2.2 SELECT INTO TABLE 5.2.3 트랜잭션(Transactions) 5.2.4 저장 프로시저와 트리거 5.2.5 외래키(Foreign Keys) 5.2.5.1 외래키를 사용하지 않는 이유 5.2.6 뷰 5.2.7 `--'을 사용한 주석 5.3 Mysql이 따르고 있는 표준은 무엇인가? 5.4 BLOB 와 TEXT 타입의 제한 5.5 COMMIT-ROLLBACK없이 어떻게 대치할 수 있을까? 6. Mysql 접근 권한 시스템 6.1 권한 시스템이란 무엇인가? 6.2 mysql 서버에 접속하기 6.2.1 비밀번호의 보안 유지 6.3 mysql에서 제공하는 권한 6.4 권한 시스템 작동 방법 6.5 접근 제어, 6.6 접근 제어, 2단계 : 요청 인증 6.7 권한 변경시 적용 방법 6.8 초기 mysql 권한설정 6.9 mysql에 새로운 사용자 권한 추가하기 6.10 비밀번호 설정 방법 6.11 접근 거부 에러가 나는 이유 6.12 크랙커에 대비하여 mysql을 안전하게 하는 방법 7. MySQL language reference. 7.1 Literals: how to write strings and numbers 7.1.1 Strings 7.1.2 Numbers 7.1.3 Hexadecimal values 7.1.4 NULL values 7.1.5 Database, table, index, column and alias names 7.1.5.1 Case sensitivity in names 7.2 Column types 7.2.1 Column type storage requirements 7.2.2 Numeric types 7.2.3 Date and time types 7.2.4 String types 7.2.5 Numeric types 7.2.6 Date and time types 7.2.6.1 Y2K issues and date types 7.2.6.2 The DATETIME, DATE and TIMESTAMP types 7.2.6.3 The TIME type 7.2.6.4 The YEAR type 7.2.7 String types 7.2.7.1 The CHAR and VARCHAR types 7.2.7.2 The BLOB and TEXT types 7.2.7.3 The ENUM type 7.2.7.4 The SET type 7.2.8 Choosing the right type for a column 7.2.9 Column indexes 7.2.10 Multiple-column indexes 7.2.11 Using column types from other database engines 7.3 Functions for use in SELECT and WHERE clauses 7.3.1 Grouping functions 7.3.2 Normal arithmetic operations 7.3.3 Bit functions 7.3.4 Logical operations 7.3.5 Comparison operators 7.3.6 String comparison functions 7.3.7 Cast operators 7.3.8 Control flow functions 7.3.9 Mathematical functions 7.3.10 String functions 7.3.11 Date and time functions 7.3.12 Miscellaneous functions 7.3.13 Functions for use with GROUP BY clauses 7.4 CREATE DATABASE syntax 7.5 DROP DATABASE syntax 7.6 CREATE TABLE syntax 7.6.1 Silent column specification changes 7.7 ALTER TABLE syntax 7.8 OPTIMIZE TABLE syntax 7.9 DROP TABLE syntax 7.10 DELETE syntax 7.11 SELECT syntax 7.12 JOIN syntax 7.13 INSERT syntax 7.14 REPLACE syntax 7.15 LOAD DATA INFILE syntax 7.16 UPDATE syntax 7.17 USE syntax 7.18 FLUSH syntax (clearing caches) 7.19 KILL syntax 7.20 SHOW syntax (Get information about tables, columns,...) 7.21 EXPLAIN syntax (Get information about a SELECT) 7.22 DESCRIBE syntax (Get information about columns) 7.23 LOCK TABLES/UNLOCK TABLES syntax 7.24 SET OPTION syntax 7.25 GRANT and REVOKE syntax 7.26 CREATE INDEX syntax 7.27 DROP INDEX syntax 7.28 Comment syntax 7.29 CREATE FUNCTION/DROP FUNCTION syntax 7.30 Is MySQL picky about reserved words? 8 MySQL Tutorial 1. 서버에 연결하기/연결끊기 2. 질문 하기(Entering Queries) 3. 데이터 베이스 만들고 사용하기 3.1 데이터베이스 만들고 선택하기 3.2 테이블 만들기 3.3 테이블에 자료를 넣어 보자. 3.4 테이블로부터 정보를 검색해 보자. 3.4.1 모든 데이터를 검색하자. 3.4.2 주어진 조건에 맞는 특정 행만을 검색해 보자. 3.4.3 특정한 열 선택하기 3.4.4 행 정열하기 3.4.5 날짜 계산MySQL은 날짜를 다루는 몇가지 함수를 제공해 준다. 3.4.6 null 값에 대해 NULL값은 특별한 값이다. 3.4.7 패턴 일치 3.4.8 행수 세기 3.5 테이블 여러개 사용하기 3.6 배치 모드(일괄 처리 모드)로 사용하기 9. mysql 서버 기능(functions) 9.1 mysql에서 지원하는 언어 9.1.1 데이터와 정열에 사용하는 문자 셋 9.1.2 새로운 문자셋 추가 9.1.3 멀티바이트 문자 지원 9.2 업데이트 로그 9.3 mysql 테이블 최대 크기 10. mysql의 최대 성능 향상 방법 10.1 버퍼 크기 조정 10.2 메모리 사용 방법 <메모리 최적화> 10.3 속도 향상에 영향을 미치는 컴파일/링크 방법 10.4 How MySQL uses indexes 10.5 WHERE 문에서 최적화하기 10.6 테이블 열고 닫는 방법 10.6.1 데이터베이스에서 많은 수의 테이블을 만들때의 단점 10.7 많은 테이블을 여는 이유 10.8 데이터베이스와 테이블에서 심볼릭 링크 사용 10.9 테이블에 락 거는 방법 10.10 테이블을 빠르고 작게 배열하는 방법 10.11 INSERT 문에서 속도에 영향을 미치는 부분 10.12 DELETE 문에서 속도에 영향을 미치는 부분 10.13 mysql에서 최대 속도를 얻는 방법 10.14 로우 포맷과 다른 점은 무엇인가? 11. mysql 벤치마크 스위트 12. mysql 유틸리티 12.1 다양한 mysql 프로그램 개요 12.2 텍스트 파일에서 데이터 입력(수입?)하기 12.3 mysql 압축 읽기 전용 테이블 생성기 13. 테이블 유지보수 및 파손 복구에 isamchk 사용하기 13.1 isamchk 명령어 사용법 13.2 isamchk 메모리 사용법 13.3 테이블 유지보수 설정 13.4 테이블 정보 얻기 13.5 파손 복구에 isamchk 사용하기. 13.5.1 에러가 났을때 테이블 점검 방법 13.5.2 테이블 복구방법 13.5.3 테이블 최적화 14 Adding new functions 14.1 Adding a new user-definable function 14.1.1 UDF calling sequences 14.1.2 Argument processing 14.1.3 Return values and error handling 14.1.4 Compiling and installing user-definable functions 14.2 Adding a new native function 15. mysql ODBC 지원 15.1 MyODBC 를 지원하는 운영체제 15.2 MyODBC에 문제가 있는 경우 15.3 MyODBC 와 잘 작동하는 프로그램 15.4 ODBC 관리자 프로그렘 설정 방법 15.5 ODBC에서 AUTO_INCREMENT 컬럼의 값 가져오기 17. 일반적인 문제 해결 방법 17.1. 데이터베이스 복사(복제?) 17.2 데이터베이스 백업 17.3 같은 머신에서 여러개의 mysqld 서버 실행하기 18 MySQL client tools 18.1 MySQL C API 18.2 C API datatypes 18.3 C API function overview 18.4 C API function descriptions 18.4.1 mysql_affected_rows() 18.4.2 mysql_close() 18.4.3 mysql_connect() 18.4.4 mysql_create_db() 18.4.5 mysql_data_seek() 18.4.6 mysql_debug() 18.4.7 mysql_drop_db() 18.4.8 mysql_dump_debug_info() 18.4.9 mysql_eof() 18.4.10 mysql_errno() 18.4.11 mysql_error() 18.4.12 mysql_escape_string() 18.4.13 mysql_fetch_field() 18.4.14 mysql_fetch_fields() 18.4.15 mysql_fetch_field_direct() 18.4.16 mysql_fetch_lengths() 18.4.17 mysql_fetch_row() 18.4.18 mysql_field_seek() 18.4.19 mysql_field_tell() 18.4.20 mysql_free_result() 18.4.21 mysql_get_client_info() 18.4.22 mysql_get_host_info() 18.4.23 mysql_get_proto_info() 18.4.24 mysql_get_server_info() 18.4.25 mysql_info() 18.4.26 mysql_init() 18.4.27 mysql_insert_id() 18.4.28 mysql_kill() 18.4.29 mysql_list_dbs() 18.4.30 mysql_list_fields() 18.4.31 mysql_list_processes() 18.4.32 mysql_list_tables() 18.4.33 mysql_num_fields() 18.4.34 mysql_num_rows() 18.4.35 mysql_ping() 18.4.36 mysql_query() 18.4.37 mysql_real_connect() 18.4.38 mysql_real_query() 18.4.39 mysql_reload() 18.4.40 mysql_row_seek() 18.4.41 mysql_row_tell() 18.4.42 mysql_select_db() 18.4.43 mysql_shutdown() 18.4.44 mysql_stat() 18.4.45 mysql_store_result() 18.4.46 mysql_thread_id() 18.4.47 mysql_use_result() 18.4.49 What results can I get from a query? 18.4.50 How can I get the unique ID for the last inserted row? 18.4.51 Problems linking with the C API 18.4.52 How to make a thread-safe client 19. 타 DB와 비교 19.1 Mysql/mSQL 비교 19.1.1 mSQL 툴을 Mysql로 바꾸기 19.1.2 mSQL 과 Mysql 클라이언트/서버 통신 프로토콜 차이점 19.1.3 How mSQL 2.0 SQL syntax differs from Mysql 19.2 PostgreSQL과 Mysql 비교 ======================================================================= - 내 용 - 5. mysql의 표준 호환성 5.1 mysql의 ANSI SQL92 확장부분 mysql에는 다른 sql 데이터베이스에서 찾을 수 없는 확장된 부분이 있다. 이런 부분을 사용하는 경우 주의해야 한다. 왜냐면 mysql에서 사용한 코 드가 다른 SQL 서버에 포팅할 수 없을 수도 있기 때문이다. 어떤 경우에 는 /*! ... */ 형식의 주석문을 사용한 MYSQL 확장을 이용해 포팅가능한 코드를 만들 수 있다. 예를 들어보자: SELECT /*! STRAIGHT_JOIN */ col_name from table1,table2 WHERE ... MYSQL의 확장 부분은 다음과 같다: - 필드타입 MEDIUMINT, SET, ENUM , 그리고 다른 BLOB 와 TEXT 타입. - 필드속성 AUTO_INCREMENT, BINARY, UNSIGNED and ZEROFILL. - 모든 문자열 비교는 기본적으로 대소문자를 구별하지 않으며 현재의 문 자셋(기본적으로 ISO-8859-1 Latin1)에 의해 정렬 순서가 결정된다. 이것 을 원하지 않으면 컬럼을 BINARY 속성으로 정의해야 하며 이런 경우에는 mysql 서버 호스트가 사용하는 ASCII 순서에 따라 문자열을 비교한다. - MYSQL은 데이터베이스를 디렉토리로 만들고 테이블은 파일이름으로 만든 다. 이것은 두가지를 함축하고 있다: ㅇ 파일이름의 대소문자를 구별하는 (대부분의 유닉스 시스템. 리눅스도 마찬가지겠지용~) 운영시스템에서는 MYSQL의 데이터베이 스 이름과 테 이블 이름은 대소문자를 구별한다. 테이블 이름을 기억하 는데 문제가 있 다면 모든 것을 소문자로 만들자. ㅇ 테이블의 백업, 이름바꾸기, 옮기기, 삭제, 복사를 위해 표준 시스템 명령을 사용할 수 있다. 예를 들어 테이블의 이름을 바꾸려면 해 당하는 테이블의 `.ISD', `.ISM' and `.frm' 파일의 이름을 바꾸면 된다. - SQL문에서 db_name.tbl_name 문을 이용하여 다른 데이터베이스의 테이블 에 접근할 수 있다. 일부 SQL 서버는 같은 기능을 지원하지만 이것을 User space라고 부른다. MYSQL은 다음과 같은 TABLESPACES를 지원하지 않는다 : create table ralph.my_table...IN my_tablespace. - 수치(숫자형) 컬럼에서 LIKE를 사용할 수 있다. - SELECT문에서 INTO OUTFILE 과 STRAIGHT_JOIN 을 사용할 수 있다. 7.11 [SELECT] 참고. -EXPLIAN SELECT는 테이블에서 어떻게 조인이 되었는지에 대한 정보를 보 여준다. - Use of index names, indexes on a subpart of a field, and use of INDEX or KEY in a CREATE TABLE statement. 7.6 [CREATE TABLE] 참고. - ALTER TABLE에서 CHANGE col_name, DROP col_name 또는 DROP INDEX 를 사용한다. 7.7 [ALTER TABLE] 참고. - ALTER TABLE 문에서 IGNORE 사용. - ALTER TABLE 문에서 다중 ADD, ALTER, DROP or CHANGE 사용 - IF EXISTS 키워드를 이용한 DROP TABLE 사용. - 한 테이블 이상에서 DROP TABLE 사용. - LOAD DATA INFILE 사용. 대부분의 경우 이 문장은 오라클의 LOAD DATA INFILE과 호환된다. 7.15 [LOAD DATA INFILE] 참고. (** 많은 양의 데이터를 한꺼번에 입력할 때 일일이 INSERT 문을 하는 것 보다 속도가 빠르다. **) - OPTIMIZE TABLE 문 사용. - 문자열은 ''' 만이 아니라 '"' 또는 ''' 로 닫을 수 있다. - escape `\' 문자 사용. - SET OPTION 문. 7.24 [SET OPTION] 참고. - GROUP BY 부분에서 모든 컬럼을 사용할 필요가 없다. 이러한 기능은 일 반적인 질의가 아닌 특정한 질의에서 성능을 향상시킨다. 7.3.12 [GROUP BY Functions] 참고. (** ANSI SQL에서는 여러 테입믈을 이용하여 GROUP BY를 사용할 때 사용 하고자 하는 모든 컬럼에 GROUP BY를 지정해 주어야 한다. 이렇게 되는 경 우 불필요한 연산이 수행될 수 있는데 MYSQL에서는 이러한 것을 없앤 것이 다. 7.3.12를 참고한다 **) - 다른 SQL 환경을 사용했던 사용자를 위해 MYSQL은 많은 기능에서 알리 아스를 지원한다. 예를 들어 모든 문자 펑션은 ANSI SQL 과 ODBC 구문을 지원한다. - MYSQL은 C 프로그래밍 언어와 같은 논리적인 OR 과 AND를 의미하는 || 와 && 를 인식한다. MYSQL에서는 || 와 OR 는 같은 말이며 && 와 AND 도 마찬가지이다. 이러한 미묘한 구문때문에 MYSQL은 string concatenation (문자열 연관, 연결?)을 위한 ANSI SQL 오퍼레이터인 || 을 지원하지 않는 다. 대신 CONCAT()를 사용한다. CONCAT() 는 많은 인자가 있어서 MYSQL 에 서 || 오퍼레이터의 사용을 변환하기 쉽다. MySQL understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL operator || for string concatenation; use CONCAT() instead. Since CONCAT() takes any number of arguments, it's easy to convert use of the || operator to MySQL. - 포팅이 가능하도록 SQL CODE에서 STRAIGHT_JOIN같은 MYSQL만의 키워드 사용을 지원하기 위해 이런 키워드를 /* */ 주석안에 내장할 수 있다. 주 석문안의 내용은 '!' 로 시작한다. 이런 경우 MYSQL에서는 주석문을 다른 MYSQL 구문과 같이 해석하지만 다른 SQL 서버에서는 이러한 확장기능을 사용하지 않고 건너띌 수 있다. 예를 보자: SELECT /*! STRAIGHT_JOIN */ * from table1,table2 WHERE ... - 다음의 기능이나 명령 사용: ㅇ CREATE DATABASE or DROP DATABASE. 7.4 [CREATE DATABASE] 참고. ㅇ MOD() 대신에 % 사용. %는 C 프로그래머를 위해 지원하며 또한 PostgresSQL과의 호환성을 위해 지원한다. ㅇ 컬럼 문에서 =, <>, <= ,<, >=,>, <<, >>, AND, OR, LIKE 사 용. ㅇ LAST_INSERT_ID(). 18.4.49 [mysql_insert_id()] 참고. ㅇ REGEXP or NOT REGEXP. ㅇ 하나나 하나 이상의 인자를 사용한 CONCAT() 나 CHAR(). MYSQL 에서 이러한 펑션은 여러개의 인자를 가질 수 있다. ㅇ BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEE-DEC-OSF1, KDAY(). ㅇ 서브스트링을 없애는 데 TRIM() 사용.(Use of TRIM() to trim substrings) ANSI SQL 에서는 단일 문자 제거만 지원한다. ㅇ 그룹 펑션에서 STD(), BIT_OR() and BIT_AND() ㅇ DELET + INSERT 대신 REPLACE 사용. 7.14 [REPLACE] 참고. ㅇ FLUSH flush_option 명령. 5.2 MYSQL에서 빠진 기능 다음의 기능들은 현재 버전의 MYSQL에 빠져있다. 다음 버전에서의 우선권 을 확인하라면 MYSQL TODO 목록을 참고하자. (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 과 관리자 오퍼 레이션과 같은 mysql만의 특수한 권한을 허용하는 부분이 포함되어 있다. Mysql에서 인증을 목적으로 사용하는 사용자 이름은 유닉스 사용자 이름 (로그인 이름)이나 위도우 사용자 이름고는 전혀 관계가 없다는 것!을 기 억하자. 대부분 mysql 클라이언트는 mysql 사용자 이름으로 현재의 유닉스 사용자 이름을 사용하여 접속하려 할 것이다. 그렇지만 이건 오직 편의를 위해서이다. 클라이언트 프로그램은 -u 나 --user 옵션으로 지정한 다른 이름을 허용한다. 이것은 mysql 사용자 이름에 비밀번호를 설정하지 않으 면 데이터베이스의 보안에 문제가 생길 수 있다는 것을 의미한다. 어떤 이 름을 사용하여 서버에 접속하려고 하는 사람은 각 이름에 비밀번호가 설정 되어 있지 않다면 접속에 성공할 것이다. 유닉스 사용자 이름이 일반적으로 8글자로 제한되어 있는 것과 다르게 mysql 사용자 이름은 16글자까지 사용할 수 있다. mysql 비밀번호는 유닉스의 비밀번호와 아무 관련이 없다. 유닉스 머신에 로그인할 때 사용하는 비밀번호와 데이터베이스에 접속할 때 사용하는 비 밀번호는 전혀 관련이 없다. 또한 mysql은 유닉스 로그인 프로세스에서 사 용하는 것과 다른 알고리즘으로 비밀번호를 암호화한다. 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 --password=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에 설정을 하면 다른 사 람들이 쉽게 비밀번호를 볼 수 있다. 이런 기능의 ps가 없는 시스템일지라 도 프로세스 환경변수를 검색할 수 있는 방법이 없다고 생각하는 것은 현 명하지 못하다. 이중에서 가장 안전한 방법은 클라이언트 프로그램이 비밀번호를 요구하거 나 적절하게 보안이 된 '.my.cnf' 파일에 비밀번호를 지정하는 것이다. 6.3 mysql에서 제공하는 권한 권한과 관련된 정보는 mysql 데이터베이스의(데이터베이스 이름이 mysql 임) user, db, host, 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, table or indexs | +-------------------+----------------+------------------------------+ | 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 (권한) | Command permitted to privilege holders | | | (권한에 따라 허용되는 명령) | +--------------------+----------------------------------------------+ | reload | reload, refresh, flush-privileges | | | , flush-hosts, flush-logs, flush-tables | +--------------------+----------------------------------------------+ | 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 권한은 서버에서 모든 사람이 읽기 가능한 파일을 읽는데 남용 될 수 있.... SELECT 문을 이용해 접근할 수 있는 내용... 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은 때론 같은 이름을 가지고 있더라도 호스트를 이용해 사용자를 구 별한다 : whitehouse.gov에서 접속하는 bill에게 특정한 권한을 허용할 수 있고 microsoft.com에서 접속하는 bill에게 다른 권한을 허용할 수 있다. mysql의 접근 제어는 두가지 단계가 있다: 단계 1: 서버에서 사용자가 연결할 수 있는지 없는지 판단 단계 2 (서버에 사용자가 연결이 허용되었을 경우) : 사용자가 수행하려는 명령에 대해 충분한 권한이 있는지 각 요청마다 서버에서 판단.예를 들면, 데이터베이스의 테이블에서 select rows를 할때, 또는 데이터베이스에서 테이블을 제거할 때 서버에서 테이블에 대한 select 권한이 있는지 데이터 베이스에 대한 제거 권한이 있는지 확인을 한다. 서버는 접근 제어의 각 두 단계에서 mysql 데이터베이스의 user, db, host 테이블을 이용한다.grant 테이블의 필드는 아래와 같다: +----------------+-----------------+---------------+---------------+ | Table name | user | db | host | +----------------+-----------------+---------------+---------------+ | Scope fields | Host | Host | Host | | (필드 범위) +-----------------+---------------+---------------+ | | User | Db | Db | | +-----------------+---------------+---------------+ | | Password | User | | +----------------+-----------------+---------------+---------------+ | | Select_priv | Select_priv | Select_priv | | +-----------------+---------------+---------------+ | | Insert_priv | Insert_priv | Insert_priv | | +-----------------+---------------+---------------+ | | Update_priv | Update_priv | Update_priv | | +-----------------+---------------+---------------+ | Privilege | Delete_priv | Delete_priv | Delete_priv | | filelds +-----------------+---------------+---------------+ | (권한 필드) | 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 field | Table_priv | Type | | +---------------+----------------+ | | Column_priv | | +-------------------+---------------+----------------+ | Other fields | Timestamp | Timestamp | | +---------------+----------------+ | | Gran색 | | +-------------------+---------------+----------------+ 각 승인(grant) 테이블은 필드 범위와 권한 필드로 구성되어 있다. 필드 범위는 테이블에서 각 엔트리의 범위를 결정한다. 다시 말하면 엔트 리가 적용되는 context(환경, 배경)이다. 예를 들면, 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 값을 비워두고, "host" 테이 블에 각 호스트 의 엔트리를 넣으면 된다.이러한 절차는 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 distribution 으로 제공하고 있다. 어떻게 작 동하고 있는지 확인하기 위해 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 | ... | +---------------------+-------------+-------------+ | localhost | root | ... | +---------------------+-------------+-------------+ | localhost | | ... | +---------------------+-------------+-------------+ | % | jeffrey | ... | +---------------------+-------------+-------------+ | % | root | ... | +---------------------+-------------+-------------+ 정열된 순서에 따라 매칭 알고리즘이 적용되며 먼저 매칭되는 것을 사용한 다. localhost에서 jeffrey가 연결을 하려할때, Host 컬럼에서 'localhost' 목록이 먼저 매칭된다. 물론 사용자 이름이 공백인 목록은 연 결하는 호스트네임과 사용자 이름에 매칭된다. ('%'/'jeffrey' 목록 또한 매칭이 된다. 그러나 테이블에서 처음으로 매칭되는 것은 아니다.) 다른 예제가 있다. user 테이블이 다음과 같다고 가정해보자: +---------------------+-------------+-------------+ | Host | User | ... | +---------------------+-------------+-------------+ | % | jeffrey | ... | +---------------------+-------------+-------------+ | thom | as.loc.gov | ... | +---------------------+-------------+-------------+ 정열된 테이블은 다음과 같다: +---------------------+-------------+-------------+ | Host | User | ... | +---------------------+-------------+-------------+ | thom as.loc.gov | | ... | +---------------------+-------------+-------------+ | % | jeffrey | ... | +---------------------+-------------+-------------+ 첫번째로 thomas.loc.gov에서 jeffrey가 연결하는 것이 매칭되며, whitehouse.gov 에서 jeffrey가 연결하는 거은 두번째로 매칭이 된다. 서버에 연결할 때 문제가 생기면, user 테이블을 출력하여 어떤 것이 먼저 매칭되는지 직접 정열을 하면 된다. 6.6 접근 제어, 2단계 : 요청 인증 연결되었다면 서버는 2단계로 들어간다. 연결이 성사되었을 때 각 요구에 대해 사용자가 수행하려는 연산의 유형에 기반하여 서버는 사용자가 충분 한 권한을 가지고 있는지 점검한다. 여기서 승인 테이블의 권한 필드가 작동한다. 권한은 user, db, host, table_priv, columns_priv 테이블의 정보를 사용한다. GRANT 와 REVOKE 명 령을 이용하여 권한 테이블을 다룰 수 있다. 7.25 [GRANT] 참고. (이전에 보았던 각 권한 테이블의 필드 목록을 참고하는 것이 도움이 될 것이다; 6.4[Privilege] 참고.) user 테이블의 승인 권한은 사용자에게 전체적인 기반을 제공하며 현재의 데이터가 어떤 것인지와는 상관이 없다. 예를 들어, user 테이블에서 사용 자에게 delete 권한을 승인했다면 서버 호스트에서 어떤 데이터베이스의 레코드라도 삭제할 수 있다! 다르게 말해서 user 테이블 권한은 슈퍼유저 권한이며, 슈퍼유저(서버나 데이터베이스 관리자 등)에게만 user 테이블에 대한 권한을 승인하는 것이 좋다. 다른 사용자에게는 user 테이블에서 권 한을 'N'로 설정하고, db와 host 테이블을 사용하여 특정 데이터베이스에 기반한 권한승인을 하는게 좋다. db 와 host 테이블은 특정 데이터베이스의 권한을 승인한다. 각 테이블의 Host 와 Db 필드에서 와일드카드 문자 '%' 와 '_' 를 사용할 수 있으며 값 이 공백이면 필드 범위(scope fields)에서 모든 값을 허용한다. '%' Host 값은 "모든 호스트"를 의미한다. db 테이블에서 Host 값이 공백이면 "host 테이블에서 더 자세한 정보를 문의하라"는 의미이다. A '%' or blank Db value in the host table means or "any database." (** or의 뜻이 무엇인 지 모르겠네요. host 테이블에서 Db 의 값이 '%' 또는 공백이면 "모든 데 이터베이스"를 의미한다는 말 같은데요 **) User 값이 공백이면 익명 사용 자로 간주된다. 서버가 시작할 때 db 와 host 테이블을 읽고 정열을 한다.(동시에 user 테 이블을 읽는다.) db 테이블은 Host, Db, User 순으로 필드 범위를 정열하 며 host 테이블은 Host, Db 순으로 필드 범위를 정열한다. user 테이블과 같이 특정하게 지정되어 있는 값이 먼저 정열되고 최소한도로 지정된 값이 나중에 정열된다. 서버에서 매칭되는 목록을 찾을때, 가장 먼저 발견한 것 을 사용한다. tables_priv 와 columns_priv 테이블은 특정한 테이블과 컬럼에 관련된 권한을 승인한다.db와 host 테이블의 Host 필드와 같이 와일드카드를 Host 필드에서 사용할 수 있다. 그렇지만 Db, Table_name, Column_name 필드에 서는 와일드카드나 공백값을 사용할 수 없다. Host 테이블에서만 와일드카드를 사용할 수 있지만 tables_priv 와 columns_priv 테이블은 db 테이블과 비슷하게 정열이 되며 정열은 간단하 다. 요청 인증 과정은 아래에서 설명한다. 접근-점검 소스 코드에 친숙하다면, 여기서 설명하는 것은 코드에서 사용된 알고리즘과는 약간 다르다는 것을 알 수 있다.여기서의 설명은 코드가 실제로 작동하는 방식과 동일하다. 단 지 설명을 간단하게 하는데서 차이가 있는 것이다. 관리자 요청에 대해서(shutdown, reload 등) 서버는 단지 user 테이블만 체크를 한다. 왜냐면 user 테이블에서만 관리자 권한을 지정하기 때문이 다. 목록에서 요청된 연산을 허용하면 접근이 허용되며 아닌 경우에는 접 근이 거부된다. 예를 들어, mysqladmin shutdown을 실행하고자 하는데 user 테이블 목록에 서는 사용자에게 shutdown 권한을 승인하지 않으면, db나 host 테이블을 체크하지 않더라도 접근이 거부된다. (이러한 테이블에는 Shutdown_priv 컬럼이 없기 때문에 이렇게 할 필요도 없다) 데이터베이스와 관련된 요청에 대해(insert, update 등) 서버는 먼저 user 테이블 목록에서 사용자의 전체(슈퍼유저) 권한을 점검한다. 목록에서 요 청한 연산을 허용하면 접근이 승인된다. user 테이블에서 전체적인 권한이 불충분하면, 서버는 db 와 host 테이블 을 점검하여 데이터베이스에 관련된 권한을 결정한다: 1. 서버는 db 테이블에서 매칭되는 Host, Db, User 필드를 찾는다. 연결하 려는 사용자의 호스트 이름과 Mysql 사용자 이름이 Host 와 User에 매칭되 다. 사용자가 접근하기 원하는 데이터베이스는 Db 필드에 매칭된다. 적합 한 Host 와 User 목록이 없으면 접근은 거부된다. 2. 매칭되는 db 테이블 목록이 있고 Host 필드가 공백이 아니면, 목록은 사용자의 데이터베이스 관련 권한을 정의한다. 3. 매칭되는 db 테이블 목록의 Host 필드가 공백이면, host 테이블에서 어 떤 호스트가 데이터베이스에 접근할 수 있는지 판단한다는 것을 의미한다. 이런 경우, 더 자세한 정보를 위해 host 테이블에서 매칭되는 Host 와 Db 필드를 찾는다. host 테이블에 매칭되는 목록이 없으면 접근은 거부된다. 매칭되는 목록이 있으면 사용자의 데이터베이스 관련 권한은 db 와 host 테이블 목록에서 권한을 intersection 하여 결정된다. (** insertection은 교집합을 생각하면 되지요. and 조건 **) 다시 말해 서, db와 host 테이블 둘 다 'Y'로 되어있을 때 권한이 설정된다.이러한 방법으로 db 테이블에서 일반적인 권한을 승인할 수 있으며, 그러고나서 host 테이블 목록을 사용해 host를 기반으로 하여 선택적으로 권한을 제한 할 수 있다.) db 와 host 테이블 목록을 이용해 데이터베이스와 관련된 권한 승인을 결 정한 후, 서버는 이러한 정보를 user 테이블에서 승인한 전체적인 권한에 추가한다. 그 결과가 요청한 연산을 허용하면 접근이 허용된다. 다른 방법 으로, 서버는 tables_priv 와 columns_priv 테이블에서 사용자의 테이블과 컬럼 권한을 점검하고 사용자의 권한에 추가한다. 그 결과에 따라 접근이 허용되거나 거부된다. 왜 서버에서 전체적인 사용자 엔트리 권한에 데이터베이스, 테이블, 컬럼 에 관련된 권한을 추가하는지가 명확하지 않다.... 이런 경우 사용자 권한 은 초기에 요청된 연산에 대하여 불충분하다... (It may not be apparent why the server adds the database-, table- and column-specific privileges to the global user entry privileges for those cases in which the user privileges are initially found to be insufficient for the requested operation.) 요청은 한가지 유형 이상의 권한이 필요하기 때문이다. 예를 들어, INSERT ... SELECT 문을 수행할 때 insert 와 select 권한 둘 다 필요하다. 사용 자의 권한은 user 테이블에서 한가지 권한을 승인하고 db 테이블 엔트리에 서 다른 권한을 승인할 것이다. 이런 경우, 사용자는 이러한 요청을 수행 하기 위해 필요한 권한을 가지고 있다. 그렇지만 서버는 자체적으로 다른 테이블에 대해서는 ..... (In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself;) ; 두 엔트리에 의해 승인된 권한이 조합되어야 한다. host 테이블은 "안전한" 서버 목록을 유지하는데 사용할 수 있다. TcX에서 는, host 테이블에는 지역 네트웍의 모든 시스템이 포함되어 있다. 여기서 는 모든 권한이 허용된다. 안전하지 않는 호스트를 가리키기 위해 host 테이블을 사용할 수 있다. 안 전하다고 생각되지 않는 공개 지역에 위치한 public.your.domain 시스템이 있다고 가정해보자. 사용자는 사용자 네트웍의 모든 호스트에 접근할 수 있으며, host 테이블 엔트리가 다음과 같은 시스템만 제외한다 : +----------------------+------+-----------------------------------+ | Host | Db | ... | +----------------------+------+-----------------------------------+ | public.your.domain | % | ... (all privileges set to 'N') | +----------------------+------+-----------------------------------+ | %.your.domain | % | ... (all privileges set to 'Y') | +----------------------+------+-----------------------------------+ 당연히 접근 권한이 원하는대로 되어 있는지 언제나 승인 테이블에서 목록 을 테스팅해야 한다. (예를 들어 mysqlaccess 를 사용) 6.7 권한 변경시 적용 방법 mysqld 가 시작할 때, 모든 승인 테이블 내용이 메모리로 올라가고 이때부 터 유효하게 된다. GRANT, REVOKE, SET PASSWORD 를 이용해 승인 테이블에 변경을 하면 바로 서버에서 인식을 한다. 권한 테이블을 직접 변경했다면(INSERT, UPDATE 등을 사용하여), 서버에서 승인 테이블을 재가동하도록 하기 위해 FLUSH PRIVIEGES 문이나 mysqladmin flush-privileges 를 실행해야 한다.그렇게 하지 않으면 서버 를 다시 시작하기 전까지 변경된 권한이 적용되지 않는다. 서버에서 권한 테이블이 변경되었다는 것을 감지했을 때, 이미 존재하던 클라이언트 연결은 다음과 같이 영향을 받는다: 1. 테이블과 컬럼 권한 변경은 클라이언트의 다음 요청부터 적용된다. 2. 데이터베이스 권한 변경은 다음의 USE db_name 명령부터 적용된다. 3. 전체적인 권한과 비밀번호 변경은 클라이언트가 다음에 연결할 때부터 적용된다. 6.8 초기 mysql 권한설정 mysql을 설치하고 나서, mysql_install_db 스크립트를 실행해서 초기 접근 권한을 설정해야 한다. 4.7.1 [Quick install] 참고. mysql_install_db 스 크립트는 mysqld 서버를 시작하고, 다음과 같이 승인 테이블의 권한을 초 기화한다: - mysql root 사용자는 슈퍼유저이며 모든 것을 할 수 있다. 로컬 호스트 에서만 연결할 수 있다. 주의 : 처음에 root 비밀번호는 비어있다. 그래서 누구나 비밀번호없이 root로 연결할 수있고 모든 권한을 승인받는다. - 익명 사용자는 'test' 나 'test_' 로 시작하는 데이터베이스에 대한 모 든 권한을 승인받는다. 모든 사용자가 로컬 호스트에서 연결할 수 있으며 익명 사용자로 간주된다. - 다른 권한은 거부된다. 예를 들어 일반 사용자는 mysqladmin shutdown 이나 mysqladmin processlist 를 사용할 수 없다. 설치했을 때 초기 권한이 폭넓게 설정되어 있기 때문에 가장 먼저 mysql root 사용자의 비밀번호를 설정해야 한다. 다음과 같이 설정하면 된다. (PASSWORD() 함수를 이용해 비밀번호를 설정해야 한다!): shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; or shell> mysqladmin -u root password new_password (** PASSWORD() 라는 함수를 사용하지 않아도 되므로 편리함. 또한 SQL문 에서 grant 명령을 이용해 설정할 수도 있지요 **) 첫번째 방법을 사용하면 직접 user 테이블의 비밀번호를 업데이트한다. 이 경우 서버가 다시 승인 테이블을 읽도록 해야 한다.(FLUSH PRIVILEGES 사 용). 왜냐하면 다른 방법으로는 변경사항을 알릴 수 없기 때문이다. (** 승인 테이블을 다시 읽지 않아서 이전에 설정했던 비밀번호가 제대로 안되는 경우가 있을 것입니다. 꼭 기억하고 있어야해요 **) root 비밀번호가 설정되었으면 서버에 root로 접속할때마다 비밀번호를 명 시해야 한다. 추가로 셋업을 하거나 테스트할 때는 비밀번호를 설정할 필요가 없기 때문 에 root 비밀번호를 빈값으로 남겨두고 싶을 것이다. 그렇지만 실제 작업 을 하기 전에는 반드시 비밀번호를 설정했는지 확인해야 한다. 기본권한을 어떻게 설정하는지 mysql_install_db 스크립트를 살펴보자. 다 른 사용자에게 권한을 어떻게 설정할지 이것을 기본으로 사용할 수 있다. 위에서 설명한 것과 다르게 초기 권한을 설정하기 원하면, mysql_install_db 스크립트를 실행하기 전에 수정하면 된다. 완전하게 승인 테이블을 다시 만들기 위해 mysql 데이터베이스를 포함하는 디렉토리의 '*ISM' 과 '*.ISD' 파일을 제거해야 한다. (이 디렉토리는 database 디렉토리에서 'mysq''이라는 이름이 붙어있다. mysqld --help 해 서 database 디렉토리의 목록을 볼 수 있다.) 원하는대로 권한을 수정한 후 mysql_install_db 스크립트를 실행하자. 6.9 mysql에 새로운 사용자 권한 추가하기 두가지 방법으로 사용자를 추가할 수 있다 : GRANT 문 사용 또는 mysql 승 인 테이블 직접 조작. GRANT 문을 사용하는 것이 더 선호되는 방법이다. 아래의 예제는 새로운 사용자를 설정하기 위해 어떻게 mysql 클라이언트를 사용하는지 보여준다. 이 예제는 이전에 설명했던것과 같이 기본값에 따라 권한을 설정하는 것으로 가정한다. 이것은 설정을 바꾸기 위해 mysqld가 실행되고 있는 같은 시스템에 있어야 한다는 것을 말한다. (**초기값은 localhost에서만 접속 가능하므로**) 또한 mysql root 사용자로 접속해야 하고 root 사용자는 mysql 데이터베이스에 대한 insert 권한과 reload 관 리자 권한이 있어야 한다. root 사용자의 비밀번호를 바꾸었으면, 아래와 같이 mysql 명령행 상태에서 비밀번호를 명시해야 한다. GRANT 문을 이용해 새로운 사용자를 추가할 수 있다: shell> mysql --user=root mysql mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysql> GRANT USAGE ON *.* TO dummy@localhost; 위 GRANT 문에서는 세 명의 사용자를 설정한다: monty : 어느 곳에서든 서버에 연결할 수 있는 완전한 슈퍼유저이지만 비 밀번호를 사용해야 한다. 우리는 monty@localhost 와 monty@"%"를 사용한 GRANT 문에 대해서 반드시 논의를 해야 한다. localhost 목록을 추가하지 않으면, mysql_install_db 에 의해 생성된 localhost 의 익명 사용자 목록 (등록?)이 로컬 호스트에서 접속할때 우선권을 갖는다. 왜냐하면 지정된 Host 필드 값이 있으며 정열 순서에서 먼저 오기 때문이다. (** 승인 테이 블의 정열 순서가 특정한 Host를 지정한 것부터 시작하는 것을 기억하자. admin : 비밀번호 없이 localhost에서 접속할 수 있으며 reload와 process 관리자 권한을 승인받은 사용자. 이경우 사용자가 mysqladmin processlist 뿐만 아니라 mysqladmin reload, mysqladmin refresh, mysqladmin flush-* 명령을 실행할 수 있다.데이터베이스와 관련된 권한은 승인되지 않았다. 이것은 추가적인 GRANT 문을 사용해 나중에 승인할 수 있다. dummy : 비밀번호없이 연결할 수 있지만 오직 localhost에서만 연결 가능 한 사용자. 권한 유형(privilege type)이 USAGE 이기 때문에 전체적인 권 한이 'N'로 설정되어 있다. USAGE 는 아무런 권한도 설정하지 않는다. 나 중에 데이터베이스와 관련된 권한을 승인할 수 있다. 또한 동일한 사용자 접근 정보를 INSERT 문을 통해 직접 추가할 수 있으며 이경우에는 서버가 승인 테이블을 다시 읽도록 알려주어야 한다.(**FLUSH PRIVILEGES 사용**) shell> mysql --user=root mysql mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y'; mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy',"); mysql> FLUSH PRIVILEGES; mysql 버전에 따라 위에서 'Y' 값이 다를 수 있다는 것을 기억하자. 3.22.11 버전 이후에서 사용할 수 있는 확장된 INSERT 문은 여기서 admin 사용자에게 사용되었다. 슈퍼유저를 설정하기 위해 권한필드를 'Y'로 한 user 테이블 목록만 만들 면 된다는 것을 기억하자. db 나 host 테이블 목록은 필요없다. (** 관리 자 권한은 db나 host 테이블과는 전혀 관련이 없다. db는 접속할 수 있는 데이터베이스에 대해 상세하게 설정하고 host 테이블은 db테이블을 좀 더 정교하게 설정하기 위해 필요한 것이다. 관리자 권한은 오직 user 테이블 만 관련되어있다 **) 마지막 INSERT 문(dummy 사용자)에서는 user 테이블의 권한 컬럼이 명확하 게 설정되지 않았다. 왜냐면 이 컬럼의 기본값은 'N'로 되어 있기 때문이 다. 다음의 예제에서는 custom 이라는 사용자를 추가한다. custom은 localhost, server.domain, whitehouse.gov에서 접속할 수 있다. localhost에서는 bankaccount 데이터베이스에만 접속할 수 있으며 whitehouse.gov에서는 expenses 데이터베이스에, 모든 세 호스트상에서는 customer 데이터베이스에 접속하길 원한다. 모든 세 호스트상에서 stupid 라는 비밀번호를 사용하길 원한다. GRANT 문을 이용 이러한 사용자 권한을 설정하기 위해 다음의 명령을 실행 하자: shell> mysql --user=root mysql mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO custom@whitehouse.gov IDENTIFIED BY 'stupid'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO custom@'%' IDENTIFIED BY 'stupid'; 승인 테이블을 직접 수정해 사용자 권한을 설정하려면 다음의 명령을 사용 하자. (마지막에 FLUSH PRIVILEGES 를 사용해야 한다는 것을 기억하자): shell> mysql --user=root mysql mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) VALUES('server.domain','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) VALUES('whitehouse.gov','custom',PASSWORD('stupid')); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y'); mysql> FLUSH PRIVILEGES; 처음의 세가지 INSERT 문은 custom 사용자가 비밀번호를 사용하여 다양한 호스트에서 접속할 수 있도록 user 테이블 목록을 추가한다. 그렇지만 그 에게 어떠한 퍼미션도 승인하지 않는다. (모든 권한은 기본값으로 'N' 이 다) 다음의 세가지 INSERT 문은 적절한 호스트에서 접속을 할 때, custom 에게 bankaccount, expenses, customer 데이터베이스에 대한 권한을 승인 하는 db 테이블 목록을 추가한다. 일반적으로 승인 테이블을 직접 수정하 였으면, 변경된 권한을 적용하기 위해 서버가 승인 테이블을 다시 읽도록 해 주어야 한다. 특정한 사용자가 특정한 도메인의 시스템에서 접속할 수 있도록 설정하고 자 한다면, 다음과 같이 GRANT 문을 설정할 수 있다: mysql> GRANT ... ON *.* TO myusername@"%.mydomainname.com" IDENTIFIED BY 'mypassword'; 승인 테이블을 직접 수정하려면 다음과 같이 한다: mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername', PASSWORD('mypassword'),...); mysql> FLUSH PRIVILEGES; ~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~ 승인 테이블을 다루기 위해 xmysqladmin, mysql_webadmin, xmysql 프로그 램을 사용할 수 있다. http://www.mysql.com/Contrib 에서 이러한 유틸리 티를 찾을 수 있다. 6.10 비밀번호 설정 방법 앞의 예제는 중요한 원칙을 보여준다 : INSERT 나 UPDATE 문에서 공백이 아닌 비밀번호를 저장할 때 반드시 암호화하기 위해 PASSWORD() 함수를 사 용해야 한다!! user 테이블은 비밀번호를 플레인텍스트(**일반 텍스트 파 일**)가 아니라 암호화된 형태로 저장하기 때문이다. 이러한 사실을 잊어 버리면 다음과 같이 비밀번호를 설정하려고 할 것이다: shell> mysql -u root mysql mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey','bLa81m0'); mysql> FLUSH PRIVILEGES; 플레인텍스트 값 'bLa81m0' 은 user 테이블에 비밀번호로 저장이 된 다.jeffrey라는 사용자가 이 비밀번호를 사용해 서버에 연결하려고 할 때 mysql 클라이언트를 이 비밀번호를 암호화해서 그 결과를 서버로 보낸다. 서버는 암호화된 비밀번호('bLa81m0'이 아니다)를 user 테이블의 비밀번호 (플레인텍스트 'bLa81m0' 값이다)와 비교한다. 비교는 실패하고 서버는 연 결을 거부한다: shell> mysql -u jeffrey -pbLa81m0 test Access denied 비밀번호는 user 테이블에 입력될 때 반드시 암호화되어야 하기 때문에, INSERT 문은 다음과 같이 사용해야 한다: mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey',PASSWORD('bLa81m0')); 또한 SET PASSWORD 문을 사용할 때도 PASSWORD() 함수를 사용해야 한다: mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('bLa81m0'); 참고 : PASSWORD() 함수는 비밀번호 암호화를 수행한다. 그렇지만 유닉스 에서 비밀번호를 암호화하는 방법과는 다르다. 유닉스 비밀번호와 mysql 비밀번호가 동일할 때 PASSWORD()가 유닉스 비밀번호 파일(** /etc/passwd 파일 **)에 암호화되어 저장된 값과 같다고 생각하면 안 된다. GRANT ... IDENTIFIED BY 문이나 mysqladmin password 명령을 사용해 비밀 번호를 설정하면 PASSWORD() 함수는 필요없다. 둘다 비밀번호를 암호화해 서 저장한다: mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'bLa81m0'; shell> mysqladmin -u jeffrey password bLa81m0 (** 당근, GRANT 문이나 mysqladmin password 명령을 사용하는게 편하겠지 요? mysql의 암호화 알고리즘이 유닉스와 다르듯 유닉스 계정과 mysql의 사용자는 전혀 다르다는 것도 다시 한번 기억하고 있어야 합니다.**) 6.11 접근 거부 에러가 나는 이유 mysql 서버에 연결하려 할 때 접근 거부 에러가 나면, 아래에서 설명하는 것에 따라 해결 방법을 찾을 수 있다: - 초기 승인 테이블 내용을 설정하기 위해 mysql을 설치한 후 mysql_install_db 스크립트를 실행하였는가? 실행하지 않았다면 스크립트 를 실행하자. 6.8 [Default privileges] 참고. 다음 명령을 이용해 초기 권한을 시험해 볼 수 있다: shell> mysql -u root test 에러없이 서버에 접속할 수 있을 것이다. mysql 데이터베이스 디렉토리에 'user.ISD' 파일이 있는지 확인해보아야 한다. (일반적으로 'mysql 설치 디렉토리/var/mysql/user.IDS' 이다) - 설치를 새로하고 난후 , 서버에 연결하고 사용자와 접근 권한을 설정해 야 한다: shell> mysql -u root mysql 초기에 mysql root 사용자만 비밀번호가 없기 때문에 서버에 연결할 수 있 다. 보안문제가 있기 때문에, 다른 mysql 사용자를 설정하기 전에 먼저 root의 비밀번호를 설정해야 한다. root로 접속하려하는데 다음의 에러가 났다고 가정하자: Access denied for user: '@unknown' to database mysql 이것은 user 테이블에 User 컬럼 = root 라는 목록이 없고, mysqld가 사용 자 클라이언트의 호스트이름을 해석할 수 없다는 것을 의미한다. 이런 경 우 --skip-grant-tables 옵션을 이용해 서버를 다시 시작해야 하고 '/etc/hosts' 를 편집하거나 '\windows\hosts' 파일에 사용자 호스트 목록 을 추가해야 한다. - 3.22.11 이전 버전에서 3.22.11이나 이후 버전으로 업데이트했다면, mysql_fix_privilege_tables 스크립트를 실행했는가? 하지 않았다면 실행 하자. mysql 3.22.11에서 GRANT 문 기능이 가능해지면서 승인 테이블 구조 가 바뀌었다. - (INSERT 나 UPDATE 문을 사용해) 승인 테이블을 직접 고쳤고 변화가 아 직 반영되지 않은 것으로 보이면, FLUSH PRIVILEGES 문을 사용하거나 mysqladmin flush-privileges 명령을 사용해 서버가 승인 테이블을 다시 읽도록 해야 한다는 것을 기억하자.그렇지 않으면 서버가 재시작하기 전까 지는 변화된 것이 반영되지 않는다. root 비밀번호를 설정하고 나서 권한 을 flush 하기까지는 비밀번호를 명시할 필요가 없다. 왜냐면 서버는 아직 비밀번호를 바꾸었는지 모르기 때문이다. - 세션 중간에 권한이 변경된 것으로 보이면 슈퍼유저가 바꾸었을 것이다. 승인 테이블을 재시작하는 것은 새로운 클라이언트 접속에 영향을 미치지 만 이미 존재하고 있던 연결은 6.7 [Privileges changes]에서 설명한대로 영향을 미친다. - 시험하기 위해, mysqld 대몬에 --skip-grant-tables 옵션을 주어 시작 하자. 그러고나서 mysql 승인 테이블을 변경할 수 있고 변경된것이 원하는 대로 작동하는지를 체크하는 mysqlaccess 스크립트를 사용할 수 있다. 원 하는대로 수정이 되었으면 mysqld 서버가 새로운 승인 테이블로 시작할 있 도록 mysq1admin flush-priveleges 를 실행한다. 주의 : 승인테이블을 재로딩하는 것은 --skip-grant-tables 옵션을 무효화 한다. 이를 통해 서버를 다운시키고 다시 재시작하지 않고도 승인 테이블 을 시작할 수 있다. - 펄, Python, ODBC 프로그램에서 접근하는데 문제가 있다면, mysql -u user_name db_name 또는 mysql -u user_name -pyour_pass db_name 으로 서 버에 접속을 시도해보자. (-p 와 비밀번호사이에는 공백이 없다는 것을 기 억하자. 또한 --password=your_pass 형태로도 사용할 수 있다) mysql 클라 이언트로 접속이 되면 프로그램에 문제가 있는 것이며 접근 권한에는 문제 가 없다. - 비밀번호가 제대로 작동하지 않으면, INSERT, UPDATE, SET PASSWORD 문 에서 비밀번호를 설정하면서 PASSWORD() 함수를 반드시 사용해야 한다는 것을 기억하자. PASSWORD() 함수는 GRANT ... INDENTIFIED BY 문이나 mysqladmin password 명령을 사용했다면 불필요하다. 6.10 [Passwords] 참 고. - localhost 는 지역 호스트 이름과 같은 말이다. 또한 호스트를 명백하게 설정하지 않은 경우 클라이언트에서 연결하려는 호스트의 기본값이다. 그 러나 MIT-pthreads를 사용하는 시스템에서는 localhost로의 연결이 제대로 작동하지 않는다. (localhost 연결은 유닉스 소켓을 통해 만들어진다. 그 렇지만 MIT-pthreads에서는 유닉스 소켓을 지원하지 않는다.) 이와 같은 시스템에서 문제를 피하려면, 서버에 호스트 이름을 명확하게 말해주기 위 해 --host 옵션을 사용해야 한다. 그러면 mysqld 서버에 TCP/IP 연결을 만 든다. 이경우, 서버 호스트 의 user 테이블 목록에 실제 호스트이름이 있어야 한다. (서버와 동일한 호스트에서 클라이언트 프로그램을 실행한다고 하더라도 마찬가지이다.) - mysql -u user_name db_name 으로 데이터베이스에 접속하려 할 때 접근 거부 에러가 나면, user 테이블에 문제가 있을 것이다. mysql -u root mysql 를 실행하여 점검하고 다음의 SQL 문을 사용하자: mysql> SELECT * FROM user; 여기서 사용자의 호스트이름과 mysql 사용자 이름과 맞는 Host 와 User 컬 럼의 목록이 포함되어 있어야 한다. - Access denied 에러 메시지는 접속하려는 사용자와 호스트 이름, 그리고 비밀번호를 사용했는지 여부를 보여줄 것이다. 일반적으로 user 테이블에 에러 메시지에서 보여준 호스트 이름과 사용자 이름과 정확하게 맞는 목록 을 가지고 있어야 한다. - 다른 시스템에서 mysql 서버에 접속할 때 다음의 에러 메시지가 나오면, user 테이블에 연결을 하려고 하는 호스트 이름이 없다는 것을 말한다: Host ... is not allowed to connect to this MySQL server (서버 호스트에서!) 명령행 유틸리티인 mysql을 사용하여 user 테이블에 연결하고자 하는 사용자/호스트 이름을 추가하여 해결할 수 있다. mysql 3.22 를 사용하고 있지 않고 연결하고자 하는 시스템의 IP 숫자나 호스트 이름을 모른다면, user 테이블에 Host 컬럼 값으로 '%' 목록을 입력하고 서버 시스템에서 --log 옵션을 사용해 mysqld 를 재시작하자. 클라이언트 시스템에서 연결을 시도한 후 mysql 로그에는 어떻게 실제로 연결을 했는 지에 대한 정보가 들어있다. (그러고나서 '%'를 로그에 나온 실제 호스트 이름으로 바꾼다. 그렇지 앟으면 보안에 문제가 생길 수 있다.) - mysql -u root test 는 작동을 하는데 mysql -h your_hostname -u root test 에서 접근에 에러가 나면, user 테이블에 정확한 호스트 이름이 없을 것이다. 일반적인 문제는 user 테이블의 Host 값에는 완전하지 않은 호스 트 이름(** 도메인은 빼고 호스트 이름만 넣은 경우**) 이 들어가 있는데 시스템의 네임 해석 루틴은 FQDN(fully-qualified domain name - ** 완전 한 도메인 이름과 호스트 이름을 사용**)으로 처리하는 경우이다(또는 거 꾸로 해석).예를 들어, user 테이블에 호스트 이름이 'taejun' 으로 되어 있는데, DNS는 mysql에 호스트 이름이 'taejun.subnet.se'라고 알려줄 수 있으며 이경우는 제대로 작동하지 않을 것이다. user 테이블에 Host 컬럼 값으로서 해당하는 IP 숫자나 호스트 이름을 추 가하자. (대신, user 테이블에 Host 값으로 와일드카드 문자를 포함할 수 있다. 예를 들어, 'taejun.%'. 그러나 호스트이름 끝에 '%'를 사용하는 것 은 안전하지 않으며 권하지도 않는다!) - mysql -u user_name test 는 작동하는데 mysql -u user_name other_db_name 은 작동하지 않는다면 db 테이블에 other_db_name 목록이 없는 경우이다. - 서버 시스템에서 mysql -u user_name db_name 은 작동을 하는데, 다른 클라이언트 시스템에서 mysql -u user_name db_name 은 작동을 하지 않는 다면, user 테이블이나 db 테이블에 클라이언트 시스템의 목록이 없는 것 이다. - 왜 Access denied 가 나는지 해결하지 못하면, user 테이블에서 와일드 카드('%' 또는 '_')를 포함하고 있는 Host 값을 가진 목록을 모두 제거하 자. 매우 일반적인 에러는 Host='%' 그리고 User='some user'로 입력을 하 고나서 , 이렇게 하면 같은 시스템에서 연결할 때 localhost를 지정할 수 있도록 허용한다고 생각하는 것이다. 이것이 제대로 작동하지 않는 이유는 기본 권한에 Host='localhost' 와 User='' 목록이 포함되어 있기 때문이 다. Host 값에 '%' 보다 더 분명한 'localhost' 목록이 있기 때문에, localhost에서 접속할 때 새로운 목록보다 먼저 선택이 된다. 정확한 절차 는 두번째 항목으로 Host='localhost' 와 User='some_user' 를 입력하거나 Host='localhost' 와 User='' 를 제거하는 것이다. - 다음의 에러가 나는 경우: Access to database denied db 나 host 테이블에 문제가 있을 것이다. 선택한 db 테이블의 목록에 Host 컬럼이 비어있다면, host 테이블에 db 테이블 목록에 적용되는 호스 트 이름이 있는지를 확인해야 한다. (** 일반적으로 db 테이블에 host 값을 비워두는 경우, host 테이블에서 접근하는 호스트를 제어할 수 있다 **) - 다음의 에러가 나는 경우: Access to database denied SELECT ... INTO OUTFILE 또는 LOAD DATA INFILE 의 SQL 문을 사용하는 경 우, user 테이블 목록에서 file 권한이 설정되어 있지 않았을 것이다. - 다른 모든 것이 실패하였을 경우, mysqld 대몬을 디버깅 옵션으로 시작 하자. (예를 들어, --debug=d,general,query) 그러면 각 명령에서 생기는 정보와 함께, 접속을 시도하는 호스트와 사용자에 대한 정보를 출력할 것 이다. G.1 [Debugging] 참고. - mysql 승인 테이블에서 다른 문제에 부딪쳤고 이 문제를 메일링리스트에 알려야겠다고 느끼면, mysql 승인 테이블을 덤프하여 제공해야 한다. mysqldump mysql 명령을 사용해 테이블을 덤프할 수 있다. 언제나 mysqlbug 스트립트를 사용하여 문제를 올리자. - 다음의 에러가 나는 경우, mysqld 대몬이 실행되지 않거나 잘못된 소켓 이나 포트로 연결하려고 시도하는 경우: Can't connect to local MySQL server Can't connect to MySQL server on some_hostname 먼저 mysqld 대몬이 실제로 작동하는지 ps를 이용해 확인한다. 소켓 파일 이 있느지 확인하고 점검을 해 보아야 한다.(일반적으로 `/tmp/mysql.sock' 임) 또는 telnet host_name 3306으로 접속을 시도해보 자. 더 자세한 정보를 위해 mysqladmin version 과 mysqladmin -h host_name version 을 사용해 볼 수 있다. 물론 참고할 것이 있는지 mysql 데이타 디렉토리의 에러 로그 파일을 점검해보자. 클라이언트 프로그램은 설정 파일이나 환경 변수에서 지정한 연결 패러미 터를 사용할 수 있다는 것을 기억하자. 명령행에서 지정하지 않았는데 클 라이언트가 잘못된 기본 연결 패러미터를 보내는 것으로 생각되면, 홈 디 렉토리에서 환경변수나 '.my.cnf' 파일을 점검해보자. 비록 여기서 지정한 연결 패러미터와는 관계가 멀지만 시스템의 전반적인 mysql 설정 파일을 점검해 볼 수 있다. 4.14.4 [Option files] 참고. 클라이언트에서 아무런 옵션도 주지 않았는데 Access denied 에러 메시지가 나오는 경우, 옵션 파 일 중에서 예전의 비밀번호를 지정하지 않았는지 확인해 보자. 4.14.4 [Option files] 참고. 6.12 크랙커에 대비하여 mysql을 안전하게 하는 방법 mysql 서버에 연결할 때 일반적으로 비밀번호를 사용해야 한다. 비밀번호 는 연결할 때 단순한 텍스트로 전송되지 않는다. 서버/클라이언트 연결은 암호화되지 않는다; 모든 정보는 연결을 볼 수 있 는 누구라도 읽을 수 있는 텍스트로 전송된다. 이 문제에 대해 걱정이 되 면 문제를 어렵게 하기 위해 압축 프로토콜(mysql 3.22 이상 버전)을 사용 할 수 있다. 보안을 더 확실하게 하기 위해 ssh를 설치할 수 있다. (http://www.cs.hut.fi/ssh 참고) 이것을 이용해 mysql 서버와 클라이언트 사이에 암호화된 TCP/IP 연결을 사용할 수 있다. mysql 시스템의 보안을 유지하게 위해 다음의 제안을 신중하게 고려하자: - 모든 mysql 사용자가 비밀번호를 사용. 어떤 사용자가 비밀번호가 없으 면 'mysql - u 사용자이름' 을 이용해 간단하게 그 사용자로 로그인할 수 있다는 것을 기억하자. 이것은 클라이언트/서버 애플리케이션의 일반적인 작동방법이다. mysql_install_db 스크립트를 실행하기 전에 이 스크립트를 수정하여 모든 사용자의 비밀번호를 바꿀 수 있다. 또는 mysql root 사용 자의 비밀번호를 바꿀 때는 다음과 같이 하면 된다: shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES; - mysql 데몬을 유닉스의 root 사용자로 시작하지 말자. mysqld 는 다른 사용자가 실행할 수 있다.또한 보안을 더 엄격하게 하기 위해 mysql이라는 유닉스 사용자를 만들 수 있다. 다른 유닉스 사용자로 mysqld를 실행하면 user 테이블에서 root 사용자 이름을 바꿀 필요가 없다. mysqld를 다른 유 닉스 사용자가 시작하기 위해 mysql.server 스크립트를 수정하면 된다. 일 반적으로 su 명령을 사용한다. 더 자세한 내용은 16.7 [Changing mysql user] 참고. - mysqld를 실행할 수 있는 사용자만이 데이터베이스 디렉토리에 읽기/쓰 기 권한을 가지고 있는지 확인. - 모든 사용자에게 process 권한을 주지 말자. mysqladmin processlist 을 출력하면 현재 실행하는 쿼리의 내용을 볼 수 있다. 그러므로 이러한 명령 을 실행할 권한이 있는 사용자는 다른 사용자의 UPDATE user SET password=PASSWORD(_'no_secure') 질의를 볼 수 있다.mysql은 process 권 한을 가진 사용자를 위해 추가적인(extra) 연결을 저장한다.그래서 mysql root 사용자는 모든 일반적인 연결이 사용되었어도 로그인하고 점검을 할 수 있다. - 모든 사용자에게 file 권한을 주지 말자. 이러한 권한이 있는 사용자는 mysqld 대몬의 권한이 있는 파일 시스템의 어느 곳에라도 파일을 저장할 수 있다.좀 더 안전하게 하기 위해 SELECT ... INTO OUTFILE 로 생성되는 모든 파일은 모든 사용자가 읽기만 할 수 있으며 이미 존재하는 파일을 덮 어씌울 수 없다. (** file 권한은 LOAD DATA INFILE , SELECT .. INTO OUTFILE 문을 이용하 여 서버에 파일을 저장하고 읽을 수 있는 권한을 허용한다. 이러한 권한을 가진 사용자는 mysql 서버가 읽고 쓸 수 있는 파일을 읽고 쓸 수 있는 권 한이 허용된다. 일반 사용자에게 이런 권한을 줄 필요는 없다. 필요한 부 분만 권한을 주는 것이 좋다. 권한을 남용말자. **) - DNS 를 신뢰하지 못한다면 승인 테입르에서 호스트이름 대신 IP를 사용 하자. 기본적으로 mysqld 의 --secure 옵션은 호스트이름을 안전하게 한 다. 어떤 경우 와일드카드 문자가 포함된 호스트이름 값을 사용할때는 매 우 조심해야 한다. - mysql.server 스크립트에서 유닉스 root 사용자의 비밀번호를 넣는다면, 이 스크립트는 오직 root만이 읽을 수 있도록 해야 한다. 다음의 mysqld 옵션은 보안과 관련되어 있다: --secure : gethostbyname() 시스템 콜에 의해 리턴된 IP 숫자가 원래의 호스트이름을 resolve 한 것과 같은지를 점검한다. 이것은 어떤 사람이 다 른 호스트 이름을 에뮬레이터해서 접근하는 것을 어렵게 만든다. 이 옵션 은 또한 호스트이름이 온전한지에 대한 점검을 추가한다. 해석하는데 때로 는 시간이 많이 걸려서 mysql 3.21에서는 기본적으로 설정이 되어 있지 않 다. mysql 3.22에서는 호스트이름을 캐쉬하고 이 옵션이 기본적으로 설정 되어 있다. (** 함수 gethostbyname()은 호스트 이름을 인자로 받아 그에 해당하는 IP 주소 및 기타 정보를 해당하는 구조체에 담아 그 구조체의 포인터를 리턴 하는 함수입니다. 쉽게 말해서 호스트 이름을 넣으면 해당 IP 주소를 찾아 주지요.**) --skip-grant-tables : 이 옵션을 사용하면 서버가 권한 시스템을 전혀 사 용하지 않는다. 그러면 모든 사용자가 모든 데이터베이스에 접속할 수 있 다! (mysqladmin reload 를 실행하여 실행중인 서버가 승인 테이블을 사용하도 록 할 수 있다.) --skip-name-resolve : 호스트이름이 해석되지 않는다. 승인 테이블의 모 든 Host 컬럼값은 반드시 IP 숫자이거나 로컬호스트이어야 한다. --skip-networking : 네트웍을 통한 TCP/IP 연결을 허용안함. mysqld와 모 든 연결은 유닉스 도메인 소켓을 통해 만들어진다. 이 옵션은 MIT-pthreads를 사용하는 시스템에서는 제대로 작동을 하지 않는다. 왜냐 면 MIT-pthreads 패키지는 유닉스 소켓을 지원하지 않기 때문이다. (** 리눅스를 사용하는 사람들에게는 상관없겠죠? 유닉스 도메인 소켓을 지원하니깐. 이와 비슷하게 postgres도 6.3버전 이후부터인가요? 기본적으 로 유닉스 도메인 소켓으로 바뀌었지요. **) 7. MySQL language reference. 7.1 Literals: how to write strings and numbers 7.1.1 Strings A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters. Examples: 'a string' "another string" Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (`\'), known as the escape character. MySQL recognizes the following escape sequences: \0 An ASCII 0 (NUL) character. \n A newline character. \t A tab character. \r A carriage return character. \b A backspace character. \' A single quote (`'') character. \" A double quote (`"') character. \\ A backslash (`\') character. \% A `%' character. This is used to search for literal instances of `%' in contexts where `%' would otherwise be interpreted as a wildcard character. \_ A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wildcard character. There are several ways to include quotes within a string: A `'' inside a string quoted with `'' may be written as `'''. A `"' inside a string quoted with `"' may be written as `""'. You can precede the quote character with an escape character (`\'). A `'' inside a string quoted with `"' needs no special treatment and need not be doubled or escaped. In the same way, `"' inside a string quoted with `'' needs no special treatment. The SELECT statements shown below demonstrate how quoting and escaping work: mysq> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | ''hello'' | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "This\nIs\nFour\nlines"; +--------------------+ | This Is Four lines | +--------------------+ If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. " ASCII 34, double quote. Represent this by `\"'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section . In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section . You should use an escape function on any string that might contain any of the special characters listed above! 7.1.2 Numbers Integers are represented as a sequence of digits. Floats use `.' as a decimal separator. Either type of number may be preceded by `-' to indicate a negative value. Examples of valid integers: 1221 0 -32 Examples of valid floating-point numbers: 294.42 -32032.6809e+10 148.00 An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number. 7.1.3 Hexadecimal values MySQL supports hexadecimal values. In number context these acts like an integer (64 bit precision). In string context these acts like a binary string where each pair of hex digits is converted to a character. mysql> SELECT 0xa+0 -> 10 mysql> select 0x5061756c; -> Paul Hexadecimal strings is often used by ODBC to give values for BLOB columns. 7.1.4 NULL values The NULL value means ``no data'' and is different from values such as 0 for numeric types or the empty string for string types. See section 18.15 Problems whit NULL values. NULL may be represented by \N when using the text file import or export formats (LOAD DATA INFILE, SELECT ... INTO OUTFILE). See section 7.15 LOAD DATA INFILE syntax. 7.1.5 Database, table, index, column and alias names Database, table, index, column and alias names all follow the same rules in MySQL: A name may consist of alphanumeric characters from the current character set and also `_' and `$'. The default character set is ISO-8859-1 Latin1; this may be changed by recompiling MySQL. See section 9.1.1 The charater set used for data and sorting. A database, table, index or column name can be up to 64 characters long. An alias name can be up to 256 characters long. A name may start with any character that is legal in a name. In particular, a name may start with a number (this differs from many other database systems!). However, a name cannot consist{{ }} only{{ }} o f numbers. It is recommended that you do not use names like 1e, because an expression like 1e+1 is ambiguous. It may be interpreted as the expression 1e + 1 or as the number 1e+1. You cannot use the `.' character in names because it is used to extend the format by which you can refer to columns (see immediately below). In MySQL you can refer to a column using any of the following forms: +--------------------+---------------------------------------------+ | Column reference | Meaning | +--------------------+---------------------------------------------+ | col_name | Column col_name from whichever table used | | | in the query contains a column of that name | +--------------------+---------------------------------------------+ | tbl_name.col_name | Column col_name from table tbl_name | | | of the current database | +--------------------+---------------------------------------------+ | db_name.tbl | Column col_name from table tbl_name | | _name.col_name | of the database db_name. This form is | | | available in MySQL 3.22 or later. | +--------------------+---------------------------------------------+ You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a statement unless the reference would be ambiguous. For example, suppose tables t1 and t2 each contain a column c, and you retrieve c in a SELECT statement that uses both t1 and t2. In this case, c is ambiguous because it is not unique among the tables used in the statement, so you must indicate which table you mean by writing t1.c or t2.c. Similarly, if you are retrieving from a table t in database db1 and from a table t in database db2, you must refer to columns in those tables as db1.t.col_name and db2.t.col_name. The syntax .tbl_name means the table tbl_name in the current database. This syntax is accepted for ODBC compatibility, because some ODBC programs prefix table names with a `.' character. 7.1.5.1 Case sensitivity in names In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in Unix and case insensitive in Win32. Note: Although database and table names are case insensitive for Win32, you should not refer to a given database or table using different cases within the same query. The following query would not work because it refers to a table both as my_table and as MY_TABLE: SELECT * FROM my_table WHERE MY_TABLE.col=1; Column names are case insensitive in all cases. Aliases on tables are case sensitive. The following query would not work because it refers to the alias both as a and as A: mysql> SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2; Aliases on columns are case insensitive. 7.2 Column types MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values. The column types supported by MySQL are listed below. The following code letters are used in the descriptions: M Indicates the maximum display size. The maximum legal display size is 255. D Applies to floating-point types and indicates the number of digits following the decimal point. Square brackets (`[' and `]') indicate parts of type specifiers that are optional. Note that if you specify ZEROFILL for a column, MySQL will automatically add the UNSIGNED attribute to the column. TINYINT[(M)] [UNSIGNED] [ZEROFILL] A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. NT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. NTEGER[(M)] [UNSIGNED] [ZEROFILL] This is a synonym for INT. BIGINT[(M)] [UNSIGNED] [ZEROFILL] A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Note that all arithmetic is done using signed BIGINT or DOUBLE values, so you shouldn't use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! Note that -, + and * will use BIGINT arithmetic when both arguments are INTEGER values! This means that if you multiply two big integers (or results from functions that return integers) you may get unexpected results if the result is larger than 9223372036854775807. FLOAT(precision) [ZEROFILL] A floating-point number. Cannot be unsigned. precision can be 4 or 8. FLOAT(4) is a single-precision number and FLOAT(8) is a double-precision number. These types are like the FLOAT and DOUBLE types described immediately below. FLOAT(4) and FLOAT(8) have the same ranges as the corresponding FLOAT and DOUBLE types, but their display size and number of decimals is undefined. In MySQL 3.23, this is a true floating point value. In earlier MySQL versions, FLOAT(precision) always has 2 decimals. This syntax is provided for ODBC compatibility. FLOAT[(M,D)] [ZEROFILL] A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. DOUBLE[(M,D)] [ZEROFILL] A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308. DOUBLE PRECISION[(M,D)] [ZEROFILL] REAL[(M,D)] [ZEROFILL] These are synonyms for DOUBLE. DECIMAL(M,D) [ZEROFILL] An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value, the decimal point, and, for negative numbers, the `-' sign. If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. In MySQL 3.23 the M argument no longer includes the sign or the decimal point. (This is according to ANSI SQL.) NUMERIC(M,D) [ZEROFILL] This is a synonym for DECIMAL. DATE A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers. DATETIME A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers. TIMESTAMP[(M)] A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format, depending on whether M is 14 (or missing), 12, 8 or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. See section 7.2.6 Date and time types TIME A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers. YEAR A year. The allowable values are 1901 to 2155, and 0000. MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. (The YEAR type is new in MySQL 3.22.) CHAR(M) [BINARY] A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. VARCHAR(M) [BINARY] A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.6.1 Silent column specification changes. TINYBLOB TINYTEXT A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See section 7.6.1 Silent column specification changes. BLOB TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. See section 7.6.1 Silent column specification changes. MEDIUMBLOB MEDIUMTEXT A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. See section 7.6.1 Silent column specification changes. LONGBLOB LONGTEXT A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. See section 7.6.1 Silent column specification changes. ENUM('value1','value2',...) An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., or NULL. An ENUM can have a maximum of 65535 distinct values. SET('value1','value2',...) A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members. 7.2.1 Column type storage requirements The storage requirements for each of the column types supported by MySQL are listed below by category. 7.2.2 Numeric types +--------------------+---------------------------+ | Column type | Storage required | +--------------------+---------------------------+ | TINYINT | 1 byte | +--------------------+---------------------------+ | SMALLINT | 2 bytes | +--------------------+---------------------------+ | MEDIUMINT | 3 bytes | +--------------------+---------------------------+ | INT | 4 bytes | +--------------------+---------------------------+ | INTEGER | 4 bytes | +--------------------+---------------------------+ | BIGINT | 8 bytes | +--------------------+---------------------------+ | FLOAT(4) | 4 bytes | +--------------------+---------------------------+ | FLOAT(8) | 8 bytes | +--------------------+---------------------------+ | FLOAT | 4 bytes | +--------------------+---------------------------+ | DOUBLE | 8 bytes | +--------------------+---------------------------+ | DOUBLE PRECISION | 8 bytes | +--------------------+---------------------------+ | REAL | 8 bytes | +--------------------+---------------------------+ | DECIMAL(M,D) | M bytes (D+2, if M < D) | +--------------------+---------------------------+ | NUMERIC(M,D) | M bytes (D+2, if M < D) | +--------------------+---------------------------+ 7.2.3 Date and time types +--------------------+---------------------------+ | Column type | Storage required | +--------------------+---------------------------+ | DATETIME | 8 bytes | +--------------------+---------------------------+ | DATE | 3 bytes | +--------------------+---------------------------+ | TIMESTAMP | 4 bytes | +--------------------+---------------------------+ | TIME | 3 bytes | +--------------------+---------------------------+ | YEAR | 1 byte | +--------------------+---------------------------+ +--------------------------+--------------------------------------+ | Column type | Storage required | +--------------------------+--------------------------------------+ | CHAR(M) | M bytes, 1 <= M <= 255 | +--------------------------+--------------------------------------+ | VARCHAR(M) | L+1 bytes, where L <= M and | | | 1 <= M <= 255 | +--------------------------+--------------------------------------+ | TINYBLOB, TINYTEXT | L+1 bytes, where L < 2^8 | +--------------------------+--------------------------------------+ | BLOB, TEXT | L+2 bytes, where L < 2^16 | +--------------------------+--------------------------------------+ | MEDIUMBLOB, MEDIUMTEXT | L+3 bytes, where L < 2^24 | +--------------------------+--------------------------------------+ | LONGBLOB, LONGTEXT | L+4 bytes, where L < 2^32 | +--------------------------+--------------------------------------+ | ENUM('value1', | 1 or 2 bytes, depending | | 'value2',...) | on the number of enumeration values | | | (65535 values maximum) | +--------------------------+--------------------------------------+ | SET('value1', | 1, 2, 3, 4 or 8 bytes, depending | | 'value2',...) | on the number of set members | | | (64 members maximum) | +--------------------------+--------------------------------------+ VARCHAR and the BLOB and TEXT types are variable-length types, for which the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes. The BLOB and TEXT types require 1, 2, 3 or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may under certain conditions change a column from a variable-length type to a fixed-length type, or vice-versa. See section 7.6.1 Silent column specification changes. The size of an ENUM object is determined by the number of different enumeration values. 1 byte is used for enumerations with up to 255 possible values. 2 bytes are used for enumerations with up to 65535 values. The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4 or 8 bytes. A SET can have a maximum of 64 members. 7.2.5 Numeric types All integer types can have an optional attribute UNSIGNED. Unsigned values can be used when you want to allow only positive numbers in a column and you need a little bigger numeric range for the column. All numeric types can have an optional attribute ZEROFILL. Values for ZEROFILL columns are left-padded with zeroes up to the maximum display length when they are displayed. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, the value is clipped to the lower endpoint of the range, and -2147483648 is stored instead. Similarly, if you try to insert 9999999999, 2147483647 is stored instead. If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column become 0 and 4294967296. Conversions that occur due to clipping are reported as ``warnings'' for ALTER TABLE, LOAD DATA INFILE, UPDATE and multi-row INSERT statements. The maximum display size (M) and number of decimals (D) are used for formatting and calculation of maximum column width. MySQL will store any value that fits a column's storage type even if the value exceeds the display size. For example, an INT(4) column has a display size of 4. Suppose you insert a value which has more than 4 digits into the column, such as 12345. The display size is exceeded, but the allowable range of the INT type is not, so MySQL stores the actual value, 12345. When retrieving the value from the column, MySQL returns the actual value stored in the column. The DECIMAL type is considered a numeric type (as is its synonym, NUMERIC), but such values are stored as strings. One character is used for each digit of the value, the decimal point (if D > 0) and the `-' sign (for negative numbers). If D is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. For example, a type specification such as DECIMAL(4,2) indicates a maximum length of four characters with two digits after the decimal point. Due to the way the DECIMAL type is stored, this specification results in an allowable range of -.99 to 9.99, much less than the range of a DOUBLE. To avoid some rounding problems, MySQL always rounds everything that it stores in any floating-point column to the number of decimals indicated by the column specification. Suppose you have a column type of FLOAT(8,2). The number of decimals is 2, so a value such as 2.333 is rounded to two decimals and stored as 2.33. 7.2.6 Date and time types The date and time types are DATETIME, DATE, TIMESTAMP, TIME and YEAR. Each of these has a range of legal values, as well as a ``zero'' value that is used when you specify an illegal value. Here are some general considerations to keep in mind when working with date and time types: MySQL retrieves values for a given date or time type in a standard format, but it attempts to interpret a variety of formats for values that you supply (e.g., when you specify a value to be assigned to or compared to a date or time type). Nevertheless, only the formats described in the following sections are supported. It is expected that you will supply legal values, and unpredictable results may occur if you use values in other formats. Although MySQL tries to interpret values in several formats, it always expects the year part of date values to be leftmost. Dates must be given in year-month-day order (e.g., '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (e.g., '09-04-98', '04-09-98'). MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa. When MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type, it converts the value to the ``zero'' value for that type. (The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.) The table below shows the format of the ``zero'' value for each type: +---------------+------------------------------------+ | Column type | ``Zero'' value | +---------------+------------------------------------+ | DATETIME | '0000-00-00 00:00:00' | +---------------+------------------------------------+ | DATE | '0000-00-00' | +---------------+------------------------------------+ | TIMESTAMP | 00000000000000 | | | (length depends on display size) | +---------------+------------------------------------+ | TIME | '00:00:00' | +---------------+------------------------------------+ | YEAR | 0000 | +---------------+------------------------------------+ The ``zero'' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write. ``Zero'' date or time values used through MyODBC are converted automatically to NULL in MyODBC 2.50.12 and above, because ODBC can't handle such values. 7.2.6.1 Y2K issues and date types MySQL itself is Y2K-safe (see section 1.6 Year 2000 compliance), but input values presented to MySQL may not be. Any input containing 2-digit year values is ambiguous, since the century is unknown. Such values must be interpreted into 4-digit form since MySQL stores years internally using four digits. For DATETIME, DATE, TIMESTAMP and YEAR types, MySQL interprets dates with ambiguous year values using the following rules: Year values in the range 00-69 are converted to 2000-2069. Year values in the range 70-99 are converted to 1970-1999. Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing 4-digit year values. 7.2.6.2 The DATETIME, DATE and TIMESTAMP types The DATETIME, DATE and TIMESTAMP types are related. This section describes their characteristics, how they are similar and how they differ. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work, there is no guarantee that they will.) The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'. The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL, or to NOW(). You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: Let MySQL set the column when the row is created. This will initialize it to the current date and time. When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. TIMESTAMP values may range from the beginning of 1970 to sometime in the year 2037, with a resolution of one second. Values are displayed as numbers. The format in which MySQL retrieves and displays TIMESTAMP values depends on the display size, as illustrated by the table below. The `full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes: +-----------------+------------------+ | Column type | Display format | +-----------------+------------------+ | TIMESTAMP(14) | YYYYMMDDHHMMSS | +-----------------+------------------+ | TIMESTAMP(12) | YYMMDDHHMMSS | +-----------------+------------------+ | TIMESTAMP(10) | YYMMDDHHMM | +-----------------+------------------+ | TIMESTAMP(8) | YYYYMMDD | +-----------------+------------------+ | TIMESTAMP(6) | YYMMDD | +-----------------+------------------+ | TIMESTAMP(4) | YYMM | +-----------------+------------------+ | TIMESTAMP(2) | YY | +-----------------+------------------+ All TIMESTAMP columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number. You can specify DATETIME, DATE and TIMESTAMP values using any of a common set of formats: As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any non-numeric character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' and '98@12@31 11^30^45' are equivalent. As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31' and '98@12@31' are equivalent. As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122459015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'. As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'. As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'. As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and