MySQL 권한 종류 및 부여, 확인, 취소 방법

MySQL의 권한을 살펴보니, Oracle과 비슷합니다. 두 데이터베이스 모두 Role이라는 개념이 있어 Role에 권한을 부여할 수도 있습니다. 서로 다른 점은 약간의 사용법이 다를 뿐입니다. 이 글에서는 MySQL에서 사용되는 권한의 종류와 이를 부여하는 방법에 대해 알아보도록 하겠습니다.

 

MySQL 권한 종류 및 부여, 확인, 취소 방법

 

MySQL 권한 범주

먼저, 데이터베이스 권한(Privileges)에 대해 살펴 보면, 권한은 데이터베이스 사용자(user)가 MySQL 서버에서 어떤 작업을 할 수 있지를 허용하는 것입니다. 예를 들어, 어떤 사용자는 데이터를 읽기만 할 수 있고, 다른 사용자는 데이터를 수정하거나 삭제할 수도 있죠. MySQL에서 이러한 권한을 어떻게 설정하는지 보겠습니다.

 

MySQL에서 제공하는 권한은 다양하지만, 크게 세 가지 범주로 나눌 수 있는데요. 글로벌 권한, 데이터베이스 권한, 테이블 권한입니다.

 

1. 글로벌 권한(Global Privileges)

글로벌 권한은 모든 데이터베이스와 테이블에 영향을 미칩니다. 주로 MySQL 서버를 관리해야 할 관리자(admin)에게 부여되는 권한입니다. 데이터베이스 관리자가 서버를 유지 보수하는 데 필요한 권한입니다. 예를 들어, 서버를 중지하거나 특정 기능을 활성화/비활성화할 수 있습니다.

 

2. 데이터베이스 권한(Database Privileges)

이 권한은 특정 database에 적용됩니다. 데이터베이스 권한은 해당 데이터베이스로 작업해야 하는 사용자에게 부여됩니다. 예를 들어, api_db 데이터베이스가 있고 이 db는 api 개발자가 사용한다면 해당 user에게 권한을 부여할 수 있습니다.  

.

3. 테이블 권한(Table Privileges):

이 권한은 특정 테이블에 대해 주는 경우입니다. 특정 테이블만 작업이 필요한 사용자에게 부여되는 권한입니다. 

 

MySQL 권한의 종류

1. ALL PRIVILEGES

이는 모든 권한을 주는 옵션입니다. 이 권한을 가진 사용자는 MySQL 서버의 모든 데이터베이스, 또는 모든 작업을 수행할 수 있습니다. 

-- 모든 데이터베이스 ALL 권한 GRANT ALL PREIVILEGES ON *.* TO 'user명'@'호스트명'; -- 해당 데이터베이스의 ALL 권한 GRANT ALL PREIVILEGES ON 데이터베이스명.* TO 'user명'@'호스트명'; -- 해당 데이터베이스의 특정 테이블의 ALL 권한 GRANT ALL PREIVILEGES ON 데이터베이스명.테이블명 TO 'user명'@'호스트명';

 

2. GRANT OPTION

이 권한은 사용자에게 해당 SQL문을 실행할 수 있는 권한을 부여하는 옵션입니다. 옵션은 아래와 같습니다. 

• SELECT: 데이터를 조회(읽기)할 수 있는 권한입니다. 데이터베이스 안의 테이블에서 정보를 가져올 때 필요합니다.
• INSERT: 데이터를 테이블에 추가할 수 있는 권한입니다. 새로운 레코드를 입력할 때 사용됩니다.
• UPDATE: 테이블에 있는 기존 데이터를 수정할 수 있는 권한입니다.
• DELETE: 테이블의 데이터를 삭제할 수 있는 권한입니다.
• CREATE: 새로운 테이블이나 데이터베이스를 생성할 수 있는 권한입니다.
• DROP: 테이블이나 데이터베이스를 삭제할 수 있는 권한입니다.
• INDEX: 테이블에 인덱스를 생성하거나 삭제할 수 있는 권한입니다.
• ALTER: 테이블 구조를 변경할 수 있는 권한입니다. 예를 들어, 테이블에 새로운 컬럼을 추가하거나 기존 컬럼을 수정할 수 있습니다
• 이 외에도 EXECUTE(스토어드 프로시저 실행), LOCK TABLES(테이블 잠금) 등의 권한이 있습니다.

-- 특정 데이터베이스의 테이블에 SELECT(조회) 권한 부여 GRANT SELECT ON 데이터베이스명.테이블명 TO 'user명'@'호스트명';

 

MySQL에서 권한 부여 방법

권한을 부여하는 방법은 MySQL의 GRANT 명령어를 사용합니다. 이 명령어를 통해 특정 사용자에게 원하는 권한을 줄 수 있어요. 예시를 통해 더 자세히 알아보시죠.

 

1. 데이터베이스에 권한 부여하기

예를 들어, test_db라는 데이터베이스에 대해 test_user 사용자에게 데이터를 조회하고, 추가하고, 수정할 수 있는 권한을 부여하려면 다음과 같이 명령어를 사용할 수 있습니다.

GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'test_user'@'호스트';

 

여기서 test_db.*는 test_db 데이터베이스의 모든 테이블을 의미하고, 'test_user'@'localhost'는 test_user 사용자가 로컬 서버에서 접속할 때 권한을 부여한다는 의미입니다.

 

2. 테이블에 권한 부여하기

만약 test_user에게 test_db 안의 emp 테이블에서만 데이터를 조회할 수 있는 권한을 주고 싶다면 이렇게 할 수 있습니다:

GRANT SELECT ON test_db.emp TO 'test_user'@'호스트';

 

이 명령어는 test_user가 emp 테이블에서 데이터를 읽을 수 있게 해줍니다.

 

MySQL 권한 확인 및 취소

1. 권한 확인하기

부여한 권한을 확인하고 싶다면 SHOW GRANTS 명령어를 사용합니다. 이 명령어는 test_user 사용자가 가진 모든 권한을 보여줍니다.

SHOW GRANTS FOR 'test_user'@'호스트';   SHOW GRANTS FOR CURRENT_USER;

 

세부적으로 권한을 보고 싶다면 아래와 같이 확인할 수 있습니다. 'mysql' 데이터베이스에 존재하는 3개의 테이블을 통해 사용자 및 데이터베이스, 테이블의 접근 권한을 관리 할 수도 있습니다. 

1. 사용자 계정 관리(mysql.user)    select * from mysql.user where user='사용자명; 2. 사용자별 Database 권한 설정  ('mysql.db'이 'tables_priv' 테이블 보다 우선시 함)    select * from mysql.db where user='사용자명'; 3. 사용자별 테이블 제어 권한 관리 (mysql.tables_priv)    select * from mysql.tables_priv';

 

2. 권한 취소하기

test_user에게 부여된 권한을 취소하고 싶다면 REVOKE 명령어를 사용합니다. 예를 들어, test_db의 모든 테이블에 대한 INSERT 권한을 취소하려면 다음과 같이 할 수 있습니다.

REVOKE INSERT ON test_db.* FROM 'test_user'@'호스트';

 

마무리

데이터베이스 권한은 적절히 관리하는 것은 보안을 유지하고 시스템을 안정적으로 운영하는 데 꼭 필요한 활동입니다. 사용자에게 너무 많은 권한을 부여하면 의도치 않은 실수나 보안 위협에 노출될 수 있기 때문입니다.

 

따라서 필요한 권한만 부여하고, 필요 없을 때는 즉시 회수하는 것이 좋습니다. 이제 MySQL에서 권한을 어떻게 부여하고 관리하는지 감을 잡으셨을 거예요.