FGA(Fine-Grained Auditing) DBMS_FGA AUDIT..

FGA는 기존의 "audit_trail" initialization parameter를 설정해야 하는 standard auditing를

대신하는 것이 아니라, standard auditing이 커버할 수 없는 상세한 수준(fine-grained level)의

auditing을 수행하기 위해 등장하게 되었습니다.

예를 들면, standard auditing의 경우에 추적할 수 없었던

특정 column 혹은 특정 data에 대한 auditing이 FGA를 이용하면 가능합니다.

 

그 외에 FGA는 auditing정보를 database table 혹은 XML file에 남기는 것과 동시에

security manager에 email을 보낼 수 있는 event handler기능도 제공합니다.

 

참고로, FGA기능을 이용하기 위해 "audit_trail" initialization parameter를 설정할 필요는 없습니다.

즉, "audit_trail"값이 "none"이라고 해도, FGA기능을 사용할 수 있습니다.

 

2. FGA 관련 tables 및 views

 

table or view name	description
fga_log$	standard auditing의 "aud$" table에 해당하며, 모든 FGA records가 저장되어 있으며, dba권한을 가진 user가 data를 delete할 수 있습니다.
dba_fga_audit_trail	standard auditing의 "dba_audit_trail" view에 해당하며, "fga_log$" table과 동일한 records를 가지며, 거의 동일한 column을 가집니다. (확인해 본 결과 "fga_log$"보다 column명이 알아보기 쉽게 되어 있더군요)
v$xml_audit_trail	"audit_file_dest" initialization parameter가 가리키는 directory에 존재하는 모든 XML files에 기록된 FGA records정보를 relational table형태로 보여줍니다. 즉, 이 view의 원본 data는 table이 아닌 XML files에 존재합니다.
dba_common_audit_trail	FGA 뿐만 아니라 standard auditing을 통해 발생한 모든 auditing records를 열람할 수 있습니다.
dba_audit_policies	"dbms_fga.add_policy"로 생성한 FGA policies정보중 일부가 저장됩니다.
fga$	"dba_audit_policies"과 마찬가지로 "dbms_fga.add_policy" 로 생성한 FGA policies정보가 저장되는데, "dba_audit_policies"와 records수는 동일하지만, 저장되는 정보는 더 적습니다.

 

※ 참고로, sysdba권한을 가지지 않은 user가 "fga_log$"에 대해 수행한 DML(insert, delete, update,

    merge)을 수행하는 경우, "audit_trail" initialization parameter값이 "none"이 아니라면,

    그 operations에 대한 audit records가 "aud$"에 기록됩니다.

SQL> connect / as sysdba  SQL> alter system set audit_trail = none scope=spfile;  System altered.      SQL> shutdown immediate;  SQL> startup;  SQL> select count(*) from aud$;  no rows selected      SQL> connect system/manager  SQL> delete from sys.fga_log$;  14 rows deleted.    SQL> rollback;  SQL> connect / as sysdba  SQL> select count(*) from aud$;  no rows selected    SQL> alter system set audit_trail = db,extended scope=spfile;  System altered.    SQL> shutdown immediate;  SQL> startup;  SQL> select count(*) from aud$;  no rows selected    SQL> connect system/manager  SQL> delete from sys.fga_log$;  14 rows deleted.    SQL> connect / as sysdba  SQL> select count(*) from sys.aud$;

 

3. FGA를 설정하기 위해 필요한 권한

 

오직 Oracle이 제공하는 "dbms_fga" PL/SQL package를 실행할 권한만 가지고 있으면 됩니다.

즉, "grant execute on dbms_fga to <username>"과 같이 권한을 부여하기만 하면 됩니다.

 

4. FGA는 어떤 식으로 audit record를 생성하는가?

 

각각의 reference에 대해 하나의 record를 생성합니다.

즉, "emp" table을 검색하는 select문 2개를 union묶은 query가 실행되는 경우,

2개의 audit records가 기록됩니다.

 

5. Fine-Graind Audit policies를 생성하는 방법

 

standard auditing의 경우, 특정 user, object, and privilege등을 audit하기 위해,

"audit … by access" 등의 command를 통해 audit options를 설정하는데,

FGA의 경우에는 이런 과정을 FGA policy를 생성한다고 말합니다.

즉, FGA policy를 생성한다는 것은,

audit할 target object and data 및 operations를 정하는 과정입니다.

 

이 FGA policy를 생성하는 과정은 "dbms_fga.add_policy"라는 procedure를 통해 이루어지며,

다음과 같이 11개의 parameters를 가지는데, 하나하나 설명해 가도록 하겠습니다.

 

 

dbms_fga.add_policy (               object_schema        varchar2,               object_name           varchar2,               policy_name            varchar2,               audit_condition       varchar2,               audit_column           varchar2,               handler_schema       varchar2,               handler_module       varchar2,               enable                      boolean,               statement_types      varchar2,               audit_trail                binary_integer in default,               audit_column_opts  binary_integer in default);

 

(1) object_schema – audit될 object가 존재하는 schema를 지정합니다.

(2) object_name – audit될 object을 지정합니다.

(3) policy_name – 새로 생성될 FGA policy의 이름으로, 원하는 문자열을 입력합니다.

(4) audit_condition – audit될 data의 조건을 지정하는데,

     select문의 where절과 같은 식으로 입력하면 됩니다.

     만약 이 parameter값을 null로 설정하거나, 아무 값도 지정하지 않는 경우에는

     모든 data가 대상이 됩니다.

(5) audit_column – audit할 column을 지정하는데, 이 parameter값을 null로 설정하거나,

     아무 값도 지정하지 않는 경우에는 모든 column가 대상이 됩니다.

(6) handler_schema – event handler를 소유하는 schema를 지정합니다.

     default값은 current schema입니다. (나중에 다시 설명)

(7) handler_module – 해당 FGA policy의 audit record를 발생시키는 database operation이

     발생하였을 때 수행될 event handler의 이름을 말하며,

     일반적으로 stored procedure가 많이 사용됩니다. (나중에 다시 설명)

(8) enable – 이 FGA policy를 enable(true)시킬지 disable(false)시킬지를 결정합니다.

     default값은 "true"입니다.

(9) statement – audit될 statement를 지정합니다. 여기에 사용될 수 있는 값은,

     "select", "delete", "insert", "update", 4가지로,

     이 4가지의 어떤 조합도 가능합니다(comma로 구분).

(10) audit_trail – FGA records가 생성될 destination을 지정하는데,

       다음의 4가지 종류가 가능합니다.

parameter value	description
dbms_fga.db	FGA records를 database상에만 남긴다
dbms_fga.db+dbms_fga.extended	(위의 내용) + 실행된 SQL문 및 bind variable에 실제 입력된 값까지 기록을 남긴다.
dbms_fga.xml	FGA records를 XML files형태로만 남긴다
dbms_fga.xml+dbms_fga.extended	(위의 내용) + 실행된 SQL문 및 bind variable에 실제 입력된 값까지 기록을 남긴다.

 

(11) audit_column_opts – 위의 5번째 parameter인 "audit_column"에 복수의 columns가

       지정된 경우에만 이 parameter가 의미를 가지는데, 다음과 같이 2가지 값이 설정될 수 있습니다.

parameter value	description
dbms_fga.any_columns	default값으로, "audit_column"에 지정된 columns중 하나라도 연관되어 있다면 FGA records가 쓰여진다.
dbms_fga.all_columns	"audit_column"에 지정된 모든 columns가 연관된 경우에만, FGA records가 쓰여진다.

 

예를 들어, "audit_column"값으로 "salary, email"이 설정되어 있다고 가정할 때,

audit_column_opts가 "dbms_fga.any_columns" 인 경우에는,

① select salary from …

② select email from …

③ select salary, email from …

이 3가지 statements 모두(①②③)에 대해 audit records가 기록되지만,

audit_column_opts가 "dbms_fga.all_columns" 인 경우에는,

③에 대해서만 audit records가 기록됩니다.

 

이제 dbms_fga.add_policy를 사용하는 방법을 예를 통해서 설명하겠습니다.

"hr" schema의 "employees" table에 대해, "department_id" column값이 "90"인 종업원의

"salary" 및 "email" column에 FGA policy를 생성하고자 하는 경우, 다음과 같이 수행하면 됩니다.

 

 

SQL> begin      2   dbms_fga.add_policy(object_schema => 'hr',      3   object_name => 'employees',      4   policy_name => 'hr_emp_policy',      5   audit_condition => 'department_id = 90',      6   audit_column => 'salary, email',      7   statement_types => 'select, insert, delete',      8   audit_trail => dbms_fga.xml + dbms_fga.extended);      9   end;    10   /

 

 

위의 경우, audit records는 database가 아닌 xml files형태로 "audit_file_dest" initialization parameter에 지정된 directory에 저장되며,

관련된 SQL문 및 사용된 bind variables의 정보도 함께 저장됩니다.

 

예를 들어 다음과 같은 SQL문을 실행했다고 합시다.

 

SQL> var ename varchar2(100);  SQL> exec :ename := 'Steven';  PL/SQL procedure successfully completed.    SQL> select first_name, email from employees      2   where department_id = 90 and first_name = :ename;

 

 

xml file에는 다음과 같이 SQL문 및 bind variable의 실제값에 관한 내용이 기록되더군요.

 

...  <AuditRecord>  <Audit_Type>2</Audit_Type>  <Session_Id>201702</Session_Id>  <StatementId>11</StatementId>  <EntryId>4</EntryId>  <Extended_Timestamp>2010-05-18T16:10:03.800881</Extended_Timestamp>  <DB_User>HR</DB_User>  <Ext_Name>oracle</Ext_Name>  <OS_User>oracle</OS_User>  <Userhost>dbserver.ge.com</Userhost>  <OS_Process>6256</OS_Process>  <Instance_Number>0</Instance_Number>  <Object_Schema>HR</Object_Schema>  <Object_Name>EMPLOYEES</Object_Name>  <Policy_Name>HR_EMP_POLICY</Policy_Name>  <Stmt_Type>1</Stmt_Type>  <Scn>10840883635121</Scn>  <Sql_Bind> #1(6):Steven</Sql_Bind>  <Sql_Text>select first_name, email from employees where department_id = 90 and first_name = :ename</Sql_Text>  </AuditRecord>  ...

 

 

만약 위의 "audit_trail" parameter의 값이 "dbms_fga.db + dbms_fga.extended"였다고 하면,

다음과 같이 dba_fga_audit_trail을 통해 audit record를 확인할 수 있습니다.

 

SQL> select policy_name, sql_text, sql_bind, from dba_fga_audit_trail;

 

 

지금까지의 예로서 실행된 SQL문들은 where절의 조건이 모두

FGA policy의 "audit_condition"에 명시된 조건을 포함하고 있습니다.

만약 "department_id = 90" 대신에 이 조건을 포함하는 보다 포괄적인 "department_id > 80"

이라는 조건을 가진 SQL문을 실행하면 과연 FGA record가 기록될까요?

 

답은 "기록된다" 입니다.

왜냐하면, FGA에서 중요한 것은,

"audit_condition"에 명시된 조건에 해당하는 data가 access되느냐 아니냐 이지,

"audit_condition"과 동일한 조건이 SQL문에 사용되는냐 아니냐가 아니기 때문입니다.

그러므로 다음과 같은 query를 실행해도 FGA record는 발생하게 됩니다.

select salary from employees where first_name = 'Steven';

select email from employees where first_name like 'S%';

 

 

6. Fine-Graind Audit policies를 disable/enable/drop하는 방법

 

(1) FGA policy를 제거하는 방법

 

"dbms_fga.drop_policy" procedure를 사용해서 제거할 수 있으며,

이 procedure는 3개의 parameter를 가집니다.

 

 

dbms_fga.drop_policy (               object_schema       varchar2,               object_name          varchar2,               policy_name           varchar2);

 

 

예를 들어, 위에서 생성한 FGA policy를 제거하고 싶다면, 다음과 같이 실행하면 됩니다.

 

SQL> begin      2   dbms_fga.drop_policy(object_schema => 'hr',      3   object_name => 'employees',      4   policy_name => 'hr_emp_policy');      5   end;      6   /

 

 

(2) enable상태의 FGA policy를 disable시키는 방법

 

"dbms_fga.disable_policy" procedure를 사용해서 제거할 수 있으며,

이 procedure는 3개의 parameter를 가집니다. 

 

 

dbms_fga.disable_policy (               object_schema       varchar2,               object_name          varchar2,               policy_name           varchar2);

 

 

예를 들어, 위에서 생성한 FGA policy를 disable시키고 싶다면, 다음과 같이 실행하면 됩니다. 

 

SQL> begin      2   dbms_fga.disable_policy(object_schema => 'hr',      3   object_name => 'employees',      4   policy_name => 'hr_emp_policy');      5   end;      6   /

 

 

(3) disable상태의 FGA policy를 enable시키는 방법

 

"dbms_fga.enable_policy" procedure를 사용해서 제거할 수 있으며,

이 procedure는 4개의 parameter를 가집니다.

 

 

dbms_fga.enable_policy (               object_schema       varchar2,               object_name          varchar2,               policy_name           varchar2,               enable                    boolean);

 

 

예를 들어, 위에서 생성한 FGA policy를 enable시키고 싶다면, 다음과 같이 실행하면 됩니다.

SQL> begin      2   dbms_fga.enable_policy(object_schema => 'hr',      3   object_name => 'employees',      4   policy_name => 'hr_emp_policy',      5   enable => true);      6   end;      7   /

 

7. FGA records가 발생하는 순간에 관리자에게 E-mail을 보내는 방법

 

(1) 우선 sysdba privilege로 login한 이후에 mail과 관련된 packages를 생성합니다.

SQL> connect / as sysdba  SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql  SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

 

(2) "smtp_out_server" initialization parameter의 값으로 SMTP server로서 이용할 수 있는

     server의 IP address 혹은 hostname을 입력한 후 database를 restart합니다.

 

SQL> alter system set smtp_out_server = 'mail.ge.com';  SQL> shutdown immediate;  SQL> startup;  SQL> show parameter smtp_out_server

 

(3) alert mail을 발송하는 FGA policy 및 event handler가 생성될 schema를 create합니다.

 

SQL> connect / as sysdba  SQL> grant create session, dba to sysadmin_fga identified by ******;  SQL> grant execute on dbms_fga to sysadmin_fga;  SQL> grant create procedure, drop any procedure to sysadmin_fga;  SQL> grant execute on utl_tcp to sysadmin_fga;  SQL> grant execute on utl_smtp to sysadmin_fga;  SQL> grant execute on utl_mail to sysadmin_fga;  SQL> grant execute on dbms_network_acl_admin to sysadmin_fga;

 

(4) ACL(Acces Control List) file을 설정합니다.

 

ACL은 저도 첨 보는 용어인데, FGA가 "utl_mail"과 같은 PL/SQL network utility package를 통해

external network services를 활용할 때에는 반드시 ACL을 설정해야 한다고 합니다.

이것에 대한 자세한 내용을 보고 싶다면 여기를 click하시기 바랍니다.

 

우선 다음과 같이 ACL을 생성하고, 참조를 위해 권한에 대한 정의도 적어 넣습니다.

SQL> begin      2   dbms_network_acl_admin.create_acl (      3   acl => 'email_server_permission.xml',      4   description => 'Enables network permissions for the e-mail server',      5   principal => 'SYSADMIN_FGA',    /* 반드시 대문자로 입력해야 한다 */      6   is_grant => true      7   privilege => 'connect');      8   end;      9   /

 

위에서 생성한 ACL을 앞의 (2)에서 설정한 E-mail server(mail.ge.com)에 할당합니다.

SQL> begin      2   dbms_network_acl_admin.assign_acl (      3   acl => 'email_server_permission.xml',      4   host => 'mail.ge.com',      5   port => 25);      6   end;      7   /

 

(5) E-mail security alert를 보내기 위한 stored procedure를 생성합니다.

SQL> create procedure email_alert (sch varchar2, tab varchar2, pol varchar2) as      2     msg varchar2(20000);      3   begin      4     msg := sch || '.' || tab || ' was accessed by intruders at (' || sysdate || ')';      5     utl_mail.send(sender => 'sender@ge.com',      6                        recipients => 'receiver@ge.com',      7                        subject => 'Table access violation on ' || sch || '.' || tab,      8                        message => msg);      9   end;    10   / PL/SQL procedure successfully completed.

 

(6) FGA policy를 생성합니다.

 

SQL> begin      2   dbms_fga.add_policy(object_schema => 'hr',      3   object_name => 'employees',      4   policy_name => 'hr_salary_chk',      5   audit_condition => 'department_id = 90',      6   audit_column => 'salary',      7   handler_schema => 'sysadmin_fga',      8   handler_module => 'email_alert',      9   statement_types => 'select, update',    10   audit_trail => dbms_fga.db + dbms_fga.extended);    11   end;    12   /  PL/SQL procedure successfully completed.    /* 다음과 같이 실행했을 때 receiver@ge.com에 mail이 도착하는지 체크해 봅시다 */  SQL> execute email_alert('hr', 'employees', 'hr_salary_chk');  PL/SQL procedure successfully completed.

만약 위의 execute email_alert(…)문의 결과로 "ORA-24247: network access denied by access control list (ACL)"가 발생하는 경우에는, ACL의 설정이 제대로 되어 있는지 check해 봅시다.

 

(7) 이제 FGA policy에 해당하는 data가 access되었을 때, E-mail이 보내지는지 확인해 봅시다.

 

당연한 이야기지만, 위의 예에서는

"audit_trail" parameter의 값으로 "dbms_fga.db"를 설정했기 때문에,

E-mail전송과는 별도로 FGA관련 table에 audit records가 기록됩니다.