DBA Hub

📋Steps in this guide1/6

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.

oracle 12cconfigurationintermediate
by OracleDba
19 views
1

Security

The privilege allows a user to create a local credential. The privilege allows a user to create a credentials for any user.
2

Create Credentials

Credentials are database objects that hold a username/password pair for authenticating local and remote external jobs. They are created using the 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. Information about credentials is displayed using the views.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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;
/

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>
3

Enable/Disable Credentials

Credentials are enabled and disabled using the and procedures respectively.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- 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>
4

Update Credentials

The procedure allows you to modify attributes of a credential.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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>
5

Drop Credentials

Credentials are dropped using the procedure.

Code/Command (click line numbers to comment):

1
2
exec dbms_credential.drop_credential('tim_hall_credential');
exec dbms_credential.drop_credential('tim_hall_win_credential');
6

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 and 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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!