Oracle DBMS_CREDENTIAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
Credentials
DBMS_SCHEDULER
Packages
Pluggable Database
Security

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