Oracle DBMS_CREDENTIAL
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 which seems like a gross violation of "best practice" for security as certainly no-one with CREATE SESSION privilege needs to execute this package. This grant should be revoked and then only granted as required.
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,
key             IN VARCHAR2 DEFAULT NULL);
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 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx