231228 Linux 복습 겸 문제

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

#1) default profile 확인

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT';

#2) password_verify_function값 null 수정

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION NULL;

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

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

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

 

#1) user 정보 확인 

SELECT * FROM dba_users WHERE username = 'HAPPY';

#2) quotas 확인

select * from dba_ts_quotas where username = 'HAPPY';

 

3. happy 유저에게 create session 권한 부여한 후 부여한 권한정보 확인하세요.
#1) 권한 부여 : create session 

GRANT CREATE SESSION TO happy;

#2) 권한 확인

SELECT * FROM dba_sys_privs WHERE grantee = 'HAPPY';

tip#) 권한 회수 

REVOKE CREATE SESSION FROM happy;

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

#1) 권한 부여하되, update일때 salary 에 권한 부여.

GRANT SELECT, UPDATE(salary) ON hr.employees TO happy;

 

#2) 권한 확인 

SELECT * FROM dba_tab_privs WHERE grantee = 'HAPPY';

#3) 컬럼에 대한 권한 확인

SELECT * FROM dba_col_privs WHERE grantee = 'HAPPY';

tip#) 권한 회수 

REVOKE SELECT, UPDATE ON hr.employees FROM happy; --권한 회수

--오류발생, update에 대한 컬럼을 따로 지정해주지 않고 update만 작성해서 권한 회수
REVOKE SELECT, UPDATE(salary) ON hr.employees FROM happy; 
/* 오류 보고 -
ORA-01750: UPDATE/REFERENCE 는 열에 의해서가 아닌 테이블 전체로부터 REVOKE 될 수 있습니다*/

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

#1) 정보 삭제

TRUNCATE TABLE sys.aud$;

#2) 현재 audit_trail 확인

show parameter audit_trail

#3) audit_trail 값 변경

alter system set audit_trail = db_extende scope=spfile;

#4) DB 내렸다가 올리기

#5) audit_trail 확인

show parameter audit_trail

#6) 감사 설정

AUDIT SELECT ON hr.employees;

#7) aud$ 확인 

SELECT * FROM sys.aud$;

#8) dba_obj_audit_opts 확인

SELECT owner, object_name, object_type, sel, ins, upd, del FROM dba_obj_audit_opts;

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

select * from user_sys_privs; -- 시스템권한
select * from user_tab_privs; -- 객체권한
select * from user_col_privs; -- 테이블의 컬럼 권한

시스템 권한

객체 권한

테이블(컬럼)에 대한 권한

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

SELECT * 
FROM hr.employees
WHERE hire_date >= TO_DATE('2007-01-01', 'yyyy-mm-dd') 
AND job_id = 'ST_CLERK';

 

8. 감사정보를 확인 하세요.

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;

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

#1) 객체 권한 회수

noaudit select on hr.employees;

#2) 감사 권한 변

ALTER SYSTEM SET audit_trail = none scope=spfile;

#3) DB 내렸다가 올리기

#4) 확인

show parameter audit_trail

#5) 확인

select * from sys.aud$;

 

#6) TRUNCATE 하기

TRUNCATE TABLE sys.aud$;

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

#1) 정책 생성

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema     => 'hr',
    object_name       => 'employees',
    policy_name       => 'fga_emp_select',
    audit_condition   => 'employee_id = 100',
    audit_column     => 'salary',
    audit_column_opts => dbms_fga.any_columns , --기본값
    enable            => TRUE, --정책은 추가하되, 돌아가지 않는다.
    statement_types   => 'SELECT'
  );
END;
/

 

#2) 생성된 정책 확인

SELECT
    object_schema,
    object_name,
    policy_name,
    policy_text,
    policy_column,
    sel,
    ins,
    upd,
    del,
    policy_column_options,
    audit_trail
FROM dba_audit_policies;

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

#1) 일반적인 select 문

SELECT salary
FROM hr.employees
WHERE employee_id = 100;

SELECT salary
FROM hr.employees
WHERE employee_id = 200;

#2) 바인드 변수로 select문

var v_id number
execute :v_id :=100
SELECT salary
FROM hr.employees
WHERE employee_id = :v_id;

#3) python 으로 select문 확인

import cx_Oracle 

conn = cx_Oracle.connect ("happy", "oracle", "192.168.56.108:1521/ora11g", encoding="UTF-8")

cursor = conn.cursor()

cursor.execute("select employee_id, salary from hr.employees where employee_id = :b_id,(100,))")
cursor.fetchone()
conn.close()

 

#) SELECT문 수행 확인

<SYS SESSION>

SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, db_user, policy_name, sql_text, sql_bind
FROM dba_fga_audit_trail;

TIP#) 어떤 언어로 어디 프로그램에서 수행했는지 확인하는 방법

SELECT * FROM v$session WHERE username = 'HAPPY';

 

12. fga정책의 생성된 정보를 확인하세요.
#) 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$, fga_log$ trucate 하세요.

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 * FROM SYS.fga_log$;

 

#) 정책에 필요없는 fga_log 정보 지우기

DELETE FROM SYS.fga_log$ WHERE ntimestamp# < '23/12/28';

SELECT * FROM SYS.fga_log$;
COMMIT;

 

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

#) 내용 지우기

TRUNCATE TABLE sys.aud$;
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 );

 

#1) 트리거 생성(내가 작성한 코드)

CREATE OR REPLACE TRIGGER emp_sal_audit
BEFORE UPDATE ON hr.employees
FOR EACH ROW
declare
    v_old_sal number;
BEGIN
    v_old_sal := :old.salary;
    
    if :new.salary != :old.salay then
       insert into hr.audit_emp (user_name, time_stamp, id, old_salary, new_salary)
       values(user, systimestamp, :old.employee_id, v_old_salary, :new.salary);
    end if;
END;
/

#1) 트리거 생성 (강사님 코드)

CREATE OR REPLACE TRIGGER hr.audit_emp_values
BEFORE UPDATE OF salary 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 where owner = 'HR';

select * from dba_source where owner = 'HR';

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;