access_flagger.sql

Oracle PLSQL for DBAs 源代码

create or replace procedure access_flagger
(
   p_table_owner    in varchar2,
   p_table_name     in varchar2,
   p_fga_policy     in varchar2
)
is
   l_fgasid       number(20);
   l_entryid      number(20);
   l_term         varchar2(2000);
   l_db_user      varchar2(30);
   l_os_user      varchar2(30);
   l_authent_type varchar2(2000);
   l_client_id    varchar2(100);
   l_client_info  varchar2(64);
   l_host_name    varchar2(30);
   l_instance_id  number(2);
   l_ip           varchar2(30);
   l_sql_text     varchar2(4000);
   l_scn          number;
begin
   l_fgasid       := sys_context('USERENV','SESSIONID');
   l_entryid      := sys_context('USERENV','ENTRYID');
   l_term         := sys_context('USERENV','TERMINAL');
   l_db_user      := sys_context('USERENV','SESSION_USER');
   l_os_user      := sys_context('USERENV','OS_USER');
   l_authent_type := sys_context('USERENV','AUTHENTICATION_TYPE');
   l_client_id    := sys_context('USERENV','CLIENT_IDENTIFIER');
   l_client_info  := sys_context('USERENV','CLIENT_INFO');
   l_host_name    := sys_context('USERENV','HOST');
   l_instance_id  := sys_context('USERENV','INSTANCE');
   l_ip           := sys_context('USERENV','IP_ADDRESS');
   l_sql_text     := sys_context('USERENV','CURRENT_SQL');
   l_scn          := sys.dbms_flashback.get_system_change_number;
   insert into flagged_access
   (
      fgasid,
      entryid,
      audit_date,
      fga_policy,
      db_user,
      os_user,
      authent_type,
      client_id,
      client_info,
      host_name,
      instance_id,
      ip,
      term,
      schema_owner,
      table_name,
      sql_text,
      scn
   )
   values
   (
      l_fgasid,
      l_entryid,
      sysdate,
      p_fga_policy,
      l_db_user,
      l_os_user,
      l_authent_type,
      l_client_id,
      l_client_info,
      l_host_name,
      l_instance_id,
      l_ip,
      l_term,
      p_table_owner,
      p_table_name,
      l_sql_text,
      l_scn
   );
end;
/