org user in db

[Linda]

Hi Dear Erman!
Hope you and your family is fine  inspite of corona situation!

our requirement: have a db user that can select only specific org data from ebs views.

Created xxuk db user in db with the  logontrigger to execute to limit to the only UK org id the data
apps.mo_global.set_policy_context('S',123);

as soon as I do as xxuk  select count(*) from apps.ap_invoices;
works correctly
but Unfortunately can do also select count(*) from apps.ap_invoices_all;
and this is what we would like to avoid.

Granting " grant select on apps.AP_INVOICES to XXUK";
automatically grants to the "SELECT ON AP.AP_INVOICES_ALL#" and can be selected..

Any ideas how we should implement it ?
Creating own views .. can require alot of maintenance efforts..

thx for advice & stay healthy!
Br,Linda :)

[ErmanArslansOracleBlog]

Hi Linda,

Thanks :) we are all good.
I hope everything is good on your side, as well.

Did you check enabling the data restriction by using virtual private database (VPD) policies?

Following MOS note may be a good start for you -> SQL Queries and Multi-Org Architecture in Release 12 (Doc ID 462383.1)

[Linda]

Hi Erman!
Thx for answering :) Thx Yes we are good as well.

Yes, I used that build-id VPD and it works for AP_invoices view well
BUT it allows to select also from apps.ap_invoices_ALL view.


when login as xxuk I set
apps.mo_global.set_policy_context('S',123);
dIsplays from ap_invoices only org_id=123 data what is what we need

BUT VPD not working on _ALL  views :(

user can also query the apps.ap_invoices_all and get ALL data

as xxuk/xxx
SQL> select count(*) from apps.ap_invoices;

  COUNT(*)
----------
     53184

SQL>  select count(*) from apps.ap_invoices_all;

  COUNT(*)
----------
    672001

The grant is given to the xxuk only this for ap_invoices view only.
grant select on apps.ap_invoices; -> did automatically in background this
GRANT SELECT ON AP.AP_INVOICES_ALL# TO XXUK;


SQL> connect / as sysdba
Connected.
SQL> revoke select on apps.ap_invoices_all from xxuk;

Revoke succeeded.

SQL> connect xxuk/xxx
Connected.
SQL> select count(*) from apps.ap_invoices;
select count(*) from apps.ap_invoices
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> connect / as sysdba
Connected.
SQL> grant select on  apps.ap_invoices to xxuk;

Grant succeeded.

SQL>  connect xxuk/xxx
Connected.
SQL> select count(*) from apps.ap_invoices;

  COUNT(*)
----------
      53184

SQL> select count(*) from apps.ap_invoices_all;

  COUNT(*)
----------
    672001


So, any suggestion  ?
We dont want them to select from apps.ap_invoices_all... :/
Thx!Br,Linda

[ErmanArslansOracleBlog]

Hi Linda,

You need to implement a custom VPD for that.

The same question is asked in this MOS document : How to Restrict Data on OE_XXX_ALL Table Using VPD(Virtual Private Database) on MOAC(Multi Org Access Control) for Order Management? (Doc ID 2282104.1)

Check this note -> Oracle Applications Multiple Organizations Access Control for Custom Code (Doc ID 420787.1)  , search by the keyword _ALL.. Check whether this note gives you a clue or not.. Update me accordingly.