MYSQL 권한

이번에 MYSQL 권한에 대해 공부를 조금 하게 되서 새롭게 알게된 사실을 공유하고자 몇자 적습니다.
기존에 올라온 자료가 있는지에 대해 검색은 해보았지만 검색능력이 좋치못해 중복일 가능성은 있습니다만..
테클은 사양하고, 조언은 새겨듣겠습니다.

테스트한 MYSQL 버젼은 4.0.25이고,
가정은 아래와 같습니다.

테스트 디비 : TEMPS, TEMPSS
테스트 테이블 : TEMP1, TEMP // 두 테이블 모두 컬럼은 NO1, NO2, NO3 으로 세개의 컬럼을 갖습니다.
테스트 계정 : NMCCM1, NMCCM2

MYSQL 권한은 COLUMNS -> TABLES -> DB -> USER 형태 입니다.
이말이 의미하는것은 상위 권한에 권한이 생성되면 하위 권한은 무시된다는 말입니다.
즉, MYSQL.USER 테이블에 NMCCM1 계정에 대한 권한이 부여되면 NMCCM1 계정에 한해선 디비권한, 테이블권한, 컬럼권한을 줄수 없다는 말이 됩니다.
그러므로 DB 권한을 주기 위해선 보통 많이 사용하는 MYSQL.DB 및 MYSQL.USER 에 INSERT 하는 방식으로 하셔도 무관하나,
테 이블 및 컬럼에 관한 권한을 주기위해선 MYSQL.DB, MYSQL.USER 테이블에 INSERT 하는 방식이 아닌 GRANT 를 사용하시던가, MYSQL.TABLES_PRIV 또는 MYSQL.COLUMNS_PRIV 테이블을 사용하셔야 합니다.

말주변이 없는 관계로 예를 하나씩 들겠습니다.

A. 컬럼 권한

1. nmccm1 계정이 temp1 테이블의 no1 컬럼에만 localhost 에서 접근해야 한다면 ?

GRANT select(no1) ON temps.temp1 TO nmccm1@localhost IDENTIFIED BY 'nmccm1pass' WITH GRANT OPTION;

2. nmccm1 계정이 temp1 테이블의 no1 컬럼에만 211.211.211.211 서버에서 접근해야 한다면 ?

GRANT select(no1) ON temps.temp1 TO nmccm1@211.211.211.211 IDENTIFIED BY 'nmccm1pass' WITH GRANT OPTION;

※ 위의 두개의 명령중 한개라도 실행하게 되면 mysql.columns_priv 과 mysql.user 테이블에 nmccm1 에 관한 레코드가 하나 추가되게 됩니다.
※ 이때 nmccm1 계정의 패스워드는 nmccm1pass 입니다.
※ 가장 하위권한을 주었기때문에 해당컬럼이 속해있는 테이블과 디비만 출력할수 볼수 있습니다. (show tables 또는 show databases 시에)
※ 권한삭제는 grant 명령어로 생성된 레코드들을 삭제해주면 됩니다.

B. 테이블 권한

1. localhost로 nmccm1 계정이 temp1 테이블의 select, insert 만 가능하게 하고, nmccm2 계정이 temp2 테이블의 update, delete 만 가능하게 하기 위해선?

GRANT select, insert ON temps.temp1 TO nmccm1@localhost IDENTIFIED BY 'nmccm1pass' WITH GRANT OPTION;
GRANT update, delete ON temps.temp2 TO nmccm2@localhost IDENTIFIED BY 'nmccm2pass' WITH GRANT OPTION;

2. 211.211.211.211 서버에서 nmccm1 계정이 temp1 테이블의 select, insert 만 가능하게 하고, nmccm2 계정이 temp2 테이블의 update, delete 만 가능하게 하기 위해선?

GRANT select, insert ON temps.temp1 TO nmccm1@211.211.211.211 IDENTIFIED BY 'nmccm1pass' WITH GRANT OPTION;
GRANT update, delete ON temps.temp2 TO nmccm2@211.211.211.211 IDENTIFIED BY 'nmccm2pass' WITH GRANT OPTION;

※ 1의 첫번째줄을 실행할경우 mysql.tables_priv 와 mysql.user 테이블에 nmccm1 에 관한 레코드가 하나 추가되게 됩니다.
※ 1의 두번째줄을 실행할경우 mysql.tables_priv 와 mysql.user 테이블에 nmccm2 에 관한 레코드가 하나 추가되게 됩니다.
※ 2의 첫번째줄을 실행할경우 mysql.tables_priv 와 mysql.user 테이블에 nmccm1 에 관한 레코드가 하나 추가되게 됩니다.
※ 2의 두번째줄을 실행할경우 mysql.tables_priv 와 mysql.user 테이블에 nmccm2 에 관한 레코드가 하나 추가되게 됩니다.
※ 테이블 권한을 주었기 때문에 이상태로 컬럼권한을 준다해도 컬럼권한은 무시됩니다.
※ 권한삭제는 grant 명령어로 생성된 레코드들을 삭제해주면 됩니다.

C. 디비 및 유저 권한

1. localhost로 nmccm1 계정이 temps 디비에 select, insert 만 가능하고 하고, nmccm2 계정이 tempss 디비에 update, delete 만 가능하게 하고 싶다면?

insert into db (host, db, user, select_priv, insert_priv) values ('localhost','temps','nmccm1','Y','Y');
insert into user values ('localhost','nmccm1',password('nmccm1pass'))
insert into db (host, db, user, update_priv, delete_priv) values ('localhost','tempss','nmccm2','Y','Y');
insert into user values ('localhost','nmccm2',password('nmccm2pass'))

1. 211.211.211.211서버에서 nmccm1 계정이 temps 디비에 select, insert 만 가능하고 하고, nmccm2 계정이 tempss 디비에 update, delete 만 가능하게 하고 싶다면?

insert into db (host, db, user, select_priv, insert_priv) values ('211.211.211.211','temps','nmccm1','Y','Y');
insert into user values ('211.211.211.211','nmccm1',password('nmccm1pass'))
insert into db (host, db, user, update_priv, delete_priv) values ('211.211.211.211','tempss','nmccm2','Y','Y');
insert into user values ('211.211.211.211','nmccm2',password('nmccm2pass'))

※ 디비 및 유저 권한을 주었기 때문에 이상태로 컬럼 또는 테이블 권한을 준다해도 무시됩니다.
※ 권한삭제는 insert 한 레코드들을 삭제해주면 됩니다.

정리가 될지 모르지만 정리해보자면

컬럼에 대한 권한을 주기 위해선 디비 및 테이블에 권한이 없는 상태로 컬럼권한을 주셔야 적용이 되고,
컬럼에 권한을 주게 되면 해당 테이블 및 해당 디비에 접근권한이 자동으로 생기게 되고,

테이블에 대한 권한을 주기 위해선 디비 권한이 없는 상태로 테이블권한을 주셔야 적용이 되고,
테이블이 권한을 주게 되면 해당 디비에 접근권한이 자동으로 생기게 되고,

계정이 MYSQL 서버에 접속하기 위해선 유저테이블에 해당계정의 레코드를 생성해주셔야 하고,
계정이 디비에 접근하기 위해선 컬럼권한, 테이블권한, 디비권한중 하나의 권한을 주셔야 디비에 접근가능합니다.