Use of DBMS_CREDENTIAL Package in Oracle

DBMS_CREDENTIAL package is used by Oracle to run the external jobs local and remote.It use to save the Operating system username/password as database object which will authenticate in DBMS Scheduler when specify.

Check all the user present credential in DBMS packages

COLUMN credential_name FORMAT A25
COLUMN username FORMAT A20
COLUMN windows_domain FORMAT A20
SELECT credential_name,
username,
windows_domain,enabled
FROM all_credentials
ORDER BY credential_name;

CREDENTIAL_NAME           USERNAME        WINDOWS_DOMAIN       ENABL
------------------------- --------------- -------------------- -----
ORACLEUSER                c##bkpuser                           TRUE
ORACLE_BKPUSER            oracle                               TRUE
WINDOWUSER                EORACLE         ORCL                 TRUE
WINDOWUSER1               EORACLE         ORCL                 TRUE

Creating and Dropping DBMS Credentials of OS
Before creating the DBMS Credentials Verify that username and password is working on Operating system.

--For Creating Normal user
exec dbms_scheduler.create_credential( credential_name => 'ORACLE_BKPUSER', username => 'Oracle', password => 'Password123');

--For Creating Domain user
exec dbms_scheduler.create_credential( credential_name => 'ORACLE_BKPUSER', username => 'oracle', password => 'Password123', windows_domain => 'ORCL' );

--For Droping User Credentials
EXEC DBMS_CREDENTIAL.drop_credential('ORACLE_BKPUSER');


For Enable and Disable the Credential in Database

--For enable
EXEC DBMS_CREDENTIAL.enable_credential('ORACLE_BKPUSER');


--For Disable
EXEC DBMS_CREDENTIAL.disable_credential('ORACLE_BKPUSER');


Change username and password for existing credential

--For change username
Exec DBMS_CREDENTIAL.update_credential(credential_name => 'ORACLE_BKPUSER',attribute => 'username', value => 'oracle');


--For change password
EXEC DBMS_CREDENTIAL.update_credential(credential_name => 'ORACLE_BKPUSER',attribute => 'password', value => 'password123');

Check the Credential associated with job in DBMS Scheduler Jobs

Col job_name for a15
select job_name,job_type,credential_name from dba_scheduler_jobs;

JOB_NAME        JOB_TYPE         CREDENTIAL_NAME
--------------- ---------------- -------------------------
JOB$_764        BACKUP_SCRIPT    ORACLE_BKPUSER