[DB] 유저 생성과 권한, 롤(Grant, Revoke)

1. 계정 및 권한 관리

1) 개요

- DCL 문을 사용해서 데이터에 대한 권한을 부여하거나 취소할 수 있다.

- 사용자: 데이터베이스에 로그인할 수 있는 계정
- 권한: 시스템 권한과 오브젝트 권한으로 분류된다.

- 롤: 권한과 롤의 모음이며 사용자에게 롤을 부여하면 롤에 부여된 권한과 롤이 함께 부여된다.

 

2) 사용자 생성

- CREATE USER: 사용자 생성 구문

 

CREATE USER user IDENTIFIED BY password

[ DEFAULT TABLESPACE tablespace

| TEMPORARY TABLESPACE {tablespace | tablespace_group_name}

| {QUOTA {size_clause | UNLIMITED} ON tablespace}...

| PROFILE profile

| PASSWORD EXPIRE

| ACCOUNT {LOCK | UNLOCK} ];

 

- IDENTIFIED BY: 사용자의 패스워드를 지정

- DEFAULT TABLESPACE: 사용자의 기본 테이블스페이스를 지정

- TEMPORARY TABLESPACE: 사용자의 기본 임시 테이블스페이스를 지정

- QUOTA: 사용자가 할당할 수 있는 테이블스페이스의 한도를 설정

- PROFILE: 사용자의 프로필을 지정

- PASSWORD EXPIRE: 사용자가 패스워드를 변경하도록 만료시킴

- ACCOUNT: 사용자의 계정을 잠그거나 잠금을 해제

 

3) 사용자 변경

- ALTER USER: 사용자 변경 구문

 

ALTER USER user IDENTIFIED BY password [REPLACE old_password];

 

- IDENTIFIED BY 절 외에도 CREATE 문에 사용되는 모든 절 사용 가능

 

4) 사용자 삭제

- DROP USER: 사용자 삭제 구문

 

DROP USER user [CASCADE];

 

- 오브젝트가 존재하면 삭제가 불가능하지만 CASCADE를 기술하면 스키마에 속한 모든 오브젝트와 함께 삭제 가능

5) 권한

- GRANT문은 사용자나 롤에 권한을 부여한다.

- 시스템 권한(System Privilege): 특정 데이터베이스 작업을 수행할 수 있는 권한

- 오브젝트 권한(Object Privilege): 특정 오브젝트를 접근할 수 있는 권한

 

6) GRANT 시스템 권한 부여

- 시스템 권한 부여 구문

 

GRANT {system_privilege | role | ALL PRIVILEGES}

[,{system_privilege | role | ALL PRIVILEGES}]...

      TO {user | role | PUBLIC} [, {user | role | PUBLIC} ]...

  [WITH {ADMIN | DELEGATE} OPTION];

 

- ALL PRIVILEGES는 SELECT ANY DICTIONARY, ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK 권한을 제외한 모든 시스템 권한을 부여한다.

- PUBLIC은 모든 사용자에게 권한을 부여한다.

 

- WITH ADMIN OPTION: 부여받은 권한이나 롤을 다른 사용자에게 부여하거나 취소 가능

- WITH DELEGATE OPTION: 부여받은 롤을 자신의 프로그램 유닛에 부여하거나 취소 가능

 

- CREATE SESSION: 데이터베이스에 접속할 수 있는 권한

- CREATE TABLE: 테이블을 생성할 수 있는 권한

- CREATE ANY TABLE: 모든 스키마에 테이블을 생성할 수 있는 권한

- UNLIMITED TABLESPACE: 모든 테이블스페이스를 제한 없이 사용할 수 있는 권한

*ANY가 포함된 시스템 권한은 모든 스키마에 대한 권한이 부여된다.

 

7) GRANT 오브젝트 권한 부여

- 오브젝트 권한 부여 구문

 

GRANT {obejct_privilege | ALL [PRIVILEGES]} [(COLUMN [, COLUMN]...)]

[,{object_privilege | ALL [PRIVILEGES]} [(COLUMN [, COLUMN]...)]...

      ON {[schema.]object | USER user [, user]... | DIRECTORY directory_name}

      TO {user | role | PUBLIC} [, {user | role | PUBLIC}]...

[WITH GRANT OPTION];

 

- 오브젝트 별로 사용할 수 있는 권한

TABLE: ALTER, DELETE, INDEX, INSERT, READ, REFERENCES, SELECT, UPDATE

VIEW: DELETE, INSERT, READ, REFERENCES, SELECT, UPDATE

SEQUENCE: ALTER, SELECT

PROCEDURE: DEBUG, EXECUTE

 

8) REVOKE 권한 취소

- 부여된 권한을 취소한다.

 

- 시스템 권한 REVOKE 구문

 

REVOKE {system_privilege | role | ALL PRIVILEGES}

{,  {system_privilege | role | ALL PRIVILEGES}]...

    FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]...;

 

- 오브젝트 권한 REVOKE 구문

 

REVOKE {system_privilege | role | ALL PRIVILEGES}

	{, {system_privilege | role | ALL PRIVILEGES}]...

    ON {[schema.]object | USER user [, user]... | DIRECTORY directory_name}

  FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]...

[CASCADE CONSTRAINTS | FORCE];

 

*CASCADE CONSTRAINTS: FK 제약조건과 관련하여 REFERENCES 권한을 취소

*FORCE: 사용자 정의 타입과 관련하여 EXECUTE 권한을 취소

 

9) 롤

- 권한과 롤의 모음

 

- CREATE ROLE: 롤을 생성

CREATE ROLE role [NOT IDENTIFIED | IDENTIFIED BY password];

- ALTER ROLE: 롤에 패스워드를 설정하거나 설정된 패스워드를 해제

ALTER ROLE role [NOT IDENTIFIED | IDENTIFIED BY password];

- DROP ROLE: 롤을 삭제

DROP ROLE role;

- SET ROLE: 현재 세션에서 롤을 활성화하거나 비활성화(SCS 문) 

SET ROLE

        { role [IDENTIFIED BY password] [, role [IDENTIFIED BY password]]...

        { ALL [ECEPT role [, role]...]

        | NONE } ;

- DEFAULT ROLE: 사용자의 기본 롤을 지정하거나 해제

ALTER USER user DEFAULT ROLE 

{role [, role]... | ALL [EXCEPT role [, role]...] | NONE};

10) MySQL에서 제공하는 권한

 

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

           *https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html