OCI-Oracle ATP- Connect via SQLPlus on Linux

Continuing from my OCI series: Moving On-Premises Oracle Apex application to OCI Oracle Autonomous Transaction Processing

To connect to the ATP instance from a Linux machine, we have to do a little more leg work.

Some things to review before we get started.
The client software should be 11.2.0.4 (full install) or higher to be able to connect to the ATP instance. Instant Client can be downloaded and installed from these links:

Oracle Instant Client Downloads
Instant Client Downloads for Linux x86-64 (64-bit)

Downloads

Download the appropriate version per your requirements. I have installed the 18.5 version.

• Basic Package - All files required to run OCI, OCCI, and JDBC-OCI applications
• Tools-optional Package SQL*Plus Package - The SQL*Plus command-line tool for SQL and PL/SQL queries
• Tools Package - Includes Data Pump, SQL*Loader and Workload Replay Client

I downloaded it to my local machine and then uploaded it to the Linux machine via FTP. There are ways to do "wget", but require more work as Oracle Authentication is required.

Once installed, I unzipped in the oracle user home

/home/oracle/instantClient

If you have the other client also installed on the machine, you may have to set-up things slightly different, but basic steps are the same.

Setup Wallet and configuration files

Download the client credentials (Wallet Zip) to the machine, and uninstall it to the home directory for oracle

drwxrwxr-x 2 oracle oracle  4096 Oct 13 17:28 Wallet_BuzzTAQDev
-rw-rw-r-- 1 oracle oracle 19814 Oct 13 17:25 Wallet_BuzzTAQDev.zip

Change the underlying file permissions to 600

chmod  600 *
[oracle@ip-172-30-0-39 Wallet_BuzzTAQDev]$ ll
total 36
-rw------- 1 oracle oracle 6669 Oct 12 23:21 cwallet.sso
-rw------- 1 oracle oracle 6624 Oct 12 23:21 ewallet.p12
-rw------- 1 oracle oracle 3244 Oct 12 23:21 keystore.jks
-rw------- 1 oracle oracle   87 Oct 12 23:21 ojdbc.properties
-rw------- 1 oracle oracle  114 Oct 12 23:21 sqlnet.ora
-rw------- 1 oracle oracle 1751 Oct 12 23:21 tnsnames.ora
-rw------- 1 oracle oracle 3336 Oct 12 23:21 truststore.jks

If Oracle database or client is already installed, we can put the tnsnames and sqlnet files in the current admin/network directory in ORACLE_HOME
If not, we will move the files to the instant client network/admin/ location

/home/oracle/instantClient/network/admin

Change the sqlnet.ora file :

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/Wallet_BuzzTAQDev")))

#SSL_SERVER_DN_MATCH=yes

Make sure that all the tnsnames in the downloaded file are in the final tnsnames.ora file

<>_high

<>_low

<>_mediom

<>_tp

<>_tpurgent

Client configuration:

Add the location of the client install in the current path variable:

export PATH=$PATH:/home/oracle/instantClient >> /home/oracle/.bash_profile

add the library path

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/oracle/instantClient >> /home/oracle/.bash_profile

If you already have sql client installed, you may have to rename the key files to ensure you are running the proper version

mv sqlplus sqlplus18

Going forward, we will use the sqlplus18 instead of sqlplus to run our scripts.

Test your connection

[oracle@ip-172-30-0-39 instantClient]$  sqlplus18 admin@buzztaqdev_medium
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Oct 15 18:13:44 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Enter password:
Message 1549 not found;  product=SQLPlus; facility=SP2
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>

Happy testing!!

Sachin
Chief Architect
Oracle Apex Enthusiast
BuzzClan

OCI - ATP Database - Basic connectivity - SQL Developer

Continuing from my OCI series: Moving On-Premises Oracle Apex application to OCI Oracle Autonomous Transaction Processing

Once the database is provisioned, you can connect from various clients.

1. Credential Zip File

The Credential zip contains the following files:

• Wallet and keystore:
• cwallet.sso
• ewallet.p12
• keystore.jks
• truststore.jks
• Configuration
• sqlnet.ora
• tnsnames.ora

TNS entries are as follows:

• High
• Low
• Medium
• TP
• TPurgent

Connecting to SQLDeveloper

Various versions will have different options when it comes to the connection type. I am sharing a couple of things I have seen. Either way, here are a few pointers. 

Download the latest SQL Developer. Cloud connections and secure connections are not available in older versions.

SQL Developer 19.2

SQL Developer 18.4

Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.

Note:

Depending on your version of SQL Developer, do not right-click Cloud Connection or Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service.

To start a new connection, start with New Connection, not the Cloud Connection.

The New/Select Database Connection dialog appears. Enter the following information:

• Connection Name - Enter the name for this cloud connection.
• Username - Enter the database username. Use the default administrator database account (admin) that is provided as part of the service.
• Password - Enter the admin user's password that you or your Autonomous Transaction Processing administrator specified when creating the service instance.
• Connection Type - Select Cloud Wallet. (or Cloud PDB)
• Configuration File - Click Browse, and select the Client Credentials zip file, downloaded from the Autonomous Transaction Processing service console by you.
• Service - In the drop-down menu, service selections are prepended with database names. Select the tpurgent, tp, high, medium, or low menu item for your database. These service levels map to the TPURGENT, TP, HIGH, MEDIUM and LOW consumer groups, which provide different levels of priority for your session. 

OR

Click Test, Save and Connect.

Link to the ObE - Connecting SQL Developer to Autonomous Transaction Processing

Next: OCI-Oracle ATP- Connect via SQLPlus on Linux

Sachin
Chief Architect
Oracle Apex Enthusiast
BuzzClan

OBIEE - Multi-tenancy User Authentication and Authorization

To facilitate Multi-Tenancy in OBIEE, a few new roles have been introduced in OBIEE. A new layer has been added which allows for administration at a tenant level, and another one to define users/authors at the tenant.

• BI Global Administrator
• Tenant Administrator
• Tenant User

Earlier posts in this series:
 OBIEE - Multi-Tenancy implementations - What is it
 OBIEE - Multi-Tenancy - Presentation Catalog

When OBIEE is configured for Multi-tenancy, there are two administration are available to configure the application. BI Global Administrator role (BIGlobalAdministrator) is used for overall global administration. This administrator controls privileges for all tenants and can access the Presentation Services Administration page, Oracle BI Administration Tool, Job Manager, Catalog Manager, and all content. This administrator is not associated with a specific tenant.

The new role, BITenantAdministrator have specific privileges that are granted in the Oracle BI Presentation Catalog for administering a tenant. Users in this role can perform. 

This role enables users to perform self-service administration tasks on one tenant. These administrators cannot access overall Presentation Services Administration page or the Privileges page. These administrators organize content for tenant users within the catalog by granting access to, creating, moving, and copying objects and folders.

Another User role, Tenant User, are equivalent to the BIAuthor and BIConsumer roles, but gives access to the artifacts within a particular tenant. 

Each Tenant in the system is assigned a GUID, and the users assigned to the tenant are also assigned GUID's to ensure that OBIEE sees them as distinct users and is shielded from name clashes and name changes. The user is based on the Tenant GUID. This GUID is also available as a session variable.

This way the user maintenance tasks are delegated to Administrators within each Tenant. Persons taking on this role should understand the OBIEE artifacts and the authorizations available 

Not all the features are Currently - multi-tenant - enabled. Here are a few:

• Catalog groups
• KPIs, scorecards, 
• BI Mobile, 
• BI Composer, 
• Oracle BI for Microsoft Office, 
• Act As functionality, 
• Direct database requests.
• Oracle RTD, BI Publisher, and Marketing Segmentation.
• Full-text catalog search with Oracle SES and Oracle Endeca Server. The basic catalog search is available.
• Oracle Essbase Components (including Financial Reporting, Calculation Manager, and Workspace).

These features are not available for BIGlobalAdminstrator for administration:

• Oracle BI Administration Tool
• Catalog Manager
• Job Manager.
• Usage tracking 
• MapViewer

Other limitations:

• Application Roles are defined system wide, and not tenant specific. Any roles defined will be available to all the tenants for selection through the dialogues.
• There are no 'Tenant' Specific configurations in the instanceconfig.xml file. These changes include privileges in the catalog, skins, and front-end customization.

Multi-Tenancy is disabled by default. A few entries needs to be added to the configuration files and domain configuration files.

A detailed documentation can be found at: Configuring for Multiple Tenants


Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

SSO - how does your Driver's License stand up to the challenge

As a frequent traveler, I can't imagine my life without one piece of plastic in my wallet- my Texas Driver's License.

On the day of travel, I use it to get past the airport security, get into my rental car, check into the hotel, get cash from my bank (I do not carry any debit cards as a safety measure), get past the paranoid girl at the check out counter who insist on seeing my id before she can swipe the credit card for that bottle of water, and prove that I am of legal drinking age as I sit down at the hotel bar after a long day.

In all of these transactions, I whip out my trusted Texas DL and authenticate myself as Mr. Sachin Jain. Some people look at the id, some run it under devices that pop up the various security features embedded within the card, and almost all of them look up to match the photo on the card with my face and confirm that I am who I am saying I am.

I was authenticated by the system using a token(my Texas Driver's License) issued by a central/trusted token provider (Texas Department of Motor Vehicles).

Once the authentication is done, my identification is confirmed. There is almost always a second piece of token which then authorizes my access to the service I am seeking- my boarding pass, notations on the boarding pass giving me premium access, TSA pre-check privileges, my reservation confirmation to a particular car for certain period of time, a hotel booking, my credit card, or my date of birth on the DL itself, .

Imagine if I had to carry a separate piece of identification for each of these interaction! My wallet would be bursting at the seams. I would have to go through the hassle of bringing the right id that will work for a particular encounter. i would have missed opportunities because I don't have  the ID for the most popular joint that yelp suggested, or for that hotel I bid for on Priceline as I am walking out of the office on Monday afternoon.

Driver License - SSO

Similarly in computer security, SSO  is a way of authenticating a user based on a Central Directory. When a user requests access to a resource, a certain web site, their HR records, email, etc. the provider redirects the user to a login page hosted by the SSO authority which presents a challenge-response, mostly in the form of a id/password combination or additional mechanisms.  Once the user gets past this screen, the SSO authority confirms the user authentication and passes a token identifying the user to the servicing application. The application can then bounce this token against its provisioning store and give access to the user to the appropriate resources based on the authorization.

Several applications can subscribe to the SSO server, thus eliminating the need for the user to maintain multiple authentication tokens (id/password) to get access. Most of the time, if the user has already been authenticated once to an application, the SSO provider can leave a token on the browser session user tries to access another application with the same SSO provider, no log-in is necessary. This provides seamless access to multiple applications

This is a very simplistic, 10,000-mile high description of how an SSO echo-system works, I hope this helps you grasp the basic concept and find similarities/differences between a real-life and virtual SSO implementations.

Tech Term of the Day
SSO:- Single Sign On

Further Reading

• https://en.wikipedia.org/wiki/Single_sign-on
• 101 Things To Know About SingleSignOn

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - Multitenancy - Presentation Catalog

As discussed in the previous post, OBIEE: Multitenancy Implementations generally involve a varied user base with different needs from the underlying data warehouse. Furthermore, the users are also able to expand the presentation catalog on their own during the lifetime of this implementation.

So, ideally, the provider will present the users with a basic set of reports and dashboards which will be deemed universal for the subject areas being covered. These will be designed after consulting with various user groups from different target audience (tenant groups). The provider should also generate basic templates which can be extended for ad hoc analysis to ensure that the users are utilizing the power of the underlying BI platform.

As this blog is focused primarily around OBIEE and supporting Oracle echo system, I will say that this should be given to the users so they can benefit from the ad hoc capabilities provided by OBIEE. With proper training, the users can form a 'power user' group within their rank-and-file. These power users can create custom reports for specific tenants or groups within them. In addition, the provider can offer custom consulting for a fee and come up with reports and dashboards for specific tenants.

To summarize:

• Global reports and dashboards for all
• Global templates for everyone to use as the starting point for ad hoc analysis
• Personal space for each users where they can save
• frequently used ad-hoc analysis
• work-in-progress before the artifacts are released for use with others within the tenancy
• Directories for each tenant to share
• Custom reports among themselves
• Proof-of-concept reports for validation (access may be restricted to certain auditors only)
• Custom development done by the provider - often for a fee - available only to users of the particular tenant. Artifacts from this section may eventually find its way into the global area, depending on popularity, maturity of the offering, etc.

Of course, all of these need to be properly secured. There will be sub-directories/sections which will have appropriate authorization considerations based on the user’s role within the tenancy.

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

Checklist to secure your computer and GMAIL accounts

While browsing, I stumbled across this nice article by Google support!

Gmail Security Checklist

Whether you just regained access to Gmail, or you want to make sure your account is secure, take a minute to complete our Gmail security checklist to make sure your mail security measures are up to date.

Some related books:

Sachin

JainSys

External table based authentication and autorization in OBIEE

Sachin
JainSys Inc. The basic out of box security available with OBIEE is via the administration tool. To administer the users/groups one logs in to the RPD and open Security Manager (Manage/Security). Groups, users and their relations are managed via this.

The drawback to this method is that every time we need to add users or manage groups, this becomes an admin task involving IT. In an enterprise application, there are other means of maintaining user access like SSO or custom admin screens which allow for the designate admin roles in the user community to manage access to users. This generally runs off of a custom profile implementation.
A typical layout for this type of security implementation is done via a set of three tables:

• USER
• ROLE
• UserInRole

This allows a many to many association between users and roles.

To take advantage of this in OBIEE we have to configure the system to query the database on login. The steps required to do so are as follows:

1. Setup database connection.

Start by importing from the database which has the security/profile tables.This Connection pool will be used later in the process.

2. Once we have the connection pool ready we now need to set up an Initialization Block. An IB is executed every time a user logs in and a session is being created. The idea is to get teh User name and password passed on through the login page and use that information to query the database and authenticate the user.

3. Open the RPD and Create an new IB by clicking the menu
and then on the Variable Manager :
We need to do this on a "Session" IB not a "Repository" as this will be done as each user logs in.
Select the Data Source Type as "Database".
(If you do not see Database as an option then you may not have a valid ODBC or Oracle client on the server. If you have been able to complete the import step above then this shold not be an issue. Else this is about time you install a client to connect to the data store.)

4. Initialization string: We now need to define a query which will take the UserId and Password passed on to the login screen and check the tables to see if this user exists and have access to this application or not. This is also our chance to grab items from the user profile like Display Name, groups they are assigned to etc.
Typical sql at this point is:

select username, grp_name, SalesRep, 2 from securitylogons where username = ':USER' and pwd = ':PASSWORD'

:USER is a way to indicate that the value of that variable will be substituted on run time.

5. Variable Target: This section allows us to define some variables to contain the results. There are some predefined session level variables:
USER ; PASSWORD; DISPLAYNAME; LOGLEVEL; EMAIL etc
See this for details

The variables should be lined up in the order they are being returned from the query to allow proper assignment.

Check the "Required for authentication" check box to indicate that this IB is used for authenticating users.

Save.

6. Testing : The logs for any issues with this test can be found at {OBIEE Dir}\OracleBI\server\Log\NQServerLogs.log.

I kept getting :
[nQSError: 13011] Query for Initialization Block 'Authentication' has failed.
[nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.

because I had the semicolon at the end of the query. Apparently statement preparation did not like it.

Once there is data in underlying tables, this should be a easy!

7. Next tasks: setting up groups and managing RPD level and Report level security. That's calls for another blog i guess!! Sachin
JainSys Inc.