[SQL] ORACLE, MySQL, MS SQL Server에서 user(schema) 생성, 권한부여하기

 

매번 user를 생성할 때마다 헷갈리고, 뭐 하나 빼먹으면 검색하게되어서 너무 불편하고..

그냥 한 번에 정리해보려고 한다.

 

Database에서 user(schema)를 생성해보자.

 

 

---

 

 

Oracle

 

• USER 생성하기

  x
  -- CREATE USER
  CREATE USER myadmin IDENTIFIED BY myadminpwd;
  

  • CREATE USER 구문으로 myadmin이라는 USER를 생성한다.
  • IDENTIFIED BY 구문으로 myadmin이라는 USER의 PASSWORD를 지정한다.

 

• USER 권한 지정

  x
  -- GRANT ROLE
  GRANT CONNECT, RESOURCE, DBA TO myadmin;
  ​
  -- GRANT ALL PRIVILEGE
  GRANT ANY PRIVILEGE TO myadmin;
  ​
  -- GRANT TABLESPACE
  GRANT UNLIMITED TABLESPACE TO myadmin;
  ALTER USER myadmin QUOTA 50M ON MYTABLESPACE1;
  ALTER USER myadmin QUOTA UNLIMITED ON MYTABLESPACE2;
  

  • GRANT A TO B 구문으로 B라는 USER에게 A 권한을 부여한다.

  • CONNECT, RESOURCE, DBA 는 ROLE로, USER에게 부여할 수 있는 권한 들을 역할에 따라 묶어놓은 것이다. 해당 위치에 ROLE이 아니라 그냥 원하는 권한들만 입력해서 부여해도 되고, 원하는 권한들을 묶어 ROLE을 새로 생성하여 부여해도 된다. (권한부여 참고)

    • CONNECT

      : CREATE SESSION

      : 해당 USER가 DATABASE에 접근할 수 있는 권한이다.

      : 이 권한을 빼먹고 USER를 생성하면 해당 스키마로는 DB에 접근이 불가능하다.

    • RESOURCE

      : CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

      : 인덱스, 프로시저, 시퀀스, 테이블, 트리거 등등.. 자원들을 생성할 수 있다.

      : ORACLE 내에서 기본적인 CRUD를 사용하려면 부여해주면 된다.

    • DBA

      : 이름에서부터 뿜어나오는 멋짐이 있다. 모든 시스템 권한이 부여되어 있는 ROLE이다.

      : 로컬에서 실습할 때는 DBA 권한을 가지고 진행하는 것이 편하고 좋지만, 실무에서 해당 역할을 함부로 부여해서는 안된다.

  • 권한명 대신 ANY PRIVILEGE 라는 키워드를 넣어주면 해당 USER에게 모든 권한을 부여할 수 있다. 이 또한 주의해서 사용해야 한다.

  • GRANT UNLIMITED TABLESPACE 를 통해 모든 테이블스페이스로의 접근 권한을 부여할 수 있다. 오라클 버전에 따라 RESOURCE 권한을 주면 주어지기도 한다.

  • 또는 QUOTA를 제한할 수 있다. CREATE문 뒤에 붙여서 사용해도 되고, 생성 후 ALTER문으로 변경해줄 수도 있다.

 

 

MySQL(MariaDB)

 

• USER 생성하기

  x
  -- CREATE USER
  CREATE USER 'myadmin'@'localhost' IDENTIFIED BY 'myadminpwd';
  

  • CREATE USER 구문으로 myadmin이라는 USER를 생성한다.
  • @ 뒤의 localhost는 호스트를 입력해주면 된다. 지금 우리는 로컬에서 생성하고, 로컬에서 DB에 접근하여 사용할 것이므로 localhost를 입력한다. 만약 다른 서버에서 접근해야한다면, 접근 서버의 IP/host를 입력하거나 % 와일드카드를 사용해도 된다.
  • IDENTIFIED BY 구문으로 myadmin이라는 USER의 PASSWORD를 지정한다.

 

• USER 권한 지정

  x
  -- GRANT PRIVILEGES
  GRANT SELECT, INSERT ON mydatabase.mytable TO 'myadmin'@'localhost';
  GRANT ALL PRIVILEGES ON * . * TO 'myadmin'@'localhost' WITH GRANT OPTION;
  

  • 오라클과 마찬가지로 ALL PRIVILEGES 키워드를 통해 모든 권한을 부여할 수 있다. SELECT, INSERT 등 특정 권한을 입력해주어도 된다.
  • 데이타베이스명.테이블명 으로 권한을 지정할 DB 및 테이블을 지정할 수 있는데, 아스타(*) 와일드카드를 통해 전체를 지정할 수 있다. 물론 데이터베이스명.* 로 특정 DB의 모든 테이블 권한주기도 가능하다.
  • WITH GRANT OPTION 을 뒤에 적어주면, 해당 유저는 본인이 가지고 있는 권한을 다른 유저에게 부여할 수 있다.

 

 

MS SQL Server

 

• USER 생성하기

  x
  -- CRETE LOGIN
  CREATE LOGIN myadmin WITH PASSWORD = 'myadminpwd';
  ​
  -- CREATE USER
  CREATE USER myadmin FOR LOGIN myadminpwd;
  

  • CREATE LOGIN A WITH PASSWORD=B 또는 CREATE USER A FOR LOGIN B 로 USER(LOGIN) 을 생성할 수 있다.
  • LOGIN: SQL Server에 대한 액세스 권한
  • USER: LOGIN 권한에 비해 더 많은 권한을 가짐. 데이터베이스 액세스 권한

 

• USER 권한 지정

  x
  -- GRANT PRIVILEGES
  GRANT EXECUTE ON mytest TO myadmin AS myRole;
  GRANT ALL PRIVILEGES TO myadmin WITH GRANT OPTION;
  

  • 마찬가지로 ALL PRIVILEGES 키워드를 통해 모든 권한을 부여할 수 있다. SELECT, INSERT 등 특정 권한을 입력해주어도 된다.
  • 권한 부여 옵션 및 권한 부여에 대한 자세한 내용은 microsoft docs 참고.