Home » RDBMS Server » Security » ORA-00904: invalid identifier on table audited with dbms_fga policy (Oracle 10g v.10.2.0.2 OS=UNIX (Solaris 64-bit SPARC))
ORA-00904: invalid identifier on table audited with dbms_fga policy [message #286983] Mon, 10 December 2007 15:11 Go to next message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
All -

I was hoping that someone may be able to help here. I have gone through all of the normal reference points and have been completely unsuccessful in resolving my issue.

We have implemented Fine-Grained Auditing on 2 tables that contain customer credit card and billing address information.

The problem that I have encountered is that I am receiving an ORA-00904: invalid identifier error everytime something tries to access the credit card table once the auditing policies are enabled.

We have set our audit_trail initialization parameter to AUDIT_TRAIL=DB and we have restarted the database instance. This error only occurs in production and did not occur in staging whilst I was testing my implementation.

We have created a user for managing all auditing activities called FGA_OWN, here are it's privileges:
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;


The FGA policies look like the following:
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);


And the FGA_OWN.CANSEEIT function is as follows:
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;
/


When I disable the policies using DBMS_FGA.DISABLE_POLICY - the error goes away and our system returns to normal (although we are not auditing access on the tables that we need, only the standard auditing is in place).

I have spent alot of time trying to research this issue on Technet and Metalink and cannot come up with anything. Do any of you have any insight? Have any of you ever seen this issue before?

Let me know if you need any more information.
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287020 is a reply to message #286983] Mon, 10 December 2007 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First thought: do "fgalogstby.allowed_to_use" and "fgalogstby.allowed_host" exist and are accessible to to fga_own. It is not in your settings.

What is your Oracle version (4 decimals).

Regards
Michel
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287026 is a reply to message #287020] Mon, 10 December 2007 23:26 Go to previous messageGo to next message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
Sorry - I was copying this from my implementation and was masking the actual user names. FGALOGSTBY and FGA_OWN are one in the same, so FGA_OWN owns those objects.

My Oracle version is 10.2.0.2.
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287069 is a reply to message #287026] Tue, 11 December 2007 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't post the real code, does the function created with definer or current user authid?

Regards
Michel
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287071 is a reply to message #287069] Tue, 11 December 2007 01:03 Go to previous messageGo to next message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
Hopefully I understand what you are saying. The function is owned by the FGAC managing user (in the example case, FGA_OWN). Is this what you meant?
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287085 is a reply to message #287071] Tue, 11 December 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE FUNCTION invoker_rights_clause.

Regards
Michel
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287097 is a reply to message #287085] Tue, 11 December 2007 01:35 Go to previous messageGo to next message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
I did not explicitly denote either. So I would say that if there was a default that would be the setting. I honestly never even thought about that. Could this be the cause? If that is the case, then why did this issue not show up in stage testing?
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287103 is a reply to message #287097] Tue, 11 December 2007 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Default value is DEFINER, so this should not be the problem (just check there is the same definition in both databases: select authid from user_procedures where...).

Are you in the exactly same patchset in both databases?
Is there any one-off patch?

Regards
Michel

Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287238 is a reply to message #286983] Tue, 11 December 2007 09:28 Go to previous messageGo to next message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
Yes, we are exactly the same patch set.

Both are running 10.2.0.2 with the July2007 CPU patch and nothing else.
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287244 is a reply to message #287238] Tue, 11 December 2007 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you copy and paste the statement execution when you get the error (including the error itself).

Regards
Michel
Re: ORA-00904: invalid identifier on table audited with dbms_fga policy [message #287301 is a reply to message #287244] Tue, 11 December 2007 21:49 Go to previous message
ladyhawkeye
Messages: 6
Registered: December 2007
Junior Member
SQL> select count(*) from dcspp_credit_card;

ORA-00904: invalid identifier

SQL>


What is unusual is the following:
a) this only occurs in our production database environment
b) normally this error has more to it. The error is usually followed by a column name or reference that SQL*Plus is considering invalid.
Previous Topic: Problem with SYSDBA login credentials
Next Topic: using DBMS_OBFUSCATION_TOOLKIT over a PGP-encrypted file
Goto Forum:
  


Current Time: Thu Mar 28 18:25:06 CDT 2024