8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

DBMS_CREDENTIAL : Persist Database and OS Credentials in Oracle Database 12c Release 1 (12.1)

In Oracle 12c the credential related sub-programs of the DBMS_SCHEDULER package have been deprecated and replaced by the new DBMS_CREDENTIAL package. From a usage perspective it feels similar.

Security
Create Credentials
Enable/Disable Credentials
Update Credentials
Drop Credentials
Miscellaneous

Security

The CREATE CREDENTIAL privilege allows a user to create a local credential. The CREATE ANY CREDENTIAL privilege allows a user to create a credentials for any user.

Create Credentials

Credentials are database objects that hold a username/password pair for authenticating local and remote external jobs. They are created using the CREATE_CREDENTIAL procedure. The procedure also allows you to specify the Windows domain for remote external jobs executed against a Windows server. Credentials are owned by SYS.

conn test/test

begin
  -- Basic credential.
  dbms_credential.create_credential(
    credential_name => 'tim_hall_credential',
    username        => 'tim_hall',
    password        => 'password');

  -- Credential including Windows domain.
  dbms_credential.create_credential(
    credential_name => 'tim_hall_win_credential',
    username        => 'tim_hall',
    password        => 'password',
    windows_domain  => 'localdomain');
end;
/

Information about credentials is displayed using the [DBA|ALL|USER]_CREDENTIALS views.

column credential_name format a25
column username format a20
column windows_domain format a20

select credential_name,
       username,
       windows_domain,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             WINDOWS_DOMAIN       ENABL
------------------------- -------------------- -------------------- -----
TIM_HALL_CREDENTIAL       tim_hall                                  TRUE
TIM_HALL_WIN_CREDENTIAL   tim_hall             LOCALDOMAIN          TRUE

2 rows selected.

SQL>

Enable/Disable Credentials

Credentials are enabled and disabled using the ENABLE_CREDENTIAL and DISABLE_CREDENTIAL procedures respectively.

-- Disable credential.
exec dbms_credential.disable_credential('tim_hall_credential');


column credential_name format a25
column username format a20
column windows_domain format a20

select credential_name,
       username,
       windows_domain,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             WINDOWS_DOMAIN       ENABL
------------------------- -------------------- -------------------- -----
TIM_HALL_CREDENTIAL       tim_hall                                  FALSE
TIM_HALL_WIN_CREDENTIAL   tim_hall             LOCALDOMAIN          TRUE

2 rows selected.

SQL>


-- Enable credential.
exec dbms_credential.enable_credential('tim_hall_credential');


column credential_name format a25
column username format a20
column windows_domain format a20

select credential_name,
       username,
       windows_domain,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             WINDOWS_DOMAIN       ENABL
------------------------- -------------------- -------------------- -----
TIM_HALL_CREDENTIAL       tim_hall                                  TRUE
TIM_HALL_WIN_CREDENTIAL   tim_hall             LOCALDOMAIN          TRUE

2 rows selected.

SQL>

Update Credentials

The UPDATE_CREDENTIAL procedure allows you to modify attributes of a credential.

begin
  dbms_credential.update_credential(
    credential_name => 'tim_hall_credential',
    attribute       => 'username', 
    value           => 'tim_hall2');

  dbms_credential.update_credential(
    credential_name => 'tim_hall_credential',
    attribute       => 'password', 
    value           => 'password2');
end;
/


column credential_name format a25
column username format a20
column windows_domain format a20

select credential_name,
       username,
       windows_domain,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             WINDOWS_DOMAIN       ENABL
------------------------- -------------------- -------------------- -----
TIM_HALL_CREDENTIAL       tim_hall2                                 TRUE
TIM_HALL_WIN_CREDENTIAL   tim_hall             LOCALDOMAIN          TRUE

2 rows selected.

SQL>

Drop Credentials

Credentials are dropped using the DROP_CREDENTIAL procedure.

exec dbms_credential.drop_credential('tim_hall_credential');
exec dbms_credential.drop_credential('tim_hall_win_credential');

Miscellaneous

For backwards compatibility, it is not mandatory to specify credentials for local external jobs. If no credentials are set the default users are as follows.

Jobs in the SYS schema run as the user who installed the Oracle software.
The default user for non-SYS jobs on UNIX platforms is specified by the run-user and run-group attributes in the "$ORACLE_HOME/rdbms/admin/externaljob.ora" file.
The default user for non-SYS jobs in Windows platforms is the user running the "OracleJobSchedulerSID" Windows service.

Oracle recommend using credentials for all local and remote external jobs as the default values may be deprecated in future.

The operating system user specified by the credential must have the necessary privileges to perform the required action. On Windows platforms this must include the "Log on as batch job" security policy, applied using the "Local Security Policies" dialog.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.