get list of user accounts - either unlocked or password is expired.

 

SELECT    

     'ALTER '||

     SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),

     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'DEFAULT')-1) ,

     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),500),'USER')) USER_PASS , USERNAME, PROFILE 

FROM  DBA_USERS 

WHERE USERNAME IN 

     (SELECT USERNAME  

      FROM DBA_USERS 

      WHERE (USERNAME  in

            (select username  from dba_users where (account_status like '%EXPIRED%' or account_status like '%LOCK%'))));

How to login to user when you don't know the password in Oracle

   steps :-

1.    select dbms_metadata.get_ddl('USER','SCOTT') from dual;
2.    change password
3.    connect 
4.    do the work
5.    change it back
6.    revert back to original password from step 1

How to reset password in Oracle after the current password has expired

declare
 cursor pass is 
SELECT    
     'ALTER '||
     SUBSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),
     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),200),'DEFAULT')-1) ,
     INSTR(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),200),'USER')) USER_PASS 
FROM  DBA_USERS 
WHERE USERNAME IN 
     (SELECT USERNAME  
      FROM DBA_USERS 
      WHERE (USERNAME  in
            (select username  from dba_users where account_status like '%EXPIRED%' and
              (
               username like 'SYSTEM' or 
               username like 'TEST%' ))));

BEGIN
  for rec in pass loop
   begin
    execute immediate rec.USER_PASS;
    exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM);
   end;
  end loop;
END;
/

GRANTS to USER

-- gen_drop_objects.sql

   set pages 0 feedb off
   accept inuser char prompt 'Enter Userid: '
   -- spool run_drop_objects_&&inuser..out
   spool run_drop_objects_MAXIMO.out
   SELECT 'DROP ' || OBJECT_TYPE || ' ' || owner||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS;'
   FROM   dba_objects
   WHERE OBJECT_TYPE = 'TABLE' and owner = upper('&inuser')
   union all
   select 'drop '||case
            when object_type='PACKAGE BODY' then 'PACKAGE'
            else object_type
          end
     ||' '||owner||'.'||object_name||' ;'
   from dba_objects
   where owner = upper('&inuser')
   /
   prompt purge dba_recyclebin
   prompt /
   
   spool off
   
   
-- gen_rdonly_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_rdonly_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select '
                                  when 'VIEW'  then 'select '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW')
/
spool off
set pages 30 feedback on

--  gen_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select, insert, update, delete '
                                  when 'VIEW'  then 'select '
                                  when 'SEQUENCE' then 'select '
                                  when 'PROCEDURE' then 'execute '
                                  when 'PACKAGE' then 'execute '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE')
/
spool off
set pages 30 feedback on


-- gen_new_grants.sql

set feedback off pages 0 lines 100
spool run_new_grants.out
select 'create synonym SCOTT_USER.'||object_name||' for '||owner||'.'||object_name||';'
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('PROCEDURE','PACKAGE','FUNCTION','TABLE')
minus
select synonym_name from dba_synonyms where table_owner='SCOTT_OWN')
/


select case
       when object_type in ('TABLE') then
       'grant select,insert,update,delete on '||owner||'.'||object_name||' to SCOTT_USER;'
       when object_type in ('VIEW','SEQUENCE') then
       'grant select on '||owner||'.'||object_name||' to SCOTT_USER;'
       else
       'grant execute on '||owner||'.'||object_name||' to SCOTT_USER;'
       end
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
minus
select distinct table_name from dba_tab_privs where owner='SCOTT_OWN' and grantee='CSX_SCOTT_USER_ROLE')
/
spool off
set feedback on pages 24 lines 140

User with Read only Access

CREATE USER scott_user IDENTIFIED BY scott_user 
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
-- From Role Privileges
GRANT COMPANY_RDONLY_ROLE TO scott_user;
GRANT COMPANY_CONNECT TOscott_user;

CREATE OR REPLACE TRIGGER scott_user.after_logon_trg
AFTER LOGON ON scott_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCOTT_OWN';
END;
/

What is causing account lock

 -- check profile

col PROFILE format a10

col LIMIT format a15

select * from dba_profiles where resource_type='PASSWORD' order by 1,2;

-- options for trouble shooting

1. audit not successful connections

2. trace 1017 event

-- Steps for option 1

Reference: oracle doc # 352389.1

Reference:   oracle doc #  221944.1

-- setup audit

sqlplus / as sysdba

AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

-- Get information related to failed connections

col USERID format a10

col USERHOST format a25

col TERMINAL format a10

select returncode, action#, userid, userhost, terminal from aud$ where returncode='1017' and action#=100;

SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE   from aud$ where returncode=1017;

NOTE : TIME is in GMT, so subtract -5 hours

-- Steps for option 2

-- to trace what is locking the account

1.       Login to sqlplus as sysdba

2.       enable trace, using

                   alter system set events ‘1017 trace name errorstack level 10’;

3.       Show parameter dump

4.       Goto the udump location

5.       grep ORA – 01017 *

6.       get the timestamp and related information  from trace generated

7.       When trace is no more needed , set it off using:

              alter system set events ‘1017 trace name errorstack off’

8.       Goto the listener.log file location

9.       Get the connectivity information for the selected time from listener log file.

Hope this help. Regards Rupam

Unable to start database - ORA-09925: Unable to create audit trail file

Error

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 28: No space left on device

Symptom

Unable to start database

Cause

----snip---

Could not open audit file: /ora01/grid/11.2.0.2/grid/rdbms/audit/+asm2_ora_25189_b.aud

Retry Iteration No: 1 OS Error: 28

Retry Iteration No: 2 OS Error: 28

Retry Iteration No: 3 OS Error: 28

Retry Iteration No: 4 OS Error: 28

Retry Iteration No: 5 OS Error: 28

OS Audit file could not be created; failing after 5 retries

Solution :

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms > du -ks audit

355614 audit

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms > cd audit

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" |wc -l

340729

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" \( -mtime +3 -o -atime +3 \) | xargs rm

oracle@tiger /ora01/grid/11.2.0.2/grid/rdbms/audit > find . -name "*.aud" |wc -l

25701

Hope this help. Regards Rupam

what is locking the oracle account

steps:-

1.       Login to sqlplus as sysdba

2.       enable trace, using

                   alter system set events ‘1017 trace name errorstack level 10’;

3.       Show parameter dump

4.       Goto the udump location

5.       grep ORA – 01017 *

6.       get the timestamp and related information  from trace generated

7.       When trace is no more needed , set it off using:

              alter system set events ‘1017 trace name errorstack off’

8.       Goto the listener.log file location

9.       Get the connectivity information for the selected time from listener log file.

Hope this help. Regards Rupam

dba_role_privs dba_tab_privs dba_sys_privs

List user Priviliges

set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27

ACCEPT username  prompt 'Enter Username : '

spool quser_privs.log

PROMPT Roles granted to user

SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');

PROMPT Table Privileges granted to a user through roles

SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
     FROM dba_role_privs WHERE grantee=UPPER('&username')
       UNION
       SELECT granted_role
     FROM role_role_privs
     WHERE role in (SELECT granted_role
                FROM dba_role_privs WHERE grantee=UPPER('&username')
               )
    ) roles, dba_tab_privs
WHERE granted_role=grantee;

PROMPT System Privileges assigned to a user through roles

SELECT granted_role, privilege
FROM ( SELECT granted_role
     FROM dba_role_privs WHERE grantee=UPPER('&username')
       UNION
       SELECT granted_role
     FROM role_role_privs
     WHERE role in (SELECT granted_role
                FROM dba_role_privs WHERE grantee=UPPER('&username')
               )
    ) roles, dba_sys_privs
WHERE granted_role=grantee;

PROMPT Table privileges assigned directly to a user

SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');

PROMPT System privileges assigned directly to a user
SELECT privilege, admin_option
FROM  dba_sys_privs
WHERE grantee=UPPER('&username');

spool off



Hope this Helps! Rupam

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?

Oracle Roles

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?