Oracle DBMS_CREDENTIAL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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
 DBMS_GSM_POOLADMIN    
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
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
Code from {ORACLE_HOME}/rdbms/admin/a11020000.sql reformatted for readability DECLARE
 CURSOR creds IS
 SELECT owner, credential_name
 FROM dba_scheduler_credentials
 WHERE username IS NULL;
BEGIN
  FOR cred_info IN creds LOOP
    dbms_credential.enable_credential('"'||cred_info.owner||'"."'|| cred_info.credential_name||'"');
  END LOOP;
END;
/
 
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
Built-in Functions
Built-in Packages
Container Database
Credentials
DBMS_SCHEDULER
Pluggable Database
Security
What's New In 12cR2
What's New In 18cR3

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