ORACLE - DAY 11

BUMSOO·2024년 8월 12일

PROFILE 생성

CREATE PROFILE insa_profile LIMIT 
FAILED_LOGIN_ATTEMPTS 2
PASSWORD_LOCK_TIME UNLIMITED;

SELECT * FROM dba_profiles WHERE profile = 'INSA_PROFILE';  
SELECT * FROM dba_users WHERE username = 'INSA';

- profile은 2개만 생성하였지만 나머지는 오라클이 default로 생성한다.

유저 PROFILE 수정

ALTER USER insa PROFILE insa_profile;
SELECT * FROM dba_users WHERE username = 'INSA';

의도적으로 로그인을 2번 이상 실패하여 locked 상태로 만들어준다

DBA가 수동으로 LOCK을 해제해줘야한다.

ALTER USER insa ACCOUNT UNLOCK;
SELECT * FROM dba_users WHERE username = 'INSA';

PROFILE 수정

ALTER PROFILE insa_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440; -- 1분만 LOCK
--PASSWORD_LOCK_TIME 1  -- 하루 LOCK
--PASSWORD_LOCK_TIME 1/24 -- 1시간 LOCK

SELECT* FROM dba_profiles WHERE profile = 'INSA_PROFILE';

3번을 의도적으로 실패 후 유저 정보를 확인

SELECT account_status, to_char(lock_date, 'yyyy-mm-dd hh24:mi:ss') as lock_date
FROM dba_users 
WHERE username='INSA';

- lock이 발생된 시간을 표시해준다.

1분이 지난 후 정상적으로 로그인을 해주면 account_status는 open으로 변경되고 lock_date도 null값으로 변경된다.

패스워드 유효기간을 변경

ALTER PROFILE insa_profile LIMIT
PASSWORD_LIFE_TIME 2/1440 -- 비밀번호 유효기간 2분으로 변경
PASSWORD_GRACE_TIME 1/1440; -- 유예기간 1분으로 설정

SELECT * FROM dba_users WHERE username = 'INSA';

- 2분이 지나 패스워드를 변경하라는 이벤트 발생

PROFILE 삭제

DROP PROFILE insa_profile;

- 그냥 DROP을 하려고 하면 해당 PROFILE을 지정 받은 유저가 있기 때문에 삭제 할 수 없다.

DROP PROFILE insa_profile CASCADE;

- CASCADE 옵션을 통해서 삭제가 가능하며, 삭제된 PROFILE을 할당 받았던 유저는 DEFAULT 프로파일로 변경된다.
- CASCADE : 유저한테 DEFAULT PROFILE로 수정한 후 삭제 한다.

RESOURCE 관리

SELECT * 
FROM dba_profiles 
WHERE profile = 'DEFAULT'
AND resource_type = 'KERNEL';

SESSIONS_PER_USER : 동일한 유저로 허용되는 세션 수
IDLE_TIME : 분 단위로 측정한 비활성 시간, 시간 초과되면 세션 kill
CONNECT_TIME : 분 단위로 측정한 연결 경과 시간, 시간 초과되면 세션 kill
CPU_PER_SESSION : 한 세션에서 사용 가능한 CPU시간 설정, 1/100초 단위로 설정
EX) 1000으로 할당하면 10초로 설정
CPU_PER_CALL : 한 문장에서 사용 가능한 CPU시간 설정, 1/100초 단위로 설정
LOGICAL_READS_PER_SESSION : 한 세션에서 읽어 들일 수 있는 BLOCK수를 제한
LOGICAL_READS_PER_CALL : 한 문장에서 읽어 들일 수 있는 BLOCK수를 제한
PRIVATE_SGA : BYTE 단위로 측정한 SGA의 전용공간(SHARED SERVER 환경(UGA공간 제한))
COMPOSITE_LIMIT : (CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION ,PRIVATE_SGA) 가중 합계로 자원 비용을 계산

resource 변경

CREATE PROFILE insa_profile LIMIT
FAILED_LOGIN_ATTEMPTS 2
PASSWORD_LOCK_TIME UNLIMITED
IDLE_TIME 1;

- idle_time을 1분으로 설정하여 1분동안 비활성화 상태이면 세션을 kill 하게 한다.

resource_limit 파라미터 value 값이 true 설정되어 있어야 resource 관리가 수행된다.

SELECT * FROM v$parameter WHERE name = 'resource_limit';

- value 값이 true이고 issys_modifiable이 immediate 이기 때문에 동적 파라미터 이다.

- 1분 동안 아무런 작업을 하지 않아 이후 작업을 수행하려고 하면 해당 오류가 발생한다.

AUDIT(감사)

감사 선택한 유저 데이터베이스 작업을 모니터링하는 것이다.
- 의심이 가는 데이터베이스 작업을 조사
- 특정 데이터베이스 작업에 대한 정보를 수집할 경우

AUDIT 설정

SHOW PARAMETER audit_trail

- DB로 되어있으면 활성화
- audit_trail 파라미터는 static 파라미터

감사 비활성화

ALTER SYSTEM SET audit_trail = none SCOPE = SPFILE;

감사 활성화

ALTER SYSTEM SET audit_trail = db SCOPE = SPFILE;

sys.aud$

SELECT * FROM sys.aud$;

- sys.aud$ 안에 있는 내용을 관리해야한다.

감사 옵션 설정

1. 문장감사

SQL문을 선택적으로 감사
AUDIT TABLE 은 CREATE TABLE, DROP TABLE, TRUNCATE TABLE 수행하는 감사

audit_trail 활성화
감사 옵션 설정
- TABLE 감사옵션 설정 AUDIT TABLE;
- TABLE 감사옵션 해지 NOAUDIT TABLE;

DBA SESSION

AUDIT TABLE; -- TABLE에 대한 감사옵션 설정

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'TABLE';

- audit_option에 table로 설정되어있는걸 확인할 수 있다.
- NOAUDIT TABLE 하게 되면 테이블에 조회되지 않는다.

INSA SESSION

CREATE TABLE emp
AS
SELECT * FROM hr.employees;

DBA SESSION

-- 감사 테이블 확인
SELECT * FROM sys.aud$;

- system 테이블로 보기는 힘드니 dictionary view로 봐보자

SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time
FROM dba_audit_object;

- 오류가 발생했을 경우 SESS 컬럼에 오류번호 표시
- ALTER TABLE 문장은 감사에 걸리지 않는다

dba창에서 insa 유저 테이블 만들기

CREATE TABLE insa.dept 
AS 
SELECT * FROM hr.departments;

-- 감사정보 확인
SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time
FROM dba_audit_object;

❗ dba가 만든 테이블은 감사에 기록되지 않는다
✅ 감사 테이블은 SYS 테이블스페이스에 대한 저장공간 문제로 이어질 수 있기 때문에 잘 체크해야 한다.

보통의 딕셔너리 테이블은 오라클이 관리 하지만 sys.aud$ 테이블은 dba가 직접 관리 할수 있다.
스토리지가 부족한 경우 TRUNCATE 할 수 있다.

TRUNCATE TABLE sys.aud$;

SELECT * FROM sys.aud$

2. 스키마(schema) object audit

특정한 유저가 소유한 테이블에 대해서 감사 설정

DBA SESSION

-- 감사 옵션 설정
AUDIT select, insert, update, delete ON hr.emp;

-- OBJECT 감사 옵션 확인
SELECT owner, object_name, object_type, sel, ins,upd, del 
FROM dba_obj_audit_opts
WHERE owner = 'HR';

INSA SESSION

SELECT * FROM hr.emp;

INSERT INTO hr.emp(ID,NAME,SAL) 
VALUES (300, 'KIM' ,100000);

UPDATE hr.emp
SET sal = sal*1.1
WHERE id = 100;

DELETE FROM hr.emp
WHERE sal < 5000;

INSA_BUHA SESSION

SELECT * FROM hr.emp;

INSERT INTO hr.emp(ID,NAME,SAL) 
VALUES (300, 'LEE' ,20000);

UPDATE hr.emp
SET sal = sal*1.1
WHERE id = 101;

DELETE FROM hr.emp
WHERE ID = 201;

DBA SESSION

SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time
FROM dba_audit_object;

감사 옵션 해제

NOAUDIT select, insert, update, delete ON hr.emp;

SELECT owner, object_name, object_type, sel, ins,upd, del 
FROM dba_obj_audit_opts
WHERE owner = 'HR';

감사 테이블 초기화

TRUNCATE TABLE sys.aud$

SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time
FROM dba_audit_object;

3. 권한감사(privilege audit)

시스템 권한을 선택적으로 감사

DBA SESSION

GRANT SELECT ANY TABLE TO insa, insa_buha;

AUDIT SELECT ANY TABLE BY insa, insa_buha; -- 특정 유저만 모니터링 할때는 BY절 사용

AUDIT CREATE TABLE BY insa, insa_buha;

SELECT * FROM dba_stmt_audit_opts;

INSA SESSION

SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
SELECT * FROM hr.locations;

CREATE TABLE emp1
AS 
SELECT * FROM hr.employees;

INSA_BUHA SESSION

SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
SELECT * FROM hr.locations;

CREATE TABLE emp1
AS 
SELECT * FROM hr.employees;

DBA SESSION

-- 감사 테이블 확인하기
SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time
FROM dba_audit_object;

AUDIT_TRAIL 설정 변경

audit_trail = DB,EXTENDED : 유저가 실행한 SQL 텍트스, 변수 값 감사

ALTER SYSTEM SET audit_trail = db,extended SCOPE = SPFILE;

DBA SESSION

-- object audit 설정
AUDIT select, insert, update, delete ON hr.emp;

INSA SESSION

var b_id number
execute :b_id := 100
print :b_id

SELECT * FROM hr.emp WHERE id = :b_id;

DBA SESSION

SELECT username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) as sess,
    to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as time,
    sql_text,
    sql_bind
FROM dba_audit_object;

- sql_text , sql_bind를 통해 수행한 sql문과 사용한 bind 변수까지 알수 있다.

- 일반적으로 현장에서는 DELETE 를 통해 감사테이블을 관리한다.

BUMSOO

이전 포스트

ORACLE - DAY 10

다음 포스트

ORACLE - DAY 11

PROFILE 생성

유저 PROFILE 수정

PROFILE 수정

PROFILE 삭제

RESOURCE 관리

resource 변경

AUDIT(감사)

AUDIT 설정

감사 비활성화

감사 활성화

sys.aud$

감사 옵션 설정

1. 문장감사

DBA SESSION

INSA SESSION

DBA SESSION

2. 스키마(schema) object audit

DBA SESSION

INSA SESSION

INSA_BUHA SESSION

DBA SESSION

3. 권한감사(privilege audit)

DBA SESSION

INSA SESSION

INSA_BUHA SESSION

DBA SESSION

AUDIT_TRAIL 설정 변경

DBA SESSION

INSA SESSION

DBA SESSION

ORACLE - DAY 10

ORACLE - DAY 12

0개의 댓글