#DB12c feature – Secure External Procedures with DBMS_CREDENTIAL

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library.

The DBMS_CREDENTIAL package is used to configure the credential. The CREATE LIBRARY statement has been enhanced for the credential specification.

A new environment variable, ENFORCE_CREDENTIAL, can be specified in extproc.ora to control the authentication by the extproc process. The default value of the parameter is FALSE. Another new environment variable, GLOBAL_EXTPROC_CREDENTIAL, serves as the default credential and is only used when the credential is not specified for a library. If ENFORCE_CREDENTIAL is FALSE and no credential has been defined in the PL/SQL library, there will be no user authentication; this means the extproc process will authenticate by using the privileges of the user running the Oracle server.

The following PL/SQL block creates a credential by using DBMS_CREDENTIAL.CREATE_CREDENTIAL. This credential is built using the ORADEV user:

BEGIN
DBMS_CREDENTIAL.CREATE_CREDENTIAL (
credential_name => 'devhost_auth',
user_name => 'oradev',
password => 'oradev')
END;
/

The library definition will include a new CREDENTIAL clause:

CREATE OR REPLACE LIBRARY myextlib
AS 'HelloWorld.so'
CREDENTIAL devhost_auth
/

When the extproc process reads the call specification and finds the shared library with a secured credential, it authenticates the library on behalf of the credential and then loads it.

 

Note – this post is an excerpt from the book “Advanced Oracle PL/SQL Developer’s Guide – Second Edition

Advertisements

OTN Yathra 2015 just got over!!

Quite well said – Investment in knowledge is the gives you the best returns. It was a great fun last week to be the speaker at OTN Yathra 2015, organized by All India Oracle Users Group (AIOUG). The OTN tour covered seven cities and I was the part of it at three cities i.e. Hyderabad, Bangalore and Chennai. I sort of loved to be the part of it as the events were tightly packed with just a day difference. Back to back events in three cities within a week’s time. My piece of stage was on Oracle 12.1.0.2 Multitenant enhancements and Oracle 12.1.0.2 Database In-Memory option.

The Hyderabad venue was at TechMahindra facility with quite huge number of registrations. Thanks to Satyendra and team for attending and coordinating the event logistics. There was good amount of interest level from the participants to listen on latest technology trends. Surprisingly, headcount from audience who have evaluated Oracle 12c was quite less. But I appreciate their keenness to know more on the topics which we presented. Nice meeting Kuassi Mensah, Gurcan Orhan and Gustavo Gonzalez during the events.

The Bangalore chapter was organized in our Oracle (OFSS) facility. AIOUG – Bangalore chapter was a major update of the morning. Yet again, I was happy to have quality participation in my sessions. During the Multitenant 12.1.0.2 enhancements, I spent more time explaining the Multitenant architecture as some of them were new to it. The sessions were pretty interactive and well received. It’s always good to meet acquaints from the industry during events and yes, such events help you to grow your professional grounds too.

The Chennai event was quite an interesting one as it was placed in Loyola College, Chennai. Having not visited a college campus for long time, it was a moment to see playgrounds and hostels. Back to our technical sessions, I appreciate the interaction of the participants. Multitenant was the talk of the hour but DBIM wrapped up their day with the conceptual understanding. And a surprise talk on Oracle Security was mesmerizing. Thanks to Sai for coordinating the events in Bangalore and Chennai.

If learning is an experience, sharing knowledge is a bliss. Thanks to AIOUG for hosting the speakers during the events which was truly appreciable. I would cordially invite your feedback/comments and suggestions on the sessions which I presented. If you have clicked the pics – don’t forget to share them too 🙂 See you again!!

Saurabh

 

Lets catch up at OTNYathra 2015

All,

Its time to be at AIOUG stage once again and the event would be the OTNYathra 2015. The event has been a great success in the past and has been able to generate/receive wide recognition and appreciation. OTNYathra focuses to evangelize the Oracle technologies to a broader and passionate audience. OTNYathra is a grand tour which is conducted in 7 cities across India in the span of two weeks. Out of these seven, you can find me in three of them – Hyderabad, Bangalore and Chennai. If you are an Oracle professional and passionate about learning, we must see each other there. I believe its a great chance to share and learn and at the same time, meet and make friends in the community.

You can find the dates, venue info and registration page here – http://www.otnyathra.com/. Follow  for activity updates on twitter. 

My sessions at the event venues –

1. Oracle 12c Multitenant – Changes in Database Release 12.1.0.2
Oracle 12c introduces Multitenant architecture which is designed for cloud infrastructure and enables the customers to easily consolidate multiple databases. The new architecture focuses on easy adoption and provides enhanced manageability. Oracle Multitenant delivers the benefits of “Manage many as One” while retaining the security, isolation and resource management at individual database level. Not only it complements all database options, but also provides quick provisioning of databases. With Oracle Database 12.1.0.2, Oracle has delivered multiple enhancements to the Multitenant option. Although, the Database In-Memory option is the most talked about feature of the release, but Multitenant option includes multiple additions to the pluggable database cloning, enables cross PDB querying, controls PDB in a data guard configuration, and many more features. These features allows the granular management of pluggable databases in a Multitenant environment. The Multitenant option, being compatible with Database In-Memory option, further raises the value bar in a consolidated environment. The session will discuss the motivations behind the enhancements to the Multitenant architecture and their applications in the real world.

2. Oracle Database 12c In-Memory option
The Oracle Database 12.1.0.2 introduces the Database In-Memory (DBIM) option which transparently accelerates analytic queries by orders of magnitude, enabling real-time business decisions. Using Database In-Memory, businesses can instantaneously run analytics and reports that previously took hours or days. Businesses benefit from better decisions made in real-time, resulting in lower costs, improved productivity, and increased competitiveness. Oracle’s In-Memory columnar technology is a pure in-memory format.   The in-memory columnar format is not persisted on storage. With the introduction of Oracle Database In-Memory option, a single database can now efficiently support mixed workloads by delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting workloads. This is possible due to this “dual-format” architecture that enables data to be maintained in both, i.e. the existing Oracle row format (buffer cache) for OLTP operations, and a new purely in-memory column format optimized for analytical processing. Embedding the in-memory capabilities into the existing Oracle Database software ensures that it is fully compatible with all existing database features, and requires no changes in the application layer.

See you there!!

Saurabh

AIOUG Tech Day at Hyderabad (Aug 23rd)

Hi there,

Last week, I spoke at AIOUG Tech Day at Hyderabad. All India Oracle Users Group (AIOUG) in association with TechMahindra organized one full day of techtalk event. Believe me, the Danube auditorium was a state-of-art facility with 500 capacity. Fabulous venue and huge crowd with similar interest. I thought of sharing my experience through a blog post.

The day started saw the green signal with a welcome speech from Narayanmurthy K, followed by a keynote from Satyendra Kumar (President, AIOUG). The first session from Satyendra touched on various aspects of Oracle 12c – primarily focused on Pluggable Database. Oracle 12c implements the concept of Multitenancy in database by allowing multiple application backends to run securely within a single database (The Container) and thereby, appearing as physical databases (Pluggable database).

Next was my turn to adore the stage by introducing Oracle 12c In-Memory – Oracle’s latest baby. My objective was to drive the motivation behind the innovation and let the audience know what exactly was the motivation. We have been hearing the buzz named “In-Memory” from quite a sometime. Columnar stores too are not new to the tech space. Innovation lies in bringing the two worlds together i.e. clubbing row store and columnar store within a single SGA. The whole architecture ensures that existing potential of row store (buffer cache) is not disturbed but the analytics (reporting and ad-hoc queries) makes use of columnar paradigm to be real-time analytics. IMCS is a native database feature, therefore no additional installation required. We learnt how to enable the feature, optimizer becomes in-memory aware, SGA gets a new static component, and storage indexes to skip columnar units. I was happy to see the growing curiosity in the audience and multiple rounds of QA. In the interest of time, I wrapped up the session but yes, there was a lot to talk about.

In the sequence were the sessions from YV Ravikumar and Nassyam Basha. Ravi briefed the audience on Flex ASM and flex cluster. He did an excellent job in demonstrating the evolution of ASM and how 12c adds flex behavior to ASM and clusterware. He showed some real time examples on his laptop which made the things more practical. Nassyam’s session on Oracle 12c Data Guard (Far sync and Fast sync) were well received. He touched upon the implementation part of the features and multiple use cases. Excellent job while taking the queries and he answered with utmost precision. One hour slot for each session was looking bit less. Far sync topology is a new stuff and planning is an important aspect. Location of the light-weight instance, how many far sync sites, and what is the failover policy must be the part of planning.

Later, we saw sessions from Mr. Phani Arega on Indexes. When to use an index and when not – was the objective of session. He had several use cases and citations to be discussed. I was back again on stage with SQL and PL/SQL enhancements in Oracle 12c. I did spent some time on introducing Multitenant 12.1.0.2 as well. The last session for the day from Satyendra was on Big Data essentials. He gave some excellent figures to show how the world is transforming and where lies the opportunity. The day wrapped up with thanks note. Though hectic, but it was great day and I must thank the audience for their patient listening on variety of topics.

Overall, the event was well received and appreciated by the audience. Thanks AIOUG for building up a great stage.

AIOUG Tech day team

Multitenant Validation lab program for ISV partners

Hi Guys,

As you all might have noticed from my social moves, we were running Multitenant Validation lab for ISV partners. Briefing on the program, the 2-day guided lab will enable the partners to test and validate their products/applications on Oracle 12c Multitenant architecture. Partners would be able to visualize and get hands-on with Multitenant Administration and also elevate customer’s confidence in adopting a verified and tested solution. The complete event contains the subject briefing and the validation lab. The preliminary list of milestones to be achieved during this lab are –

1. Oracle 12c Container Database installation
2. PDB provisioning and data import
3. PDB cloning
4. Application connectivity to the pluggable databases
5. Data mobility by unplugging and plugging in a PDB between two container databases

Partners can always wish to explore further on Multitenant aspects like availability, performance, manageability, provisioning and data mobility.

Last week, I was onsite to conduct two such validation labs with couple of partners located in Ahmedabad, India. First partner successfully tested two of its Telecom billing applications on Multitenant while second one validated a Banking application. Will be sharing some of the key takeaways from the event –

1. Java connection link must follow the EZconnect method. The Pluggable databases (PDB) runs as a service within the 12c Multitenant Container Database. The SID is of the container while PDB services get registered with the SID. For the applications to connect directly to the PDB, use service_name and not the SID. It should look like – jdbc:oracle:thin:@hostname:1521/pdb1

2. The JDBC version – If you see connection failure with the ORA-28040 exception, you have a couple of solutions. You can either upgrade to an Oracle 11g or 12c JDBC driver or set SQLNET.ALLOWED_LOGON_VERSION on both client and server (to 8 or 9) to support older client connections.

3. If you encounter “ORA-12518: TNS:listener could not hand off client connection”, try increasing the number of processes to a substantial value.

I am happy to see the response from the community and the zeal the partners show to take a step ahead and jointly execute the activity. Thanks for that. If anyone of you get interested in pursuing the program, reach me out and I can take it onward.

-Saurabh

Oracle 12c Technical Hands-On Workshop

I have been running many tech events and briefings on Oracle 12c for India Partners. This time around, I thought of posting my latest event reviews.

This week, I wrapped up the Oracle 12c Technical Hands-On workshop in Oracle facility, Gurgaon. It was a 2-day event from Oracle Database Product Management. The focus of the workshop was primarily on Oracle 12c Multitenant architecture along with the hands-on labs using a virtualbox image. The audience was comprised of the representatives from the key Oracle partners in the NCR area. I appreciate their interest and in spite of odds like the last night rain and massive traffic, they showed up in time. I was the lead instructor for this event along with my senior colleague Mr. Rick Pandya. Thanks to Rick who has flown from Chicago, to be with me for several partner events in India.

Here was the agenda of the workshop –

  • Introduction to Oracle 12c Multitenant Architecture
  • Administration and Management of Multitenant Databases
  • Cloning, Consolidation, Relocating, Backup/Recovery, Security
  • Migrating to Multitenant Architecture
  • Upgrading to 12c CDB using DBUA
  • Performance Monitoring and Resource Management
  • Heat Maps and ILM, Temporal Validity, In-Database Row archiving

For all reasons, Multitenant was the focus and area of interest. Certain excerpts like share-able components of a Container Database, PDB provisioning, remote cloning, spfile, control file and common users were the area of exploration. Some of the folks were good to find that – in a pluggable container, the instance name (from V$INSTANCE) and db name (from V$DATABASE) appears to be the container name because of the obvious reasons.

The real challenge was the hands-on part of the workshop where the participants were required to carry the high-end configuration laptops, but only few of them could manage it. An 8G laptop with 100G of space was expected to run the exercises on database upgrade, backup and migration. We gave a shot by trimming the vbox image to run on 4G lappe. It did worked but host OS performance was in a toss.

My next stop is in Chennai where I’ll be driving this event for specific Partners. See you all 🙂

Saurabh K. Gupta

AIOUG Sangam 11

 Hi Guyzzz

Just back from AIOUG Sangam11. Wonderful effort, event, sessions and most important grrat stage to share with some great names. Arup Nanda, Murali Vallath, Kuassi Mensah and many more. Kudos to the arrangement and and event management too.

The Day 1 (9th Dec) started off with registration. Was glad to see our bags and great material. Saw Murali at the doorstep and the day started off.

Murali addressed the session and thanked the event management team and introduced Arup to kick off the session. Arup Nanda ‘The LONGTIME DBA’ was the Show Stopper of the whole event. And he should be !!

Rich Niemiec started off with a session on Rolta. He showed us some pics of Oracle Corp…was amazed to be the part of it.

 

 

 

 

 

 

Arup started off with a session on Profiler in PL/SQL. Great sessions, great views and great information. Kuassi Mensah delivered a nice session on Oracle Net security. Murali too gave a presentation on RAC subject, but couldn’t catch up match as I have no background. Hemant came up with the day to day challenges of a DBA, their role and expectations. Fabulous !!

Day 2 (10th Dec) again saw a long session from Arup until Lunch. Superb stamina and grasp !! Got to know some new concepts on Cache fusion, DBA best practices, and introduced the logic behind Exdata. I could see the roadmap of DBA to DMA (Database Machine Administrator). Again a good one!!

Meanwhile I got introduced to Arup Nanda with a handshake and told him about myself. Told him about my online works and he asked me to get in touch for SELECT journal too. Nice chat time !!

Later I attended session by Hemant on Partitioning. Though it was an elementary one but enough to fill up 1 hour session. The next session by Prabhakar Gongloor was another masterpiece where he showed some screendumps of Oracle 12C. I believe we would be only of those few ones who could see it within 2 months of the version release. I could see him hiding something by saying its ‘Special Technique’ through which EM can connect to DB even if it is down (as far as I could understand).

As I referred one of my colleague for the seminar, I received a coupon worth INR500, redeemable on the same day against the books at the counter. I got one copy of Oracle XML DB.

The conference ended with photo sessions. I too got few with Arup, Bryn, Rich and Mensah. Soon I would publish on my blog. Received a Tshirt from OTN too.

Next year, it would be in August, 2012…I will be happy to volunteer the event and would surely like to see myself at the stage right there 🙂

Once again, Thanks Oracle, Thanks AIOUG !!

By for now

Saurabh