#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

“Advanced Oracle PL/SQL Developer’s Guide – Second Edition” is now available

B04511_Cover

The second edition of my first work on Advanced PL/SQL is available now. The new book titled “Advanced Oracle PL/SQL Developer’s Guide – Second Edition” has been published by Packt Publishing. Secure your copies using either of the below links –

Packt Publishing – https://www.packtpub.com/big-data-and-business-intelligence/advanced-oracle-plsql-developers-guide-second-edition

Amazon – http://www.amazon.com/Advanced-Oracle-SQL-Developers-Guide/dp/1785284800

Here is the book abstract and content –

Book Description

Oracle Database is one of the most popular databases and allows users to make efficient use of their resources and to enhance service levels while reducing the IT costs incurred. Oracle Database is sometimes compared with Microsoft SQL Server, however, Oracle Database clearly supersedes SQL server in terms of high availability and addressing planned and unplanned downtime. Oracle PL/SQL provides a rich platform for application developers to code and build scalable database applications and introduces multiple new features and enhancements to improve development experience.

Advanced Oracle PL/SQL Developer’s Guide, Second Edition is a handy technical reference for seasoned professionals in the database development space. This book starts with a refresher of fundamental concepts of PL/SQL, such as anonymous block, subprograms, and exceptions, and prepares you for the upcoming advanced concepts. The next chapter introduces you to the new features of Oracle Database 12c, not limited to PL/SQL. In this chapter, you will understand some of the most talked about features such as Multitenant and Database In-Memory. Moving forward, each chapter introduces advanced concepts with the help of demonstrations, and provides you with the latest update from Oracle Database 12c context. This helps you to visualize the pre- and post-applications of a feature over the database releases. By the end of this book, you will have become an expert in PL/SQL programming and will be able to implement advanced concepts of PL/SQL for efficient management of Oracle Database.

Table of Contents

1 – OVERVIEW OF PL/SQL PROGRAMMING CONCEPTS
2 – ORACLE 12C SQL AND PL/SQL NEW FEATURES
3 – DESIGNING PL/SQL CODE
4 – USING COLLECTIONS
5 – USING ADVANCED INTERFACE METHODS
6 – VIRTUAL PRIVATE DATABASE
7 – ORACLE SECUREFILES
8 – TUNING THE PL/SQL CODE
9 – RESULT CACHE
10 – ANALYZING, PROFILING, AND TRACING PL/SQL CODE
11 – SAFEGUARDING PL/SQL CODE AGAINST SQL INJECTION
12 – WORKING WITH ORACLE SQL DEVELOPER

What You Will Learn

Learn and understand the key SQL and PL/SQL features of Oracle Database 12c
Understand the new Multitenant architecture and Database In-Memory option of Oracle Database 12c
Know more about the advanced concepts of the Oracle PL/SQL language such as external procedures, securing data using Virtual Private Database (VPD), SecureFiles, and PL/SQL code tracing and profiling
Implement Virtual Private Databases to prevent unauthorized data access
Trace, analyze, profile, and debug PL/SQL code while developing database applications
Integrate the new application development features of Oracle Database 12c with the current concepts
Discover techniques to analyze and maintain PL/SQL code
Get acquainted with the best practices of writing PL/SQL code and develop secure applications

I would eagerly wait for your reviews and feedback. I hope the book to strengthen your PL/SQL concepts and appear for Advanced PL/SQL certification exam with confidence. Thanks

 

Saurabh