Audit(감사), 간단한 문제

YoonSeo Park ·2023년 12월 26일
0

Oracle Architecture

목록 보기
5/11

2023/12/26


문제


[문제 1]
tablespace 생성하세요.
tablespace 이름 : oltp_tbs
datafile 위치 및 이름 : /u01/app/oracle/oradata/ora11g/oltp_tbs01.dbf
datafile 사이즈 : 10m
datafile 자동 확장 활성화
extent 관리 : local uniform size 1m
segment space management: auto

DROP TABLESPACE oltp_tbs INCLUDING CONTENTS
AND DATAFILES; 

CREATE TABLESPACE oltp_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/oltp_tbs01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

[문제2]
user 생성하세요.
user 이름 : sawon01
user 비밀번호 : oracle
default tablespace : oltp_tbs
temporary tablespace : temp
default tablespace quota : unlimited

user 생성하세요.
user 이름 : sawon02
user 비밀번호 : oracle
default tablespace : oltp_tbs
temporary tablespace : temp
default tablespace quota : 10m

ALTER PROFILE DEFAULT LIMIT
PASSWORD_VERIFY_FUNCTION null;

SELECT * FROM dba_profiles where profile = 'DEFAULT';

CREATE USER sawon01
IDENTIFIED BY oracle
DEFAULT TABLESPACE oltp_tbs
TEMPORARY TABLESPACE temp
QUOTA unlimited ON oltp_tbs;


CREATE USER sawon02
IDENTIFIED BY oracle
DEFAULT TABLESPACE oltp_tbs
TEMPORARY TABLESPACE temp
QUOTA 10m ON oltp_tbs;

[문제3]
SAWON_ROLE 을 생성한 후 SAWON_ROLE에 권한 부여하세요.
시스템 권한 : create session, create table, create view, create sequence, create procedure
객체 권한 : hr.employees에 대한 select, insert, update, delete
hr.departments에 대한 select

create role SAWON_ROLE;

GRANT create session, create table, create view, create sequence, create procedure TO SAWON_ROLE;
select * from dba_sys_privs where grantee='PROG'



GRANT select, insert, update, delete ON hr.employees TO SAWON_ROLE;
GRANT select ON hr.departments TO SAWON_ROLE;

select * from dba_tab_privs where grantee='SAWON_ROLE';

[문제 4] sawon01, sawon02 유저에게 sawon_role 부여하세요.

GRANT SAWON_ROLE TO sawon01;
GRANT SAWON_ROLE TO sawon02;

select * from dba_role_privs where grantee='SAWON01';
select * from dba_role_privs where grantee='SAWON02';

[문제5] sawon01, sawon02 접속해서 시스템 권한 , 객체 권한 확인

[oracle@oracle ~]$ sqlplus sawon1/oracle

SQL> select * from session_roles;

ROLE
------------------------------
SAWON_ROLE

[문제 6]. sawon01 유저는 사원 정보를 저장하기 위한 테이블을 생성 합니다.

테이블 이름 : dept
테이블 저장되는 테이블 스페이스 : oltp_tbs
컬럼 :
dept_id number(3)
dept_name varchar2(30)
제약 조건 :
dept_id 컬럼: primary key, 제약조건 이름 : dept_dept_id_pk

테이블 이름 : emp
테이블 저장되는 테이블 스페이스 : oltp_tbs
컬럼 :
emp_id number(3)
name varchar2(30)
sal number(10)
day date
dept_id number(3)
제약 조건 :
emp_id 컬럼 : primary key, 제약조건 이름 : emp_emp_id_pk
dept_id 컬럼 : foreign key, 제약조건 이름 : emp_dept_id_fk, 참조 : dept테이블에 dept_id

CREATE TABLE dept (
    dept_id NUMBER(3),
    dept_name VARCHAR2(30),
    CONSTRAINT dept_dept_id_pk PRIMARY KEY (dept_id)
) TABLESPACE oltp_tbs;



CREATE TABLE emp (
    emp_id NUMBER(3),
    name VARCHAR2(30),
    sal NUMBER(10),
    day DATE,
    dept_id NUMBER(3),
    CONSTRAINT emp_emp_id_pk PRIMARY KEY (emp_id),
    CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
) TABLESPACE oltp_tbs;

[문제 7] hr.department 테이블에 있는 department_id, department_name 컬럼들의 모든 데이터를 sawon01.dept 테이블로 데이터 로드한 후 영구히 저장하세요. ctas 아님

INSERT INTO dept (dept_id, dept_name)
SELECT department_id, department_name
FROM hr.departments;

[문제 8] hr.employees 테이블에 있는 employee_id, last_name, salary, hire_date, department_id 컬럼들의 모든 데이터를 sawon01.emp 테이블로 로드한 후 영구히 저장하세요.

INSERT INTO emp (emp_id, name, sal, day, dept_id)
SELECT employee_id,last_name, salary, hire_date, department_id
FROM hr.employees;

[문제 9] sawon01 유저의
dept 테이블에 소속 사원이 있는 부서 정보만 출력 하세요.

select *
from dept d
where EXISTS (select 'x' from emp
		where dept_id = d.dept_id);

[문제 10] sawon01 유저의
dept 테이블에 소속 사원이 없는 부서 정보만 출력하세요.

select *
from dept d
where NOT EXISTS (select 'x' from emp
		where dept_id = d.dept_id);

[문제 11] 사원1번
사원들의 사원 번호, 사원 이름, 급여, 부서 이름을 출력하세요.

select e.emp_id, e.name, e.sal, d.dept_name
from emp e, dept d
where e.dept_id = d.dept_id;

[문제 12] 부서이름별로 부서의 총액 급여, 평균급여를 보는 뷰(dept_agg_view)를 생성하세요.

create view dept_agg_view
as
select d.dept_name, e.sum_sal, e.avg_sal
from dept d, (select dept_id, sum(sal) sum_sal, trunc(avg(sal)) avg_sal
                        from emp
                        group by dept_id) e
where d.dept_id = e.dept_id;


-- 다른것

CREATE VIEW dept_agg_view AS
SELECT
    d.dept_id,
    d.dept_name,
    SUM(e.sal) AS total_salary,
    AVG(e.sal) AS avg_salary
FROM
    dept d
    JOIN emp e ON d.dept_id = e.dept_id
GROUP BY
    d.dept_id, d.dept_name;

[문제 13] 생성한 뷰의 정보를 확인하세요.

select * from dept_agg_view;

[문제 14] 사원1에서
사원번호를 입력 값으로 받아서 그 사원의 이름, 급여, 부서 이름을 출력하는 프로시저를 생성하세요.
만약에 없는 사원 번호 값이 들어 오면 "The sawon does not exist."라고 처리해 주세요.
프로시저 이름은 emp_proc

set serveroutput on

execute emp_proc(100)
NAME : King, SAL : 24000, DEPT_NAME: Executive

execute emp_proc(500)
The 사원 does not exist.

CREATE OR REPLACE PROCEDURE emp_proc(
	p_id in number
)
is
    v_name   VARCHAR2(30);
    v_sal    NUMBER(10);
    v_dept_name VARCHAR2(30);
begin

	select e.name, e.sal, d.dept_name
	into v_name,v_sal,v_dept_name
	from emp e, dept d
	where emp_id = p_id
	and e.dept_id = d.dept_id;

	dbms_output.put_line('NAME: ' || v_name || ', SAL: ' || v_sal || ', DEPT_NAME: ' || v_dept_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(p_id||' The sawon does not exist.');
    WHEN OTHERS THEN
        dbms_output.put_line('error: ' || SQLERRM);
end;
/



SQL> execute emp_proc(100)
NAME: King, SAL: 24000, DEPT_NAME: Executive

PL/SQL procedure successfully completed.

SQL> execute emp_proc(500)
500 The sawon does not exist.

PL/SQL procedure successfully completed.

[문제 15] emp_proc 소스를 확인하세요.

 select text from user_source where name = 'EMP_PROC' order by line;

[문제 16] emp_proc 프로시저에 대한 execute 권한을 sawon2에게 부여 해주세요

grant execute on emp_proc to sawon02;

[문제 17] sawon02 유저는 emp_proc 프로시저에 대한 객체 권한을 확인 한 후 실행해 보세요.

[oracle@oracle ~]$ sqlplus sawon02/oracle

SQL> select * from user_tab_privs;


SQL> set serveroutput on

SQL> execute sawon01.emp_proc(100)
NAME: King, SAL: 24000, DEPT_NAME: Executive

PL/SQL procedure successfully completed.

SQL> execute sawon01.emp_proc(500)
500 The sawon does not exist.

PL/SQL procedure successfully completed.

[문제 18] sawon01 유저는 sawon02 유저에게 부여한 emp_proc 프로시저에 대한 객체 권한을 취소해주세요.

[문제 19] sawon_profile 를 생성하세요.
패스워드 3번 실패하면 계정 잠금
패스워드 주기는 30일
패스워드 주기 유예 기간 5일
이전 패스워드를 재 사용하려면 30일 이후에 최소한 한번은 바꿔야 한다.
패스워드에 복잡성 체크 verify_function_11g설정
접속한후 10분동안 아무작업을 수행하지 않으면 자동으로 kill

DROP PROFILE sawon_profile CASCADE;

CREATE PROFILE sawon_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_LIFE_TIME 30
    PASSWORD_GRACE_TIME 5
    PASSWORD_REUSE_TIME	30
    PASSWORD_REUSE_MAX 1
    PASSWORD_VERIFY_FUNCTION verify_function_11G
    IDLE_TIME 10;

alter system set resource_limit=ture;

[문제 20] 프로파일 확인하세요.

SELECT * FROM dba_profiles where profile = 'SAWON_PROFILE';

[문제 21] sawon_profile을 수정하세요.

접속한후 5분동안 아무작업을 수행하지 않으면 자동으로 kill
동일한 유저이름으로 2명만 접속할수 있게 설정

alter profile sawon_profile LIMIT
    IDLE_TIME 5
    SESSIONS_PER_USER 2;

[문제 22] sawon_profile을 sawon01, sawon02 유저에게 설정하세요.

ALTER USER sawon01 PROFILE sawon_profile;
ALTER USER sawon01 PROFILE sawon_profile;

[문제 23] sawon_profile을 삭제한 후 sawon01, sawon02 유저의 프로파일을 확인 하세요.

DROP PROFILE sawon_profile CASCADE;


■ Audit (감사)

: user db 작업을 모니터하는것이다.

  • 의심이 가는 db 작업을 조사
  • 특정 db 작업에 대한 정보를 수집할 경우

▶ 1. 감사 활성화

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

select * from v$parameter where name = 'audit_trail';
  • 감사 비활성화
alter system set audit_trail = none scope = spfile;
  • 감사 활성화 (옵션 db : 문장 그대로가 보이지않고 DROP TABLE 이런식으로 무슨 작업을 한지만 나온다.)
alter system set audit_trail = db scope = spfile;
  • sys.aud$ 안에 있는 내용을 관리해야 한다.
select * from sys.aud$;

▶ 2. 감사 옵션 설정, 감사 해제 삭제

● 문장 감사

sql문을 선택저으로 감사

  • table 감사 옵션
    : audit table은 create table, drop table, truncate table 수행하는 감사
audit table;
  • SQL문 성공, 실패 해도 BY ACCESS 한다는 뜻이다.
select * from dba_stmt_audit_opts where audit_option = 'TABLE';

[sawon02 session]

SQL> conn sawon02/oracle
Connected.

SQL>
CREATE TABLE sawon02.emp
AS 
SELECT * FROM hr.employees;

[sys session]

select * from sys.aud$;
  • 감사 옵션 해제후, 삭제
noaudit table;
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
truncate table sys.aud$;

● 감사 audit table 시나리오

[sys session]

audit table;
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
select * from sys.aud$;

[sawon02 session]

SQL> drop table emp purge;

Table dropped.

SQL> create table sawon02.emp_copy
as select * from hr.employees;  2

Table created.

[sys session]

select * from sys.aud$;

  • 감사 조회
select * from dba_audit_object;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;

스키마 관리전 샘플 테이블 생성

[hr session]

SQL> conn hr/hr

drop table hr.dept cascade constraints purge;
drop table hr.emp cascade constraints purge;


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;

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_fk foreign key(dept_id) references hr.dept(dept_id);

select * from user_constraints where table_name in ('EMP','DEPT');

grant select, insert, update, delete on hr.emp to sawon01, sawon02;
grant select, insert, update, delete on hr.dept to sawon01, sawon02;

select * from user_tab_privs where grantee in ('SAWON01','SAWON02');

▶ 스키마(schema) object audit

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

● 스키마 오브젝트 감사 시나리오

[sys session]

스키마 객체 감사 설정

audit select, insert, update, delete on hr.emp;
audit select, insert, update, delete on hr.dept;
  • 스키마 오브젝트 감사 확인
select owner, object_name, object_type, sel, ins, upd, del
from dba_obj_audit_opts;

[sawon01 session]

[oracle@oracle ~]$ sqlplus sawon01/oracle
SQL> select sal from hr.emp where id = 100;

       SAL
----------
     24000
       

[sys session]

select * from dba_audit_object;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;

[sawon01 session]

SQL> insert into hr.emp(id, name, sal, dept_id) values(300,'james',1000,10);

[sys session]

select * from sys.aud$;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;

[sawon02 session]

SQL> conn sawon02/oracle
Connected.
SQL> select sal from hr.emp where id =300;

no rows selected

SQL> insert into hr.emp(id, name, sal,dept_id) values(400,'scott',1000,500); -- 오류가 난것도 감사에 걸린다.
insert into hr.emp(id, name, sal,dept_id) values(400,'scott',1000,500)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPTID_FK) violated - parent key not
found

[sys session]

select * from sys.aud$;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;

스키마 객체 감사 해제

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$;

[hr session]

  • 권한 모두 회수
SQL> revoke all on hr.emp from sawon01, sawon02;

Revoke succeeded.

SQL> revoke all on hr.dept from sawon01, sawon02;

Revoke succeeded.

SQL> select * from user_tab_privs where grantee in ('SAWON01','SAWON02');

no rows selected

▶ 권한(privilege) audit

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

● 스키마 오브젝트 감사 시나리오 (audit_trail=db)

[sys session]

  • db 안에 모든 테이블 select 할 권한 부여
grant select any table to sawon01,sawon02;
  • 권한 보기
select * from dba_sys_privs where grantee in ('SAWON01','SAWON02');
  • 권한 감시 설정
    : select any table 권한을 가진곳은 전부 조회하겠다.
audit select any table;
  • 특정 유저만 세션 권한 감시 설정
audit select any table by sawon01,sawon02 by session;
  • 확인
select * from dba_stmt_audit_opts;

select * from dba_stmt_audit_opts
where audit_option = 'SELECT ANY TABLE';

[sawon01 session]

SQL> conn sawon01/oracle
Connected.
SQL> select * from hr.locations;
SQL> select * from dept;

[sys session]

select * from dba_audit_object;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;

● 스키마 오브젝트 감사 시나리오 (audit_trail=db_extended)

SQL> show user
USER is "SYS"
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
  • audit_trail=db_extended
    : audit_trail=db_extended 유저가 실행한 SQL 텍트스 및 변수 값 감사
SQL> alter system set audit_trail=db_extended scope=spfile;
System altered.

[sys session]

audit select on hr.employees;
grant select, insert, update, delete on hr.emp to sawon01, sawon02;
grant select, insert, update, delete on hr.dept to sawon01, sawon02;

[sawon01 session]

var v_id number

execute :v_id := 100
print :v_id
select salary from hr.employees where employee_id = :v_id;

[sys session]

noaudit select on hr.employees;

audit select any table by sawon01,sawon02 by session;
select * from dba_stmt_audit_opts
where audit_option = 'SELECT ANY TABLE';

[sawon01 session]

select * from hr.locations;

[sawon02 session]

select * from hr.departments;

[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_trail;

객체 권한을 먼저 아까 주었으므로

grant select any table to sawon01,sawon02;

객체 권한이 있는 경우는 객체권한을 먼저 받았으므로
객체 권한 때문에 그 테이블을 select 하므로
감사에 걸리지않는다.


  • 시스템 권한 감사 취소 (audit을 설정할때와 다르게 by session 빼준다.)
noaudit select any table by sawon01,sawon02;

오라클 11g 버그
: select any table의 권한을 유저한테 주고
select any table의 audit 설정을 하면 잘보이는데
select any table의 noaudit 설정을 하여도 계속 감사에 걸린다. (-> audit_trail=none 으로 변경후 DB껏다키고 다시 audit_trail=db_attended 로 바꿔준다)

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;
  • 시나리오
[sys session]
grant select any table to sawon01,sawon02;
truncate table sys.aud$;
NOAUDIT SELECT ANY TABLE BY sawon01;
NOAUDIT SELECT TABLE BY sawon01;
[sawon01 session]
select * from hr.locations;
select * from hr.employees;
[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
from dba_audit_trail;

2023/12/27


  • 유저 확인
select * from dba_users;
drop user insa01 cascade;
drop user insa02 cascade;
  • 유저 생성
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');
  • 권한부여
grant create session, create table, unlimited tablespace to insa01, insa02;
  • unlimited tablespace:
    : db내에 모든스페이스를 사용할수 있는 권한

select * from dba_sys_privs where grantee in ('INSA01','INSA02');

▶ sys.aud$ 딕셔너리 테이블을 새로운 테이블스페이스로 이관작업

● sys.aud$ 딕셔너리 테이블을 system 테이블스페이스에서 audit_tbs 테이블스페이스로 이관 시나리오

  • AUD$ 테이블스페이스 위치 확인
select table_name, tablespace_name from dba_tables where table_name ='AUD$';

select * from sys.aud$;
  • 테이블스페이스 생성
CREATE TABLESPACE audit_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/audit_tbs01.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
  • 테이블스페이스, 데이터파일 확인
select * from dba_tablespaces;
select * from dba_data_files;
  • aud$의 기존 테이블스페이스에서 지정한 테이블 스페이스로 변경 (pl/sql)
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;
/
  • AUD$ 테이블스페이스 위치 변경됐는지 확인
select table_name, tablespace_name from dba_tables where table_name ='AUD$';

▶ 감사 활성화/ 비활성화

  • 감사 타입 확인
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
  • 감사 비활성화 (scope=spfile 이니깐 db껏다 켜야한다.)
SQL> alter system set audit_trail=none scope=spfile;
  • 감사 활성화 (db_extended)
SQL> alter system set audit_trail=db_extended scope=spfile;

▶ SQL문 감사

[sys session]

  • audit table (db 레벨에서)
audit table;

SQL> select * from dba_stmt_audit_opts where audit_option = 'TABLE';

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------

TABLE                                    BY ACCESS  BY ACCESS

[insa01 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;

[sys session]

  • 감사 확인
select * from sys.aud$;

select username, owner, obj_name, action_name,
    decode(returncode,'0','success',returncode) sess,
    to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') day
from dba_audit_trail;
  • noaudit table (세션을 다시열어야 작동한다.)
noaudit table
  • 특정 유저한테만 audit table
audit table by insa02;
  • audit 확인
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
  • audit 기록 지우기
truncate table sys.aud$;

[insa01 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_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 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_trail;
  • noaudit table
noaudit table by insa02;
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
truncate table sys.aud$;

▶ 객체 감사

[hr session]

drop table hr.dept cascade constraints purge;
drop table hr.emp cascade constraints purge;


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;

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_fk foreign key(dept_id) references hr.dept(dept_id);

select * from user_constraints where table_name in ('EMP','DEPT');

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;
  • 확인
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;
insert into hr.emp(id,name,sal,dept_id) 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]

  • 확인
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_trail;

[insa02 session]

SQL> conn insa02/oracle
Connected.
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_trail;

[insa02 session]

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_trail;
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$;

▶ 시스템 권한 감사

[sys session]

  • select any table 권한주기
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_tab_privs;
select * from user_sys_privs;
select * from hr.employees;
select * from hr.departments;
select * from hr.locations;

[insa02 session]

  • 작업
select * from user_tab_privs;
select * from user_sys_privs;
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_trail;
  • 감사 audit create session
    : session이 create 될때마다 로그가 쌓인다.
audit create session;
  • 확인
select * from dba_stmt_audit_opts where audit_option = 'CREATE SESSION';
  • audit 확인
select * from dba_audit_session;
select os_username, username, to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') logon_time
    ,action_name, to_char(logoff_time,'yyyy/mm/dd hh24:mi:ss') logoff_time
from dba_audit_session;
  • 논리적 I/O
    : 디스크에서 메모리에 올려놓고
    메모리에서 데이터를 읽거나 쓰는 것을 의미합니다.

  • 피지컬 I/O
    메모리에서 디스크를 올려놓는것
    디스크와 같은 물리적인 저장 장치에 데이터를 읽거나 쓰는 것을 의미합니다.

  • noaudit, 특정유저만 audit

truncate table sys.aud$;
noaudit create session;
audit create session by insa01;

▶ fga_log$ 딕셔너리 테이블을 새로운 테이블스페이스로 이관작업

● 시나리오

[sys session]

  • audit 비활성화
alter system set audit_trail=none scope=spfile;
  • fga_log$ 테이블스페이스 확인
select table_name, tablespace_name from dba_tables where table_name ='FGA_LOG$';
select * from sys.fga_log$;
  • fga_log$의 테이블스페이스 이관(변경)
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;
/
  • 이관됐는지 fga_log$ 테이블스페이스 확인
select table_name, tablespace_name from dba_tables where table_name ='FGA_LOG$';

▶ Fine Grained Auditing

: 컨텐츠를 기준으로 데이터 엑세스 모니터

  • select, insert, update, delete, merge 를 수행할때 감사
  • 테이블 , 뷰에 있는 하나 이상의 열에 설정한다.
  • dbms_fga 패키지를 사용하여 관리
  • audit 정책 생성
begin
    dbms_fga.add_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'emp_pol1',
            -- 특정한 컬럼을 감사할때 Fine Grained Auditing
            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;
/

audit_condition => 'department_id=10'
: department_id=10 에 대해서 조작한것만 감사
.
audit_column => 'salary,commission_pct'
audit_column_opts => dbms_fga.all_columns
: and -> sal and comm 둘다 조작할때 감사
audit_column_opts => dbms_fga.any_columns
: 기본값, or -> sal or comm 둘중 하나라도 조작할때 감사

  • audit 정책 확인
select
    object_schema, object_name, policy_name, policy_text,policy_column,
    sel,ins,upd,del,policy_column_options, audit_trail
from dba_audit_policies;
  • audit 정책 삭제
begin
    dbms_fga.drop_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'emp_pol1'
            );
end;
/

[insa01 session]

  • 작업
select * from hr.employees;

select * from hr.employees where department_id =10;
select * from hr.employees where department_id =20;
select salary from hr.employees where department_id =10;
select commission_pct from hr.employees where department_id =10;

select employee_id, salary, commission_pct
from hr.employees where department_id =10;

select employee_id,salary
from hr.employees
where department_id=10
and commission_pct is not null;

update hr.employees
set salary = salary * 1.1, commission_pct=0.1
where department_id = 10;

select *
from hr.employees
where employee_id =200;

[sys session]

  • fga 감사 확인
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;
2023/12/23 07:27:49	INSA01	EMP_POL1	select * from hr.employees
2023/12/23 07:31:23	INSA01	EMP_POL1	select * from hr.employees where department_id =10
2023/12/23 07:31:27	INSA01	EMP_POL1	"select employee_id, salary, commission_pct
from hr.employees where department_id =10"
2023/12/23 07:32:11	INSA01	EMP_POL1	"select employee_id,salary
from hr.employees
where department_id=10
and commission_pct is not null"
2023/12/23 07:34:56	INSA01	EMP_POL1	"update hr.employees
set salary = salary * 1.1, commission_pct=0.1
where department_id = 10"
2023/12/23 07:51:12	INSA01	EMP_POL1	"select *
from hr.employees
where employee_id =200"
  • audit정책 삭제, 확인
begin
    dbms_fga.drop_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'emp_pol1'
            );
end;
/


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';

select * from sys.fga_log$;
truncate table sys.fga_log$;

▶ DBA 감사

SQL>  show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ora11g/a
                                                 dump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
  • audit_file_dest
    : sys의 이력정보 데이터가 있는곳
  • audit_sys_operations
    : sys의 이력정보를 감사할것인지 아닌지 false,true
  • 확인 ( static 파라미터다.)
select * from v$parameter where name='audit_sys_operations';
  • audit_sys_operations=true , 따라서 scope=spfile, db껏다키기(운영중에는 힘들다.)
alter system set audit_sys_operations=true scope=spfile;
  • sys의 이력정보를 볼수있다.
[oracle@oracle adump]$ pwd
/u01/app/oracle/admin/ora11g/adump
[oracle@oracle adump]$ ls -alt
[oracle@oracle adump]$ vi ora11g_ora_24970_20231223081054808580143795.aud
  • audit_sys_operations=false
alter system set audit_sys_operations=false scope=spfile;

[sys session]

  • 테이블 생성
create table drop_obj_log(
    obj_user varchar2(50),
    obj_name varchar2(30),
    obj_type varchar2(30),
    drop_time timestamp
    )
tablespace audit_tbs;
  • DDL 트리거 생성
create or replace trigger drop_obj_trigger
before drop on database
begin
    insert into drop_obj_log(obj_user,obj_name,obj_type,drop_time)
    values(user, ora_dict_obj_name,ora_dict_obj_type,systimestamp); -- 함수로 작성
end;
/

만약 [hr session] DROP TABLE hr.emp PURGE
user: hr
ora_dict_obj_name: emp
ora_dict_obj_type: table
systimestamp : 트리거 시간

  • 트리거 확인
select * from dba_triggers
where trigger_name = 'DROP_OBJ_TRIGGER';

[hr session]

  • 작업
CREATE TABLE hr.emp_10
as
select * from hr.employees where department_id=10;
drop table hr.emp_10 PURGE

create view hr.emp_30
as
select * from hr.employees where department_id=30;
drop view hr.emp_30;

create sequence id_seq;
drop sequence id_seq;

[sys session]

  • 작업
create user ora10 identified by oracle;
drop user ora10;
  • 트리거 걸린 테이블조회
SELECT * FROM drop_obj_log;

  • 트리거 삭제
drop trigger drop_obj_trigger
  • DROP, TRUNCATE 사용못하게 만드는 트리거 생성 (DB레벨, 논리적으로 db레벨에서 drop을 막는것은 허용하지 않는다.-> 스키마 레벨에서 막는게 일반적이다.)
create or replace trigger no_drop_truncate
before drop or truncate on database
begin
    raise_application_error(-20001,'DROP or TRUNCATE 할수없음');
end;
/
  • 트리거 삭제
drop trigger no_drop_truncate;
  • DROP, TRUNCATE 사용못하게 만드는 트리거 생성 (schema레벨)
create or replace trigger hr_no_drop_truncate
before drop or truncate on hr.schema
begin
    raise_application_error(-20001,'DROP or TRUNCATE 할수없음');
end;
/
  • 트리거 삭제
drop trigger hr_no_drop_truncate;

2023/12/28


문제


[문제 1] default profile을 확인 한후 password_verify_function 값을 null 수정하세요.


SQL>  show parameter audit_trail

alter system set audit_trail = none scope = spfile;

[문제 2] 유저를 생성한 후 유저의 정보를 확인 하세요.

user 이름 : happy
user 비밀번호 : oracle
default tablespace : users
temporary tablespace : temp
default tablespace quota : 1m


CREATE USER happy
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 1m ON users;

[문제 3] happy 유저에게 create session 권한 부여한 후 부여한 권한정보 확인하세요.


grant create session to happy;
select * from dba_sys_privs where grantee='HAPPY';

[문제 4] happy 유저에게 hr.employees 테이블에 대한 select, update(salary) 권한을 부여한 후 부여한 권한정보 확인하세요.


grant select, update(salary) on hr.employees to happy; -- 특정 컬럼만 업데이트 권한 부여
select * from dba_tab_privs where grantee='HAPPY';
select * from dba_col_privs where grantee='HAPPY';

revoke select,update on hr.employees from happy;
-- 특정컬럼 업데이트 권한 회수 못한다.

[문제 5] 감사 정보가 저장되어 있는 aud$를 truncate 하신후 hr.employees 테이블을 select를 수행할때 마다 감사 정보가 생성되도록 감사를 설정하세요.
감사는 audit_trail를 이용하시고 sql문장도 감사정보에 생성되도록 하세요. 감사 정보를 확인하세요.


alter system set audit_trail = db_extended scope = spfile;

truncate table sys.aud$;
audit select on hr.employees;

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_trail;

[문제 6] happy로 접속한 후 dba로 받은 시스템 권한과 객체 권한 정보를 확인 하세요.


select * from user_tab_privs;
select * from user_sys_privs;
select * from user_col_privs;
select * from session_roles;

[문제 7] happy유저는 hr.employees의 데이터 중에 2007년 이후에 입사한 사원들 중에 job_id가 ST_CLERK인 사원들의 모든 정보를 출력하세요.


[happy session]

select *
from hr.employees
where hire_date >= to_date('2007/01/01','yyyy/mm/dd')
and job_id = 'ST_CLERK';

[문제 8] 감사정보를 확인 하세요.


[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_trail;

[문제 9] 5번에서 생성한 객체 권한 감사 삭제 하고 감사는 비활성화 하세요.

truncate table sys.aud$;
noaudit select on hr.employees;

[문제 10] hr.employees에 있는 데이터 중에 100번 사원의 급여 정보에 대해 select문을 수행하면 감사 정책의 정보가 생성하도록 fga정책을 설정하려고 합니다.
정책이름은 fga_emp_select 이름으로 생성한 후 설정된 정보 확인 하세요.


begin
     dbms_fga.add_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'fga_emp_select',
            audit_condition => 'employee_id=100',
            audit_column => 'salary',
            enable => true,
            statement_types => 'select'
            );
end;
/

select
    object_schema, object_name, policy_name, policy_text,policy_column,
    sel,ins,upd,del,policy_column_options, audit_trail, enabled
from dba_audit_policies;
  • 정책 비활성화
begin
     dbms_fga.disable_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'fga_emp_select'
            );
end;
/
  • 정책 활성화
begin
     dbms_fga.enable_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'fga_emp_select'
            );
end;
/
select
    object_schema, object_name, policy_name, policy_text,policy_column,
    sel,ins,upd,del,policy_column_options, audit_trail, enabled
from dba_audit_policies;

[문제 11] happy로 접속하셔서 100번 사원의 급여 정보를 조회 하세요.


select salary
from hr.employees
where employee_id = 100;

[문제 12] fga정책의 생성된 정보를 확인하세요.


select to_char(timestamp, 'yyyy/mm/dd hh24:mi:ss') timestamp
    ,db_user,policy_name, sql_text, sql_bind
from dba_fga_audit_trail;

[문제 13] fga_emp_select 정책을 삭제 하세요.


begin
    dbms_fga.drop_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'fga_emp_select'
            );
end;
/

[문제 14] audit trail를 유지 관리 해야합니다. aud,fgalog, fga_log truncate 하세요.


delete from sys.fga_log$ where ntimestamp# < '23/12/28';
-- 특정시간 전 fga.log정보 지우기

truncate table sys.aud$;
select * from sys.fga_log$;
truncate table sys.fga_log$;

[문제 15]. hr.employees 급여 정보를 수정할때 마다 값 기준의 감사 정보가 생성해야 합니다.

SQL> CREATE TABLE hr.audit_emp (
user_name VARCHAR2(30),
time_stamp timestamp,
id NUMBER(6),
old_salary NUMBER,
new_salary NUMBER );


CREATE OR REPLACE TRIGGER update_obj_trigger
AFTER
UPDATE ON hr.employees
FOR EACH ROW
BEGIN
   INSERT INTO hr.audit_emp(user_name, time_stamp, id, old_salary, new_salary)
   VALUES(user, systimestamp, :old.employee_id, :old.salary, :new.salary);
END;
/


select * from dba_triggers;
select * from dba_source;

[문제 16] happy 유저는 hr.employees테이블의 데이터중에 200 번 사원의 급여를 기존 급여를 10%인상 한 후 영구히 저장하세요.


update hr.employees
set salary = salary * 1.1
where employee_id = 200;

commit;

[문제 17] 값의 감사 정보를 확인 하세요.


SELECT * FROM hr.audit_emp;

▶ fga 정책으로 프로시저 실행

[sys]

  • 테이블 생성
create table hr.fga_log(
    user_name varchar2(30),
    time_stamp timestamp,
    user_sql varchar2(100));
  • 프로시저 생성
create or replace procedure hr.fga_proc
(object_schema varchar2, object_name varchar2, policy_name varchar2)
is
    pragma autonomous_transaction; -- 프로시저 안에서만 트랜잭션 처리
begin
    insert into hr.fga_log(user_name, time_stamp, user_sql)
    values(sys_context('userenv','session_user'),systimestamp,
    sys_context('userenv','current_sql'));

    commit;    
end;
/
  • sys_context('userenv','session_user')
    : 현재세션 유저이름
  • sys_context('userenv','current_sql')
    : sql문
  • 프로시저 확인
select * from dba_source where owner= 'HR' and name = 'FGA_PROC';
  • fga정책 생성 (정책에 걸리면 프로시저 실행)
begin
     dbms_fga.add_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'fga_emp_log',
            audit_condition => 'employee_id=100',
            audit_column => 'salary',
            enable => true,
            statement_types => 'select',
            handler_schema => 'hr',
            handler_module => 'fga_proc'
            );
end;
/
  • handler_schema
    : 프로시저 사용자
  • handler_module
    : 프로시저 이름
  • 정책확인
select * from dba_audit_policies;

[happy session]

select salary
from hr.employees
where employee_id = 100;
  • 확인
select * from hr.fga_log;

▶ 종속성 관계

[hr session]

  • 함수 생성
create or replace function validate_comm(v_c in number)
return boolean
is
    v_max_comm number;
begin
    select max(commission_pct) into v_max_comm from hr.employees;
    
    if v_c > v_max_comm then
        return FALSE;
    else
        return TRUE;
    end if;

end validate_comm;
/
  • 함수확인
select text from user_source where name ='VALIDATE_COMM' order by line;
  • 프로시저 생성
create or replace procedure reset_comm(v_comm in number)
is
    g_comm number := 0.1;
begin
    if validate_comm(v_comm) then
        dbms_output.put_line('OLD : '||g_comm);
        g_comm := v_comm;
        dbms_output.put_line('now : '||g_comm);
    else
        raise_application_error(-20200,'Invalid commission');
    end if;
end;
/
  • 프로시저 확인
select text from user_source where name ='RESET_COMM' order by line;

종속성 관계
reset_comm -> validate_comm -> employees(commission_pct)

  • 따라서 employees(commission_pct)가 문제가 생기거나 사이즈, 타입등이 변경되면 reset_comm,validate_comm 는 실행 불가능한 상태로 된다.
  • 내가 참조하는 객체가 구조, 컬럼의 타입이 변경되면 나는 실행 불가능한 상태가 된다.
  • HR 의 스키마들 조회
select object_name, object_type, status
from dba_objects
where owner = 'HR';
  • INVALID 상태인 스키마들 조회 (찾아서 컴파일 해줘야한다.)
select *
from dba_objects
where status = 'INVALID';
  • 참조하는 오브젝트 조회
select *
from dba_dependencies
where referenced_name in ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');

  • NAME
    : 참조를 당하는객체이름
  • REFERENCED_OWNER
    :참조를 하는객체이름
  • hr.employees의 commission_pct 사이즈 변경
alter table hr.employees modify commission_pct number(3,2);
  • 확인
select object_name, object_type, status
from dba_objects
where owner = 'HR'
and object_name in ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');

[hr session]

  • 실행
execute reset_comm(0.3)

parse 단계에서 sematic 체크할때
오브젝트 타입을 보고, 그다음에 status 를 확인하고
만약 INVALID 이면 내부적으로 RE-컴파일 해서 VALID 상태로 자동으로 만들어준다.
하지만, 종속의 관계가 깊으면 깊을수록 시간이 오래걸리므로 INVVALID 상태인것은 확인하고 컴파일 해줘야한다.

  • VALID 상태가 되도록 컴파일 해준다.
alter function hr.validate_comm compile;
alter procedure hr.reset_comm compile;
  • 스크립트를 만들어놓는다
    ● 프로시저,함수,트리거
select 'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
from dba_objects
where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE')
and status ='INVALID';

● 패키지

select 'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
from dba_objects
where object_type = 'PACKAGE BODY'
and status ='INVALID';
  • alter 실패한 쿼리의 이유는 참조하는 오브젝트가 사라지거나 변경되었을때이다. 따라서
SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql

스크립트를 실행하고
일일히 확인해줘야한다.

desc sys.deptree_fill
execute sys.deptree_fill('table','hr','employees')
select * from sys.deptree;

  • 0 <- 1 참조하는것이고
    2,3... 은 그 앞을 참조하는 트리구조형식이다.
profile
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)

0개의 댓글