Oracle DBMS_CREDENTIAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Interface for authenticating and impersonating EXTPROC callout functions, as well as external jobs, remote jobs and file watchers from the SCHEDULER.
AUTHID CURRENT_USER
Dependencies
ALL_CREDENTIALS DBMS_ISCHED DBMS_UTILITY
CDB_CREDENTIALS DBMS_SCHEDULER SCHEDULER$_CREDENTIAL
DBA_CREDENTIALS DBMS_SYS_ERROR USER_CREDENTIALS
Documented Yes
Exceptions
Error Code Reason
ORA-27469 Invalid credential attribute
ORA-27476 Identified credential does not exist
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmscred.sql
a11020000.sql
Subprograms
 
CREATE_CREDENTIAL
Create a new username and password pair dbms_credential.create_credential(
credential_name IN VARCHAR2,
username        IN VARCHAR2,
password        IN VARCHAR2,
database_role   IN VARCHAR2 DEFAULT NULL,
windows_domain  IN VARCHAR2 DEFAULT NULL,
comments        IN VARCHAR2 DEFAULT NULL,
enabled         IN BOOLEAN  DEFAULT TRUE);
conn sys@pdbdev as sysdba

DECLARE
 cname   user_credentials.credential_name%TYPE := 'UWCRED';
 uname   user_credentials.username%TYPE := 'UWCLASS';
 pwd     sys.scheduler$_credential.password%TYPE := 'ZzYzX6*';
 dbrole  VARCHAR2(30) := NULL;
 windom  sys.scheduler$_credential.domain%TYPE := NULL;
 comment user_credentials.comments%TYPE := 'Test Cred';
 enable  BOOLEAN := FALSE;
BEGIN
  dbms_credential.create_credential(cname, uname, pwd, dbrole, windom, comment, enable);
END;
/

col username format a10
col password format a45
col domain format a10
col comments format a10

SELECT *
FROM scheduler$_credential;
-- note flag = 0
 
DISABLE_CREDENTIAL
Disable an existing credential dbms_credential.disable_credential(
credential_name IN VARCHAR2,
force           IN BOOLEAN DEFAULT FALSE);
exec dbms_credential.disable_credential('UWCred', TRUE);

SELECT *
FROM scheduler$_credential;
-- note flag = 0
 
DROP_CREDENTIAL
Drop an existing credential or comma separated list of credentials dbms_credential.drop_credential(
credential_name IN VARCHAR2,
force           IN BOOLEAN DEFAULT FALSE);
exec dbms_credential.drop_credential('UWCred', TRUE);

SELECT *
FROM scheduler$_credential;
 
ENABLE_CREDENTIAL
Enable a disabled credential dbms_credential.enable_credential(credential_name IN VARCHAR2);
exec dbms_credential.enable_credential('UWCred');

SELECT *
FROM scheduler$_credential;
-- note flag = 4
 
UPDATE_CREDENTIAL
Alter the value of a credential attribute dbms_credential.update_credential(
credential_name IN VARCHAR2,
attribute       IN VARCHAR2, -- USERNAME, PASSWORD, WINDOWS_DOMAIN, or COMMENTS
value           IN VARCHAR2);
SELECT *
FROM scheduler$_credential;

exec dbms_credential.update_credential('UWCred', 'PASSWORD', 'ZzYzX9*');

SELECT *
FROM scheduler$_credential;
 
Demo
This demo uses all 5 procedures within the package and assumes that you have created the ORABASE PDB with the UWCLASS user as its admin user. conn uwclass/uwclass@pdbdev

DECLARE
 cname   user_credentials.credential_name%TYPE := 'UWCRED';
 uname   user_credentials.username%TYPE := 'UWCLASS';
 pwd     sys.scheduler$_credential.password%TYPE := 'ZzYzX6*';
 dbrole  VARCHAR2(30) := NULL;
 windom  sys.scheduler$_credential.domain%TYPE := NULL;
 comment user_credentials.comments%TYPE := 'Test Credential Creation';
 enable  BOOLEAN := FALSE;
BEGIN
  dbms_credential.create_credential(cname, uname, pwd, dbrole, windom, comment, enable);
END;
/

desc dba_credentials

col owner format a10
col credential_name format a20
col username format a10
col windows_domain format a15
col comments format a25
col password format a30

SELECT *
FROM dba_credentials;

exec dbms_credential.enable_credential('UWCred');

SELECT *
FROM dba_credentials;

SELECT obj#, username, password
FROM sys.scheduler$_credential;

exec dbms_credential.update_credential('UWCred', 'PASSWORD', 'ZzYzX9*');

SELECT obj#, username, password
FROM sys.scheduler$_credential;

exec dbms_credential.disable_credential('UWCred', TRUE);

SELECT *
FROM dba_credentials;

exec dbms_credential.drop_credential('UWCred', TRUE);

SELECT *
FROM dba_credentials;

Related Topics
Container Database
Credentials
DBMS_SCHEDULER
Packages
Pluggable Database
Security
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved