RE: [OBIEE EMG] Oracle BIA: Alternative User for the APPS database user (Oracle eBS)

[Amin Adatia]

The genius DBS has no suggestions?

 

I think eBS works with views but maybe any table will do the trick. You might also need synonyms unless you want to prefix with owner for each table/view.

 

Have a look at what APPS has in terms or privileges and roles and synonyms.

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

 

From: obiee-enterpri...@googlegroups.com [mailto:obiee-enterpri...@googlegroups.com] On Behalf Of Daan Bakboord
Sent: Thursday, April 04, 2013 04:50
To: obiee-enterpri...@googlegroups.com
Cc: Daan Bakboord
Subject: [OBIEE EMG] Oracle BIA: Alternative User for the APPS database user (Oracle eBS)

 

All,

 

I am working in an Oracle BIA environment were we need to extract data from Oracle eBS. Normally we use the APPS database user. For Security reasons, our DBA's want us to use another (Read-Only) user, which hasn't got enough rights. 

 

Does anybody know which privileges a database user should have to extract data from Oracle eBS in an Oracle BIA setting?

 

I doubt whether 'select any table' and 'execute any procedure' is sufficient. For me the OOTB ETL in Oracle BIA is to some extend a Black Box, so I don't know for sure what happens, other than 'select any table' and 'execute any procedure'.

 

It seems that Oracle doesn't provide any documentation whatsoever whith regards to the privileges of the database users.

 

Thanks in advance!

 

- Daan Bakboord

--
--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterprise-methodology@google groups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).
 
---
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-met...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[venkatareddy gali]

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

obiee-enterpri...@googlegroups.com

[Matthieu Lombard]

Hi all,

This information should be in the installation documents.

http://www.oracle.com/technetwork/middleware/bi-foundation/documentation/bi-apps-098545.html

I will confirm in a few.

Thanks

Matthieu

obiee-enterpri...@googlegroups.com

[Shyam Varan Nath]

If you are using SCM analytics - then Bill of Material (BoM) related ETL jobs write a temp table on EBS side... this is the only time where read-only is not enough.

Thanks

Shyam

On Thu, Apr 4, 2013 at 7:49 AM, Daan Bakboord <daanba...@hotmail.com> wrote:

Thanks Venkat,

I am afraid we have the read-only Apps user right now, so that wouldn't solve the problem. It's the execute, which is giving problems and maybe it's more next to that, so that's our challenge.

The DBA wants a complete list of tables and procedures used in the ETL, which is a hell of a job since we have to check all the mappings. The DAC tables only isn't sufficient, because of the executes in the mappings.

- Daan

On Thursday, April 4, 2013 12:58:00 PM UTC+2, venkatareddy gali wrote:

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

[Naeem Akhtar]

May I have pleasure to kill that DBA with a Shotgun?

No, Ok.

Just read only access will not be sufficient on apps. Depending on the subject are there are some tasks in ETL (EAM Cost Fact) which need execute rights as well.

May be below scenario work.

Create user ebs identified by ebs

Create role sse_role

Grant connect,resource to sse_role

Grant sse_role to ebs

Regards

Naeem Akhtar

On Thu, Apr 4, 2013 at 4:49 PM, Daan Bakboord <daanba...@hotmail.com> wrote:

Thanks Venkat,

I am afraid we have the read-only Apps user right now, so that wouldn't solve the problem. It's the execute, which is giving problems and maybe it's more next to that, so that's our challenge.

The DBA wants a complete list of tables and procedures used in the ETL, which is a hell of a job since we have to check all the mappings. The DAC tables only isn't sufficient, because of the executes in the mappings.

- Daan

On Thursday, April 4, 2013 12:58:00 PM UTC+2, venkatareddy gali wrote:

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

[venkatareddy gali]

Hi Dan,

I am not sure how much time you can spend on this, but can perform couple of dry runs with rea-only user and identify the tasks that fail with insufficient privileges.

As Shyam mentioned in other mail, there are only couple BI Apps mappings that require execute privileges on source system package/procedures.

What BI Apps modules are you implementing, source system version, source db version?

Venkat

On Thu, Apr 4, 2013 at 12:49 PM, Daan Bakboord <daanba...@hotmail.com> wrote:

Thanks Venkat,

I am afraid we have the read-only Apps user right now, so that wouldn't solve the problem. It's the execute, which is giving problems and maybe it's more next to that, so that's our challenge.

The DBA wants a complete list of tables and procedures used in the ETL, which is a hell of a job since we have to check all the mappings. The DAC tables only isn't sufficient, because of the executes in the mappings.

- Daan

On Thursday, April 4, 2013 12:58:00 PM UTC+2, venkatareddy gali wrote:

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

[chet justice]

Check out the lineage doc in note 1274680.1

From there you should, at least, be able to get the tables and views necessary. 

After looking at it, your DBA may just give you SELECT ANY TABLE. I'm hoping they are serious and will use that document.

On Thu, Apr 4, 2013 at 4:49 AM, Daan Bakboord <daanba...@hotmail.com> wrote:

All,

I am working in an Oracle BIA environment were we need to extract data from Oracle eBS. Normally we use the APPS database user. For Security reasons, our DBA's want us to use another (Read-Only) user, which hasn't got enough rights. 

Does anybody know which privileges a database user should have to extract data from Oracle eBS in an Oracle BIA setting?

I doubt whether 'select any table' and 'execute any procedure' is sufficient. For me the OOTB ETL in Oracle BIA is to some extend a Black Box, so I don't know for sure what happens, other than 'select any table' and 'execute any procedure'.

It seems that Oracle doesn't provide any documentation whatsoever whith regards to the privileges of the database users.

Thanks in advance!

- Daan Bakboord

--

[Matthieu Lombard]

Hi Dan,

The OBI Apps 7.9.6.3 release notes mentions some guidelines for this EBS OLTP user for OBIA:

Please look into that:

http://docs.oracle.com/cd/E20490_01/bia.7963/e20489/chapter.htm#BGBBDFJB

Thanks

Matthieu

2013/4/4 Daan Bakboord <daanba...@hotmail.com>

Thanks Venkat,

I am afraid we have the read-only Apps user right now, so that wouldn't solve the problem. It's the execute, which is giving problems and maybe it's more next to that, so that's our challenge.

The DBA wants a complete list of tables and procedures used in the ETL, which is a hell of a job since we have to check all the mappings. The DAC tables only isn't sufficient, because of the executes in the mappings.

- Daan

On Thursday, April 4, 2013 12:58:00 PM UTC+2, venkatareddy gali wrote:

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

[Amin Adatia]

The DBA has taken a few too many courses and very few real application implementation situations. to mimick what APPS does is not a simple thing.

But Policy is Policy and damn the work stoppages


Regards
Amin Adatia
KnowTech Solutions Inc ( http://knowtech.ca )
Mobile +1-613-864-8378

---

Date: Thu, 4 Apr 2013 04:49:57 -0700
From: daanba...@hotmail.com
To: obiee-enterpri...@googlegroups.com
CC: dbak...@scamander.com
Subject: Re: [OBIEE EMG] Oracle BIA: Alternative User for the APPS database user (Oracle eBS)

Thanks Venkat,

I am afraid we have the read-only Apps user right now, so that wouldn't solve the problem. It's the execute, which is giving problems and maybe it's more next to that, so that's our challenge.

The DBA wants a complete list of tables and procedures used in the ETL, which is a hell of a job since we have to check all the mappings. The DAC tables only isn't sufficient, because of the executes in the mappings.

- Daan

On Thursday, April 4, 2013 12:58:00 PM UTC+2, venkatareddy gali wrote:

Hi Daan,

There seems an established procedure to create read-only APPS user among Apps DBA community.

Please see if your Apps DBA can create a read-only APPS user similar to the one mentioned @
http://oracle.anilpassi.com/read-only-schema-in-oracle-apps-11i-2.html

 

In case apps DBA ask you for a list of objects that your BI Apps ETL require access to, depending on the BI Apps module you are implementing, you may provide Apps DBA with a source system table, view list (for synonym creation) either form DAC or from ETL Lineage document available on Oracle support.

Regards,

Venkat Gali

[Alenatri gmail]

I would open an SR with Oracle. They cannot assume we will use APPS.

As per the list of source tables, you could query the informatica repository:

http://aambarish.blogspot.com/2012/01/normal-0-false-false-false-en-us-x-none.html?m=1

Sent from my iPhone

[Kris Abburu]

Dan,

I ran into a similar issue with a previous client. I found this information from Oracle support. Make sure you grant select for the new user on all the tables.

I got it to working after couple of DAC runs (creating synonyms for missing tables .)

 

In Oracle Business Intelligence Applications, If customers with EBS OLTP wants to use non-APPS userid for ETL, following are a set of high level steps that can help customer's OBIA implementation team with configuration steps to accomplish the goal:

1. Identify all the Source tables used in OBIApps by querying the “Source Tables” folder in Informatica in the SDE_ORA* folder that is being used by the implementation.(I used Informatica Repository Query to extract the table names from the source adaptor folder)

2. Create a schema in EBS (say OBIA)

3. Create Synonyms for all the objects that are identified in step 1 (with SELECT only option)

4. The PL/SQL packages that need EXECUTE grants in the newly create schema (say OBIA).
· PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME
· PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER
· INV_CONVERT.INV_UM_CONVERSION
· INV_CONVERT.INV_UM_CONVERT
· HRI_OLTP_VIEW_WRKFRC.CALC_ABV
· PA_HR_UPDATE_API.GET_JOB_LEVEL
· HRI_BPL_UTILIZATION.CONVERT_DAYS_TO_HOURS
· HR_MISC_WEB.GET_USER_DEFINED_JOB_SEGMENTS

5. Apart from the above, the following table (Used in BOM explosion PL/SQL code) needs to be truncatable using this new user:
· OPI.OPI_OBIA_W_BOM_HEADER_DS

6. In Informatica, following sessions have hardcoding in the SQL override with prefix of APPS for the tables. This needs to be fixed.
· SDE_ORA_UOMConversionGeneral_IntraClass*
· SDE_ORA_UOMConversionGeneral_InterClass* "

From: Alenatri gmail <alen...@gmail.com>
To: "obiee-enterpri...@googlegroups.com" <obiee-enterpri...@googlegroups.com>
Cc: "obiee-enterpri...@googlegroups.com" <obiee-enterpri...@googlegroups.com>; Daan Bakboord <dbak...@scamander.com>
Sent: Thursday, April 4, 2013 10:24 AM
Subject: Re: [OBIEE EMG] Oracle BIA: Alternative User for the APPS database user (Oracle eBS)

I would open an SR with Oracle. They cannot assume we will use APPS.

As per the list of source tables, you could query the informatica repository:

http://aambarish.blogspot.com/2012/01/normal-0-false-false-false-en-us-x-none.html?m=1

Sent from my iPhone

On Apr 4, 2013, at 10:51 AM, Amin Adatia <amin....@outlook.com> wrote:

The DBA has taken a few too many courses and very few real application implementation situations. to mimick what APPS does is not a simple thing.

But Policy is Policy and damn the work stoppages


Regards

Amin Adatia
KnowTech Solutions Inc ( http://www.knowtech.ca/ )
Mobile +1-613-864-8378

[tomsi...@gmail.com]

Guys...if u need an alteranate for APPS schema then please query the OOTB dac container to fetch all the registered source system tables and assign the grants to APPS-alternate schemA

Sent via BlackBerry from T-Mobile

---

From: Kris Abburu <abbur...@yahoo.com>

Sender: obiee-enterpri...@googlegroups.com

Date: Thu, 4 Apr 2013 11:04:05 -0700 (PDT)

To: obiee-enterpri...@googlegroups.com<obiee-enterpri...@googlegroups.com>

ReplyTo: obiee-enterpri...@googlegroups.com

Cc: Daan Bakboord<dbak...@scamander.com>

[Amin Adatia]

And give only select else what is the point?

---

Subject: Re: [OBIEE EMG] Oracle BIA: Alternative User for the APPS database user (Oracle eBS)

To: obiee-enterpri...@googlegroups.com
CC: dbak...@scamander.com
From: tomsi...@gmail.com
Date: Thu, 4 Apr 2013 23:02:24 +0000