Restricting user access to Connection Pool

We can restrict any user from accessing the Connection Pool using the Permission option in Connection Pool.

Just click on the Permission button, then you will get "Permission - Connection Pool " window. By default, this window will show only user Groups. To see users also, check the Show all users/groups option.

For each user and group, you can allow or disallow access privileges for an object by clicking in the check box to toggle among the following options:

• A check mark indicates that a permission is granted.
• An X indicates that a permission is denied.
• An empty check box indicates that a permission has not been modified. 

In the above picture, we can find one user group (Test) and two users (U1,U2). Now, let us deny access to the Group called Test.To deny access, just make the check box cross (X), as shown below.

All the users of that group cant access the connection pool. That is when the user runs a report, then he will get an error like : [nQSError: 19007] The user does not have sufficient privilege to access the database

Note : Assume that,
           you have created two connection pools(eg. CP1 and CP2) for a Data source in the physical layer,
           and you have a user called U1 and you have denied access to the connection Pool C1.
 When a user U1 requests connection, as the user don not have permission to Connection pool C1,
 OBI Server will route the request to the second Connection Pool C2. So, If you want any user to be                     denied to access the underlying data source, remove access to all connection poll, not any single connection pool.

Setting Up Database Authentication

The Oracle BI Server can authenticate users through database logons. If a user has read permission on a specified database, the user will be trusted by the Oracle BI Server. Unlike operating system authentication, this authentication can be applied to Oracle BI Presentation Services users.

Database authentication can be used in conjunction with external table authentication. If external

table authentication succeeds, then database authentication is not performed. If external table

authentication fails, then database authentication is performed.

Database authentication requires the user ID to be stored in the Oracle BI repository.

To set up database authentication

1 Create users in the repository named identically to the users in a database. Passwords are not stored in the repository.

2 Assign the permissions (including group memberships, if any) you want the users to have.

3 Specify the authentication database in the Security section of the NQSConfig.INI file.That is uncomment  the below lines by removing # symbol in config file.

.4 Create a DSN for the database.

5 Import the database into the Physical layer. You do not need to import the physical table objects.

The database name in the Physical layer has to match the database name in the NQSConfig.INI

file (as specified in Step 3).

6 Set up the connection pool without a shared logon.

When a user logs on to the Oracle BI Server, the server attempts to use the logon name and

password to connect to the authentication database using the first connection pool associated with it. If this connection succeeds, the user is considered to be authenticated successfully.

If the logon is denied, the Oracle BI Server issues a message to the user indicating an invalid user ID or password.

Setting Up External Table Authentication

Instead of storing user IDs and passwords in an Oracle BI repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes.

The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users.

NOTE: If a user belongs to multiple groups, the group names should be included in the same column separated by semicolons.

External table authentication can be used in conjunction with database authentication. If external

table authentication succeeds, then database authentication is not performed. If external table

authentication fails, then database authentication is performed.

External table authentication uses Oracle BI session variables that you define using the Variable

Manager of the Administration Tool.

Session variables get their values when a user begins a session by logging on. Certain session

variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.

To set up external table authentication, you define a system variable called USER and associate it with an initialization block that is associated with an external database table.

Whenever a user logs in, the user ID and password will be authenticated using SQL that queries this database table for authentication.

 After the user is authenticated successfully, other session variables for the user could

also be populated from the results of this SQL query

The presence of a defined system variable USER determines that external authentication is done.

Associating USER with an external database table initialization block determines that the user will be authenticated using the information in this table.

To set up external table authentication

1. Import information about the external table into the Physical layer. In this illustration, the

database sql_nqsecurity contains a table named securitylogons and has a connection pool named

External Table Security.

2. Select Manage > Variables to open the Variable Manager.

3. Select Initialization Blocks on the left tree pane.

4. Right-click on white space in the right pane, and then click on New Initialization Block from the right-click menu.

5. In the Initialization Block dialog box, type the name for the initialization block.

6. Click on the Select Database from the Data Source Connection drop-down list.

7. Click on Browse to search for the name of the connection pool this block will use.

8. In the Initialization String area, type the SQL statement that will be issued at authentication

time.

The values returned by the database in the columns in your SQL will be assigned to variables.

The order of the variables and the order of the columns will determine which columns are

assigned to which variables.

Consider the SQL in the following example:

select username, grp_name, SalesRep, 2 from securitylogons where username =

':USER' and pwd = ':PASSWORD'

This SQL contains two constraints in the WHERE clause:

■ :USER (note the colon) equals the ID the user entered when logging on.

■ :PASSWORD (note the colon again) equals the password the user typed.

The query will return data only if the user ID and password match values found in the specified

table.

You should test the SQL statement outside of the Oracle BI Server, substituting valid values for

:USER and :PASSWORD to verify that a row of data returns.

9 If this query returns data, the user is authenticated and session variables will be populated.

Because this query returns four columns, four session variables will be populated. Create these

variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking Edit Data Target button.

If a variable is not in the desired order, click on the variable you want to reorder and use the Up

and Down buttons to move it.

10 Click OK to save the initialization block.

Order of Authentication

If the user does not type a logon name, then OS authentication is triggered, unless OS authentication is explicitly turned off in the NQSConfig.INI file. Additionally, OS authentication is not used for Oracle BI Presentation Services users.

The Oracle BI Server populates session variables using the initialization blocks in the desired order that are specified by the dependency rules defined in the initialization blocks. If the server finds the session variable USER, it performs authentication against an LDAP server or an external database table, depending on the configuration of the initialization block with which the USER variable is associated.

Oracle BI Server internal authentication (or, optionally, database authentication) occurs only after these other possibilities have been considered.

Security in OBIEE

Security in OBIEE

Security in Oracle BI can be classified broadly into the following three types.

1. Object Level security/authorization

2. Data Level security/authorization

3. User Authentication / User Level Security

Object Level security

Object-level security controls the visibility to business logical objects based on a user's role.

You can set up object-level security for -

·         Repository level: In Presentation layer of Administration Tool, we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.

 Web level: This provides security for objects stored in the Presentation Catalog, such as dashboards, dashboards pages, folder and reports. You can only view the objects for which you are authorized. For example, a mid level manager may not be granted access to a dashboard containing summary information for an entire department.

Data Level security

Data-level security controls the visibility of data (content rendered in subject areas, dashboards, Oracle BI Answers, and so on) based on the user's association to data in the transactional system.

This controls the type and amount of data that you can see in a report. When multiple users run the same report, the results that are returned to each depend on their access rights and roles in the organization. For example, a sales vice president sees results for all regions, while a sales representative for a particular region sees only data for that region. 

 User Authentication in OBIEE :

The goal of the authentication configuration is to get a confirmation of the identity of a user based on the credentials provided. 

In OBIEE, the credentials provided are hold in this two variables:

·         USER

·         PASSWORD

The authentication process in OBIEE is managed by the BI Server.

OBIEE Support four types of authentication.

1)LDAP Authentication : Users are authenticated based on credentials stored in LDAP.This is the BEST method to do authentication in OBIEE and it supports company’s Single Sign On (SSO) philosophy as well.

2)External Table Authentication : you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes.
To know about configuration of External Table Authentication click hear >>.

3)DatabaseAuthentication: The Oracle BI Server can authenticates user based on database logins. If a user has read permission on a specific database. Oracle BI Presentation Services authenticates those users. To know about configuration of Database Authentication click hear >>.


4)Oracle BI Server User Authentication: You can maintain lists of users and their passwords in the Oracle BI repository using the Administration Tool. The Oracle BI Server will attempt to authenticate users against this list when they log on.



Oracle BI Server User Authentication is not quite popular as it has its support/maintenance issue associated, once system grows beyond certain users.

Difference between Authentication and Authorization

So many developers are having the confusion with the words Authentication and Authorization. Here is a small explanation of it.

Authentication is nothing but validating the user i.e., checking user’s username and  password to identify him.

Whereas authorization is the process of giving privileges to the authenticated users. That means all authenticated users can not performs all operations. Depending on his roles some privileges are given to them in the form authorization.

For example for a particular bank website, customers, employees, administrators can login into that websites. But the options available to these persons are different at customer level , bank employee level, administrators level etc. This is authorization.

To Restrict access to database during particular time period

We can restrict access to any database during particular time period  for any user.
Open the Administrator Tool, Go to Manage -> security.
In Security Manager, click on Users in left pane, then  on right pane select the user and open properties(right click on user and select properties).
Then click on Permission Tab.

In the User/Group Permissions dialog box, click the Query Limits tab and expand the dialog box to view all columns then to restrict access to a database during particular time periods, in the Restrict column, click the ellipsis button.

In the Restrictions dialog box, perform the following steps: 
1. To select a time period, click the stat time and drag t end time.
2. To expilicitly allo access, click Allow.
3. To explicitly disallow access, click Disallow.

In my case, I selected paint database(clicked on the ellipsis located beside to paint ) and restrict access to that database from 6am to noon on every Monday for that user.

Then click OK. Restart the OBI server services and check it presentation services by login  as that user.

Oracle BI Administrator Account

The Oracle BI Administrator account (user ID of Administrator) is a default user account in every Oracle BI repository. This is a permanent account. It cannot be deleted or modified other than to change the password and logging level. It is designed to perform all administrative tasks in a repository, such as importing physical schemas, creating business models, and creating users and groups.

NOTE:  The Oracle BI Administrator account is not the same as the Windows NT and Windows 2000 Administrator account. The administrative privileges granted to this account function only within the Oracle BI Server environment.

When you create a new repository, the Oracle BI Administrator account is created automatically and has no password assigned to it. You should assign a password for the Oracle BI Administrator account as soon as you create the repository.  

Any query issued from the Oracle BI Administrator account has complete access to the data; no restrictions apply to any objects.

User Name and Group Name cant be same for OBI Presentation Catalog

This is a small tip for Presentation Services Administrator.
While creating user group, I gave group name same as the user name, I received an error like 'Error Mapping Groups '. After that I gone through some blogs and got to know that "USER NAME AND GROUP NAME CAN NOT BE SAME".





So, while creating groups for Presentation Service Catalog, make sure that username and groupname must not be same.

Set Minimum Length for Passwords

When we create a Repository, Administrator account will be the default account with no password and we can create as many user accounts we need. By default Administrator will not be having any password. We have to set password for user accounts, there is no restriction on password length, i.e password may contain any number of characters.

We can set the minimum length for passwords in the NQSConfig.INI file using the MINIMUM_PASSWORD_LENGTH setting.

By default, value of MINIMUM_PASSWORD_LENGTH is 0.

If you want the minimum length for password is 5, then set MINIMUM_PASSWORD_LENGTH = 5. 

Note : ensure that the existing passwords length must be greater than and equal to 5. Otherwise login will be failed. So before changing the value for MINIMUM_PASSWORD_LENGTH, make sure that all passwords length is more than or equal to that value.

No Log Found

This is the usual message we get when we try to open the log file(ie, When we click on View Log in Session Management). This is due to the logging level the current user having.

The logging level is a parameter which control the level of information that you can retrieve in the log file.
 If we want the user to see log information in the log file, we should change the logging level in RPD.

Logging level = 0 means, no access to log file.

You can enable logging level for individual users, you cannot configure a logging level for a group.
In normal operations :

• users have a logging level set to 0
• administrator have a logging level set to 2
   
   Description of logging levels

  Logging Levels	Logging Level Information That Is Logged
  Level 0	No logging
  Level 1	Logs the SQL statement issued from the client application Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query
  Level 2	Logs everything logged in Level 1 Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application
  Level 3	Logs everything logged in Level 2 Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails
  Level 4	Logs everything logged in Level 3 Additionally, logs the query execution plan.
  Level 5	Logs everything logged in Level 4 Additionally, logs intermediate row counts at various points in the execution plan.

   
  To change the logging level follow the below steps : 
  • In the Administration Tool, select Manage > Security . The Security Manager dialog box appears.
  • Double-click the user's user ID. The User dialog box appears.
  • Set the logging level by clicking the Up or Down arrows next to the Logging Level field.

   
  
   

Reset the Adminisrator Tool Password in OBIEE

Step1 : Open NQSConfig.INI file (Path : C:\OracleBI\server\Config\NQSConfig.INI)
Step2 : Go to Security part and find AUTHENTICATION_TYPE = BYPASS_NQS;
            Uncomment (remove #) this part.
Step3 : Stop the BI Server service
Step4 : Close Administration Tool, if opened. (Then only the changes are affecting to the Administration Tool)
Step5 : Open Administration Tool, then rpd which needs password reset, in offline mode.
Step5 : Give any password, it accepts.
Step6 : Now go to Manage > Security. From here it’s the normal process how you change password.
Step7 : Save the changes to rpd.
Step8 : Revert back the changes in NQSConfig.INI
Step9 : Start BI Server and open rpd with new password.