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;
: user db 작업을 모니터하는것이다.
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;
select * from sys.aud$;
sql문을 선택저으로 감사
- table 감사 옵션
: audit table은 create table, drop table, truncate table 수행하는 감사audit table;
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$;
[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');
: 특정한 유저가 소유한 테이블에 대해서 감사 설정
[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
: 시스템 권한을 선택적으로 감사
[sys session]
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;
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 하므로
감사에 걸리지않는다.
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');
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;
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;
/
select table_name, tablespace_name from dba_tables where table_name ='AUD$';
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL> alter system set audit_trail=none scope=spfile;
SQL> alter system set audit_trail=db_extended scope=spfile;
[sys session]
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
audit table by insa02;
select * from dba_stmt_audit_opts where audit_option = 'TABLE';
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 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]
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;
select * from dba_stmt_audit_opts where audit_option = 'CREATE SESSION';
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;
[sys session]
alter system set audit_trail=none scope=spfile;
select table_name, tablespace_name from dba_tables where table_name ='FGA_LOG$';
select * from sys.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;
/
select table_name, tablespace_name from dba_tables where table_name ='FGA_LOG$';
: 컨텐츠를 기준으로 데이터 엑세스 모니터
- 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 둘중 하나라도 조작할때 감사
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]
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"
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$;
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
select * from v$parameter where name='audit_sys_operations';
alter system set audit_sys_operations=true scope=spfile;
[oracle@oracle adump]$ pwd
/u01/app/oracle/admin/ora11g/adump
[oracle@oracle adump]$ ls -alt
[oracle@oracle adump]$ vi ora11g_ora_24970_20231223081054808580143795.aud
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 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;
[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';
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 는 실행 불가능한 상태로 된다.
- 내가 참조하는 객체가 구조, 컬럼의 타입이 변경되면 나는 실행 불가능한 상태가 된다.
select object_name, object_type, status
from dba_objects
where owner = 'HR';
select *
from dba_objects
where status = 'INVALID';
select *
from dba_dependencies
where referenced_name in ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');
- NAME
: 참조를 당하는객체이름- REFERENCED_OWNER
:참조를 하는객체이름
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 상태인것은 확인하고 컴파일 해줘야한다.
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';
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... 은 그 앞을 참조하는 트리구조형식이다.