How to Keep a History of Database Logins

[usli06]

Hello!

Does anyone know of a nice easy way that I can keep a history of database user logins? I know that we have the sessions table but it's transient. Is it recommended that I copy data from this table to a new sessions_hist table, for example? I'm used to having an "on logon" trigger in Oracle.

Note, I'd rather not have to parse the Vertica log file to get this info. either

[id10t]

Hi!

Vertica has no triggers so to implement sessions_history will not be easy.
May be table v_monitor.USER_SESSIONS be useful?

Code: Select all

select * from user_sessions;

OUTPUT(example)

Code: Select all

-[ RECORD 1 ]-----------+------------------------------
node_name               | v_test_db_node0001
user_name               | dbadmin
session_id              | new-host-2-4922:0x3822
session_start_timestamp | 2012-08-01 17:07:41.412037+03
session_end_timestamp   | 
is_active               | t
client_hostname         | 127.0.0.1:35903
client_pid              | 29862
client_label            | 
ssl_state               | 
authentication_method   | 
-[ RECORD 2 ]-----------+------------------------------
node_name               | v_test_db_node0001
user_name               | dbadmin
session_id              | new-host-2-4922:0xf
session_start_timestamp | 2012-08-01 09:41:22.210834+03
session_end_timestamp   | 2012-08-01 11:51:59.825419+03
is_active               | f
client_hostname         | 127.0.0.1:34965
client_pid              | 5070
client_label            | 
ssl_state               | 
authentication_method   | 
...

[usli06]

Thanks for your reply, skwa.

We're trying to capture the data in user_sessions table but we miss connections that are are very short. They show up in the Vertica log though. I suppose we're going to have to figure out how to report off of it. This is all about a HIPPA compliance issue. Nasty stuff...

[jpcavanaugh]

The data collector should be gathering this information. What exactly do you need to have for compliance and I will double check?

[usli06]

Hi foo,

We need the user name, access date and the query executed.

Thanks!

[alamb]

Look at dc_session_starts and dc_session_ends

For example:

Code: Select all

alamb=> select * from dc_session_starts limit 1;
             time              | node_name |  session_id   |      user_id      | user_name | client_hostname | client_pid | client_label | client_version | ssl_state | authentication_method | is_internal | session_type 
-------------------------------+-----------+---------------+-------------------+-----------+-----------------+------------+--------------+----------------+-----------+-----------------------+-------------+--------------
 2012-08-06 15:46:02.378568-04 | node01    | tldr-6715:0x2 | 45035996273704962 | alamb     |                 |          0 | Startup      |                |           |                       | t           | STARTUP
(1 row)

alamb=>