Stop Oracle Password Expiry – Set Password Life Time to Unlimited

Oracle Password Life Time Set to Unlimited
-------------------------------------------------------

In Oracle database, sometimes user password gets expired automatically.
This happens because PASSWORD_LIFE_TIME is defined in the user profile.

To avoid password expiry, we can set password life time to UNLIMITED.

This is mostly required for application users and service accounts.


Step 1: Check user profile

First, check which profile is assigned to the user.

SELECT username, profile
FROM dba_users
WHERE username = 'ROHIT';

Most of the time, user is using the DEFAULT profile.


Step 2: Set password life time to unlimited

If user is using DEFAULT profile, run below command.

ALTER PROFILE DEFAULT
LIMIT PASSWORD_LIFE_TIME UNLIMITED;

If user is using some other profile, replace profile name.

ALTER PROFILE APP_PROFILE
LIMIT PASSWORD_LIFE_TIME UNLIMITED;


Step 3: Verify the setting

After changing the profile, verify the password life time value.

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_LIFE_TIME';


Step 4: Reset expired password (if required)

If user password is already expired, reset the password and unlock the account.

ALTER USER ROHIT IDENTIFIED BY new_password;
ALTER USER ROHIT ACCOUNT UNLOCK;


Now the user password will not expire automatically.

This setting is very useful for application schemas and batch users.
For normal users, password expiry is recommended for better security.

Oracle Role Creation With Examples

Oracle Role Creation With Examples

In this blog we will discuss how to create a role in Oracle Database and
how to give rights to users using that role.

Roles are very useful because instead of giving permissions to each user
one by one, we can put all permissions inside a role and then assign
that role to users.

Create Role 

Example: CREATE ROLE view_role;

Grant Privileges to Role 

Example: GRANT CREATE SESSION TO view_role;
GRANT SELECT ANY TABLE TO view_role;

We can also give table-level rights. Example: GRANT SELECT ON
hr.employees TO view_role;

Create User 

Example: CREATE USER rohit IDENTIFIED BY rohit123;

Grant Role to User 

Example: GRANT view_role TO rohit;

Revoke Privileges from Role

Example: REVOKE SELECT ANY TABLE FROM
view_role;

Drop Role

Example: DROP ROLE view_role;

Conclusion Using roles in Oracle is very helpful for managing user
permissions. Instead of granting many privileges to each user
separately, it is better to create one role, assign all required rights
to that role, and then grant the role to users. This makes
administration easy and clean.