#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

#C15LV Collaborate’15 updates!!

Hi All,

IMG_20150411_203427~2

Last week, I registered my presence at Collaborate’15 in Las Vegas as a speaker, attendee and at Oracle demo booth duty. It was an awesome experience meeting some polished brains and smart minds. After the first day of registration, I attended the #DBIM12c session by Maria Colgan, #DB12c Multitenant by John McHugh and #Exadata session by Dan Norris – its always good to hear the most recent updates on something you have been working on.

IMG_20150414_145303IMG_20150412_142332~2

The topic of my talk was “I/O Resource Management on Exadata”. Thanks to all those who attended my session 662 at Banyan D conference room (inspite of Database In-Memory bootcamp and other concurrent sessions). The objectives of my session was to discuss the management of server resources in the cases of database consolidation, including the configuration, monitoring and best practices. At the same time, provide the latest updates on IO Resource Management. I appreciate the interaction of the audience who came forward with their experiences, problems and understand how IORM can resolve resource conflict issues. The hour long session was well received, which reminds me to remind all the session attendees – do fill in the session evaluation 🙂

IMG_20150413_184947

IMG_20150414_174609

 

 

 

 

 

 

 

 

After my stint as speaker, I took over the demo booth duty for Oracle Database 12c. Thanks to all those who passed by booth#54 and showed interest in understanding Oracle Database 12c, Multitenant, In-Memory. Glad we were able to help but sorry, our booth didn’t had freebies though!! We discussed some complex database deployments and tried to figure out better and easy solutions for those. On and off, I also visited partner booths and I truly appreciate their efforts in installing really nice structures and innovative ideas to attract more people.

One of the challenge which I could see and experience was planning the sessions to attend. Many concurrent sessions left the attendees in confusion as to which one to attend and which one to leave. But believe me, thats the beauty of this conference, you get things in extra size only. Just grab a cup of coffee and get set go.

Special mention to all those involved in coordinating and event administration. Thanks to IOUG for organizing the bring some great minds together. Hope to see you all next year as well. Thanks all

Saurabh

 

 

Oracle Database 12c PRAGMA UDF and WITH clause enhancements

Here are two interesting enhancements in Oracle database 12c PL/SQL.

PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can be invoked instantly in the SELECT statement while the procedures can be invoked from the functions used in the declaration section.

PL/SQL functions defined using PRAGMA UDF – SQL and PL/SQL have different memory representations of values. Therefore, interconversion is involved while “switching” from one engine to other and vice versa. It allows you to define the PL/SQL subprogram outside the SQL statement but matching the performance of an inlined PL/SQL program.

Let us do a small test to see the performance gains-

1. Created a test table T

CREATE TABLE t
(
PK integer not null,
n1 integer not null,
n2 integer not null,
n3 integer not null,
constraint t_PK primary key(PK)
)
/

2. Inserting some random data in the table T using DBMS_RANDOM

DECLARE
commit_count constant pls_integer := 100000;
nof_Rows constant pls_integer := 20*commit_count;
Zero constant integer not null := 0;
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;

M1 constant integer not null := 2*THS;
M2 constant integer not null := 2*BIL;
Hi constant integer not null := 2*TIL;
BEGIN
DBMS_Random.Seed(To_Char(Sysdate, 'MM-DD-YYYY HH24:MI:SS'));
for j in 1..Nof_Rows loop
declare
n1 integer not null := DBMS_Random.Value(Zero, M1);
n2 integer not null := DBMS_Random.Value(M1, M2);
n3 integer not null := DBMS_Random.Value(M2, Hi);
begin
insert into t(PK, n1, n2, n3) values(j, n1, n2, n3);
end;
if Mod(j, commit_count) = 0 then
commit;
end if;
end loop;
commit;
END;
/

3. Table has undergone good number of transactions; so lets gather the table stats

begin
DBMS_Stats.Gather_Table_Stats('SCOTT', 'T');
end;
/

4. Here is the objective. Let us create the PL/SQL function to display an integer as a multiple of appropriate unit of “Thousand”, “Million”,”Billion” or “Trillion”. We shall do this activity in different fashions to compare the performance. We shall record the timing for each case.

a) Using a conventional pre 12c standalone function to set the BASELINE

CREATE OR REPLACE FUNCTION F_ShowVal_pre12c(n IN integer) return varchar2 is
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
RETURN
CASE
WHEN n <= THS-1 then To_Char(n, '999999')||' units'
WHEN n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
WHEN n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
WHEN n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
ELSE To_Char(n/TIL, '999999')||' Trillion'
END;
END F_ShowVal_pre12c;
/

SET TIMING ON
SELECT F_ShowVal_pre12c(n1) n1, F_ShowVal_pre12c(n2) n2, F_ShowVal_pre12c(n3) n3 FROM t
/

b) Using Pure SQL – Without using a function or 12c enhancement

SET TIMING ON
SELECT PK,
case
when n1 <= 999 then To_Char(n1, '999999')||' units'
when n1/1000 <= 999 then To_Char(n1/1000, '999999')||' Thousand'
when n1/1000000 <= 999 then To_Char(n1/1000000, '999999')||' Million'
when n1/1000000000 <= 999 then To_Char(n1/1000000000, '999999')||' Billion'
Else To_Char(n1/1000000000000, '999999')||' Trillion'
end,
case
when n2 <= 999 then To_Char(n2, '999999')||' units'
when n2/1000 <= 999 then To_Char(n2/1000, '999999')||' Thousand'
when n2/1000000 <=999 then To_Char(n2/1000000, '999999')||' Million'
when n2/1000000000 <=999 then To_Char(n2/1000000000, '999999')||' Billion'
Else To_Char(n2/1000000000000, '999999')||' Trillion'
end,
case
when n3 <= 999 then To_Char(n3, '999999')||' units'
when n3/1000 <= 999 then To_Char(n3/1000, '999999')||' Thousand'
when n3/1000000 <= 999 then To_Char(n3/1000000, '999999')||' Million'
when n3/1000000000 <= 999 then To_Char(n3/1000000000, '999999')||' Billion'
Else To_Char(n3/1000000000000, '999999')||' Trillion'
end
FROM t
/

c) Declaring the PL/SQL function in the subquery’s WITH clause

SET TIMING ON
WITH
function ShowVal(n IN integer) return varchar2 is
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
return
case
when n <= THS-1 then To_Char(n, '999999')||' units'
when n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
when n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
when n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
Else To_Char(n/TIL, '999999')||' Trillion'
end;
end ShowVal;

SELECT showVal(n1) n1, showVal(n2) n2, showVal(n3) n3
FROM t
/

d) Declaring the PL/SQL function using PRAGMA UDF

CREATE OR REPLACE FUNCTION F_ShowVal(n IN integer) return varchar2 is
PRAGMA UDF;
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
RETURN
CASE
WHEN n <= THS-1 then To_Char(n, '999999')||' units'
WHEN n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
WHEN n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
WHEN n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
ELSE To_Char(n/TIL, '999999')||' Trillion'
END;
END F_ShowVal;
/

SET TIMING ON
SELECT F_ShowVal(n1) n1, F_ShowVal(n2) n2, F_ShowVal(n3) n3
FROM t
/

Recorded the timings from the above cases (a), (b), (c) and (d) in the below matrix. Here is the performance comparison from the above scenarios –

plsql

Interview with Saurabh K. Gupta, Author of ‘Oracle Advanced PL/SQL Developer Professional Guide’

Image

 

 

Saurabh is the author of our recently published Oracle Advanced PL/SQL Developer Professional Guide Which helps master the advanced features of PL/SQL to design and optimize code using real-time demonstrations.

 

 

 

 

 

 

 

Find the complete interview at the below link

http://authors.packtpub.com/content/interview-saurabh-k-gupta-author-oracle-advanced-plsql-developer-professional-guide