Monitoring Login Failures

Posted March 12, 2018 by Soniya Shah, Information Developer

Designers discussing project in office sample library
This blog post was authored by Curtis Bennett. Security is an ever-present topic on the minds of database administrators. Certainly Vertica has a number of security features like Kerberos, and SSL. This blog isn’t about those! Instead, let’s take a look at a much more simplistic aspect of security which Vertica tracks – login failures. LOGIN_FAILURES is a Vertica system table that tracks – you guessed it, login failures. Every time someone tries to sign into Vertica and fails, an entry is recorded here. The entry also tracks the various reasons why a failure might occur. Usually this could be the result of a typo in a password, but it could also be that the user’s access has been revoked. Most production systems I’ve seen have a number of login failures – and most of these are benign issues. But it’s possible that some login failures could be malicious in nature – perhaps a disgruntled employee looking to cause some damage, or get access to records they shouldn’t see. Another scenario that can happen is a script that runs, yet fails because a password has changed, and the script hasn’t been updated with that information. Those situations can cause far-reaching issues that might take a while to observe otherwise. Here are two SQL statements that can track two different kinds of login failure scenarios. CREATE TUNING RULE user_r9 (LOGIN_FAILURES, RECURRING_FAILURES, days_to_check=14, attempts=3) AS SELECT NULL AS time ,'User ''' || user_name || ''' had ' || num_failures || ' failed signon attempts at ' || hour_minute || ' between ' || begin_t || ' and ' || end_t || '.' AS observation_description , NULL AS table_id , NULL AS transaction_id , NULL AS statement_id , 'SELECT * FROM v_monitor.login_failures WHERE user_name = ''' || user_name || ''';' AS tuning_parameter , 'Recurring login failures are a likely indication of a problem in an automated script or process.' AS tuning_description , 'Investigate the source of the login failures and correct as necessary.' AS tuning_command , 'MEDIUM' AS tuning_cost FROM (SELECT user_name , TRIM(TO_CHAR(HOUR(login_Timestamp), '00')) || ':' || TRIM(TO_CHAR(MINUTE(login_timestamp), '00')) AS hour_minute , MIN(DATE(login_timestamp)) OVER (PARTITION BY user_name, HOUR(login_timestamp), MINUTE(login_timestamp)) AS begin_t , MAX(DATE(login_timestamp)) OVER (PARTITION BY user_name, HOUR(login_timestamp), MINUTE(login_timestamp)) AS end_t , COUNT(user_name) OVER (PARTITION BY user_name, HOUR(login_timestamp), MINUTE(login_timestamp)) AS num_failures FROM v_monitor.login_failures WHERE login_timestamp >= CURRENT_TIMESTAMP - (SELECT current_value FROM v_internal.vs_tuning_rule_parameters WHERE tuning_rule = 'user_r9' AND parameter = 'days_to_check')::INTERVAL DAY) AS recurring_failures WHERE end_t - begin_t >= (SELECT current_value FROM v_internal.vs_tuning_rule_parameters WHERE tuning_rule = 'user_r9' AND parameter = 'attempts')::INTEGER ; –Finds repeated login attempts. –A repeated login attempt had more than 10 signon attempts per day within the last 14 days. CREATE TUNING RULE user_r10 (LOGIN_FAILURES, REPEAT_ATTEMPTS, days_to_check=14, attempts=10) AS SELECT NULL AS time ,’User ”’ || user_name || ”’ had ‘ || num_failures || ‘ failed signon attempts’ || ‘ ON ‘ || login_date || ‘.’ AS observation_description , NULL AS table_id , NULL AS transaction_id , NULL AS statement_id , ‘SELECT * FROM v_monitor.login_failures WHERE user_name = ”’ || user_name || ”’;’ AS tuning_parameter , ‘Review excessive sign-in failures that occur in a short timeframe. Failure Reason: ‘ || reason AS tuning_description , ‘Investigate the source of the login failures and correct as necessary.’ AS tuning_command , ‘MEDIUM’ AS tuning_cost FROM (SELECT user_name, reason, DATE(login_timestamp) AS login_date, COUNT(user_name) AS num_failures FROM v_monitor.login_failures WHERE login_timestamp >= CURRENT_TIMESTAMP – (SELECT current_value FROM v_internal.vs_tuning_rule_parameters WHERE tuning_rule = ‘user_r10’ AND parameter = ‘days_to_check’)::INTERVAL DAY GROUP BY user_name, reason, DATE(login_timestamp) HAVING COUNT(user_name) >= (SELECT current_value FROM v_internal.vs_tuning_rule_parameters WHERE tuning_rule = ‘user_r10’ AND parameter = ‘attempts’)::INTEGER) lf ; The first is possible malicious attacks – numerous attempts at signing in within a relatively short amount of time. The second is probably more benign, but resolving them could still be important – an automated script that fails at roughly the same time every day. These aren’t normal SQL however. These have been written up as Workload Analyzer rules. The Workload Analyzer (WLA) runs once a day, running various “rules” in the database to find and report on common problems. These could be things like missing statistics, or issues with poorly tuned long-running queries. Workload Analyzer rules get reported in the Management Console, and the great thing about the WLA is that you can easily add your own rules, which is what is presented below. Just run these as-is. They will add new rules into your WLA, and any occurrences will get reported into Management Console. Alternatively, you can take away the “CREATE” line at the top and just run the remaining SQL. You will have to create the actual rule first, since the SQL references the vs_tuning_rule_parameters table – and that entry won’t exist unless you create the rule. Lastly, if you want to alter the rules, you can do so easily with the ALTER TUNING RULE command. For example, if I wanted to adjust rule user_r9 to check only 7 days’ worth of logins, I could run: ALTER TUNING RULE user_r9 set days_to_check=7 ; These are rules numbers 9 and 10 from my personal library of tuning rules (hence the names). I’ll share the remaining rules in different blogs!