create user FGA_OWN identified by FGA_OWN
default tablespace FGADATA
temporary tablespace TEMP
PROFILE ADMINISTRATOR;

grant resource to FGA_OWN;
grant create session to FGA_OWN;
grant select any dictionary to FGA_OWN;
alter user FGA_OWN quota unlimited on FGADATA;
grant execute on sys.dbms_fga to FGA_OWN;
create directory audit_dir as '/var/tmp/';
grant write on directory audit_dir to FGA_OWN;
grant select, delete on sys.aud$ to FGA_OWN;
grant delete on sys.fga_log$ to FGA_OWN;
grant select on dba_common_audit_trail to FGA_OWN;
grant select on dba_fga_audit_trail to FGA_OWN;
grant select on dba_audit_trail to FGA_OWN;
grant audit any to FGA_OWN;
grant audit system to FGA_OWN;
grant select on audit_actions to FGA_OWN;

begin
dbms_fga.add_policy(
object_schema=>'SCHEMA_NAME',
object_name=>'CREDIT_CARD_TABLE',
policy_name=>'ACCESS_CREDIT_CARD',
audit_condition=>'FGA_OWN.CANSEEIT > 0',-- func. defined below
audit_column=> NULL, -- audit all columns in table
handler_schema=> NULL,
handler_module=> NULL,
statement_types=>'SELECT,INSERT,UPDATE,DELETE',
enable=>TRUE);

begin
dbms_fga.add_policy(
object_schema=>'SCHEMA_NAME',
object_name=>'BILL_ADDR_TABLE',
policy_name=>'ACCESS_BILL_ADDR',
audit_condition=>'FGA_OWN.CANSEEIT > 0',-- func. defined below
audit_column=> NULL, -- audit all columns in table
handler_schema=> NULL,
handler_module=> NULL,
statement_types=>'SELECT,INSERT,UPDATE,DELETE',
enable=>TRUE);

create or replace function FGA_OWN.CANSEEIT return number
as
v_count integer:=0;
v_hostname VARCHAR2(100);

begin
select count(*) into v_count from 
   (select sys_context ('USERENV','SESSION_USER')
    from dual
    minus
    select username from fgalogstby.allowed_to_use);

v_hostname := LOWER(sys_context('USERENV','HOST'));

if instr (v_hostname, '.') > 0 then
   v_hostname := substr(v_hostname,1, instr(v_hostname, '.')-1);
end if;

if instr (v_hostname, '\') > 0 then
   v_hostname := replace(v_hostname,chr(0));
end if;

if v_count = 0 then
   select count(*) into v_count 
   from (select v_hostname
   from dual
   minus
   select lower(machine) from fgalogstby.allowed_host 
   where lower(machine) = v_hostname);
end if;

return v_count;

end;
/

SQL> select count(*) from dcspp_credit_card;

ORA-00904: invalid identifier

SQL>