Rem Filename   : userinfo.sql
Rem 
Rem Parameters : &1 = the user for which information should be listed.
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x.x, V9.x.x
Rem
Rem Modification history
Rem --------------------
Rem 28-jun-2001 Initial creation.
Rem 17-aug-2001 Added exception handling in case user (&1) does not exist.
Rem 19-oct-2001 Added database name and run date in title.
Rem 02-nov-2001 Replaced "External name" attribute by "Default roles".
Rem
Rem ==================================================================================================
Rem
Rem Description : Displays information about a given user including:
Rem                
Rem               - The default and temporary tablespace.
Rem               - All system privileges granted to the user.
Rem               - All roles granted to the user.
Rem               - All object privileges granted to the user.
Rem 
Rem ==================================================================================================

set serveroutput on
set trimspool    on
set pagesize     999
set feedback     off
set verify       off
set recsep       off

alter session set nls_numeric_characters = ',.';

DECLARE
  uAccStatus         dba_users.account_status%type;
  uLockDate          dba_users.lock_date%type;
  uExpiryDate        dba_users.expiry_date%type;
  uDefTabSpace       dba_users.default_tablespace%type;
  uTempTabSpace      dba_users.temporary_tablespace%type;
  uCreated           dba_users.created%type;
  uProfile           dba_users.profile%type;
  database_name      varchar2(30);
  date_now           varchar2(30);
  defrole_state      varchar2(30);

BEGIN
  BEGIN
  DBMS_OUTPUT.PUT_LINE('&1');
    SELECT account_status, lock_date, expiry_date, 
           default_tablespace, temporary_tablespace,
           created, profile
      INTO uAccStatus, uLockDate, uExpiryDate,
           uDefTabSpace, uTempTabSpace,
           uCreated, uProfile
      FROM dba_users
     WHERE username = upper('&1');
    SELECT name, to_char(sysdate,'DD-MON-YYYY') INTO database_name, date_now
      FROM v$database;
    SELECT decode(defrole, 0, 'NONE', 1, 'ALL', 2, 'SOME', 3, 'ALL EXCEPT SOME', '???' )
      INTO defrole_state
      FROM sys.user$ 
     WHERE name = upper('&1');

    dbms_output.put_line( 'Properties of user ' || upper('&1') || 
                          lpad( '(on database ' || database_name || ', date ' || date_now || ')', 54)   );
    dbms_output.put_line( rpad( '=', 85, '=' ));
    dbms_output.put_line( 'Default tablespace   : ' || rpad(uDefTabSpace, 25)
                          || 'Status        : '|| uAccStatus );
    dbms_output.put_line( 'Temporary tablespace : ' || rpad(uTempTabSpace, 25)
                          || 'Lock date     : ' || to_char(uLockDate,'DD-MON-YYYY') );
    dbms_output.put_line( 'Creation date        : ' || rpad(to_char(uCreated,'DD-MON-YYYY'), 25)
                          || 'Expiry date   : ' || to_char(uExpiryDate,'DD-MON-YYYY') );
    dbms_output.put_line( 'Profile              : ' || rpad(uProfile, 25)
                          || 'Default roles : ' || defrole_state );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line( 'User <' || upper('&1') || '> does not exist.'); 

  END;

END;
/

COLUMN Quota FORMAT A20 HEADING "Quota (Kbytes)"

SELECT tablespace_name "Tablespace", 
       decode( max_bytes, -1, 'unlimited', to_char(max_bytes/1024,'999G999') ) "Quota"
  FROM dba_ts_quotas
 WHERE username = upper('&1');


COLUMN GrantedRole FORMAT A30 HEADING "Granted Role"
COLUMN Privilege   FORMAT A30 HEADING "Privilege"
COLUMN AdminOption FORMAT A12 HEADING "Admin Option"
COLUMN DefaultRole FORMAT A12 HEADING "Default Role"

SELECT granted_role   "GrantedRole", 
       admin_option   "AdminOption",
       default_role   "DefaultRole"
  FROM dba_role_privs
 WHERE grantee  = upper('&1')
 ORDER BY granted_role;

SELECT privilege      "System privilege",
       admin_option   "AdminOption"
  FROM dba_sys_privs
 WHERE grantee = upper('&1')
 ORDER BY privilege;


COLUMN Owner       FORMAT A30 HEADING "Object owner"
COLUMN TableName   FORMAT A30 HEADING "Object name"
COLUMN Privilege   FORMAT A12 HEADING "Privilege"
COLUMN Grantable   FORMAT A12 HEADING "Grantable"

SELECT owner        "Owner",
       table_name   "TableName",
       privilege    "Privilege",
       grantable    "Grantable"
  FROM dba_tab_privs
 WHERE grantee = upper('&1')
 ORDER BY owner, table_name,privilege;

PROMPT

set feedback  on