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
Related articles.
- Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 1 (12.1)
- All Scheduler Articles
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
andrun-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:
- DBMS_CREDENTIAL
- DBMS_SCHEDULER
- Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 1 (12.1)
- ALL_CREDENTIALS
- All Scheduler Articles
Hope this helps. Regards Tim...