Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: The problem in using VPD

    Hi, all
    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
    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!
    Last edited by zs_shine; 03-09-04 at 04:57.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Have you checked metalink regarding this behaviour?

    You appear to have a reproducible test case - raise a TAR with Oracle.

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    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)

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    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)
    Either this is documented behaviour, or it is a bug.

    Check metalink and the documentation for restrictions on changing the access policy.
    If there are none, then you have a simple reproducible testcase - raise a TAR with Oracle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •