I want to use FGAC(Oracle Fine Grained Access Control ) in my system. But it looks that the system will only analyze the sql for one time.
On one terminal my program process is just like:
A login --> do his job --> logout -->B login --> do his job.
Both A and B use the same oracle account(A and B is just the application account, not the database account).
But it is very funny is if A and B use the same sql statement, it will return the same result, regardless I have set the policy already.
For example, in table tblstaff there are 4600 rows.
A is administrator,
select count(*) from tblstaff
Then B login, there are a policy for this, select count(*) from tbstaff should only return 900 rows. But it also return 4600.
In another way, if B login first, the system will return 900, but result will never change regardless I use A login(the same account) later.
I trace the system a long time, and very sure that all the context attributes and policy are correct.
In additional I notice the following process:
-- here program set the context to set the policy to administrator
select count(*) from tblstaff ----->return 4600
-- here program set the context to set the policy to normal staff
select count(*) from tblstaff --------> return 4600 (read from the shared_pool)
select count(*) from tblstaff where 1=1 ------> return 900 (apply the policy)
So I believe the Oracle will not apply the policy again once it see the same sql statement regardless whether the context has been reset(I try the dbms_session.reset_package, but useless).
I try to flush the shared_pool before each sql, it is OK, but I cannot flush it everytime before run any sql statement.
I have one book(expert one-on-one oracle) tell me that it is no problem for oracle 8.1.7.
I don't know whether there are any parameters I need to set?
I am a programmer but not a DBA, anybody can help me?
Thank quite a lot!
Originally posted by SkyWriter
Have you checked metalink regarding this behaviour?
You appear to have a reproducible test case - raise a TAR with Oracle.
What is the meaning?
My program is not very complex. Just using a simple policy.
But when I update the data in context, the sql statement will not be analyzed again.(It should be set to invalid and apply the new policy)