DBA Hub

📋Steps in this guide1/5

Multitenant : Common Mandatory Profiles in the CDB Root in Oracle Database 21c

This article demonstrates using a common mandatory profile to enforce password complexity in all PDBs without affecting existing user-level profiles.

oracle 21cconfigurationintermediate
by OracleDba
16 views
1

Setup

Create a DBA common user in the root container, and a DBA user in the PDB. We will use these users to test the password verification later.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
-- CDB
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create user c##my_dba_user identified by DbaPassword1 container=all;
grant create session, dba, pdb_dba to c##my_dba_user container=all;

-- PDB
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create user my_dba_user identified by DbaPassword1;
grant create session, dba, pdb_dba to my_dba_user;
2

User Profiles

We connect to the PDB and create a password verification function, which will accept any password so long as it is longer than 8 characters. We create a normal profile that references this password verification function, and assign it to the DBA user in the PDB. We connect to the DBA user in the PDB and test it. Notice it fails when we set the password to a value shorter than 8 characters.

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 sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create or replace function user_pwd_verify_function (
  username     varchar2,
  password     varchar2,
  old_password varchar2)
return boolean is
begin
  if not ora_complexity_check(password,
                              chars     => 8)
  then
    return(false);
  end if;
  return(true);
end;
/

create profile user_profile 
  limit password_verify_function user_pwd_verify_function;

alter user my_dba_user profile user_profile;

conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

alter user my_dba_user identified by short;

*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 8 characters


alter user my_dba_user identified by DbaPassword1;

User altered.

SQL>
3

Mandatory Profiles

In the documentation you will see these referred to as "Common Mandatory Profiles", "Generic Mandatory Profiles" and "Mandatory Profiles", depending on the context. We connect to a privileged user in the root container and create a password verification function that matches our needs. We create a mandatory profile using a similar syntax to a regular profile, with the addition of the keyword. For a mandatory profile we can only set the and password limits, but we can set other non-password limits. In this example we set the , and limits. We can alter the limits using the statement. We will set everything but the limit back to the default values. We can drop the profile using the command. The examples below require this profile, so don't drop it yet.

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
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create or replace function c##cdb_pwd_verify_function (
  username     varchar2,
  password     varchar2,
  old_password varchar2)
return boolean is
begin
  if not ora_complexity_check(password,
                              chars     => 16,
                              uppercase => 1,
                              lowercase => 1,
                              digit     => 1,
                              special   => 1)
  then
    return(false);
  end if;
  return(true);
end;
/

create mandatory profile c##cdb_manadatory_profile 
  limit 
    password_verify_function cdb_pwd_verify_function
    password_grace_time 7
    idle_time 300
  container = all;

alter profile c##cdb_manadatory_profile 
  limit 
    password_grace_time default
    idle_time default
  container = all;

drop profile c##cdb_manadatory_profile;
4

Common Mandatory Profiles

With the mandatory profile created, we set it as the initialization parameter value in the root container, which makes it the default action for all users in the root container and all PDBs. This is now a common mandatory profile. This mandatory profile will run before any user-specific profiles, so the most restrictive limit will take effect. We test it at the CDB and PDB level and see the same password verification check is affecting both levels. Notice at the PDB level the password length limit is now 16 characters, not 8 characters. The mandatory profile has run before the user profile, so the more restrictive limit has taken effect. To remove the common mandatory profile we need to reset the initialization parameter. Unfortunately this doesn't take effect without a restart of the database.

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
alter system set mandatory_user_profile=c##cdb_manadatory_profile;

show parameter mandatory_user_profile

NAME                   TYPE   VALUE
---------------------- ------ -------------------------
mandatory_user_profile string C##CDB_MANADATORY_PROFILE
SQL>

-- CDB
conn c##my_dba_user/DbaPassword1@//localhost:1521/cdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user c##my_dba_user identified by DbaPassword1 container=all;

*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters


-- PDB
conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user my_dba_user identified by DbaPassword1;
*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters

conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

alter system reset mandatory_user_profile;

show parameter mandatory_user_profile

NAME                   TYPE   VALUE
---------------------- ------ -------------------------
mandatory_user_profile string C##CDB_MANADATORY_PROFILE
SQL>

conn / as sysdba
shutdown immediate;
startup;

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string
SQL>
5

PDB Mandatory Profiles

We can set a mandatory profile at the PDB-level, so it is PDB-specific. We connect to the PDB (not the CDB) and set the initialization parameter as before. Assuming you reset the common mandatory profile before, we should see this setting has no impact at the CDB level, just the PDB level. For more information see: - Common Mandatory Profiles in the CDB Root - CREATE PROFILE - ALTER PROFILE - Multitenant : All Articles Hope this helps. Regards Tim...

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
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter system set mandatory_user_profile=c##cdb_manadatory_profile;

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>

-- CDB
conn c##my_dba_user/DbaPassword1@//localhost:1521/cdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string
SQL>


alter user c##my_dba_user identified by DbaPassword1 container=all;

User altered.

SQL>


-- PDB
conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user my_dba_user identified by DbaPassword1;
*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters

Comments (0)

Please to add comments

No comments yet. Be the first to comment!