User Creation

User Creation

 Model ID     : USER1

 New Username : NEWUSER

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

      CREATE USER "USER1" IDENTIFIED BY "passowrd"

             DEFAULT TABLESPACE "USERS"

        TEMPORARY TABLESPACE "TMP"

            quota 10g on users

            password expire;      

select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USER1') from dual;

            GRANT CREATE VIEW TO "USER1"

            GRANT SELECT ANY TABLE TO "USER1"

            GRANT CREATE TABLE TO "USER1"

            GRANT CREATE SESSION TO "USER1"

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USER1') from dual;

            no rows selected

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER1') from dual;

      

User Profile

User Profile

      select username,account_status,expiry_date,profile from dba_users where username like 'MYSIP_FE';

      select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where PROFILE='ORANGE_APPLICATION_PROFILE'

 ------------------------------------------------------

       select username,account_status,expiry_date from dba_users where account_status in ('EXPIRED','LOCKED');

User Auditing

User Auditing

  -----------------------------------------------

 SYSDBA priviledges

      select * from v$pwfile_users;

      select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTED_ROLE='DBA';

  Audit

      SHow parameter audit_trail

      Show parameter audit

       

 To check the failed loggin 

      set lines 130

      col OS_USERNAME for a20

      col USERNAME for a20

      col USERHOST for a20

      select OS_USERNAME,USERNAME,ACTION_NAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode

      from dba_audit_trail 

      where returncode > 0

      action code-- logon (if account locked )

      reture code-- Ora error

Value for Action# column of 100 indicates a login record.

          select   ACTION#, userid,userhost,returncode, count(*)  from sys.aud$ 

         where NTIMESTAMP# >= sysdate-15  and returncode <> 0 and Action#=100

         group by  ACTION#, userid,userhost,returncode having count(*) > 100 

         order by ACTION#, userid,userhost

User Management

• User Creation
• User Auditing
• User Profile