PolarDB:CREATE PROFILE

1. (Optional) Create a password complexity verification function

To enforce password complexity, you first need to create a password verification function. This function is called when a user sets or changes a password. If the password does not meet the specified rules, the function throws an exception to prevent the operation.

-- Create a function named pwd_verify
CREATE OR REPLACE FUNCTION public.pwd_verify(
  username     IN VARCHAR2,
  password     IN VARCHAR2,
  old_password IN VARCHAR2
) RETURN BOOLEAN
AS
BEGIN
  -- Rule 1: The password cannot be empty.
  IF password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Password cannot be empty');
  END IF;

  -- Rule 2: The password must be at least 8 characters long.
  IF LENGTH(password) < 8 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters long');
  END IF;

  -- Rule 3: The password must contain at least one uppercase letter, one lowercase letter, and one special character.
  IF NOT REGEXP_LIKE(password, '[A-Z]') OR 
     NOT REGEXP_LIKE(password, '[a-z]') OR
     NOT REGEXP_LIKE(password, '[^A-Za-z0-9]') THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password must contain an uppercase letter, a lowercase letter, and a special character');
  END IF;

  RETURN TRUE;
END;
/

You can also implement more complex verification logic in the function to meet your security requirements. For example, you can check whether the password contains the username or is too similar to previous passwords.

-- Example of creating a custom password verification function
CREATE OR REPLACE FUNCTION custom_password_verify
(username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    -- Check password length
    IF LENGTH(password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters long');
    END IF;

    -- Check if the password contains a number
    IF NOT REGEXP_LIKE(password, '[0-9]') THEN
        RAISE_APPLICATION_ERROR(-20002, 'Password must contain a number');
    END IF;

    -- Check if the password contains an uppercase letter
    IF NOT REGEXP_LIKE(password, '[A-Z]') THEN
        RAISE_APPLICATION_ERROR(-20003, 'Password must contain an uppercase letter');
    END IF;

    -- Check if the password contains a lowercase letter
    IF NOT REGEXP_LIKE(password, '[a-z]') THEN
        RAISE_APPLICATION_ERROR(-20004, 'Password must contain a lowercase letter');
    END IF;

    -- Check if the password contains a special character
    IF NOT REGEXP_LIKE(password, '[^A-Za-z0-9]') THEN
        RAISE_APPLICATION_ERROR(-20005, 'Password must contain a special character');
    END IF;

    RETURN TRUE;
END;

2. Create a PROFILE

Use the CREATE PROFILE command to create a new profile and specify its security policies.

-- Create a profile named secure_profile
CREATE PROFILE secure_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 3         -- Allow a maximum of 3 failed logon attempts
    PASSWORD_LIFE_TIME 60           -- Password is valid for 60 days
    PASSWORD_REUSE_TIME 30          -- Old passwords cannot be reused within 30 days
    PASSWORD_REUSE_MAX 5            -- Password must be changed 5 times before reuse
    PASSWORD_VERIFY_FUNCTION pwd_verify -- Use the function created in the previous step to verify complexity
    PASSWORD_LOCK_TIME 1            -- Lock the account for 1 day
    PASSWORD_GRACE_TIME 7;          -- 7-day grace period after password expiration

3. View PROFILE information

You can query the dba_profiles view to check the configuration information of the PROFILE.

-- View PROFILE information
SELECT *
FROM dba_profiles
WHERE profile = 'SECURE_PROFILE'
ORDER BY profile, resource_name;

4. Assign the PROFILE to a user

After you create a PROFILE, you must assign it to a user for the policies to take effect.

• Assign a profile when you create a new user:

  CREATE USER john_doe IDENTIFIED BY "SecurePass123!" PROFILE secure_profile;

• Assign a profile to an existing user:

  ALTER USER jane_doe PROFILE secure_profile;

5. Manage user account status

If a user account is locked because it triggered a PROFILE policy, such as exceeding the number of failed logon attempts, manually unlock the account.

• Unlock a user account:

  ALTER USER john_doe ACCOUNT UNLOCK;

• Reset the password and unlock the account:
  When you reset a user's password, the account is automatically unlocked.

  ALTER USER john_doe IDENTIFIED BY "NewSecurePass456!" ACCOUNT UNLOCK;