12 Replies Latest reply: Mar 22, 2014 9:01 AM by EdStevens RSS

    All users DB login information

    07499ddc-970a-4f3a-8258-49dd82548c5b

      Hi All,

       

      We are using Oracle 10g and we enabled the auditing,

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      audit_sys_operations boolean TRUE
      audit_syslog_level string
      audit_trail string DB

      But we are not getting all users login information in DBA_AUDIT_TRAIL and sys.aud$.

      There are information for some users but not for all users. Is it mean that other user are not audited.

      How do we find user list which are currently audited.

       

      Thanks !

        • 1. Re: All users DB login information
          Antonio Navarro

          You must audit logon users, maybe DBA_AUDIT_SESSION can help

           

          HTH - Antonio NAVARRO

          • 2. Re: All users DB login information
            BDK

            The three main views are shown below.

             

             

            DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).

            DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).

            DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

             

             

            Example:

            --------

            SQL> STARTUP

            Next we audit all operations by the one specified user (eg:SCOTT)

             

             

            SQL>AUDIT ALL BY SCOTT BY ACCESS;

             

             

            SQL>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,DELETE TABLE BY SCOTT BY ACCESS;

             

             

            The following query displays the information from the database audit trail

             

             

            SQL>SELECT username, extended_timestamp, owner, obj_name, action_name FROM dba_audit_trail WHERE owner = 'SCOTT' ORDER BY timestamp

             

             

            HTH

             

            Regards,

            -DK

            • 3. Re: All users DB login information
              07499ddc-970a-4f3a-8258-49dd82548c5b

              Hi

               

              DBA_AUDIT_SESSION is not showing all users login information .  So my question is that, is it mence the other users which information are not present in   DBA_AUDIT_TRAIL and sys.aud$,DBA_AUDIT_SESSION are not audited currently right ?

               

              If Yes, Then is there any way to check on which users the audit is enabled?

              • 4. Re: All users DB login information
                mtefft

                You say that connections are being audited for some, but not all users? Are the missing users SYS ? Connections by SYS will only go to the operating system audit trail.

                 

                Also, post results of these queries:

                SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE PRIVILEGE LIKE '%SESSION%';

                SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION LIKE '%SESSION%';

                • 5. Re: All users DB login information
                  07499ddc-970a-4f3a-8258-49dd82548c5b

                  Yes connections are being audited for some, but not for all users. My database having more than 1000 users but only I can see the logon information for around 150 users.

                   

                  Below are the result for queries:-

                   

                  SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION LIKE '%SESSION%';

                   

                   

                  USER_NAME             PROXY_NAME            AUDIT_OPTION                             SUCCESS              FAILURE

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

                                                                                   CREATE SESSION                         NOT SET                 BY ACCESS

                  ADMIN_NGRAPH                                       CREATE SESSION                         BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                       ALTER SESSION                            BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                       RESTRICTED SESSION                  BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                       DEBUG CONNECT SESSION          BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                                  CREATE SESSION                         BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                                  ALTER SESSION                            BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                                  RESTRICTED SESSION                  BY ACCESS            BY ACCESS

                  ADMIN_AMANDALE                                  DEBUG CONNECT SESSION          BY ACCESS            BY ACCESS

                   

                   

                  9 rows selected.

                   

                   

                  SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS WHERE PRIVILEGE LIKE '%SESSION%';

                   

                   

                  USER_NAME            PROXY_NAME           PRIVILEGE                                SUCCESS                  FAILURE

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

                                                                                CREATE SESSION                         NOT SET                 BY ACCESS

                  ADMIN_NGRAPH                                    CREATE SESSION                         BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                    ALTER SESSION                            BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                    RESTRICTED SESSION                  BY ACCESS            BY ACCESS

                  ADMIN_NGRAPH                                    DEBUG CONNECT SESSION          BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                               CREATE SESSION                         BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                               ALTER SESSION                            BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                               RESTRICTED SESSION                  BY ACCESS            BY ACCESS

                  ADMIN_AGAVENDE                               DEBUG CONNECT SESSION          BY ACCESS            BY ACCESS

                   

                   

                  9 rows selected.

                  • 6. Re: All users DB login information
                    mtefft

                    Well, there's your answer. You are not auditing successful connections by default - you are doing so only for a few.

                     

                    Try AUDIT CREATE SESSION ;

                    and you should see the'NOT SET' change to 'BY ACCESS' and you should see the conenctions being audited.

                    • 7. Re: All users DB login information
                      07499ddc-970a-4f3a-8258-49dd82548c5b

                      Thanks for update.

                       

                      So you mean that all user are not auditing. But one question, in above two queries we can see that only two user are audited but when we check the DBA_AUDIT_TRAIL and sys.aud$ view I will find around 150 users audit information.

                       

                      Can you please let me know how this is happening.

                       

                      Thanks!

                      • 8. Re: All users DB login information
                        mtefft

                        What events are being audited?

                         

                        SELECT USER, ACTION_NAME, RETURNCODE , COUNT(*)  FROM DBA_AUDIT_TRAIL

                        WHERE TIMESTAMP >= SYSDATE-1 AND USER NOT IN ('ADMIN_AGAVENDE','ADMIN_NGRAPH')

                        GROUP BY USER, ACTION_NAME, RETURNCODE;

                        • 9. Re: All users DB login information
                          07499ddc-970a-4f3a-8258-49dd82548c5b

                          Below is the output of the query, but we selected record for last 30 days.

                           

                           

                          USERNAME             ACTION_NAME                  RETURNCODE   COUNT(*)

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

                          TSIMON                           LOGON                              1017          1

                          BUNDOR16                      LOGON                              1017          1

                          MGRIGO16                       LOGON                              1017          1

                          KWOY90                          LOGON                              1017          1

                          BIARNESA                       LOGON                              1017          1

                          JSTADE16                        LOGON                              1017          1

                          JSTADE08                        LOGON                              1017          1

                          OORTEGAT                      LOGON                              1017          1

                          KOSSELAE                      LOGON                              1017          1

                          ODBC_SUPPLIER_DB      LOGON                              1017         1

                          IALTME08                        LOGON                              1017          1

                          ODBC_RLEENDERS        LOGON                              1017          1

                          ODBC USERID                LOGON                              1017          2

                          TPOCH08                        LOGON                              1017          1

                          SMUFFE16                     LOGON                              1017          1

                          GGLAS                           LOGON                              1017          1

                          KDIETMAN                      LOGON                              1017         1

                          FVRANC80                      LOGON                              1017          1

                          RCHERUPA                    LOGON                              1017          1

                          RROESN91                     LOGON                              1017          1

                          ADAMLE                         LOGON                              1017          1

                          SMONFE16                     LOGON                              1017          1

                          TEST                              LOGON                              1017          1

                          MKUPER12                    LOGON                              1017          1

                          UGEISS08                      LOGON                              1017          1

                          VSCHIR16                      LOGON                              1017          1

                          ODBC_JANSEN              LOGON                              1017          1

                          VSCHOUPP                   LOGON                              1017          1

                          OBIWAN4                      LOGON                              1017          1

                          FORGBER                     LOGON                              1017          1

                          MHEINZ08                      LOGON                              1017          1

                          KAPSAPRE                   LOGON                              1017          1

                          DPROCHOW                 LOGON                              1017          1

                          JTOTH12                       LOGON                              1017          1

                          KZISKO08                     LOGON                              1017          1

                          JSCHRE16                    LOGON                              1017          1

                          MPETRUS                    LOGON                              1017          1

                          AGROTE16                   LOGON                              1017          1

                          RTEICH16                     LOGON                              1017          1

                          ODBC_BROBECK         LOGON                              1017          1

                          EYARAN08                   LOGON                              1017          1

                          KTHOR70                      LOGON                              1017          1

                          NVCAMPEN                  LOGON                              1017          1

                          FSTUEF70                    LOGON                              1017          1

                          IWESSE08                   LOGON                              1017          1

                          JCSARAY                    LOGON                              1017          1

                          BMENZ16                    LOGON                              1017          1

                          RRAIKAR                     LOGON                              1017          1

                          LEENDGES                 LOGON                              1017          1

                          LEENDALI                   LOGON                              1017          1

                          UHESSE16                  LOGON                              1017          1

                          RENGEL80                  LOGON                              1017          1

                          MURKS                       LOGON                              1017          4

                          TSIMON16                   LOGON                              1017          1

                          MHEINZ16                   LOGON                              1017          2

                          ODBC_BASWARE       LOGON                              1017          2

                          UMOGATHA                LOGON                              1017          1

                          DFELSKE                    LOGON                              1017          1

                          ODBC_SF_COM_ERKELENZ           LOGON           005          1

                          CDOERR                     LOGON                              1017          1

                          SYSTEM                     LOGON                              1017          1

                          ODBC_LEENDERS      LOGON                              1017         1

                          UHAARI16                   LOGON                              1017          1

                          SUPER_WS                LOGON                              1017          1

                          CHRING08                   LOGON                              1017          1

                          ODBC_STEENPUT       LOGON                              1017         1

                          TBURUC16                  LOGON                              1017          1

                          PADAVI08                   LOGON                              1017          1

                          SHABIG08                   LOGON                              1017          1

                          AELSCH08                  LOGON                              1017          1

                          PKOELI80                   LOGON                              1017          1

                          JKOEVE16                  LOGON                              1017          1

                          KDIETM16                   LOGON                              1017          1

                          OVOGT16                    LOGON                              1017          1

                          KBAIER16                   LOGON                              1017          1

                          RBRAND80                  LOGON                              1017          1

                          SKLEIN16                    LOGON                              1017          1

                          TBENDE08                   LOGON                              1017          1

                          ODBC_FRVRANKEN    LOGON                              1017          1

                          SNOERE16                  LOGON                              1017          1

                          MBELGE09                  LOGON                              1017          1

                          STEENDLI                    LOGON                              1017          1

                          MLEISS16                    LOGON                              1017          1

                          MKLAVE80                  LOGON                              1017          1

                          DJANSE08                   LOGON                              1017          2

                          JHECKM12                  LOGON                              1017          1

                          AMIN_AMANDALE       LOGON                              1017          1

                          FMANDERS                LOGON                              1017          1

                          ODBC_SF_COM_ERKELENZ           LOGON          1017          1

                          KEVENEPO                LOGON                              1017          1

                          SCHLE109                  LOGON                              1017          1

                          HOFFER                     LOGON                              1017          2

                          WENGEL16                LOGON                              1017          1

                          DFERNAND                LOGON                              1017          1

                          BCRNJE16                  LOGON                              1017          1

                          PVDVEE80                 LOGON                              1017          1

                          BSCHLU12                 LOGON                              1017          1

                          RWANDE08                LOGON                              1017          1

                          MHEYNDRI                 LOGON                              1017          1

                          THBAUE16                  LOGON                              1017          1

                          BJESSE16                  LOGON                              1017          1

                          IKOSCH16                  LOGON                              1017          1

                          VFRUTH08                  LOGON                              1017          1

                          MBELGE16                 LOGON                              1017          1

                          STEENBLI                   LOGON                              1017          1

                          STEENALI                   LOGON                              1017          1

                          LVIS80                        LOGON                              1017          1

                          OBIWAN3                   LOGON                              1017          2

                          SIVATURI                   LOGON                              1017          1

                          BFASSB12                 LOGON                              1017          1

                          ADMIN                        DELETE                                0            1

                          AKOEPP08                 LOGON                              1017          1

                          CTASKIN                    LOGON                              1017          1

                          SGRIGA08                  LOGON                              1017          1

                          SAHORST                   LOGON                              1017          1

                          PINDE                         LOGON                              1017          2

                          TMETZ08                     LOGON                              1017          1

                          MDUMRA12                 LOGON                              1017          1

                          AGHAFO12                  LOGON                              1017          1

                          WANDER12                 LOGON                              1017          1

                          RWANDE13                 LOGON                              1017          1

                          RUPPER93                  LOGON                              1017          1

                          VLENZ08                     LOGON                              1017          1

                          LEENDDLI                   LOGON                              1017          1

                          STEENGES                 LOGON                              1017          1

                          CBILAN12                    LOGON                              1017          1

                          TESTSEL                     LOGON                              1017          1

                          PSTANC16                   LOGON                              1017          1

                          DKUEHN                      LOGON                              1017          1

                          PSOUJANY                  LOGON                              1017          2

                          ADMIN                         LOGON                              1017          1

                          EDI                              LOGON                              1017          1

                          JPORRAS                    LOGON                              1017          1

                          DKEMME08                  LOGON                              1017          1

                          DBRETS16                    LOGON                              1017          1

                          OBAMA                         LOGON                              1017          1

                          NKALYANK                   LOGON                              1017          1

                          SSAHADEV                  LOGON                              1017          1

                          IBAKIR                          LOGON                              1017          1

                          SAWEBE08                   LOGON                              1017          1

                          MDUMRA90                  LOGON                              1017          1

                          WKOOP16                    LOGON                              1017          1

                          LEENDBLI                     LOGON                              1017          1

                          LEENDGDL                   LOGON                              1017          1

                          STEENGDL                   LOGON                              1017          1

                          ODBC_USERMGT          LOGON                              1017          1

                          • 10. Re: All users DB login information
                            mtefft

                            Notice in your audit options (which you posted above) you are auditing CREATE SESSION on FAILURE.

                             

                            Notice the returncoode on all of those audited logins was not zero (meaning successful) but was 1017 or 005 which indicates an unsuccessful login. (ORA-1017 is: invalid username/password; logon denied; I don't know what the 005 is.).

                            • 11. Re: All users DB login information
                              07499ddc-970a-4f3a-8258-49dd82548c5b

                              Thanks for update. Can you please let me know how can I enable logon audit for all users.

                              • 12. Re: All users DB login information
                                EdStevens

                                07499ddc-970a-4f3a-8258-49dd82548c5b wrote:

                                 

                                Thanks for update. Can you please let me know how can I enable logon audit for all users.

                                AUDIT