Oracle User Analysis
-- User creation and maintenance

CREATE USER any_username IDENTIFIED BY pass_word
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

ALTER USER any_username  IDENTIFIED BY  new_password;

CREATE USER global_user
IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US'
DEFAULT TABLESPACE g_tablespace
QUOTA 5M ON g_tablespace;

SELECT account_status FROM dba_users WHERE username = 'any_username';

SELECT last_login FROM dba_users WHERE username = 'any_username';

SELECT username, password_versions FROM dba_users;

SELECT dd.username, du.account_status 
FROM dba_users_with_defpwd  dd, dba_users du
WHERE dd.username = du.username
ORDER BY du.account_status, dd.username;

-- Time when account expires (password reset)

SELECT ptime  FROM sys.user$ WHERE name = 'any_username';

-- User profiles

SELECT * FROM dba_profiles  ORDER BY profile;

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT';

-- Proxy Users

SELECT * FROM proxy_users;


Database Login Details

Oracle registered trademark of Oracle Corporation.

Last Revised On: August 25th, 2014

  1151