user_privs.sql

-- ************************************************************************************************************
-- * Filename           : user_privs.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show all system,table privs and granted role infor for a user 
-- * Usage              : @user_privs.sql
-- * Version            : v1
-- **********************************************************************************************************
set echo off
set define '&'
define username = &1
set tab off verify off
col USERNAME for a25
col ACCOUNT_STATUS for a15
col PROFILE for a30
col CREATED heading User|created_dt for a18
col PTIME heading Password|last|changed_dt for a18
col LTIME heading Password|last|locked_dt for a18
col EXPIRY_DATE heading Password|expire|date for a18
col default_tablespace heading Tablespace for a25
col  "User account status" for a80
col "Table_name" for a30
set lines 250 pages 49999


SELECT
	d.name DB_NAME,
    du.username,
    du.default_tablespace,
    du.account_status,
    du.profile,
    to_char(du.created,'DD-MON-YY HH24:MI:SS') created,
	to_char(du.expiry_date,'DD-MON-YY HH24:MI:SS') expiry_date
FROM
    dba_users du,
	v$database d
    --sys.user$ u
WHERE
    username = upper(TRIM('&username'))
;

SELECT GRANTEE "USERNAME",null "Table_name",PRIVILEGE "PRIVILEGE" FROM DBA_SYS_PRIVS WHERE GRANTEE = '&username'
union
SELECT GRANTEE "USERNAME",null,GRANTED_ROLE "PRIVILEGE" FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&username'
union
SELECT GRANTEE "USERNAME",TABLE_NAME,PRIVILEGE "PRIVILEGE" FROM DBA_TAB_PRIVS WHERE GRANTEE = '&username'