Oracle | DBMS_CREDENTIAL

Oracle DBMS_CREDENTIAL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 Yes: Packages and Types Reference

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 only CREATE SESSION privilege needs to execute this package. This grant should be revoked and only granted as required.

Source {ORACLE_HOME}/rdbms/admin/dbmscred.sql

Subprograms

CREATE_CREDENTIAL	DROP_CREDENTIAL	UPDATE_CREDENTIAL
DISABLE_CREDENTIAL	ENABLE_CREDENTIAL	Demo

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;