Role과 Profile

Role의 생성과 활성화, Profile을 이용한 자원 및 비밀번호 정책 설정과 조회

ROLESET ROLEDEFAULT ROLEPROFILERESOURCE_LIMITFAILED_LOGIN_ATTEMPTS

#핵심 요약

Role은 여러 권한을 하나로 묶은 권한 그룹임
사용자에게 Role 하나만 주고, 권한을 바꿀 때는 Role만 고치면 그 Role을 가진 모든 사용자에게 반영됨

Role은 세션에서 켜고 끌 수 있음(SET ROLE)
접속할 때 기본으로 켜질 Role은 ALTER USER ... DEFAULT ROLE로 정함

Profile은 사용자별 자원 한도(세션 수, CPU 시간, 유휴 시간 등)와 비밀번호 정책(실패 횟수, 잠금 기간, 만료 등)을 묶은 설정임
사용자 한 명에게 Profile 하나가 적용되며 기본은 DEFAULT임

자원 한도를 실제로 적용할지는 RESOURCE_LIMIT이 정하며 19c 기본값은 TRUE임

#개념과 동작 원리

#Role을 쓰는 이유

이유	내용
권한 관리 용이성	권한을 Role에 묶어 두고 Role만 부여, 수정
동적 권한 관리	세션에서 Role을 켜고 꺼서 권한을 일시 차단 예: 야간 배치 시간에만 특정 Role 활성화
선택적 가용성	여러 Role을 부여해 두고 필요한 것만 활성화해 최소 권한 원칙을 유연하게 적용

#Profile 항목

항목	단위	의미
`SESSIONS_PER_USER`	개수	한 사용자가 동시에 만들 수 있는 최대 세션 수
`CPU_PER_SESSION`	1/100초	한 세션이 쓸 수 있는 최대 CPU 시간(1000이면 10초)
`CPU_PER_CALL`	1/100초	SQL 호출 한 번이 쓸 수 있는 최대 CPU 시간
`CONNECT_TIME`	분	한 세션이 접속 상태로 유지될 수 있는 최대 시간
`IDLE_TIME`	분	작업 없이 유지될 수 있는 최대 유휴 시간
`LOGICAL_READS_PER_SESSION`	블록 수	한 세션의 최대 논리적 읽기
`LOGICAL_READS_PER_CALL`	블록 수	SQL 호출 한 번의 최대 논리적 읽기
`PRIVATE_SGA`	바이트(K, M)	공유 서버 세션이 쓸 수 있는 Private SGA 최대 크기
`COMPOSITE_LIMIT`	Service Unit	여러 자원 사용량을 종합한 한도
`PASSWORD_LIFE_TIME`	일	비밀번호 만료 기간
`PASSWORD_LOCK_TIME`	일	로그인 실패로 계정이 잠기는 기간
`FAILED_LOGIN_ATTEMPTS`	횟수	잠기기 전 허용되는 연속 로그인 실패 횟수
`PASSWORD_VERIFY_FUNCTION`	함수 이름	비밀번호 복잡도 검사 함수
`PASSWORD_REUSE_TIME`	일	이전 비밀번호를 다시 쓸 수 있기까지의 기간
`PASSWORD_REUSE_MAX`	횟수	이전 비밀번호 재사용 전 필요한 변경 횟수
`PASSWORD_GRACE_TIME`	일	만료 뒤 변경할 수 있는 유예 기간
`INACTIVE_ACCOUNT_TIME`	일	접속하지 않은 계정을 잠그기까지의 기간

#필수 명령어 또는 SQL

#Role 만들고 부여

SQL*Plus객체 또는 설정을 변경함

CREATE ROLE dev_role;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO dev_role;

GRANT dev_role TO scott;
GRANT dev_role TO king;

결과 해석: 개별 실행 출력은 기재 없음

#세션에서 Role 켜고 끄기

SQL*Plus (해당 사용자 세션)현재 세션 설정만 변경함

SET ROLE NONE;
SET ROLE dev_role;
SET ROLE dev_role, report_role;

SQL*Plus객체 또는 설정을 변경함

ALTER USER scott DEFAULT ROLE dev_role;

결과 해석:

SET ROLE은 현재 세션에만 적용됨
SET ROLE dev_role, report_role처럼 나열하지 않은 Role은 그 세션에서 꺼짐

#Role 조회

SQL*Plus조회만 함

SELECT role FROM dba_roles;
SELECT * FROM session_roles;
SELECT * FROM role_sys_privs WHERE role IN ('HR_MGR', 'HR_CLERK');
SELECT * FROM role_tab_privs WHERE role IN ('HR_MGR', 'HR_CLERK');

결과 해석:

SESSION_ROLES는 현재 세션에서 켜진 Role, ROLE_SYS_PRIVS와 ROLE_TAB_PRIVS는 Role에 담긴 시스템 권한과 객체 권한임

#Profile 확인

SQL*Plus조회만 함

SHOW PARAMETER resource_limit

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT';

SELECT username, profile FROM dba_users WHERE username = 'SCOTT';

결과 해석:

SQL*Plus에서 #은 주석이 아님

#Profile 생성, 적용, 수정, 삭제

SQL*Plus객체 또는 설정을 변경함

CREATE PROFILE limited_user_profile LIMIT
  cpu_per_session 1000
  idle_time 5
  sessions_per_user 2;

SQL*Plus객체 또는 설정을 변경함

ALTER USER king PROFILE p_test;

SQL*Plus객체 또는 설정을 변경함

ALTER PROFILE p_test LIMIT sessions_per_user UNLIMITED;
ALTER PROFILE p_test2 LIMIT sessions_per_user DEFAULT;

결과 해석:

SQL*Plus데이터 또는 파일을 삭제함, 되돌릴 수 없음

DROP PROFILE bad_sql_profile CASCADE;

결과 해석:

실습에서는 UNLIMITED와 DEFAULT 값의 의미를 설명하지 않았음
CASCADE로 지우면 그 Profile을 쓰던 사용자에게 어떤 Profile이 적용되는지는 기재 없음

#로그인 실패 잠금과 해제

SQL*Plus객체 또는 설정을 변경함

CREATE PROFILE p_lock3 LIMIT
  failed_login_attempts 3
  password_lock_time 1/1440;

ALTER USER <SCHEMA_OWNER> PROFILE p_lock3;
ALTER USER <SCHEMA_OWNER> ACCOUNT UNLOCK;

결과 해석:

PASSWORD_LOCK_TIME의 단위가 일이므로 1/1440은 1분임

#사용 조건과 주의사항

RESOURCE_LIMIT이 FALSE면 자원 한도는 적용되지 않음
비밀번호 항목도 이 파라미터의 영향을 받는지는 확인하지 않았음