Oracle Password Policy

[former_member343226]

Dear All,

I want to set the Oracle Password Policy for our SAP Systems. Our DB users include SYS, SAPSR3, etc. assigned to the database profiles "DEFAULT" and "SAPUPROF". The policy has to be implemented on both the profiles. Can you please advice how to alter them for the following requirement:

1. Minimum Password length=8

2. Password Complexity having atleast 1 Capital Letter and 1 Numeric Value

3. Failed Login atttempts = 3

Your prompt response on the same will be appreciated, Thanks.

Best Regards,

[ACE-SAP · Accepted solution]

Hi

This can be done using the profile password verification function (PASSWORD_VERIFY_FUNCTION).

You can use / customize the function provided with the here under SAP note to fulfill your needs.

Regards

1522952 - Password Complexity Verification Function

To enable the password verification function

ALTER PROFILE SAPUPROF LIMIT PASSWORD_VERIFY_FUNCTION verify_function_sap;

sap_utlpwdmg.sql serves as a template for SAP. You can customize this function according your password policy requirements.

[former_member343226 · Accepted solution]

Dear Yves & Raegan,

I checked the SQL script "utlpwdmg.sql" however I was not able to find the function "verify_function_sap". Whereas function "verify_function_11g" exists containing the complexity required.

Now I have 2 questions related to the same:

1. Do I have to use the function "verify_function_sap" or "verify_function_11g"?

2. For other dba_users (example:- system, sys, ops$users), i want to have the same level of policies implemented. Can I assign the SAPUPROF profile to the these system users ? If yes, will it impact my current operations ?

Thank you for your valuable feedback

[former_member188883 · Accepted solution]

Hi J K

1. Do I have to use the function "verify_function_sap" or "verify_function_11g"?

Please use verify_function_11g for Oracle 11g

2. For other dba_users (example:- system, sys, ops$users), i want to have the same level of policies implemented. Can I assign the SAPUPROF profile to the these system users ? If yes, will it impact my current operations ?

Suggestion would be not to change profiles for OPS$users. If you really want to change it, my suggestion is to test it out into a non-Production environment first before you roll-out to production.

Hope this helps.

Regards,

Deepak Kori

[former_member343226 · Accepted solution]

Deepak,

Thank you for the response.

Is there any SAP supporting document or sap note which can recommend on not to apply for system users other than SAPSR3/SAPSR3DB.

This is a requirement from audit.

Thanks and Regards,

[former_member188883 · Accepted solution]

Hi J K ,

There is a SAP note 1868094 - Overview: Oracle Security SAP Notes

Which has information on security parameter recommendations  as per SAP. But it does not include the point which I shared earlier.

Hence to meet audit requirements my suggestion is to test the same in a non-Production environment and test any impact with SAP functionality and DB operations.

OP$users are used for connection between application and DB. So any change in permission or authorization to these users might have some impact on day-to-day operations.

Hope this helps.

Regards,

Deepak Kori

[former_member343226 · Accepted solution]

The solution is as follows for future reference:-

==============================================================================

SAPUPROF PROFILE

==============================================================================

Step 1: Transfer sap_utlpwdmg.sql to respective server

Step 2: Execute script from /admin location : @sap_utlpwdmg.sql

Step 3: Alter SAPUPROF for failed login attemps:

    ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;

Step 4: Verify:

    select resource_name, limit from dba_profiles where profile='SAPUPROF';

==============================================================================

DEFAULT PROFILE

==============================================================================

Step 1: Execute script from /admin location: @utlpwdmg.sql

Step 2: alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

Step 3: alter profile default limit FAILED_LOGIN_ATTEMPTS 5;

Step 4: Verify:

    select resource_name, limit from dba_profiles where profile='DEFAULT';

[Reagan]

and to limit the FAILED_LOGIN_ATTEMPTS

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 3;

Regards

RB