1) 유저생성
select * from dba_users where username in ('INSA01', 'INSA02');
drop user insa01 cascade; -- insa01 존재한다면, 삭제
drop user insa02 cascade; -- insa02 존재한다면, 삭제
create user insa01
identified by oracle
default tablespace users
temporary tablespace temp;
create user insa02
identified by oracle
default tablespace users
temporary tablespace temp;
select * from dba_users where username in ('INSA01', 'INSA02');
select * from dba_ts_quotas where username in ('INSA01', 'INSA02');
2) 권한 부여
unlimited tablespace : db 내의 모든 tablespace 의 권한
grant create session, create table, unlimited tablespace to insa01, insa02;
2-1) 권한 확인
select * from dba_sys_privs where grantee in ('INSA01', 'INSA02');
3)★ sys.aud$ 딕셔너리 테이블을 새로운 테이블스페이스로 이관작업
select table_name, tablespace_name from dba_tables where table_name = 'AUD$';
select * from sys.aud$; -- 확인이 안될 수도 있다.
4) tablespace 생성
CREATE TABLESPACE audit_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/audit_tbs01.df' SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT AUTO;
4-1) 생성 확인
select * from dba_tablespaces where tablespace_name = 'AUDIT_TBS';
select * from dba_data_files where tablespace_name = 'AUDIT_TBS';
5) 익명블록(package) 생성
BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'audit_tbs');
END;
/
5-1) 이관작업 확인
- sys.aud$ 딕셔너리 테이블을 system tablespace -> audit tbs tablespace로 이관
select table_name, tablespace_name from dba_tables where table_name = 'AUD$';
6) 데이터 이관작업 원위치로 변경
BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'system');
END;
/
6-1) 이관작업 확인
- sys.aud$ 딕셔너리 테이블을 audit tbs tablespace -> system tablespace로 이관
select table_name, tablespace_name from dba_tables where table_name = 'AUD$';
7) 다시 새로운 곳으로 데이터 이관작업
BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'audit_tbs');
END;
/
7-1) 확인
select table_name, tablespace_name from dba_tables where table_name = 'AUD$';
select * from sys.aud$; -- 감사에는 관련이 없다.
8) 감사 활성화 및 비활성화
SQL> show parameter audit_trail
#1) 내용 삭제
SQL> truncate table sys.aud$;
#2) 감사 비활성화로 변경
SQL> alter system set audit_trail = none scope = spfile;
#3)DB 내리기
SQL> shutdown immediate
#4) DB 올리기
SQL> startup
#5) 확인하기
SQL> show parameter audit_trail
#6) 데이터 확인
SQL> select * from sys.aud$;
#7) 감사 활성화로 변경
SQL> alter system set audit_trail = db_extended scope = spfile;
#8) DB 내렸다가 다시 올리기
SQL> shutdown immediate
SQL> startup
#9) 확인
SQL> show parameter audit_trail
■ SQL문 감사
<SYS SESSION>
#1) create table, drop table, truncate table 문장에 대한 감사 수행
#)데이터 베이스 레벨로 SQL문
SQL> audit table;
#) 확인
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
<INSA01 SESSION>
#) test용으로 생성하고 지우기
CREATE TABLE insa_tab (id number, name VARCHAR2(20), day date);
ALTER TABLE insa_tab MODIFY name varchar2(30);
TRUNCATE TABLE insa_tab;
DROP TABLE insa_tab PURGE;
<SYS SESSION>
select * from sys.aud$;
-- aud$를 자세히 보기 위함
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
tip#) 사이즈 조절
SQL> col username format a10
SQL> col owner format a10
SQL> col obj_name format a15
SQL> col action_name format a20
SQL> col sess format a15
SQL> col sql_text format a40
SQL> col sql_bind format a10
SQL> set linesize 600
#) 지우기
truncate table sys.aud$; -- 보기편하기 위해서 수행
noaudit table; -- SQL문 감사 취소SQL> select * from dba_stmt_audit_opts where audit_option = 'TABLE';
#) 변경
SQL> audit table by insa02; -- 특정한 유저에게 SQL문 감사 설정
SQL> select * from dba_stmt_audit_opts where audit_option = 'TABLE';
<새로운 insa02 session>
CREATE TABLE insa_tab (id number, name VARCHAR2(20), day date);
ALTER TABLE insa_tab MODIFY name varchar2(30);
TRUNCATE TABLE insa_tab;
DROP TABLE insa_tab PURGE;
<insa02 session>
CREATE TABLE insa_new (id number, name VARCHAR2(20), day date);
ALTER TABLE insa_new MODIFY name varchar2(30);
TRUNCATE TABLE insa_new;
DROP TABLE insa_new PURGE;
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
--INSA02 만 감사 확인
#) 설정 해지
<SYS SESSION>
SQL> noaudit table by insa02;
SQL> select * from dba_stmt_audit_opts where audit_option = 'TABLE';
SQL> TRUNCATE TABLE sys.aud$;
<hr session>
drop table hr.dept cascade CONSTRAINTS purge;
drop table hr.emp cascade CONSTRAINTS purge;
#1) test table 생성
create table hr.dept
as
select department_id dept_id, department_name dept_name
from hr.departments;
create table hr.emp
as
select employee_id id, last_name name, salary sal, department_id dept_id
from hr.employees;
#2) 제약조건 추가
alter table hr.dept add constraint deptid_pk primary key(dept_id);
alter table hr.emp add constraint empid_pk primary key(id);
alter table hr.emp add constraint emp_deptid_pk foreign key(dept_id) references hr.dept(dept_id);
select * from user_constraints where table_name in ('EMP', 'DEPT');
#3) 권한 insa01, insa02 부여 : SELECT + DML 권한
grant select, insert, update, delete on hr.emp to insa01, insa02;
grant select, insert, update, delete on hr.dept to insa01, insa02;
select * from user_tab_privs where grantee in ('INSA01', 'INSA02');
■ 객체 감사
<SYS SESSION>
-- 감사 테이블에 설정
audit select, insert, update, delete on hr.emp;
audit select, insert, update, delete on hr.dept;
SQL> select owner, object_name, object_type, sel, ins, upd, del from dba_obj_audit_opts;
<INSA01 SESSION>
-- 감사 확인하기 위한 쿼리문
select * from user_tab_privs;
select sal from hr.emp where id = 100;
-- test용
insert into hr.emp values(300, 'james', 1000, 10);
update hr.emp
set sal = sal*1.1
where id = 101;
delete from hr.emp where id = 200;
rollback;
<SYS SESSION>
#) INSA01 SQL문 감사
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
<INSA02 SESSION>
SQL> conn insa02/oracle
SQL> show user
SQL> var v_id number
SQL> execute :v_id :=200
SQL> select sal from hr.emp where id = :v_id;
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
<insa02 session>
#) 익명블록 test
declare
v_id number := 400;
v_name varchar2(10) := 'scott';
v_sal number := 1000;
v_dept_id number := 10;
begin
insert into hr.emp(id, name, sal, dept_id)
values (v_id, v_name, v_sal, v_dept_id);
commit;
end;
/
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
USERNAME OWNER OBJ_NAME ACTION_NAME SESS DAY SQL_TEXT SQL_BIND
---------- ---------- --------------- -------------------- --------------- ------------------- ---------------------------------------- ----------
INSA02 HR EMP SESSION REC success 2023-12-27 11:27:08 INSERT INTO HR.EMP(ID, NAME, SAL, DEPT_I #1(3):400
D) VALUES (:B4 , :B3 , :B2 , :B1 ) #2(5):sco
/* 바인드 변수 순서대로 */ tt #3(4):1
000 #4(2):
10
INSA01 HR EMP SESSION REC success 2023-12-27 11:17:29 delete from hr.emp where id = 200
INSA01 HR EMP SESSION REC success 2023-12-27 11:17:28 update hr.emp
set sal = sal*1.1
where id = 101
<SYS SESSION>
- 감사 취소, 권한 회수
noaudit select, insert, update, delete on hr.emp;
noaudit select, insert, update, delete on hr.dept;
select owner, object_name, object_type, sel, ins, upd, del from dba_obj_audit_opts;
truncate table sys.aud$;
revoke all on hr.emp from insa01, insa02;
revoke all on hr.dept from insa01, insa02;
select * from dba_tab_privs where grantee in ('INSA01', 'INSA02');
- 권한을 select any table 로 부여
select * from dba_sys_privs where grantee in ('INSA01', 'INSA02');
grant select any table to insa01, insa02;
select * from dba_sys_privs where grantee in ('INSA01', 'INSA02');
■ 시스템 권한 감사
audit select any table by insa01, insa02;select * from dba_stmt_audit_opts where audit_option = 'SELECT ANY TABLE';
<INSA01 SESSION>
select * from user_sys_privs;
-- 테스트 해보기
/* sys에서 감사에 확인되지 않는다면, 새로운 session에서 실행 */
select * from hr.employees;
select * from hr.departments;
select * from hr.locations;
<INSA02 SESSION>
select * from user_sys_privs;
-- 테스트 해보기
/* sys에서 감사에 확인되지 않는다면, 새로운 session에서 실행 */
select * from hr.countries;
<SYS SESSION>
select username, owner, obj_name, action_name,
decode(returncode, '0', 'success', returncode) sess,
to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
sql_text, sql_bind
from dba_audit_object;
audit create session;
select * from dba_stmt_audit_opts where audit_option = 'CREATE SESSION';
-- session 로그인/로그아웃 확인 가능
SELECT os_username,
username,
to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') day,
action_name,
to_char(logoff_time, 'yyyy-mm-dd hh24:mi:ss') logoff_time
FROM dba_audit_session;
select * FROM dba_audit_session;
LOGOFF_LREAD
- 로지컬하게 읽어낸 블록수(로지컬 I/O)
- 사용자 세션이 로그아웃할 때 해당 세션이 읽은 논리 읽기 횟수
- 논리 읽기(LREAD) : DB 에서 읽은 블록의 수. 캐시에서 읽어온 것이든, 물리 디스크에서 읽어온 것이든 상관없이 읽은 블록의 총 수
LOGOFF_PREAD
- 피지컬 I/O, 사용자 세션이 로그아웃할 떄 해당 세션이 수행한 물리적인 읽기 횟수
- 물리적 읽기(PREAD) : 디스크에서 데이터를 읽어오는 횟수. 디스크 I/O가 발생할 때마다 PREAD가 증가하며, 이는 DB에서 블록을 읽어오는 작
LOGOFF_LWRITE
- WRITE한 수, 세션 로그오프 시에 해당 세션이 기록한 변경 로그(LW)의 수
- DB의 변경 로그에 대한 활동 추적.
- DB session 의 종료 시에 발생한 활동에 대한 정보를 제공하며 변경로그의 양이나 활동이 많을수록 이 값을 통해 해당 세션의 활동을 추척할 수 있다.
#) 감사 취소 및 감사 부여
noaudit create session;
truncate table sys.aud$;
audit create session by insa01;
<sys session>
#) 감사 적용시키
#1) 감사 비활성화
SQL> alter system set audit_trail = none scope = spfile;
#2)DB 내리기
SQL> shutdown immediate
#3) DB 올리기
SQL> startup
#4) 확인하기
SQL> show parameter audit_trail
#5) 'FGA_LOG$' 확인
※ FGA_LOG$ 딕셔너리 테이블을 audit_tab 테이블스페이스로 이관작업
SQL>
select table_name, tablespace_name from dba_tables where table_name = 'FGA_LOG$';
SQL>
select * from sys.fga_log$;
#5-1) 익명블록_이관작업시, 수행할 쿼리문
BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
audit_trail_location_value => 'audit_tbs');
END;
/
#5-2) 이관작업 확인
-- sys.aud$ 딕셔너리 테이블을 system tablespace -> audit tbs tablespace로 이관
select table_name, tablespace_name from dba_tables where table_name = 'FGA_LOG$';
■ Fine Grained Auditing (Oracle DB에서 특정한 행이나 열에 대한 감사를 수행하는 기능)
:= ⓐ DB 내에서 민감한 정보에 접근 또는 수정이 발생할 때 그 이벤트를 기록하고 모니터링하는데 사용
ⓑ DB 보안 및 규정 준수를 강화하기 위해 사용되며, 민감한 데이터에 대한 접근을 추첮하고 검증하는데 유용하다.
- 컨텐츠(:= 조건)를 기준으로 데이터 액세스 모니터
- select, insert, update, delete, merge 를 수행할 때 감사가 돌아간다.
- 테이블, 뷰에 있는 하나 이상의 열에 설정한다.
- dbms_fga 패키지를 사용하여 관리
1) 특정한 이벤트 감사 : 특정한 쿼리문인 SELECT문, UPDATE문, INSERT문, DELETE문 와 같은 DB 작업에 대한 감사 수행
2) 컬럼 또는 핸 수준의 감사 : 특정 테이블의 특정 컬럼 또는 특정 행에 대한 감사를 수행할 수 있다.
ex) 급여 정보나 소셜 보안 번호와 같은 민감한 데이터에 대한 접근을 감사 가능.
3) 세분화된 정책 및 조건 설정 : 특정 사용자, 응용 프로그램, IP 주소, 시간대 등의 조건을 정의하여 감사를 수행할 수 있다.
4) 세부적인 감사 로그 : 감사 이벤트에 대한 자세한 로그를 생성한다. 누가, 언제, 어떤 조건에서 특정 데이처에 접근했는지 등의 정보를 기록한다.
#) fga 생성
begin
dbms_fga.add_policy(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'emp_pol1',
audit_condition => 'department_id = 10',
audit_column => 'salary, commission_pct',
audit_column_opts => dbms_fga.all_columns,
enable => true,
statement_types => 'select, insert, update, delete');
end;
/tip#) 매개변수 해석
1) object_schema => 'hr' : 감사 정책을 덕용할 대상 테이블의 소유자(스키마).
2) object_name => 'employees' : 감사 정책을 적용할 대상 테이블의 이름
3) policy_name => 'emp_pol1' : 추가하는 감사 정책의 이름
4) audit_condition => 'department_id = 10' : 감사 이벤트를 활성화할 때 적용할 조건
5) audit_colums => 'salary, commission_pct' : 감사할 컬럼을 지정
6) audit_colum_opts => 'dbms_fga.all_columns' : 감사할 컬럼에 대한 추가 옵션
- dbms_fga.all_columns : and 조건, 모든 열에 대한 감사
- dbms_fga.any_columns : 기본값, or 조건
- dbms_fga.no_colums : 컬럼에 대한 감시를 수행하지 않음
- dbms_fga.colum_masking : 컬럼 값을 마스킹하여 보호할 필요가 있는 경우 사용
- dbms_fga.colum_data_options : 각 컬럼에 대한 감사를 구체적으로 설정
7) enable => true : 감사 정책을 활성화 여부.
- enable => true : 감사 정책 활성화. 정책이 현재 실행 중이며 감사 이벤트를 기록.
- enable => false : 감사 정책 비활성화. 정책이 현재 실행 중이지 않으며 감사 이벤트를 기록하지 않음.
8) statement_type => 'select, insert, update, delete' : 감사를 수행할 SQL문의 유형 지
#) fga 삭제
begin
dbms_fga.drop_policy(
object_schema => 'hr' ,
object_name => 'employees' ,
policy_name => 'emp_pol1');
end;
/
<SYS SESSION>
select * from sys.fga_log$;
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss' timestamp, db_user, policy_name, sql_text, sql_bind
from dba_fga_audit_trail;- 데이터가 없어 테이블만 조회
<INSA01 SESSION>
-- 감사 check O
select * from hr.employees;
<SYS SESSION>
select * from sys.fga_log$;
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, db_user, policy_name, sql_text, sql_bind
from dba_fga_audit_trail;
<INSA01 SESSION>
test 해보기
select * from hr.employees where department_id = 10; -- 감사 check O
select * from hr.employees where department_id = 20; -- 감사 check X
select salary from hr.employees where department_id = 10; -- 감사 check X
select commission_pct from hr.employees where department_id = 10; -- 감사 check X
select employee_id, salary, commission_pct from hr.employees where department_id = 10; -- 감사 check O
select employee_id, salary from hr.employees where department_id = 10 and commission_pct is not null; -- 감사 check O
<SYS SESSION>
select * from sys.fga_log$;
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, db_user, policy_name, sql_text, sql_bind
from dba_fga_audit_trail;
<INSA01 SESSION>
update hr.employees
set salary = salary * 1.1, commission_pct = 0.1
where department_id = 10; -- 권한 불충분
<SYS SESSION>
- 권한 부여 및 확인
grant select, insert, update, delete on hr.employees to insa01, insa02;
select * from dba_sys_privs where grantee in ('INSA01', 'INSA02');
<INSA01 SESSION>
update hr.employees
set salary = salary * 1.1, commission_pct = 0.1
where department_id = 10;
<SYS SESSION>
- 확인
select * from sys.fga_log$;
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, db_user, policy_name, sql_text, sql_bind
from dba_fga_audit_trail;
select object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail
from dba_audit_policies;
select * from dba_audit_policy_columns where policy_name = 'EMP_POL1';tip) 테이블 및 각 테이블의 컬럼
| 테이블명 | 해석 | 컬럼명 | 해석 |
| sys.fga_log$ | FAG 에 의해 생성된 감사 이벤트 로그를 저장 | SQL_TEXT | 감사된 SQL문 텍스트 |
| SQL_BIND | SQL문의 바인드 변수 값 | ||
| POLICY_NAME | 감사 정책의 이름 | ||
| DB_USER | SQL을 실행한 사용자 | ||
| OBJECT_NAME, OBJECT_OWNER |
감사된 대상 객체의 이름 및 소유자 | ||
| TIMESTAMP | 이벤트가 발생한 시간 | ||
| dba_fga_audit_trail | FGA 에 의해 생성된 감사 이벤트의 요악 정보를 제공하는 VIEW | TIMESTAMP | 이벤트가 발생한 시간 |
| DB_USER | SQL을 실행한 사용자 | ||
| POLICY_NAME | 감사 정책의 이름 | ||
| SQL_TEXT | 감사된 SQL문 텍스트 | ||
| SQL_BIND | SQL문의 바인드 변수 값 | ||
| dba_audit_policies | DB 에 정의된 모든 감사 정책에 대한 정보를 제공 | OBJECT_SCHEMA, OBJECT_NAME, |
감사 정책이 적용된 대상 객체의 스키마 및 이름 |
| POLICY_NAME | 감사 정책의 이름 | ||
| POLICY_TEXT | 감사 정책의 조건 | ||
| POLICY_COLUMN | 감사 정책이 적용된 열의 이름 | ||
| POLICY_COLUMS_OPTIONS | 열에 대한 추가 옵션 | ||
| AUDIT_TRAIL | 감사 트레일 옵션(DB, XML, EXTENDED 등) | ||
| dba_audit_policy_columns | 감사 정책에 적용된 열에 대한 정보를 제공하는 view | OBJECT_SCHEMA, OBJECT_NAME, |
감사 정책이 적용된 대상 객체의 스키마 및 이름 |
| POLICY_NAME | 감사 정책의 이름 | ||
| POLICY_COLUMN | 감사 정책이 적용된 열의 이름 | ||
| ENABLED | 감사 정책이 활성화 유무 | ||
| CONDITION | 열에 적용된 조건 |
# fga 삭제
begin
dbms_fga.drop_policy(
object_schema => 'hr' ,
object_name => 'employees' ,
policy_name => 'emp_pol1');
end;
/
# fga 삭제 확인
select object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail
from dba_audit_policies;
select * from dba_audit_policy_columns where policy_name = 'EMP_POL1';
'Data Base > Linux' 카테고리의 다른 글
| 231228 Linux_감사 정책, 종속성 관계, 컴파일 (0) | 2023.12.28 |
|---|---|
| 231227 Linux_DBA 감사, TRIGGER (1) | 2023.12.27 |
| 231226 Linux_Audit(감사) (0) | 2023.12.26 |
| 231222 Linux_password file 생성, SYSDBA ROLE 인증체크 (1) | 2023.12.22 |
| 231222 Linux_RESOURCE 관리, PROFILE 관리 (0) | 2023.12.22 |