Security - User Profile


          clear columns 

          set lines 300 

          set pages 66 

          set feedback off 

          set verify off 

          set echo off 

          

          set markup html on spool on 

          col resource_name form a30 

          col external_name form a30 

          

          spool account_profile_&&username..html 

          

          select ACCOUNT_STATUS,LOCK_DATE, EXPIRY_DATE,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, INITIAL_RSRC_CONSUMER_GROUP 

          from dba_users where username = '&&username'; 

          select * from dba_sys_privs where grantee = '&&username'; 

          select * from dba_role_privs where grantee = '&&username'; 

          select GRANTEE, table_name, privilege from dba_tab_privs where grantee = '&&;username' order by table_name, privilege; 

          

          spool off 

          exit;


        select GRANTEE, table_name, privilege from dba_tab_privs 

        union 

        select GRANTEE, PRIVILEGE, 'NA' from dba_sys_privs 

        union 

        select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from  dba_role_privs;

Clone User


        set long 99999 lines 100 

        set head off 


        begin
          dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
          dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
        end;
         /

        select dbms_metadata.get_ddl('USER','USERNAME') from dual; 

        select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'USERNAME') from dual; 

        select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'USERNAME' ) from  dual; 

        select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'USERNAME' ) from dual; 

        select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'USERNAME' ) from dual; 

        select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'USERNAME' ) from dual;

Unlock all Users


        select 'alter user '||username||' account unlock;'||chr(10) 

        from dba_users 

        where upper(account_status) like '%LOCKED%'

Change User Password and By-pass Security Audit Standard

    alter profile APPUSER_PROFILE limit PASSWORD_LIFE_TIME 365;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_MAX 12;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_TIME 365;
alter profile APPUSER_PROFILE limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_APPUSER;
alter profile ENDUSER_PROFILE limit PASSWORD_REUSE_MAX 12;

alter profile APPUSER_PROFILE limit PASSWORD_LIFE_TIME unlimited;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_MAX unlimited;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_TIME unlimited;
alter profile APPUSER_PROFILE limit PASSWORD_VERIFY_FUNCTION SBSA_VERIFY_FUNCTION;


alter user &user identified by &pass;

select expiry_date from dba_users where upper(username) = upper('&user');

alter profile APPUSER_PROFILE limit PASSWORD_LIFE_TIME 365;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_MAX 12;
alter profile APPUSER_PROFILE limit PASSWORD_REUSE_TIME 365;
alter profile APPUSER_PROFILE limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_APPUSER;
alter profile ENDUSER_PROFILE limit PASSWORD_REUSE_MAX 12;

select expiry_date from dba_users where upper(username) = upper('&user');

Oracle Maintained Users

    select username from dba_users where oracle_maintained = 'Y' order by username;

Some Oracle Maintained Roles

    select role from dba_roles where oracle_maintained = 'Y' order by role;

HOME