nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Port current OBIEE RPD semantic layer connection pools functionality to the OAC semantic modeler

Rudy Hruska

OAC Semantic Modeler Enhancement Request.pdf

OAC Enhancement request

This request is to port current OBIEE RPD semantic layer connection pools functionality to the OAC semantic modeler.

The ability to use session variables in the username to pass the IDP username, so a proxy user can be used for Database level Fine Grain Access.

Provide the ability to execute a post login connection script to allow setting contexts or setting roles with a password.

These limitations are an impediment to switching from the legacy RPD to the OAC Semantic Modeler.

Problem Statement

OAC semantic modeler connection pools to not allow variables in the username so all users have the same access to the database. The inability to use variables like session variables does not allow for Oracle fine grained access policies to be applied. This eliminates the ability to utilize one of the most powerful database security features ever implemented (fine grained access). All users show up as the same service account making it difficult to tie query activity to specific user when trouble shooting and tuning. For more information about Oracle Virtual Private Database (VPD) and data level security see the following links.

https://docs.oracle.com/cd/B13789_01/network.101/b10773/apdvcntx.htm https://blogs.oracle.com/analytics/post/implementing-data-level-security-for-oracle-analytics-cloud-using-database-proxy-user-analytics-session-variable

Please see the attached file for more details…

Find more posts tagged with

Delivered

Status: Delivered

Comments

Michal Zima

I vote hundred times for this idea. Web based semantic modeler should support same flexibility as Connection Pool object in RPD poses - which is definitively not the case for Connection object used in Semantic Modeler. We use (in our OAS environments) variables for Connection Pool properties as standard and thus migration to Semantic Modeler from Administration Tool/RPD (which we would consider due to multi user development concept in Semantic Modeler) is not possible. And I would also add following to this requirement:

Adding the ability (this pays for on-prem OAS only) to refer connect descriptor from tnsnames.ora in Connection object definition (instead of "pasting" full connect string) - tnsnames.ora represents centralized place for maintaining connection definitions to Oracle databases and we are leveraging it in OAS . Pls - @Bret Grinslade - Oracle Analytics-Oracle , @Pravin Janardanam-Oracle - could you take those into consideration for Semantic Modeler improvement (there is quite a lot of work to be done for Semantic Modeler being adequate replacement of BI Admin Tool).

Pravin Janardanam-Oracle

@Rudy Hruska Semantic Modeler does allow specifying connection scripts for Run on connect, Run before query, Run after query, and Run on disconnect.

You can alter the session to run on connect to set session context.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SESSION.html#GUID-27186B28-7EFC-4998-B1ED-2B905CC0211B

OAC_Connection_Pool_On_Connect_Scripts.jpg

Juan Flores-190415

Hi facing the same issue, here it says solution delivered?, is there any documentation how to add variables in web semantic model connections?

Michal Zima

@Pravin Janardanam-Oracle @Bret Grinslade - Oracle Analytics-Oracle @Benjamin Arnulf-Oracle This idea has been incorrectly set to the Delivered state. What has been required - the ability to reference variables in the Connection Pool definition (as it is possible when developing RPD in the BI Administration Tool) is definitely not possible in Semantic Modeler - thanks to the concept of System Connections reference in the Connection Pool definition (and Connection definition does not have this capability - not even referencing connect descriptor defined in tnsnames.ora as has been required by us as customer). So pls, revise this Idea and set it back to another status. Thanks

Pravin Janardanam-Oracle

@Juan Flores-190415 For the scenario of using Oracle Virtual Private Database (VPD) and data level security, have you tried setting the session context in connection pool using Run on connect, Run before query. This is discussed here:

https://forums.oracle.com/ords/apexds/post/obiee-security-using-vpd-6022

https://forums.oracle.com/ords/apexds/post/obiee-11g-repository-question-session-context-4895

Pravin Janardanam-Oracle

@Michal Zima Connection Pool definitions can reference variables in the "Connection Scripts". Connection definition has been separated from Connection Pool in Semantic Modeler with external system connections. This avoids one of the significant challenges we had with the need for updating the connection username / password in the connection pool during Semantic Model migration from one environment to another.

Michal Zima

@Pravin Janardanam-Oracle Sorry to say that Pravin, but you are talking about completely different thing. What is wanted (as this can be done in Connection Pool definition in RPD) is to reference in following properties of Connection Pool:
Data Source Name
User Name
static variables. Reason for using that in RPD is simple - we have huge RPD with plenty of DB objects in physical layer, having plenty of Connection Pool objects and many Connection Pool objects refer still the same DB instance (but connecting with different username serving for different "reporting" application). So we create static repository variable with value of connect descriptor name from tnsnames.ora and then reference this static variable in all Connection Pool objects, connecting to the same DB instance. It is very effective and we are maintaining connection definition in one place (static variable). If we want to "redirect" Connection Pool to different DB, we simplt change the value of static variable and it is done. And this is missing in Semantic Modeler which "externalize" credentials and connection details to System Connection object defined in DV - which does not have that "flexibility" (and it even does not allow to refer connect descriptor from tnsnames.ora, you need to "paste" full connect string into connection definition). Is the use case clear now for you ?

Pravin Janardanam-Oracle

@Michal Zima I appreciate the details. My response is with respect to the OP concerns regarding unable to use VPD, row level security and "Provide the ability to execute a post login connection script to allow setting contexts…".

Regarding the need for a static variable for a connection descriptor, the same is accomplished using "External System Connections." Multiple connection pools can be mapped to the same connection. When a connection descriptor needs to be updated—unlike a static variable, which requires an update to the Semantic Model—with the system connection, only the connection definition needs to be updated, and thus, no Semantic Model update or redeployment is required.

The feature that is missing now is the capability to use a proxy username with a USER variable for the connection username. I have raised this as a separate idea

https://community.oracle.com/products/oracleanalytics/discussion/26137/support-for-variables-in-connection-usernames

If this is required for VPD and row level security, the use of session context should accomplish this use case.

Regarding a central tnsnames.ora for Oracle database connections, I have raised another idea

https://community.oracle.com/products/oracleanalytics/discussion/26138/use-connection-description-from-tnsnames-ora

Michal Zima

@Pravin Janardanam-Oracle Thanks for you response. But your reply:
"Regarding the need for a static variable for a connection descriptor, the same is accomplished using "External System Connections." Multiple connection pools can be mapped to the same connection. When a connection descriptor needs to be updated—unlike a static variable, which requires an update to the Semantic Model—with the system connection, only the connection definition needs to be updated, and thus, no Semantic Model update or redeployment is required."
does not fully cover the use-cases. As I mentioned, you can have plenty of Connection Pools in RPD, connecting to same Oracle Db instance, but using different DB accounts for querying. And in RPD, Data Source Name all those Connection Pool will be referring to same static variable (but will differ with User Name/Password properties). This scenario cannot be achieved with "External System Connections" as you are pointing. But if you will be able to deliver (quite soon - in next OAS yearly release) feature "Reference of tnsnames.ora connect descriptor in Connection definition" (newly created Idea):

https://community.oracle.com/products/oracleanalytics/discussion/26138/use-connection-description-from-tnsnames-ora

which I am asking for pretty long time (we had discussion, also about this missing option pretty long time ago - 2 years or so during our call about Semantic Modeler, @Bret Grinslade - Oracle Analytics-Oracle has been on this call as well if I remember correctly) , then we can live without those static variable reference in data source name. Thanks-

Rudy Hruska

I appreciate the discussion, and I agree with @Pravin Janardanam-Oracle. The original problem statement is simple "Port current OBIEE RPD semantic layer connection pools functionality to the OAC semantic modeler". The RPD connection pools allow for the use of an Oracle proxy user (Username: <proxy_user_name>[VALUEOF(NQ SESSION.USER)]). This passes the user identity of OAC user down to the database in a completely unified manner. The Oracle database proxy user provides the customer the ability to track and audit all database activity to a single user, whether they connect through SQL*plus, PLSQL developer, OAC, or any other application. If we have a long running query, we can see it in session manager with the same name as the OAC user. Loging in through the proxy user strips user of all the user's permissions (even DBA roles) and assigns just the roles granted through the proxy. The combination of proxy user and VPD, gives total control of application roles and VPD at the database kernel instead of all the end points. I realize there are ways to work around using the proxy user by setting contexts, but I think the OOTB proxy user and VPD database capabilities are an incredibly powerful combination, and we've used them in the RPD for over 15 years.

Thanks Rudy