CHAPTER 15. 사용자, 권한, 롤 관리-DO IT! 오라클로 배우는 데이터베이스 입문

CHAPTER 15. 사용자, 권한, 롤 관리

15-1. 사용자 관리

사용자란?

오라클 데이터베이스에서는 데이터베이스에 접속하여 데이터를 관리하는 계정을 사용자(USER)로 표현

 

사용자 관리가 필요한 이유

업무 분할과 효율, 보안을 고려하여 업무에 따라 여러 사용자들을 나눔

오라클 데이터베이스는 테이블, 인덱스, 뷰 등 여러 객체가 사용자 별로 생성되므로 업무 별 사용자를 생성한 후 각 사용자 업무에 맞는 데이터 구조를 만들어 관리하는 방식을 사용할 수 있음.

대표 사용자를 통해 업무에 맞는 데이터 구조를 먼저 정의한 뒤에 사용할 수 있는 데이터 영역을 각 사용자에게 지정해 줄 수도 있음

데이터베이스 스키마란?

데이터베이스에서 데이터 간 관계, 데이터 구조, 제약 조건 등 데이터를 저장 및 관리하기 위해 정의한 데이터베이스 구조의 범위를 스키마(schema)를 통해 그룹 단위로 분류

오라클 데이터베이스에서는 스키마와 사용자를 구별하지않고 사용하기도 함

사용자는 데이터를 사용 및 관리하기 위해 오라클 데이터 베이스에 접속하는 개체를 뜻하고, 스키마는 오라클 데이터 베이스에 접속한 사용자와 연결된 개체를 의미함.

사용자 생성

오라클 사용자를 생성할 때는 create user문을 사용

기본적으로 사용자 이름과 패스워드만 지정해 주면 사용자를 생성할 수 있음.

create user 사용자이름(필수)
identified by 패스워드(필수)
default tablespace 테이블 스페이스 이름(선택)
temporary tablespace 테이블 스페이스(그룹) 이름(선택)    -- 임시 데이터 저장소
profile 프로파일 이름(선택)
password expire(선택)
account [lock/unlock](선택)

system접속 후 생성한 사용자에게 권한 부여하기

grant create session to 사용자;

사용자 정보 조회

사용자 또는 사용자 소유 객체 정보를 얻기 위해 데이터 사전을 사용할 수 있음

select * from all_users
where username='사용자이름';

select * from dba_users
where username='사용자이름';

select * from dba_objects
where username='사용자이름';

오라클 사용자의 변경과 삭제

오라클 사용자 변경

사용자 정보를 변경할 때에는 alter user문을 사용함

 

패스워드 변경하기

alter user 사용자명
identified by 패스워드

사용자 생성과 마찬가지로 사용자 정보 변경도 system사용자로 수행해야함.

 

오라클 사용자 삭제

drop user문을 사용하여 사용자 삭제

만약 삭제하려는 사용자가 다른 곳에서 접속 되어 있다면 삭제되지 않음.

drop user 사용자명

 

오라클 사용자와 객체 모두 삭제

사용자 스키마에 객체가 있을 경우 cascade옵션을 사용하여 사용자와 객체를 모두 삭제할 수 있음.

drop user 사용자명 cascade;

15-2. 권한 관리

데이터베이스는 접속 사용자에 따라 접근할 수 있는 데이터 영역과 권한을 지정해 줄 수 있는데, 오라클에서는 권한을 시스템 권한(system privilege)과 객체 권한(object privilege)으로 분류

권한: 보안을 위해 존재하며 , db내에서 작업 범위를 결정

• 시스템 권한: 데이터베이스의 작업 관리
  • create session, create table, create view…
• 객체 권한: 객체의 접근 권한을 관리, 각 객체의 소유권자가 보유
  • select on emp, insert on emp, update on emp…

role: 권한의 묶음( 권한 관리의 용이성 증대)

시스템 권한이란?

오라클 데이터베이스의 시스템 권한(system privilege)→데이터 베이스 관리 권한이 있는 사용자가 부여할 수있는 권한

• 사용자 생성과 정보 수정 및 삭제
• 데이터베이스 접근
• 오라클 데이터 베이스의 여러 자원과 객체 생성 및 관리 등의 권한

any키워드가 들어있는 권한은 소유자에 상관없이 사용 가능한 권한을 의미

시스템 권한 분류 시스템 권한 설명

USER(사용자)	CREATE USER	사용자 생성 권한
	ALTER USER	생성된 사용자의 정보 수정 권한
	DROP USER	생성된 사용자의 삭제 권한
SESSION(접속)	CREATE SESSION	데이터베이스 접속 권한
	ALTER SESSION	데이터베이스 접속 상태에서 환경 값 변경 권한
TABLE(테이블)	CREATE TABLE	자신의 테이블 생성 권한
	CREATE ANY TABLE	임의의 스키마 소유 테이블 생성 권한
	ALTER ANY TABLE	임의의 스키마 소유 테이블 수정 권한
	DROP ANY TABLE	임의의 스키마 소유 테이블 삭제 권한
	INSERT ANY TABLE	임의의 스키마 소유 테이블 데이터 삽입 권한
	UPDATE ANY TABLE	임의의 스키마 소유 테이블 데이터 수정 권한
	DELETE ANY TABLE	임의의 스키마 소유 테이블 데이터 삭제 권한
	SELECT ANY TABLE	임의의 스키마 소유 테이블 데이터 조회 권한
INDEX(인덱스)	CREATE ANY INDEX	임의의 스키마 소유 테이블의 인덱스 생성 권한
	ALTER ANY INDEX	임의의 스키마 소유 테이블의 인덱스 수정 권한
	DROP ANY INDEX	임의의 스키마 소유 테이블의 인덱스 삭제 권한
VIEW(뷰)	(생략)	뷰와 관련된 여러 권한
SEQUENCE(시퀀스)	(생략)	시퀀스와 관련된 여러 권한
SYNONYM(동의어)	(생략)	동의어와 관련된 여러 권한
PROFILE(프로파일)	(생략)	사용자 접속 조건 지정과 관련된 여러 권한
ROLE(롤)	(생략)	권한을 묶은 그룹과 관련된 여러 권한

 

프로파일 생성

CREATE PROFILE profile_name LIMIT
SESSIONS_PER_USER 10           -- 사용자당 최대 세션 수
CPU_PER_SESSION 100000         -- 세션당 최대 CPU 시간 (1/100초 단위)
CPU_PER_CALL 10000             -- 호출당 최대 CPU 시간 (1/100초 단위)
CONNECT_TIME 60                -- 세션당 최대 연결 시간 (분 단위)
IDLE_TIME 30                   -- 세션 비활동 최대 시간 (분 단위)
LOGICAL_READS_PER_SESSION 1000 -- 세션당 최대 논리적 읽기 수
LOGICAL_READS_PER_CALL 100     -- 호출당 최대 논리적 읽기 수
COMPOSITE_LIMIT 5000000        -- 세션당 리소스 소비 총합
PRIVATE_SGA 1024K              -- 세션당 최대 SGA 사설 메모리 (KB 단위)
FAILED_LOGIN_ATTEMPTS 3        -- 최대 로그인 실패 시도 수
PASSWORD_LIFE_TIME 30          -- 비밀번호 유효 기간 (일 단위)
PASSWORD_REUSE_TIME 365        -- 이전 비밀번호 재사용 가능 기간 (일 단위)
PASSWORD_REUSE_MAX 5           -- 비밀번호 재사용 최대 횟수
PASSWORD_VERIFY_FUNCTION verify_function -- 비밀번호 검증 함수
PASSWORD_LOCK_TIME 1           -- 로그인 실패 시 계정 잠금 시간 (일 단위)
PASSWORD_GRACE_TIME 7;         -- 비밀번호 변경 유예 기간 (일 단위)

 

시스템 권한 부여

create user문을 통해 사용자를 처음 생성한 후 데이터베이스 접속을 허가하기 위해서 grant명령어를 실행

grant [시스템 권한] to [사용자이름/롤(role)이름 /public]
[with admin option]

• with admin option은 현재 grant문을 통해 부여 받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받음.
• 현재 사용자가 권한이 사라져도, 권한을 재 부여한 다른 사용자의 권한은 유지

grant에 사용된 resource키워드

resource는 오라클 데이터베이스에서 제공하는 롤(role)중 하나

select role
from dba_roles;

롤은 여러 권한을 하나의 이름으로 묶어 권한 부여 관련 작업을 간편하게 하려고 사용

만약 grant문에 resouce를 지정하지 않는다면, 사용자에게 테이블 생성 권한을 부여해도 create문으로 테이블을 생성할 수 없거나 테이블이 생성되더라도 insert문에서 오류 메시지를 출력하며 동작하지 않는 경우가 발생

오류 메시지에서 테이블 스페이스는 테이블이 저장되는 공간을 의미하며 따로 지정하지 않으면 기본 테이블스페이스 users가 할당

resource롤에는 사용자를 생성할 때 사용 테이블스페이스의 영역을 무제한 사용 가능(unlimited tablespace)하게 해주는 권한이 포함되어 있기 때문에 , resouce롤을 grant문에 추가하면 별 문제 없이 사용자가 테이블을 생성하고 신규 데이터를 저장할 수 있음.

하지만 테이블스페이스(테이블이 저장되는 공간) 영역 사용에 한계를 두지 않는 unlimited tablespace권한은 엄밀한 관리가 필요한 경우에 적절하지 않으므로 사용자 생성 및 수정할 때 quota절로 사용 영역에 제한을 두기도 함.

alter user 사용자명
quota 용량 on users;

이러한 이슈 때문에 오라클 데이터베이스 12c버전에서는 resource롤에 unlimited tablespace권한을 부여하지 않음.

시스템 권한 취소

grant명령어로 부여나 권한의 취소는 revoke명령어를 사용

revoke [시스템 권한] from [사용자 이름 / 롤(role)이름 / public]

객체 권한이란?

객체 권한: 특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한

객체 권한 분류 객체 권한 설명

TABLE(테이블)	ALTER	테이블 변경 권한
	DELETE	테이블 데이터 삭제 권한
	INDEX	테이블 인덱스 삭제 권한
	INSERT	테이블 데이터 삽입 권한
	REFERENCES	참조 테이블 생성 권한
	SELECT	테이블 조회 권한
	UPDATE	테이블 데이터 수정 권한
VIEW(뷰)	DELETE	뷰 데이터 삭제 권한
	INSERT	뷰 데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	뷰 조회 권한
	UPDATE	뷰 데이터 수정 권한
SEQUENCE(시퀀스)	ALTER	시퀀스 수정 권한
	SELECT	시퀀스의 CURRVAL과 NEXTVAL 사용 권한
PROCEDURE(프로시저)	(생략)	프로시저 관련 권한
FUNCTION(함수)	(생략)	함수 관련 권한
PACKAGE(패키지)	(생략)	패키지 관련 권한

객체 권한 부여

grant [객체 권한/all privileges]
on [스키마.객체 이름]
to [사용자 이름/롤(role)이름/public]
[with grant option]

• all privileges는 객체의 모든 권한을 부여함을 의미

grant select on 스키마.객체(테이블) to 사용자;
grant insert on 스키마.객체 to 사용자;

-- 여러 권한 한번에 부여
grante select,insert on 스키마.객체 to 사용자;

객체 권한 취소

revoke [객체 권한/all privileges](필수)
on [스키마.객체 이름](필수)
from [사용자 이름/롤(role) 이름/public](필수)
[cascade constraints/force](선택)

15-3. 롤 관리

롤이란?

신규 생성 사용자는 아무런 권한이 없으므로 오라클 데이터 베이스에서 제공하는 다양한 권한을 일일이 부여해 주어야 하는 불편한 점을 해결하기 위해 롤을 사용

롤은 여러 종류의 권한을 묶어 놓은 그룹을 뜻함.

롤을 사용하면 여러 권한을 한번에 부여하고 해제할 수 있으므로 권한 관리 효율을 높일 수 있음.

롤은 오라클 데이터베이스를 설치할 때 기본으로 제공되는 사전 정의된 롤(predefined roles)과 사용자 정의 롤(user roles)로 나뉨.

사전 정의된 롤

CONNECT롤

alter session
create user
create database link
create sequence
create session
create synonym
create table
create view

RESOURCE롤

사용자가 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 기본 시스템 권한을 묶어 놓은 롤

create trigger여
create sequence
create type
create procedure
create cluster
create operator
create indextype
create table

보통 새로운 생성자를 생성하면 CONNECT롤과 RESOURCE롤을 부여하는 경우가 많음

CONNECT롤에서 뷰를 생성하는 CREATE VIEW권한과 동의어를 생성하는 CREATE SYNONYM권한이 제외되었기 때문에 뷰와 동의어 생성 권한을 사용자에게 부여하려면 두 가지 권한을 따로 부여해 주어야함.

DBA롤

데이터베이스를 관리하는 시스템 권한을 대부분 가지고 있음.

오라클 11g기분 202개 권한을 가진 매우 강력한 롤

사용자 정의 롤

사용자 정의 롤은 필요에 의해 직접 권한을 포함 시킨 롤을 뜻함.

1.create role문으로 롤을 생성

2.grant명령어로 생성한 롤에 권한을 포함 시킴

3.grant명령어로 권한이 포함된 롤을 특정 사용자에게 부여

4.revoke명령어로 롤을 취소 시킴.

롤 생성 권한 포함

롤을 생성하려면 데이터 관리 권한이 있는 사용자인 system계정으로 접속하여야 함.

create role 롤이름;

GRANT CONNECT,RESOURCE,CREATE VIEW, CREATE SYNONYM
TO 생성한 롤이름;

GRANT 롤이름 TO 사용자명;

 

부여된 롤과 권한 확인

사용자에 현재 부여된 권한과 롤을 확인하려면

USER_SYS_PRIVS , USER_ROLE_PRIVS 데이터 사전을 사용

데이터 관리 권한을 가진 계정은 DBA_SYS_PRIVS , DBA_ROLE_PRIVS를 사용해도 됨

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

 

부여된 롤 취소

GRANT명령어로 부여한 ROLE을 취소할 때 revoke문을 사용하여 롤을 취소할 수 있음.

revoke 롤이름 from 사용자명;

 

롤 삭제

롤 삭제는 DROP명령어를 사용

롤을 삭제하면 해당 롤을 부여받은 모든 사용자의 롤이 취소(REVOKE)됨

잊기 전에 한번 더!

Q1. 다음조건을 만족하는 SQL문을 작성해 보세요.

1)SYSTEM계정으로 접속하여 PREV_HW계정을 생성해보세요

2)비밀번호는 ORCL로 지정합니다.접속권한을 부여하고 PREV_HW계정으로 접속이 잘되는지 확인

CREATE USER PREV_HW
IDENTIFIED BY ORCL;

GRNAT CREATE SESSION TO PREV_HW;

 

Q2. SCOTT계정으로 접속하여 위에서 생성한 PREV_HW계정에 SCOTT소유의 EMP, DEPT, SALGRADE테이블에 SELECT권한을 부여하는 SQL문을 작성해보세요.권한을 부여했으면 PREV_HW계정으로 SCOTT의 EMP,DEPT,SALGRADE테이블이 잘 조회되는지 확인해보세요.

GRANT SELECT ON SCOTT.EMP TO PREV_HW;
GRANT SELECT ON SCOTT.DEPT TO PREV_HW;
GRANT SELECT ON SCOTT.SALGRADE TO PREV_HW;

-- 또는

BEGIN
    EXECUTE IMMEDIATE 'GRANT SELECT ON SCOTT.EMP TO PREV_HW';
    EXECUTE IMMEDIATE 'GRANT SELECT ON SCOTT.DEPT TO PREV_HW';
    EXECUTE IMMEDIATE 'GRANT SELECT ON SCOTT.SALGRADE TO PREV_HW';
END;

 

Q3. SCOTT계정으로 접속하여 PREV_HW계정에 SALGRADE테이블의 SELECT권한을 취소하는 SQL문을 작성해보세요. 권한의 변경이 완료되면 다음과 같이 PREV_HW계정으로 SALGRADE테이블의 조회 여부를 확인해봅시다.

REVOKE SELECT ON SCOTT.SALGRADE FROM PREV_HW;

SELECT명령문의 검색결과

1. 2차원 구조의 표
2. 컬럼의 모든 행은 데이터 타입이 같아야 함

DECODE 함수

Oracle의 DECODE 함수는 조건에 따라 다른 값을 반환하는 함수로, SQL에서 if-else 문과 유사하게 사용됩니다. 기본적으로 특정 값을 다른 값으로 변환할 때 사용

사용법

DECODE(column, search1, result1, search2, result2, ..., default_result)

• column: 비교할 컬럼 또는 표현식
• search: 비교할 값
• result: 비교 값과 일치할 경우 반환할 값
• default_result: 어느 비교 값과도 일치하지 않을 경우 반환할 기본 값

예시

예제 1: 간단한 DECODE 사용

SELECT
    ENAME,
    DECODE(JOB, 'CLERK', '사원', 'MANAGER', '관리자', 'ANALYST', '분석가', '기타') AS 직책
FROM
    EMP;

위 예제에서는 JOB 컬럼의 값이 'CLERK'이면 '사원', 'MANAGER'이면 '관리자', 'ANALYST'이면 '분석가'를 반환하며, 그 외의 값은 '기타'를 반환

 

예제 2: DECODE와 숫자 비교

SELECT
    ENAME,
    DECODE(SAL,
           1000, 'Low Salary',
           2000, 'Medium Salary',
           3000, 'High Salary',
           'Other') AS SALARY_LEVEL
FROM
    EMP;

위 예제에서는 SAL 컬럼의 값에 따라 'Low Salary', 'Medium Salary', 'High Salary' 또는 'Other'를 반환

 

예제 3: 중첩 DECODE 함수

SELECT
    ENAME,
    DECODE(DEPTNO,
           10, DECODE(JOB, 'CLERK', '10번 부서 사원', '기타'),
           20, DECODE(JOB, 'MANAGER', '20번 부서 관리자', '기타'),
           '기타') AS 부서_직책
FROM
    EMP;

위 예제에서는 DEPTNO와 JOB 컬럼의 값을 기반으로 중첩된 DECODE 함수를 사용하여 값을 반환

DECODE 함수는 SQL 쿼리 내에서 복잡한 조건 로직을 간단하게 처리할 수 있게 해줌.

그러나 복잡한 로직이 필요한 경우에는 CASE 문을 사용하는 것이 더 가독성이 좋을 수 있음.

 

행 번호 표시 방법

도구-환경 설정-코드 편집기-행 여백-행 번호 표시

 

실행 순서

5 select: 검색 대상 표현식 (컬럼, 함수, 계산식)

1 from:검색 대상 집합(테이블, 뷰, 서브 쿼리)

2 where: 행 제한을 위한 조건

3 group by: 그룹 생성을 위한 표현 식

4 having;그룹 조건

6 order by: 정렬