Home » SQL & PL/SQL » SQL & PL/SQL » Queries for User and Password Policy (Oracle DB 11g)
Queries for User and Password Policy [message #648043] Mon, 15 February 2016 10:08 Go to next message
fanigadu20
Messages: 1
Registered: February 2016
Location: India
Junior Member
Hi Team,


There is an audit requirement to extract all the users and their corresponding password policy. I am not getting the password policy used. I would need help to get the same.


Thanks.


[EDITED by LF: fixed topic title typo; was "passpord"]

[Updated on: Tue, 16 February 2016 14:09] by Moderator

Report message to a moderator

Re: Queries for User and Passpord Policy [message #648046 is a reply to message #648043] Mon, 15 February 2016 10:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
Senior Member
Hint - profile.

SY.
Re: Queries for User and Passpord Policy [message #648048 is a reply to message #648043] Mon, 15 February 2016 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

SQL> col table_name format a20
SQL> col comments format a80
SQL> select * from dict where lower(comments) like '%profile%';
TABLE_NAME           COMMENTS
-------------------- -----------------------------------------------
DBA_PROFILES         Display all profiles and their limits
DBA_SQL_PROFILES     set of sql profiles


DBA_PROFILES

CREATE PROFILE

[Updated on: Mon, 15 February 2016 11:50]

Report message to a moderator

Re: Queries for User and Passpord Policy [message #648052 is a reply to message #648048] Mon, 15 February 2016 14:56 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
select a.username,a.profile,b.resource_name,b.limit
from dba_profiles b,dba_users a
where a.profile = b.profile
and b.resource_type = 'PASSWORD'
ORDER BY A.USERNAME,B.RESOURCE_NAME;
Previous Topic: truncating a empty table
Next Topic: using index and cursor
Goto Forum:
  


Current Time: Thu Jun 25 12:07:43 CDT 2026