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
How to Keep a History of Database Logins
Moderator: NorbertKrupa
Re: How to Keep a History of Database Logins
Hi!
Vertica has no triggers so to implement sessions_history will not be easy.
May be table v_monitor.USER_SESSIONS be useful?
OUTPUT(example)
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;
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 |
...
Re: How to Keep a History of Database Logins
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...
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...
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: How to Keep a History of Database Logins
The data collector should be gathering this information. What exactly do you need to have for compliance and I will double check?
Re: How to Keep a History of Database Logins
Hi foo,
We need the user name, access date and the query executed.
Thanks!
We need the user name, access date and the query executed.
Thanks!
Re: How to Keep a History of Database Logins
Look at dc_session_starts and dc_session_ends
For example:
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=>