Query for Oracle User Password Status
The article “Query for Oracle User Password Status” allows you to view the password status of an account in the Oracle database.
When an Oracle account is created, if a specific profile is omitted from the CREATE command, Oracle assigns the DEFAULT PROFILE to the created user.
The DEFAULT PROFILE has various resource and password configuration parameters, including password expiration, i.e., after how many days the password expires, and after how many consecutive password errors the user is locked out of the Oracle database.
In the article “Query for Oracle User Password Status,” the display of password expiration is made possible through the selection of the following fields:
USERNAME – Names of the databaseSCHEMAs.
ACCOUNT_STATUS – Account status, whether LOCKED or EXPIRED.
PROFILE – Profile associated with the user where machine resource quantities and password management are assigned.
CREATED – User creation date.
GRANTED_ROLE – The name of the associated role.
This information is extracted from the DBA_USERS and DBA_ROLE_PRIVS views joined by the JOIN username = grantee.
Query to display the password status of accounts in an Oracle database, the PROFILE, and associated ROLES.
set lines 300
set pages 999
col user for a15
col ACCOUNT_STATUS for a20
col PROFILE for a25
col granted_role for a35
select distinct username, account_status, profile, created, granted_role
from dba_users, dba_role_privs where username=grantee
order by 1;To view my guides on CRS Commands, click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin
0 Comments