User account details

 

 

############################

Account  Password  History 

############################

select

  a.name,

   b.password_date

from

   sys.user$ a,

   sys.user_history$ b

where

   a.user# = b.user#;

############################

Account  last   lock time 

############################

 select NAME,LTIME   from user$   where NAME='abc';

SQL> select username,

  2  account_status,

  3  lock_date,

  4  expiry_date,

  5  profile

  6  from dba_users

  7  where username='SCOTT';

SELECT t2.*, t1.*

  FROM dba_audit_session t2, dba_audit_session t1

 WHERE     t2.returncode = 28000

       AND t1.timestamp < t2.timestamp

       AND t1.returncode = 1017

       AND t1.username = t2.username

select

    os_username,

   username,

   userhost,terminal client,

   action_name,

   returncode,timestamp

from

      dba_audit_session

      where

      returncode='1017'

      and

      username=='<username>'

      order by timestamp desc

############################

DDl  of USer 

############################

QL> set long 9999

SQL> set lin 300 pagesize 300

SQL> with t as

( select dbms_metadata.get_ddl('USER','SCOTT') ddl from dual )

select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' QUERY

from t;

-- Create user...

 select 'create user INFOC_I10_READ  identified by values '||''''||PASSword||''''||

 ' default tablespace '||default_tablespace||

 ' temporary tablespace '||temporary_tablespace||' profile '||

 profile||';'

 from sys.dba_users

 where username = 'INFOC_I10_READ';

-- Grant Roles...

 select 'grant '||granted_role||' to INFOC_I10_READ'||

 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'

 from sys.dba_role_privs

 where grantee = 'INFOC_I10_READ';

-- Grant System Privs...

 select 'grant '||privilege||' to INFOC_I10_READ'||

 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'

 from sys.dba_sys_privs

 where grantee = 'INFOC_I10_READ';

-- Grant Table Privs...

 select 'grant '||privilege||' on '||owner||'.'||table_name||' to INFOC_I10_READ;'

 from sys.dba_tab_privs

 where grantee = 'INFOC_I10_READ';

-- Grant Column Privs...

 select 'grant '||privilege||' on '||owner||'.'||table_name||

 '('||column_name||') to INFOC_I10_READ;'

 from sys.dba_col_privs

 where grantee = 'INFOC_I10_READ';

-- Set Default Role...

 select 'alter user INFOC_I10_READ default role '|| granted_role ||';'

 from sys.dba_role_privs

 where grantee = 'INFOC_I10_READ'

 and default_role = 'YES';

select dbms_metadata.get_ddl('DB_LINK' ,'MPR1DRDG.WORLD','L0280643') from dual;

############################

Take Password Backup 

############################

   with t as

  ( select dbms_metadata.get_ddl('USER','SCOTT') ddl from dual )

  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'

  from t;

  with t as

  ( select <b>TO_CHAR</b>(dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )

  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'

  from t;

with t as

  ( select <b>TO_CHAR</b>(dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )

  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'

  from t;

---In 10g:

SELECT 'ALTER USER '||username||' IDENTIFIED BY VALUES '''||password||'';' end FROM dba_users;

--Onward 11g:

SELECT 'ALTER USER '||a.username||' IDENTIFIED BY VALUES '''||b.spare4||''';'  FROM dba_users a,sys.user$ b WHERE b.name = a.username;

Generate Script for particular user

select u.username

,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd

from dba_users u

join sys.user$ s

on u.user_id = s.user#

where u.username = upper('&username');

############################

Connect to user without knowing password

############################

--- You can connect to another user without knowing the password, with grant connect through privilege

--- Suppose a user TEST1 wants to connect to TEST2 user and create a table and we don’t know the password of TEST2.

Conn / as sysdba

SQL >alter user TEST2 grant connect through TEST1;

User altered.

SQL >conn TEST1[TEST2]

Enter password:< Give password for TEST1>

SQL >show user

USER is "TEST2"

SQL >create table emp_test as select * from emp;

Table created.

SQL > conn / as sysdba

connected

SQL > select owner from dba_tables where table_name='EMP_TEST';

OWNER

------

TEST2