Use of DBMS_CREDENTIAL Package in Oracle

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

1 thought on “Use of DBMS_CREDENTIAL Package in Oracle

  1. Pingback: ORA-27451: CREDENTIAL_NAME cannot be NULL | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.