SQL - 사용자, 권한, 롤 관리

사용자 관리

사용자: 데이터베이스에 접속하여 데이터를 관리하는 계정
데이터를 활용한 서비스 규모가 크거나 작은 규모의 여러 서비스를 통합한 방식 등 실무에서 사용하는 여러 종류의 서비스는
한 사용자가 관리하기에는 데이터 분량이 너무 방대하거나 구조가 복잡해지는 경우가 많다.
따라서 업무 분할과 효율, 보안을 고려하여 업무에 따라 여러 사용자들을 나눈다.

데이터베이스 스키마

데이터베이스에서 데이터 간 관계, 데이터 구조, 제약 조건 등 데이터를 저장 및 관리하기 위해
정의한 데이터베이스 구조의 범위를 스키마를 통해 그룹 단위로 분류한다.
오라클 데이터베이스에서는 스키마와 사용자를 구별하지 않고 사용하기도 한다.
사용자는 데이터를 사용 및 관리하기 위해 오라클 데이터베이스에 접속하는 개체를 뜻하고,
스키마는 오라클 데이터베이스에 접속한 사용자와 연결된 겍체를 의미한다.
SCOTT 계정에서는 SCOTT는 사용자이고 SCOTT이 생성한 테이블·뷰·제약 조건·인덱스·시퀀스 등
데이터베이스에서 SCOTT 계정으로 만든 모든 객체는 SCOTT의 스키마가 된다.

사용자 생성·조회·삭제

오라클 사용자를 생성할 때는 CREATE USER문을 사용한다.
하지만 다음 명령어는 SCOTT 계정으로 접속한 상태에서는 권한이 없어 실행되지 않는다.
그래서 SYSTEM 계정으로 접속하여 권한을 부여한다.

-- cmd
-- SYSTEM 사용자로 접속
SQLPLUS SYSTEM/비밀번호
-- USER 사용자 생성
CREATE USER ORCLSTUDY IDENTIFIED BY ORACLE;
-- ORCLSTUDY 사용자에게 권한 부여
GRANT CREATE SESSION TO ORCLSTUDY;

-- 사용자 정보 조회
SELECT * FROM ALL_USERS WHERE USERNAME = 'ORCLSTUDY';

-- 사용자 정보(패스워드) 변경
ALTER USER ORCLSTUDY IDENTIFIED BY ORCL;

-- 사용자 삭제
DROP USER ORCLSTUDY;

-- 사용자와 객체 모두 삭제
-- 사용자 스키마에 객체가 있을 경우
DROP USER ORCLSTUDY CASCADE;

권한 관리

데이터베이스는 접속 사용자에 따라 접근할 수 있는 데이터 영역과 권한을 지정해 줄 수 있다.
오라클에서는 권한을 시스템 권한과 객체 권한으로 분류하고 있다.

시스템 권한

시스템 권한은 사용자 생성과 정보 수정 및 삭제, 데이터베이스 접근,
오라클 데이터베이스의 여러 자원과 객체 생성 및 관리 등의 권한을 포함한다.
이러한 내용은 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한이다.

-- 시스템 권한 부여

-- CREATE SESSION 권한 부여(SYSTEM 계정)
GRANT CREATE SESSION TO ORCLSTUDY;

CREATE USER ORCLSTUDY IDENTIFIED BY ORACLE;

-- 사용자 권한 부여
GRANT RESOURCE, CREATE SESSION, CREATE TABLE TO ORCLSTUDY;

-- ORCLSTUDY 사용자로 테이블 생성
CREATE TABLE TEMP1
(
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);
INSERT INTO TEMP1 VALUES ('USER', 'GRANT_TEST');
SELECT * FROM TEMP1;

-- 시스템 권한 취소(SYSTEM 계정)
REVOKE RESOURCE, CREATE TABLE FROM ORCLSTUDY;

객체 권한

객체 권한은 특정 사용자가 생성한 테이블·인덱스·뷰·시퀀스 등과 관련된 권한이다.
가령 SCOTT 소유 테이블에 ORCLSTUDY 사용자가 SELECT나 INSERT 등의 작업이 가능하도록 허용할 수 있다.

-- 객체 권한 부여
CREATE TABLE TEMP
(
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

GRANT SELECT, INSERT ON TEMP TO ORCLSTUDY;

-- ORCLSTUDY로 사용 권한을 부여받은 TEMP 테이블 사용
INSERT INTO SCOTT.TEMP VALUES ('TEXT', 'FROM ORCLSTUDY');
SELECT * FROM SCOTT.TEMP;

-- 객체 권한 취소
REVOKE SELECT, INSERT ON TEMP FROM ORCLSTUDY;

SELECT * FROM SCOTT.TEMP;

롤 관리

롤은 여러 종류의 권한을 묶어 놓은 그룹을 뜻한다.
룰은 다음처럼 나뉜다.

• 사전 정의된 롤(predefined roles)
• 사용자 정의 롤(user roles)

사전 정의된 롤

• CONNECT 롤: 사용자가 데이터베이스에 접속하는 데 필요한 CREATE SESSION 권한을 가진다.
• RESOURCE 롤: 사용자가 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 기본 시스템 권한을 묶어 놓은 롤이다.

보통 새로운 사용자를 생성하면 CONNECT 롤과 RESOURCE 롤을 부여하는 경우가 많다.
CONNECT 롤엥서 뷰를 생성하는 CREATE VIEW 권한과 동의어를 생성하는 CREATE SYNONYM 권한이 제외되었기 때문에 뷰와 동의어 생성 권한을 사용자에게 무여하려면 따로 부여해주어야 한다.

• DBA 롤: 데이터베이스를 관리하는 시스템 권한을 대부분 가지고 있다.

사용자 정의 롤

사용자 정의 롤은 필요에 의해 직접 권한을 포함시킨 롤이다.
다음 절차에 따라 롤을 생성해서 사용할 수 있다.

1. CREATE ROLE문으로 롤 생성
2. GRANT 명령어로 생성한 롤에 권한을 포함시킴
3. GRNAT 명령어로 권한이 포함된 롤을 특정 사용자에게 부여
4. REVOKE 명렁어로 롤을 취소시킴

-- SYSTEM 계정으로 ROLESTUDY 롤 생성 및 권한 부여
CONN SYSTEM/비밀번호
CREATE ROLE ROLESTUDY;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SYNONYM TO ROLESTUDY;

-- OBCLSTUDY 사용자에게 롤 부여
GRANT ROLESTUDY TO ORCLSTUDY;

-- 부여된 롤 취소
REVOKE ROLESTUDY FROM DRCLSTUDY;

-- 롤 삭제
DROP ROLE ROLESTUDY;