Tag Archives: 11g
How-to: Direct Database Request Configuration OBIEE 11g
The ability to execute SQL statements directly against the data warehouse, also known as 'Direct Database Requests' is not new to OBIEE 11g. Usually used a last resort, the direct database request has many limitations including:
None the less, if a client has a specific reporting requirement that cannot easily be tied into an existing data model, then the direct database request becomes a viable alternative.
As most OBIEE Architects know, to enable Direct Database Requests is done within Answers in the 'Manage Privileges' Section:
- Physical SQL statement is always executed
- Does not utilize OBIEE 11g's BI Server engine for maximum query optimization
- Increased run time
None the less, if a client has a specific reporting requirement that cannot easily be tied into an existing data model, then the direct database request becomes a viable alternative.
As most OBIEE Architects know, to enable Direct Database Requests is done within Answers in the 'Manage Privileges' Section:
After applying the above security privilege, the 'expected result' is for a member of the applicable application role to be granted the ability execute direct database reports. Which in turn leads to a surprise when the user is still unable to execute a direct database request.
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 13017] User or application role has not been granted the Direct Database Access privilege to access the database. Please verify the User/Group Permissions in the Oracle BI Administration Tool.Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error.
Why?
What many developers forget to remember is that components of security are still managed within the repository, even in OBIEE 11g.
The ability to control who can execute direct database requests is managed within the Admin Tool's Identity Manager. For every applicable application role, you must grant direct database requests to each specific subject area:
Manage -> Identity -> Application Roles Tab -> Permissions
Note that by default 'Execute Direct Database Requests' is set to ignore, which inherits privileges granted by its parent security role. This normally implies 'disallow'. By setting the the permission to 'allow' , you are granting the user the ability to run a direct database request against a specific subject area.
From a security perspective, granting access to specific subject areas is the preferred method of authorization when the alternative is a blank 'grant' or 'deny' via Manage Privileges.
As outlined in my primer on object level security & inheritance , inheritance applies but direct approval or denial supersedes any implied security rules.
keywords: identity manager, direct database requests, obiee 11g, answers, manage privileges, security, OPR4ONWY:U9IM8TAC:OI2DL65P
How-to: Automatic Repository Deployment and Promotion Process OBIEE 11g
A typical deployment process an OBIEE 11g repository will follow in most production environments resembles the following:
The development zone represents a series of developer machines modifying a repository either by:
We're going to focus on the 'Production Deployment Path' that takes the repository from the Dev/Unit test machine and migrates it through the deployment path from Assembly Test through Production.
This production path is critical because it's at this point where the repository leaves the 'safe haven' of the developer environment and goes through various stages of testing, usually performed by another team. Each testing team will have their own BI Server and database that the repository must connect to for testing.
Usually, the repository remains the same through all environments except for:
We're going to perform the assembly test to production deployment process in a completely automated fashion by:
We're going to create an eXtensible Universal Database Markup Language (XUDML for short) that contains connection pools specific for each environment. This file is generated by biserverxmlgen and is basically the repository exported to XML. The way to accomplish this in OBIEE 10g was using UDML which has seen been deprecated and is not supported by Oracle - see Oracle Note 1068266.1.
"libnqsclusterapi64.so: open failed: No Such file or directory"
You will only need to re-generate this file if you change your connection pool information. This XUDML file will be used to update connection pools of your target environment.
Where:
Example:
You now have a system test repository that you can upload to your applicable environment.
Many web sites show how to upload the repository via the FMW Enterprise Manager, but that is generally alot slower and not as efficient as scripting it.
The uploadRPD.py script below performs five tasks:
The aforementioned code works on scaled out (clustered) environments since there is only one active admin server. The code will connect to the active admin server located in your first node, and WLST will propagate changes to each node. You can validate this by navigating to the local repository folder of each node.
To run the script, load wlst located at :
and perform the execfile command as follows:
In conclusion, the entire repository deployment process can be executed by the following two scripts:
The development zone represents a series of developer machines modifying a repository either by:
- Multi User Directory Environment Configuration (MUDE)
- Local development machines where each developer migrates their changes to a centralized OBIEE 11g dev/unit test box via a patch-merge process
We're going to focus on the 'Production Deployment Path' that takes the repository from the Dev/Unit test machine and migrates it through the deployment path from Assembly Test through Production.
This production path is critical because it's at this point where the repository leaves the 'safe haven' of the developer environment and goes through various stages of testing, usually performed by another team. Each testing team will have their own BI Server and database that the repository must connect to for testing.
Usually, the repository remains the same through all environments except for:
- Connection Pools
- Environment specific server variables
We're going to perform the assembly test to production deployment process in a completely automated fashion by:
- Generating an XUDML file that connections connection pool information
- Generating a new system test repository by applying the System test XUDML to the assembly test repository
- Using WLST to upload the RPD to the specif iced environment
Step 1: Generate the XUDML file for the assembly, system, staging and production environments
We're going to create an eXtensible Universal Database Markup Language (XUDML for short) that contains connection pools specific for each environment. This file is generated by biserverxmlgen and is basically the repository exported to XML. The way to accomplish this in OBIEE 10g was using UDML which has seen been deprecated and is not supported by Oracle - see Oracle Note 1068266.1.
Step 1.1 - Set Variables via bi-init.sh
Note the space between the '.' and the '/' . This is required for the i-init.sh script to propagate through all folders
. /export/obiee/11g/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh
Step 1.2 - Generate XUDML file
Navigate to export/obiee/11g/Oracle_BI1/bifoundation/server/bin/ and run:biserverxmlgen -R C:testconnpoolbase.rpd -P Admin123 -O c:testconnpooltest.xml -8
- Replace base.rpd with your source RPD - i.e. if you want to generate connection pool information for assembly test, base.rpd should represent your assembly test repository.
- -O generates the output XML file
- -8 represents the UTF-8 formatting for the XML file
- -P represents the password of the base repository
"libnqsclusterapi64.so: open failed: No Such file or directory"If you are successful, your output should be as follows:
Step 1.3 Remove inapplicable entries
For connection pool migrations, your script should only include:
<?xml version="1.0" encoding="UTF-8" ?><Repository xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DECLARE>
<Connection Pool ......>
</ConnectionPool>
</DECLARE>
</Repository>
You will only need to re-generate this file if you change your connection pool information. This XUDML file will be used to update connection pools of your target environment.
Step 2: Apply XUDML file to base repository
Let's say you have an assembly test repository and a system test XUDML file. The biserverxmlexec.sh script will take your assembly test repository, system test XUDML file and generate a 'system test repository' using the following command located in export/obiee/11g/Oracle_BI1/bifoundation/server/bin/biserverxmlexec -I input_file_pathname [-B base_repository_pathname] [-P password]
-O output_repository_pathname
Where:
- input_file_pathname is the name and location of the XML input file you want to execute base_repository_pathname is the existing repository file you want to modify using the XML input file (optional). Do not specify this argument if you want to generate a new repository file from the XML input file.password is the repository password.
- If you specified a base repository, enter the repository password for the base repository. If you did not specify a base repository, enter the password you want to use for the new repository.
- The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release.
- output_repository_pathname is the name and location of the RPD output file you want to generate
Example:
biserverxmlexec -I testxudml.txt -B rp1.rpd -O rp2.rpd
Give password: my_rpd_password
You now have a system test repository that you can upload to your applicable environment.
Step 3: Upload Repository to BI Server via WLST
Many web sites show how to upload the repository via the FMW Enterprise Manager, but that is generally alot slower and not as efficient as scripting it.
The uploadRPD.py script below performs five tasks:
- Connects to WLST
- Locks the System
- Uploads the RPD
- Commits Changes
- Restarts BI Services
connect('user','pass','server')
user = ''
password = ''
host = ''
port = ''
rpdpath = '/path/path2/repository.rpd'
rpdPassword = ''
# Be sure we are in the root
cd("....")
print(host + ": Connecting to Domain ...")
try:
domainCustom()
except:
print(host + ": Already in domainCustom")
print(host + ": Go to biee admin domain")
cd("oracle.biee.admin")
# go to the server configuration
print(host + ": Go to BIDomain.BIInstance.ServerConfiguration MBean")
cd ('oracle.biee.admin:type=BIDomain,group=Service')
biinstances = get('BIInstances')
biinstance = biinstances[0]
# Lock the System
print(host + ": Calling lock ...")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("lock", objs, strs)
except:
print(host + ": System already locked")
cd("..")
# Upload the RPD
cd (biinstance.toString())
print(host + ": Uploading RPD")
biserver = get('ServerConfiguration')
cd('..')
cd(biserver.toString())
ls()
argtypes = jarray.array(['java.lang.String','java.lang.String'],java.lang.String)
argvalues = jarray.array([rpdpath,rpdPassword],java.lang.Object)
invoke('uploadRepository',argvalues,argtypes)
# Commit the system
print(host + ": Commiting Changes")
cd('..')
cd('oracle.biee.admin:type=BIDomain,group=Service')
objs = jarray.array([],java.lang.Object)
strs = jarray.array([],java.lang.String)
invoke('commit',objs,strs)
# Restart the system
print(host + ": Restarting OBIEE processes")
cd("....")
cd("oracle.biee.admin")
cd("oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service")
print(host + ": Stopping the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("stop", params, signs)
BIServiceStatus = get("ServiceStatus")
print(host + ": BI ServiceStatus " + BIServiceStatus)
print(host + ": Starting the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("start", params, signs)
BIServerStatus = get("ServiceStatus")
print(host + ": BI ServerStatus " + BIServerStatus)
The aforementioned code works on scaled out (clustered) environments since there is only one active admin server. The code will connect to the active admin server located in your first node, and WLST will propagate changes to each node. You can validate this by navigating to the local repository folder of each node.
To run the script, load wlst located at :
/export/obiee/11g/oracle_common/common/bin/wlst.sh
and perform the execfile command as follows:
execfile(‘/path/path1/path2/uploadRPD.py’)
In conclusion, the entire repository deployment process can be executed by the following two scripts:
- biserverxmlexec (provided by Oracle)
- uploadRpd.py (see above)
keywords: Repository, RPD deployment, repository deployment automation, obiee 11g, scripted rpd deployment, wlst
FYI: User able to log-in even with Invalid (or no) password in OBIEE 11g
With the release of Oracle Business Intelligence (OBIEE) 11g, the 'old' (read: OBIEE 10g) way of authenticating users has been deprecated, mainly - authenticating a user's credentials via the repository which i've outlined in this post . At its core, 10g authentication was accomplished using init blocks that populated the :USERS and :GROUPS session variables in the repository.
OBIEE 11g, on the other hand, authenticates via authenticators in weblogic such as Oracle Internet Directory. This guide actually covers OID authentication in OBIEE 11g .
The OBIEE 10g method for authentication still exists in 11g, and unfortunately it is still possible to configure 11g init blocks so that the query does not check the password of the user.
For example:
would just check the user id and not the password was correct but not check the password. In a scenario where such an INIT block exists and is set to act as an authentication block, this can lead to users being able to log in with any (or no) password. It can also lead to some apparently odd/inconsistent behaviour. Consider the scenario where Users A and B both exist in OID which is set as the primary identity store. But User B also exists in a database which is referenced by an INIT block as described above. Both try to login using the wrong password. User A will simply fail. However, while User B will fail Authentication against OID, because the BI Server knows there is an Authentication INIT block set, it will then attempt to run that for each of them and in the case of User B, because their username is in the USER_ID column of the USERS table, they will be allowed in as the INIT block query apparently succeeds, even though it does not in fact correctly check the user’s password.
There is no 'fix' for this other than to force username validation for init blocks that use the :USER block or completely avoid using the :USER session variable.
OBIEE 11g, on the other hand, authenticates via authenticators in weblogic such as Oracle Internet Directory. This guide actually covers OID authentication in OBIEE 11g .
The OBIEE 10g method for authentication still exists in 11g, and unfortunately it is still possible to configure 11g init blocks so that the query does not check the password of the user.
For example:
SELECT USER_ID FROM USERS WHERE USER_ID = ':USER'
would just check the user id and not the password was correct but not check the password. In a scenario where such an INIT block exists and is set to act as an authentication block, this can lead to users being able to log in with any (or no) password. It can also lead to some apparently odd/inconsistent behaviour. Consider the scenario where Users A and B both exist in OID which is set as the primary identity store. But User B also exists in a database which is referenced by an INIT block as described above. Both try to login using the wrong password. User A will simply fail. However, while User B will fail Authentication against OID, because the BI Server knows there is an Authentication INIT block set, it will then attempt to run that for each of them and in the case of User B, because their username is in the USER_ID column of the USERS table, they will be allowed in as the INIT block query apparently succeeds, even though it does not in fact correctly check the user’s password.
There is no 'fix' for this other than to force username validation for init blocks that use the :USER block or completely avoid using the :USER session variable.
Oracle has acknowledged this in security document 1359798.1
keywords: OBIEE 11g security, data level security, initialization blocks, USER session variables, weblogic authenticators,
FYI: Overview on Object Level Security, Application Roles, and Inheritance in OBIEE 11g
In Oracle Business Intelligence (OBIEE) 11g, Oracle has fundamentally changed how we map users to various security privileges.In OBIEE 10g, Object Level Security was enforced using the USER session variable, which mapped to a GROUP session variable. This created a list of possible 'groups', which a developer would then apply security restrictions to, in either Answers (Managed Privileges) and/or Security Managed in the repository.
A high level flow is outlined below:
In OBIEE 11g, security authentication is enforced in the Weblogic Admin Server, and a user's security privileges are tied to their corresponding Application Roles in Fusion Middleware as shown in the diagram below:
In this example, I created Folder 3, which grants 'read' access to Test Role 1 and 'modify' access to 'Test Role 2'. 'Testuser1' is a member of both 'Test Role 1' and 'Test Role 2'.
As expected, testuser1 does not have access to Folder 4 because of Test Role 3
A high level flow is outlined below:
In OBIEE 11g, security authentication is enforced in the Weblogic Admin Server, and a user's security privileges are tied to their corresponding Application Roles in Fusion Middleware as shown in the diagram below:
The key take away is that object level security is applied to application roles and not groups. Why application roles? In Weblogic and Fusion Middleware, we can actually assign certain privileges to an application roles - we call these 'Application Policies'. For example, we can grant a certain application role the ability to 'edit the repository', or 'act as another user'. This feature, not possible in OBIEE 10g, now allows us to not only control what objects are being viewed, but also gives us the capability to control who can execute certain actions within the BI environment. This topic will be discussed in much greater detail in another guide.
Now let's go over the basic rules of Object Level Security for Application Roles in OBIEE 11g:
- If a user is a direct member of an application role, they will have access to the reports allowed by that application role.
- If a user is not a member of an application role, they will not have access to the reports allowed by that application role.
- If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.
- unless the user is explicitly denied. Explicit denial supersedes all security privileges.
- If a user is a member of Application Role X, and Application Role X is a member of Application Role Y, the privileges in Application Role X supersede the privileges of Application Role Y
Let's cover each scenario in detail:
- If a user is a direct member of an application role, they will have access to the reports allowed by that application role.
In this example, I granted Application Role 'Test Role 1' full control to folder 'Folder 1'. I then logged in as 'testuser1' who is a member of Application Role 'Test Role 1'. And as expected, testuser1 can read/write/edit/delete the folder.
- If a user is not a member of an application role, they will not have access to the reports allowed by that application role.
In this example, I created 'Folder 2', only accessible by members of the 'BIAdministrator Application Role'. I then log in as a 'testuser1', which is not a member of the 'BIAdministrator Application Role'
As BIAdministrator:
As testuser1:
Note that in the above scenario, 'denying' the application role access accomplishes the same thing as taking no action onto the application role role (i.e. ignoring it completely)
- If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.
In this example, I created Folder 3, which grants 'read' access to Test Role 1 and 'modify' access to 'Test Role 2'. 'Testuser1' is a member of both 'Test Role 1' and 'Test Role 2'.
As expected, Testuser1 has modify rights to Folder 3 (noted by 'X', ability to delete), despite being a member of Test Role 1 which only grants the user read access
- If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.
- unless the role is explicitly denied
In this example, TestUser1 is a member of Test Role 1 and Test Role 2 and Test Role 3. Test Role 1 grants testuser1 open rights, Test Role 2 grants testuser1 modify rights and Test Role 3 is explicitly denied.
As expected, testuser1 does not have access to Folder 4 because of Test Role 3
- If a user is a member of Application Role X, and Application Role X is a member of Application Role Y, the privileges in Application Role X supersede the privileges of Application Role Y
In this example, testuser1 is a member of application role 'Test Role 4'. Application role 'Test Role 5' is a member of application Role 'Test Role 4'. Test Role 4 grants 'open' privileges to Folder 5 and Test Role 5 grants 'full control' to Folder 5.
As expected, testuser1 only has read/open access to Folder 5 even though Application Role 'Test Role 5' grants full control. This is because direct inheritance overrides indirect inheritance
Even if the inherited role explicitly denies access to folder 5, the user will still be able to access folder 5 because the direct role grants read/open access:
Note how testuser1 has modify access to Folder 5 (noted by the 'X') , despite inheriting a role that is denied access to the same folder.
These basic rules can be applied to any hierarchy, no matter how complex. Think you've mastered these 4 basic rules? Identify the final privileges for User 1 in the scenario below:
* example courtesy of Oracle - found in Oracle Business Intelligence Fusion Middleware Security Guide
Result:
- User is a direct member of Role 1 and 2 and indirect member of Role 3, Role 4 and Role 5
- User has no access to Dashboard A
- User has open access to Dashboard B
- User has full control of Dashboard C
- User has no access to Dashboard E
- User has open access to Dashboard D
keywords : object level security, obiee security, obiee application roles, obiee 11g security, weblogic application roles, obiee inheritance
How-to: Impersonate or Act As Other Users (e.g. End Users) in OBIEE 11g
In a typical Oracle Business Intelligence (OBIEE) 11g environment, potential defects or data discrepancies arise that will require the production support team to investigate or research the potential issue. A useful way to research the issue is by having the developer 'act as' the specific end user who is encountering the defect, usually by logging in as their account or an account with similar security rights.
For security reasons, the developer or support team is usually never given the end users login credentials so unless they re-create an account with the same privileges, they're unable to 'see what the end user sees'.
In OBIEE 11g Oracle outlines a detailed method for acting as another user through a 'proxy', outlined here .
This process requires pre-configuration and deployment of key tables to facilitate acting as another user, and is not set up 'out of the box'.
Although there is value in setting up the Act As / Proxy functionality (for example, a supervisor wants to delegate some of his work to his direct report), from a development and troubleshooting perspective, impersonation can be achieved with the HTTP header and and the following parameters:
Before you can use this feature, the appropriate privilege must be added to your Administrator role (usually BIAdministrator Application Role):
Step 2: Add required roles to Administrator's Application Policies
In the obi_ strip, click the edit button for the Administration Application Policies and search for
keywords: obiee impersonate, obiee act as, obiee 11g security, obiee 11g answer
For security reasons, the developer or support team is usually never given the end users login credentials so unless they re-create an account with the same privileges, they're unable to 'see what the end user sees'.
In OBIEE 11g Oracle outlines a detailed method for acting as another user through a 'proxy', outlined here .
This process requires pre-configuration and deployment of key tables to facilitate acting as another user, and is not set up 'out of the box'.
Although there is value in setting up the Act As / Proxy functionality (for example, a supervisor wants to delegate some of his work to his direct report), from a development and troubleshooting perspective, impersonation can be achieved with the HTTP header and and the following parameters:
- NQUser
- NQPassword
- Impersonate
For example:
http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate
Before you can use this feature, the appropriate privilege must be added to your Administrator role (usually BIAdministrator Application Role):
- oracle.bi.server.impersonateUser
- oracle.bi.server.queryUserPopulation
Step 1: Navigate to Application Policies in Fusion Middleware (:7001/em/)
Farm_BIFoundation_domain -> Business Intelligence -> coreapplication -> security -> Application Roles
Step 2: Add required roles to Administrator's Application Policies
In the obi_ strip, click the edit button for the Administration Application Policies and search for
- oracle.bi.server.impersonateUser
- oracle.bi.server.queryUserPopulation
No restart is required. You can now use:
http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate
to act as other end users. You will inherit all application roles as if you were the actual user.
keywords: obiee impersonate, obiee act as, obiee 11g security, obiee 11g answer
