oracle audit

oracle audit

1. sys login

C:\>sqlplus sys

2. 상태확인

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

3. AUDIT_TRAIL 설정

SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
System altered.

4. DB restart

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

5. 상태확인

SQL> show parameter audit_trail;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

6. AUDIT 설정

audit execute on SCOTT.pr_test by access;

noaudit execute on SCOTT.pr_test by access;

AUDIT DROP ANY PROCEDURE BY ACCESS;
AUDIT ALTER ANY PROCEDURE BY ACCESS;
AUDIT EXECUTE ANY PROCEDURE BY ACCESS;

등등

6. 사용자접속

SQL> conn SCOTT/TIGER
Connected.

7. procedure 실행

SQL> var z varchar2(8)
SQL> execute pr_test(:z)

PL/SQL procedure successfully completed.

SQL> print z

Z
----------------------------------------------------------------
20181122

8. 확인

sys접속후

select * From dba_audit_trail;

---- scott.pr_test -------------------------------------------------

create or replace procedure pr_test(o_rtn_msg out varchar2)
 is
    ls_rtn varchar2(8);
begin
    select to_char(sysdate, 'yyyymmdd') into ls_rtn from dual;
    o_rtn_msg := ls_rtn;
end;

--------------------------------------------------------------------

참고

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4007.htm#SQLRF01107