46 DBMS_CREDENTIAL
The DBMS_CREDENTIAL
package provides an interface for authenticating and impersonating EXTPROC
callout functions, as well as external jobs, remote jobs and file watchers from the SCHEDULER
.
See Also:
-
Oracle Database Administrator's Guide regarding Specifying Job Credentials
-
Oracle Database Security Guide regarding Guidelines for Securing External Processes
This chapter contains the following topics:
46.1 DBMS_CREDENTIAL Overview
Credentials are database objects that hold a username/password pair for authenticating and impersonating EXTPROC
callout functions, as well as remote jobs, external jobs and file watchers from the SCHEDULER
.
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.
46.2 DBMS_CREDENTIAL Security Model
Every Oracle credential has a unique credential name and you can associate a credential through its unique credential name with EXTPROC
by means of a PL/SQL alias library.
In order to associate a credential with a PL/SQL alias library and external procedure, you must have the CREATE
AND
/
OR
REPLACE
LIBRARY
privilege or CREATE
AND/OR
REPLACE
FUNCTION
/
PROCEDURE
privilege and read permission of the DLL or shared object that the alias library to be associated with so that you can create and/or replace function or procedure to make use of the alias library.
Once authenticated, EXTPROC
must act on behalf of the client based on client's identity defined in the supplied user credential. If not authenticated, EXTPROC
must return an error message.
In order to create or alter a credential, you must have the CREATE
CREDENTIAL
privilege. If you are attempting to create or alter a credential in a schema other than your own, you must have the CREATE
ANY
CREDENTIAL
privilege.
46.3 DBMS_CREDENTIAL Operational Notes
As the existing CREATE OR REPLACE LIBRARY
statement and CREATE OR REPLACE FUNCTION/PROCEDURE
do not support a CREDENTIAL
clause, this model requires syntax and semantic changes in CREATE OR REPLACE LIBRARY
and CREATE OR REPLACE FUNCTION/PROCEDURE
statement.
For example:
CREATE OR REPLACE LIBRARY test AS '$ORACLE_HOME/bin/test.so' CREDENTIAL ricky_cred; CREATE OR REPLACE FUNCTION ftest1 (x VARCHAR2, y BINARY_INTEGER) RETURN BINARY_INTEGER AS LANGUAGE C LIBRARY test NAME "negative" PARAMETERS(x STRING, y INT);
The credential name defined in the CREDENTIAL
clause is a name of a database object. Therefore, do not enclose the credential name with single or double quotes.
An example of a credential being used on an external job:
BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'example_job', job_type => 'EXECUTABLE', job_action => '/bin/ls', credential_name => 'ricky_cred'); END; /
46.4 Summary of DBMS_CREDENTIAL Subprograms
This table lists the DBMS_CREDENTIAL
subprograms and briefly describes them.
Table 46-1 DBMS_CREDENTIAL Package Subprograms
Subprogram | Description |
---|---|
Creates a stored username/password pair in a database object called an Oracle credential |
|
Disables an Oracle credential |
|
Drops an Oracle credential |
|
Enables an Oracle credential |
|
Updates an existing Oracle credential |
46.4.1 CREATE_CREDENTIAL Procedure
This procedure creates a stored username/password pair in a database object called an Oracle credential. You can also use this procedure to manage the credentials used for accessing files stored in cloud object storage.
Syntax
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);
Parameters
Table 46-2 CREATE_CREDENTIAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the credential. It can optionally be prefixed with a schema. This cannot be set to |
|
User name to login to the operating system or remote database to run a job if this credential is chosen. This cannot be set to |
|
Password to login to the remote operating system to run a job if this credential is chosen. It is case sensitive. |
|
Whether a database job using this credential should attempt to log in with administrative privileges. Values: |
|
For a Windows remote executable target, this is the domain that the specified user belongs to. The domain will be converted to uppercase automatically. |
|
A text string that can be used to describe the credential to the user. The Scheduler does not use this field. |
|
Determines whether the credential is enabled or not |
Usage Notes
-
Credentials reside in a particular schema and can be created by any user with the
CREATE
CREDENTIAL
orCREATE
ANY
CREDENTIAL
system privilege. To create a credential in a schema other than your own, you must have theCREATE
CREDENTIAL
orCREATE
ANY
CREDENTIAL
privilege. -
The user name is case sensitive. It cannot contain double quotes or spaces.
-
Attempting to create a credential with an existing credential name returns an error. To alter an existing credential, users must drop the existing credential first using the DROP_CREDENTIAL Procedure.
-
Attempting to drop an existing credential, which is already referenced by alias libraries, returns an error. To drop an existing credential without any checking, users must set the
force
parameter of DROP_CREDENTIAL Procedure toTRUE
. -
You may also alter a credential, by means of the UPDATE_CREDENTIAL Procedure.
Examples
Create a Basic Credential
CONN scott
Enter password: password
BEGIN
-- Basic credential.
DBMS_CREDENTIAL.CREATE_CREDENTIAL(
credential_name => 'JAMES_SMITH',
username => 'james_smith',
password => 'password');
END
Create a Windows Credential
CONN scott
Enter password: password
-- Credential including Windows domain
BEGIN
DBMS_CREDENTIAL.CREATE_CREDENTIAL(
credential_name => 'JAMES_SMITH_WIN_CREDENTIAL',
username => 'james_smith',
password => 'password',
windows_domain => 'localdomain');
END
Display Information about Credentials
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 FROM user_credentials ORDER BY credential_name; CREDENTIAL_NAME USERNAME WINDOWS_DOMAIN ------------------------- -------------------- -------------------- JAMES_SMITH_CREDENTIAL james_smith JAMES_SMITH_WIN_CREDENTIAL james_smith LOCALDOMAIN 2 rows selected. SQL>
46.4.2 DISABLE_CREDENTIAL Procedure
This procedure disables an Oracle credential.
Syntax
DBMS_CREDENTIAL.DISABLE_CREDENTIAL ( credential_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Parameters
Table 46-3 DISABLE_CREDENTIAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the credential. It can optionally be prefixed with a schema. This cannot be set to |
|
If |
Usage Notes
-
Credentials reside in a particular schema and can be disabled by any user with the
CREATE
CREDENTIAL
orCREATE
ANY
CREDENTIAL
system privilege. To disable a credential in a schema other than your own, you must have theCREATE
ANY
CREDENTIAL
privilege. -
A credential for an OS user can be viewed as an entry point into an operating system as a particular user. Allowing a credential to be disabled lets an administrator (or credential owner) to quickly, easily and reversibly disallow all logins from the database to the OS as a particular user of external jobs, database jobs, file transfers, external procedures, and file watching. To enable an existing disabled credential, you need to use the ENABLE_CREDENTIAL Procedure.
-
A library can become invalid if the properties of the credential – windows domain, username, password, its enable/disable bit – are changed.
46.4.3 DROP_CREDENTIAL Procedure
This procedure drops an Oracle credential.
Syntax
DBMS_CREDENTIAL.DROP_CREDENTIAL ( credential_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Parameters
Table 46-4 DROP_CREDENTIAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the credential. It can optionally be prefixed with a schema. This cannot be set to |
|
If set to |
Usage Notes
Only the owner of a credential or a user with the CREATE
ANY
CREDENTIAL
system privilege may drop the credential.
Examples
EXEC DBMS_CREDENTIAL.DROP_CREDENTIAL('JAMES_SMITH_CREDENTIAL', FALSE); EXEC DBMS_CREDENTIAL.DROP_CREDENTIAL('JAMES_SMITH_WIN_CREDENTIAL', FALSE);
46.4.4 ENABLE_CREDENTIAL Procedure
This procedure enables an Oracle credential.
Syntax
DBMS_CREDENTIAL.ENABLE_CREDENTIAL ( credential_name IN VARCHAR2);
Parameters
Table 46-5 ENABLE_CREDENTIAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the credential. It can optionally be prefixed with a schema. This cannot be set to |
Usage Notes
-
Credentials reside in a particular schema and can be disabled by any user with the
CREATE
CREDENTIAL
OR
CREATE
ANY
CREDENTIAL
system privilege. To disable a credential in a schema other than your own, you must have theCREATE
CREDENTIAL
OR
CREATE
ANY
CREDENTIAL
privilege. -
A credential for an OS user can be viewed as an entry point into an operating system as a particular user. Allowing a credential to be disabled would allow an administrator (or credential owner) to quickly, easily and reversibly disallow all logins from the database to the OS as a particular user (external jobs, file transfers, external procedures, file watching). To disable an existing credential, you need to use the DISABLE_CREDENTIAL Procedure.
-
A library can become invalid if the properties of the credential – windows domain, username, password, its enable/disable bit – are changed.
46.4.5 UPDATE_CREDENTIAL Procedure
This procedure updates an existing Oracle credential.
Syntax
DBMS_CREDENTIAL.UPDATE_CREDENTIAL ( credential_name IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2);
Parameters
Table 46-6 UPDATE_CREDENTIAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the credential. It can optionally be prefixed with a schema. This cannot be set to |
|
Name of attribute to update: |
|
New value for the selected attribute |
Usage Notes
-
Credentials reside in a particular schema and can be created by any user with the
CREATE
CREDENTIAL
orCREATE
ANY
CREDENTIAL
system privilege. To create a credential in a schema other than your own, you must have theCREATE
ANY
CREDENTIAL
privilege. -
The user name is case sensitive. It cannot contain double quotes or spaces.
-
EXTPROC
alias libraries that reference the updated credential will become invalid. A library becomes invalid if the properties of the credential – windows domain, username, password, its enable/disable bit – are changed.
Examples
Update a Basic Credential
CONN scott
Enter password: password
BEGIN
-- Basic credential.
DBMS_CREDENTIAL.UPDATE_CREDENTIAL (
credential_name => 'JAMES_SMITH_CREDENTIAL',
attribute => 'password',
value => 'password2');
DBMS_CREDENTIAL.UPDATE_CREDENTIAL (
credential_name => 'JAMES_SMITH_CREDENTIAL',
attribute => 'username',
value => 'james_smith');
END;
Update a Windows Credential
CONN scott
Enter password: password
-- Credential including Windows domain
BEGIN
DBMS_CREDENTIAL.UPDATE_CREDENTIAL(
credential_name => 'JAMES_SMITH_WIN_CREDENTIAL',
username => 'james_smith',
password => 'password',
windows_domain => 'localdomain');
END
Display Information about Credentials
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 FROM all_credentials ORDER BY credential_name; CREDENTIAL_NAME USERNAME WINDOWS_DOMAIN ------------------------- -------------------- -------------------- JAMES_SMITH_CREDENTIAL james_smith JAMES_SMITH_WIN_CREDENTIAL james_smith LOCALDOMAIN 2 rows selected. SQL>