목차
1. 권한과 Role
1-1 유저에 대한 권한과 롤 조회
1-2 시스템 권한 할당과 해제
1-3 객체 권한 할당과 해제
1-4 롤 생성과 권한 할당
1-5 디폴트 롤과 SET 명령을 이용한 롤 사용
1-6 보안 강화를 위한 유저와 롤 구현 실습
2. 패스워드 보안 및 리소스 관리
2-1 프로파일 - 패스워드 관리
2-2 암호 설정
2-3 프로파일 - 리소스 관리
3. Profile 관리
3-1 프로파일 조회
3-2 프로파일 생성과 제한 설정
3-3 프로파일 할당과 적용
1. 권한과 Role
1-1 유저에 대한 권한과 롤 조회
● 사용 명령어
SQL> SELECT grantee, privilege, admin_option FROM dba_sys_privs;- 유저 또는 롤에 할당된 시스템 권한을 조회한다.
- GRANTEE : 권한을 할당받은 유저
- PRIVILEGE : 권한
- ADMIN_OPTION : 할당받은 권한을 관리자와 동일하게 사용할 수 있는지에 대한 여부
SQL> SELECT grantee, owner, table_name, privilege, grantor FROM dba_tab_privs;- 유저 또는 롤에 할당된 객체 권한을 조회한다.
- OWNER : 대상 오브젝트의 소유자
- TABLE_NAME : 객체 권한이 할당된 테이블 명
- GRANTOR : 객체 권한을 할당해 준 유저 (sys로 작업할 경우 오너와 동일하게 표시된다.)
SQL> SELECT grantee, granted_role FROM dba_role_privs;- 유저 또는 롤에 할당된 role을 조회한다.
- GRANT_ROLE : 할당된 롤 명
● 실습
- 실습용 유저 생성 후 확인한다.
- 실습용 롤 생성 후 확인한다.
- 유저에게 시스템 권한을 할당한다.
- 롤에 시스템 권한을 할당한다.
- 롤과 유저에 할당된 시스템 권한을 확인한다.
- 객체 권한 할당을 위한 테이블을 생성한다.
- 유저와 롤에 객체 권한을 할당한다.
- 유저와 롤에 할당된 객체 권한을 확인한다. sys로 작업했음에도 오너가 준 것으로 인식한다.
- u1 유저에 r1, r2 롤을 할당한다.
- u2 유저에 r2 롤을 할당한다.
- 유저와 롤에 할당된 롤을 확인한다.
1-2 시스템 권한 할당과 해제
● 사용 명령어
SQL> GRANT <시스템 권한>,<시스템 권한> .......
2 TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
3 [WITH ADMIN OPTION];- 유저 또는 롤에 시스템 권한을 할당한다.
- PUBLIC : DB의 모든 유저에게 권한을 할당한다.
- WITH ADMIN OPTION : 할당된 권한을 SYS 관리자와 동일하게 할당하거나 해제할 수 있다.
SQL> REVOKE <시스템 권한>,<시스템 권한> .......
2 FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];- 유저 또는 롤의 시스템 권한을 해제한다.
- 퍼블릭으로 부여한 권한은 퍼블릭으로만 해제 가능하다.
● 실습
- 예제용 유저의 권한을 조회한다.
- 유저에게 권한 부여 후 확인한다.
- u1은 any 키워드로 create table 권한을 얻었으므로 u1으로 모든 스키마에 테이블 생성이 가능하다.
- u1이 할당받은 create sequence 권한을 u2에게 with admin option으로 다시 할당한다.
- u2가 할당받은 create sequence 권한을 u3에게 다시 할당한다. (u1 - u2 - u3 순서로 할당)
- 시스템 권한을 그랜터를 관리하지 않으므로 권한 할당의 선후 관계를 확인할 수 없다.
- 권한 할당의 선후 관계를 확인할 수 없으므로 u2가 u1의 권한을 해제할 수 있다.
- 퍼블릭으로 생성한 권한만을 퍼블릭으로 해제할 수 있다.
1-3 객체 권한 할당과 해제
● 사용 명령어
SQL> GRANT <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
2 TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];
3 [WITH GRANT OPTION];- 유저 또는 롤에 객체 권한을 할당한다.
- PUBLIC : DB의 모든 유저에게 권한을 할당한다.
- WITH GRANT OPTION : 할당된 권한을 다른 유저에게 할당할 수 있다.
SQL> REVOKE <객체 권한>,<객체 권한> ....... ON <[user명].객체명>, <[user명].객체명> ......
2 FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];- 유저 또는 롤에 객체 권한을 해제한다.
- 직접 할당한 권한만 해제 가능하다.
● 실습
- u11 테이블 생성 후 해당 테이블에 대한 select 권한을 u2에게 with grant option으로 할당한다.
- u2는 해당 테이블에 대한 select 권한을 u3에게 할당한다.
- 권한을 부여해준 사용자를 그랜터로 인식한다.
- u1 소유인 테이블에 대한 권한이라도 u1이 할당한 권한이 아니라면 권한 해제가 불가능하다.
- u3의 그랜터인 u2의 권한을 해제하여 종속된 u3의 권한까지 해제한다.
- 객체 권한을 부여하는 경우 그에 따른 책임은 그랜터에게 부과된다.
- sys 관리자가 일반 유저 테이블의 객체 권한을 할당하는 경우 대상 객체의 오너를 그랜터로 인식한다.
1-4 롤 생성과 권한 할당
● 사용 명령어
SQL> CREATE ROLE <role 명>
2 [IDENTIFIED BY <암호>];- 롤을 생성한다.
SQL> DROP ROLE <role 명>;- 롤을 삭제한다.
SQL> GRANT <role 명> TO <user명 | role 명>;- 롤을 유저나 롤에 할당한다.
- 롤이 롤에 할당되는 경우 순환으로 할당될 수 없다.
● 실습
- 새로운 롤을 만들 경우 참조용으로 미리 만들어진 롤이 존재한다.
- 사용할 롤을 생성한다.
- 롤에 시스템 권한을 부여한다.
- 롤에 부여된 시스템 권한을 조회한다. role_sys_privs의 정보는 모두 dba_sys_privs에서 조회 가능하다.
- 롤에 객체 권한을 부여한다.
- 롤에 부여된 객체 권한을 조회한다. role_tab_privs의 정보는 모두 dba_tab_privs에서 조회 가능하다.
- 롤에 롤을 부여한다.
- 롤에 부여된 롤을 조회한다. role_role_privs의 정보는 모두 dba_role_privs에서 조회 가능하다.
- 롤에 롤을 부여하는 경우 순환해서는 안된다.
1-5 디폴트 롤과 SET 명령을 이용한 롤 사용
● 사용 명령어
SQL> ALTER USER <user 명>
2 DEFAULT ROLE { <role 명>, ...... | ALL [EXCEPT <role 명>] | none };- 사용자에 부여된 롤 중 디폴트 롤을 지정한다.
- EXCEPT : 전체 롤 중 디폴트에서 제외될 롤을 지정한다.
SQL> SET role
2 { <role 명> [IDENTIFIED BY <암호>, ...... ] || ALL [EXCEPT <role 명>] | none }- 현재 세션에서 사용할 롤을 지정한다.
SQL> SELECT * FROM SESSION_ROLES;- 현재 세션에서 사용 중인 롤을 조회한다.
● 실습
- 실습용 테이블 스페이스, 유저를 생성한다.
- 실습용 롤을 생성한다. ro2와 ro3는 암호를 정의한다.
- 각 롤에 권한을 부여한다.
- 생성된 세 개의 롤을 insa 유저에 할당한다.
- insa 유저의 디폴트 롤을 ro1으로 지정한다. (디폴트 롤 기본값 = all)
- insa 유저로 접속 시 디폴트 롤인 ro1 만이 활성화된다.
- 롤에 지정된 패스워드는 set role 수행 시 반드시 작성한다.
- 재접속 시 다시 디폴트 롤만이 활성화된다.
1-6 보안 강화를 위한 유저와 롤 구현 실습
● DB 구축 환경
- 프로젝트명 : insa
- User
* insa_ma : 프로젝트 관련 table 소유 스키마로 세그먼트 생성 시에만 사용된다.
* insa_al : insa_ma 세그먼트에 대한 ALTER 작업만을 수행한다.
* insa_in : data 입력과 검색만을 수행한다.
* insa_up : data 수정과 검색을 수행한다.
insa_ma와 insa_al과 같은 유저는 주로 모델러, insa_in과 insa_up과 같은 유저는 주로 애플리케이션 개발자가 이용한다.
- Tablespace
* insa T/S : 세그먼트 저장용 테이블스페이스이다.
* indx T/S : 인덱스용 테이블스페이스이다.
- Role
* r_basic : 접속 관련 권한
* r_master : segment에 대한 생성 권한 (default role로 지정하지 않는다.)
* r_alter : insa_ma segment에 대한 ALTER 권한
* r_insert : insa_ma segment에 대한 INSERT 권한
* r_update : insa_ma segment에 대한 UPDATE, DELETE 권한
- Table
* insa_ma 스키마의 table 목록 in01, in02, in03 테이블의 구조는 임의로 결정한다.
● 구현 과정
① 테이블스페이스 생성
② 유저 생성
③ 롤 생성
④ 롤에 권한 할당
⑤ 유저에 롤 할당
● 실습
① 테이블스페이스 생성
- insa 테이블과 인덱스 테이블을 생성한다.
② 유저 생성
- 유저를 생성한다.
- insa_ma 외의 유저는 입력, 수정, 검색 등 만을 하므로 쿼타 할당이나 디폴트 테이블스페이스는 필요하지 않다.
- 치환 변수 &를 이용해 insa_ma 유저에 테이블을 생성한다.
- '/'는 직전 sql문을 실행한다.
③ 롤 생성
- in_master 롤은 암호를 설정한다.
- 'R#_%' ESCAPE '#' : '#'을 이스케이프 문자로 이용해 본래 와일드 문자인 '_'을 일반 문자열로 인식시켜 'R_'로 시작하는 문자열을 검색한다.
④ 롤에 권한 할당
- create table, create sequence, create trigger, create procedure, create view 가 가장 기본적인 크리에이트 권한이다.
- 각 롤에 할당된 시스템 권한과 객체 권한을 확인한다.
⑤ 유저에 롤 할당
- insa_ma 유저에 롤을 할당한다.
- r_master 롤은 매우 중요하므로 디폴트로 활성화시키지 않는다.
- 유저별 업무 권한을 분리하여 보안을 강화한다.
2. 패스워드 보안 및 리소스 관리
2-1 프로파일 - 패스워드 관리
데이터베이스의 보안과 효율적인 관리를 위해 데이터베이스 관리자는 프로파일을 사용하여 패스워드 관리 및 리소스 관리를 한다.
프로파일이란 패스워드 및 리소스 제한을 명명한 집합으로, create user 혹은 alter user 명령으로 유저에게 할당한다.
활성화/비활성화가 가능하며 기본적으로 DEFAULT 프로파일을 따른다
● 패스워드 계정 잠금
로그인 일정 횟수 이상 실패 시 계정에 락을 건다. 락의 지속기간을 설정할 수 있다.
| 파라미터 | 설명 |
| FAILED_LOGIN_ATTEMPTS | 계정 잠금 전 가능한 로그인 시도 실패 횟수 |
| PASSWORD_LOCK_TIME | 지정된 로그인 시도 실패 횟수 이후 계정이 잠기는 일 수 - 1/24, 5/24와 같이 분수를 이용하여 시간 단위로도 설정 가능하다. |
● 패스워드 만기일 및 유예기간
패스워드의 만기일을 정하고 이후 유예기간 동안은 반드시 패스워드를 변경하도록 설정할 수 있다.
| 파라미터 | 설명 |
| PASSWORD_LIFE_TIME | 한 암호의 최대 사용 기간 |
| PASSWORD_GRACE_TIME | 암호 만기 이후 암호 변경까지의 유예기간 |
● 패스워드 히스토리
이전에 사용한 패스워드를 기억하여 이전 패스워드 재사용에 대한 제약을 설정할 수 있다.
| 파라미터 | 설명 |
| FAILED_LOGIN_ATTEMPTS | 주어진 일 수 동안 암호 재사용할 수 없도록 지정 |
| PASSWORD_LOCK_TIME | 주어진 횟수만큼 암호를 변경해야만 이전 암호 재사용이 가능하도록 지정 |
- 둘을 동시에 지정 시 어떤 설정이 적용될지 모르므로 동시에 지정하지 않는다.
● 패스워드 확인
패스워드 생성 시 복잡도를 설정할 수 있다.
| 파라미터 | 설명 |
| PASSWORD_VERIFY_FUNCTION | 암호 생성 전 암호 복잡성을 검사하는 PL/SQL 함수 |
● 사용자 제공 패스워드 함수
function_name(
userid_parameter IN VARCHAR2(30),
password_parameter IN VARCHAR2(30),
old_password_parameter IN VARCHAR2(30))
RETURN BOOLEAN
● 암호 확인 함수 VERIFY_FUNCTION
오라클은 utlpwdmg.sql 스크립트에 의해 VERIFY_FUNCTION이라는 기본 PL/SQL 함수 형태로 복잡성 확인 함수를 제공한다.
규칙은 다음과 같다.
- 최소 길이는 4글자이다.
- 암호는 유저 이름과 달라야 한다.
- 암호는 최소한 영문자 1개, 숫자 1개, 특수 문자 1개로 구성되어야 한다.
- 암호는 최소한 3글자까지 이전 암호와 달라야 한다.
2-2 암호 설정
● 프로파일 생성 - 암호 설정
다음과 같이 암호 설정 프로파일을 생성한다.
CREATE PROFILE grace_5 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_GRACE_TIME 5;- 상단의 LIMIT은 문법이므로 빼서는 안된다.
● 프로파일 변경 - 암호 설정
ALTER PROFILE을 이용해 암호 제한을 변경한다.
ALTER PROFILE default LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;- default라는 이름의 프로파일은 실제로는 절대 바꾸지 않는다. 예제용 문장일 뿐이다.
● 프로파일 삭제 - 암호 설정
DROP PROFILE을 이용해 프로파일을 삭제하며, 이때 DEFAULT 프로파일은 삭제할 수 없다.
DROP PROFILE developer_prof;
DROP PROFILE developer_prof CASCADE;- cascade는 할당한 유저로부터 프로파일을 취소한다. 이 경우 디폴트 프로파일로 돌아간다.
2-3 프로파일 - 리소스 관리
리소스 관리 제한은 세션 레벨, 호출 레벨 혹은 두 레벨 모두에서 시행 가능하다.
* 세션 레벨: 세션 접속 한 번당 사용가능한 리소스량을 제한한다.
* 호출 레벨: 문장 하나가 사용할 수 있는 리소스량을 제한한다.
CREATE PROFILE 명령을 이용해 프로파일에서 리소스 제한을 정의할 수 있다.
● 자원 제한 활성화
ALTER SYSTEM 명령을 이용해 초기화 파라미터 RESOURCE_LIMIT을 TRUE로 설정하여 파라미터를 활성화한다. 해당 값이 FALSE일 경우 프로파일의 리소스 관련 설정은 적용되지 않는다.
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
● 세션 레벨에서 자원 제한 설정
세션 레벨에서의 설정은 세션 연결 시마다 시행된다. 자원 제한 설정은 다음과 같다.
| 자원 | 설명 |
| CPU_PER_SESSION | 총 cpu 사용 가능 시간 (1/100초 단위) - 100일 경우 1초이다. |
| SESSIONS_PER_USER | 한 계정이 동시에 접속 가능한 세션 수 |
| CONNECT_TIME | 접속 후 사용 가능 시간 (분 단위) |
| IDLE_TIME | 아무 동작 없이 세션 유지 가능한 시간 (분 단위) |
| LOGICAL_READS_PER _SESSION | 데이터 블록 수 (물리적 및 논리적 읽기) - 논리적 읽기는 메모리를 의미하며 데이터 버퍼 캐시에서 읽는다. - 물리적 읽기는 데이터 파일을 읽는다. |
● 호출 레벨에서 자원 제한 설정
호출 레벨에서의 설정은 sql문 실행 중 호출 시마다 시행된다. 자원 제한 설정은 다음과 같다.
| 자원 | 설명 |
| CPU_PER_CALL | 문장 하나가 cpu를 읽을 수 있는 시간 (1/100초 단위) |
| LOGICAL_READS_PER_CALL | 문장 하나가 읽을 수 있는 블록 갯수 - 풀 테이블 스캔을 막기 위해 설정한다. |
● 프로파일 생성 - 자원 제한
다음과 같이 자원 제한 프로파일을 생성한다.
CREATE PROFILE developer_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;
3. Profile 관리
3-1 프로파일 조회
● 사용 명령어
SQL> SELECT DISTINCT profile FROM DBA_PROFILES;- 프로파일의 목록을 확인한다.
SQL> SELECT * FROM DBA_PROFILES
2 ORDER BY PROFILE, RESOUECE_TYPE;- 각 프로파일에 정의된 설정 값을 확인한다.
SQL> SELECT username, profile FROM DBA_USERS;- 각 유저에게 할당된 프로파일을 조회한다.
● 실습
- 프로파일 목록이 저장된 딕셔너리를 확인한다.
- 프로파일의 목록을 확인한다. DEFAULT는 오라클의 디폴트 프로파일의 이름으로, 삭제가 불가능하다.
- 각 프로파일에 정의된 제한 사항을 확인한다.
- 각 유저 별로 정의된 프로파일을 조회한다. 모든 유저가 DEFAULT 프로파일에 따라 제한된다.
3-2 프로파일 생성과 제한 설정
● 사용 명령어
SQL> CREATE[| ALTER] PROFILE <profile 명> LIMITED
2 COMPOSITE_LIMIT [<설정값> | UNLIMITED | DEFAULT]
3 SESSIONS_PER_USER [<설정값> | UNLIMITED | DEFAULT]
4 PRIVATE_SGA [<설정값> | UNLIMITED | DEFAULT]
5 CONNECT_TIME [<설정값> | UNLIMITED | DEFAULT]
6 IDLE_TIME [<설정값> | UNLIMITED | DEFAULT]
7 LOGICAL_READS_PER_CALL [<설정값> | UNLIMITED | DEFAULT]
8 LOGICAL_READS_PER_SESSION [<설정값> | UNLIMITED | DEFAULT]
9 CPU_PER_CALL [<설정값> | UNLIMITED | DEFAULT]
10 CPU_PER_SESSION [<설정값> | UNLIMITED | DEFAULT]
11 PASSWORD_VERIFY_FUNCTION [<설정값> | NULL | DEFAULT]
12 PASSWORD_REUSE_MAX [<설정값> | UNLIMITED | DEFAULT]
13 PASSWORD_REUSE_TIME [<설정값> | UNLIMITED | DEFAULT]
14 PASSWORD_LIFE_TIME [<설정값> | UNLIMITED | DEFAULT]
15 FAILED_LOGIN_ATTEMPTS [<설정값> | UNLIMITED | DEFAULT]
16 PASSWORD_LOCK_TIME [<설정값> | UNLIMITED | DEFAULT]
17 PASSWORD_GRACE_TIME [<설정값> | UNLIMITED | DEFAULT];- 프로파일을 생성한다.
- UNLIMITED : 제한하지 않는다.
- DEFAULT : DEFAULT 프로파일과 동일한 값을 가진다.
- COMPOSITE_LIMIT : CONNECT_TIME, PRIVATE_SGA, CPU_PER_SESSION, READ_PER_SESSION 등의 값을 통합해서 제한한다.
- SESSIONS_PER_USER : 계정 당 접속 가능한 세션 숫자
- PRIVATE_SGA : Shared server환경에서 SGA에 사용가능한 SP 전용 메모리 크기 (MB)
- CONNECT_TIME : 접속 유효 시간 (분)
- IDLE_TIME : 비활성 접속 한계 (분)
- LOGICAL_READS_PER_CALL : 한 문장에서 읽기 가능한 블록 개수
- LOGICAL_READS_PER_SESSION : 한 세션에서 읽기 가능한 블록 개수
- CPU_PER_CALL : 한 문장에서 사용 가능한 CPU 시간 (1/100 초)
- CPU_PER_SESSION : 한 세션에서 사용 가능한 CPU 시간 (1/100 초)
- PASSWORD_VERIFY_FUNCTION : 패스워드 복잡성을 확인하는 함수
- PASSWORD_REUSE_MAX : 패스워드 재사용까지 변경 횟수
- PASSWORD_REUSE_TIME : 패스워드 재사용까지 제한 기간
- PASSWORD_LIFE_TIME : 패스워드 유효 기간
- FAILED_LOGIN_ATTEMPTS : 패스워드 오류 허용 횟수
- PASSWORD_LOCK_TIME : 패스워드 오류에 의한 락 유지 기간 (일)
- PASSWORD_GRACE_TIME : 패스워드 만료 이후 암호 변경까지 유예 기간
- 프로파일에 대한 ALTER 문장은 CREATE 문장과 동일하다.
● 실습
- resource_limit 파라미터를 확인한다.
- 값이 false인 경우 프로파일의 리소스 관련 설정이 무시되므로 true로 설정한다.
- insa 프로파일을 생성한다.
: 계정 당 접속 가능한 세션 수: 1개, 비활성 접속 한계 시간: 1분, 접속 유효 시간 2분
- 패스워드 오류 허용 횟수 : 3회, 패스워드 오류에 의한 락 유지 기간: 1일로 수정한다.
3-3 프로파일 할당과 적용
● 사용 명령어
SQL> ALTER USER <user 명>
2 PROFILE <profile 명>;- 사용자에게 프로파일을 할당한다.
- CREATE USER 명령을 통해 할당하는 것도 가능하다.
- 유저에 프로파일을 지정하지 않으면 디폴트 프로파일에 적용받는다.
● 실습
- emp 유저를 생성한다.
- emp에게 connect, resource 권한을 부여한다.
- emp 유저에 insa 프로파일을 지정한다.
- 첫 번째 세션에서 emp로 접속한다.
- 두 번째 세션에서 emp로 접속 시도 시 계정 당 접속 가능한 세션 수를 1개로 설정했으므로 접속이 불가하다.
- 패스워드 오류 허용 횟수가 3회이므로 이후 정상적인 입력에도 접속이 불가하다.
- 관리자로 접속하여 emp 유저의 락을 해제한다.
'일일 정리' 카테고리의 다른 글
| Table 관리 (0) | 2025.04.24 |
|---|---|
| DB 패스워드 파일, Table 관리 (0) | 2025.04.23 |
| 오라클 유저 관리, 유저 생성과 관리 실습, 권한 관리, 롤 관리 (1) | 2025.04.22 |
| 오라클 자동 실행, Tablespace와 data file (0) | 2025.04.19 |
| Redo log file, Redo log file 관리, 오라클 Network 설정과 접속, 테이블 스페이스 (0) | 2025.04.17 |