Home » RDBMS Server » Security » The problem in using VPD
The problem in using VPD [message #29509] Mon, 08 March 2004 22:53 Go to next message
Zheng Shuai
Messages: 2
Registered: March 2004
Junior Member
Hi, all
I want to use FGAC(Oracle Fine Grained Access Control, also called VPD) 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
return 4600,
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
A login
select count(*) from tblstaff   -----[>]return 4600
B login
-- 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!
Re: The problem in using VPD [message #29515 is a reply to message #29509] Tue, 09 March 2004 04:06 Go to previous messageGo to next message
Martin Chadderton
Messages: 35
Registered: May 2003
A few people have problems with FGAC, there isn't enough information in your post to identify if it really is a problem with cursor caching (typical "problem" in PL/SQL) or not, but I suggest you read


where Tom goes through mechanisms for "solving" this "problem".

Post back if it doesn't help.
Re: The problem in using VPD [message #29528 is a reply to message #29515] Tue, 09 March 2004 23:25 Go to previous message
Zheng Shuai
Messages: 2
Registered: March 2004
Junior Member
Thanks a lot for you recommend the article.
I have already gone through the article and it is quite helpful for me to understand the problem. In fact, the appearance of my problem is exactly similiar with that are mentioned in that article---the sql statement does not reparsed.
But(it is so unlucky that I say but_-_),I try all the solutions that mentioned in the article, only the flush shared_pool can force the oracle to do the reparse. I try the dbms_rls.refresh_policy or reconnect oracle, but useless. If I use the same oracle account login to the oracle, regardless the terminal I use(I try on different PCs), the sql statement is not reparsed. It looks like the shared_pool is identified by the userid.
For a real system, I cannnot always flush the shared_pool.
I have a book written by Tom(expert 1to1 oracle) and follow it. But it works incorrectly. My develop oracle is 8.1.7.
So any oracle parameters should be set first before I use VPD?
I check the database setting, I don't know whether they are correctly set for using VPD.

compatible = 8.1.0
cursor_sharing = EXACT
optimizer_features_enable = 8.1.7
Previous Topic: Database Auditing
Next Topic: Auditing
Goto Forum:

Current Time: Tue Dec 06 08:53:33 CST 2022