Vertica Profile Management
In Vertica a profile enforces password practices – how user passwords are created, reused, and validated.
Benefits of using Profiles in Vertica
Maintain database security by using password management features.
To see the existing profiles on your database use the query bellow:
dbadmin= select * from profiles;
-[ RECORD 1 ]------------------+------------------
profile_id | 45035996273720540
profile_name | default
password_life_time | unlimited
password_grace_time | unlimited
password_reuse_max | unlimited
password_reuse_time | unlimited
failed_login_attempts | unlimited
password_lock_time | unlimited
password_max_length | unlimited
password_min_length | unlimited
password_min_letters | unlimited
password_min_lowercase_letters | unlimited
password_min_uppercase_letters | unlimited
password_min_digits | unlimited
password_min_symbols | unlimited
- by default a profile is created called default profile. When a new user is created and no profile is given to him by default the default profile is associated to the new user account.
How to create a database profile in Vertica
Base Syntax:CREATE PROFILE <profile name LIMIT <parameter UNLIMITED|VALUE;- only a superuser can create a profile.
What are the option parameters that we can use when creating a profile
password_life_time - how many days the password will be valid. password_grace_time - numbers of days after the password expires and the users are forced to change their password. password_reuse_max - how many times you need to change the password before reusing an old password. password_reuse_time - how many days must pass before you can reuse an old password. failed_login_attempts - number is consecutive failed login attempts, when number is reached the account will be locked. password_lock_time - how many days the account stays locked after the failed login attempts is reached. password_max_length- max number of characters a password can have. password_min_length - min number of characters a password can have. password_min_letters - min number of letters a password should contain. password_min_lowercase_letters - min number of Lowercase letters a password should contain. password_min_uppercase_letters - min number of Uppercase letters a password should contain. password_min_digits - min number of digits a password should contain. password_min_symbols - min number of symbols a password should contain.Examples of profiles we can use in Vertica
1 - Create a profile that allows only one failed login attempt and after this the account get's locked.dbadmin= CREATE PROFILE log_attempt_1 LIMIT failed_login_attempts 1;
CREATE PROFILE
-- see new profile definition
dbadmin= select * from profiles where profile_name='log_attempt_1';
-[ RECORD 1 ]------------------+------------------
profile_id | 45035996273792650
profile_name | log_attempt_1
password_life_time | default
password_grace_time | default
password_reuse_max | default
password_reuse_time | default
failed_login_attempts | 1
password_lock_time | default
password_max_length | default
password_min_length | default
password_min_letters | default
password_min_lowercase_letters | default
password_min_uppercase_letters | default
password_min_digits | default
password_min_symbols | defaultdbadmin= CREATE USER test identified by '123' PROFILE log_attempt_1;
CREATE USER
dbadmin= select * from users where user_name='test';
-[ RECORD 1 ]-----+--------------------------------------------------
user_id | 45035996273792742
user_name | test
is_super_user | f
profile_name | log_attempt_1
is_locked | f
lock_time |
resource_pool | general
memory_cap_kb | unlimited
temp_space_cap_kb | unlimited
run_time_cap | unlimited
all_roles |
default_roles |
search_path | "$user", public, v_catalog, v_monitor, v_internaldbadmin= c - test/1234
FATAL 3781: Invalid username or password
Previous connection kept
dbadmin= c - test
Password:
FATAL 4975: The user account "test" is locked due to too many invalid logins
HINT: Please contact the database administrator
Previous connection kept
-- verify user account settings
dbadmin= select user_name , profile_name , is_locked from users where user_name='test';
-[ RECORD 1 ]+--------------
user_name | test
profile_name | log_attempt_1
is_locked | t- we can see that profile definition enforced the account to get locked after a failed user login.
dbadmin= CREATE PROFILE pass_def LIMIT
dbadmin- password_min_length 10
dbadmin- password_min_uppercase_letters 2;
CREATE PROFILE
--see profile definition
dbadmin= select * from profiles where profile_name='pass_def';
-[ RECORD 1 ]------------------+------------------
profile_id | 45035996273793776
profile_name | pass_def
password_life_time | default
password_grace_time | default
password_reuse_max | default
password_reuse_time | default
failed_login_attempts | default
password_lock_time | default
password_max_length | default
password_min_length | 10
password_min_letters | default
password_min_lowercase_letters | default
password_min_uppercase_letters | 2
password_min_digits | default
password_min_symbols | default
-- alter the user test profile to pass_def profile
dbadmin= Alter USER test PROFILE pass_def;
ALTER USER
dbadmin= select * from users where user_name='test';
-[ RECORD 1 ]-----+--------------------------------------------------
user_id | 45035996273792742
user_name | test
is_super_user | f
profile_name | pass_def
is_locked | t
lock_time | 2015-04-24 11:57:16.35325-04
resource_pool | general
memory_cap_kb | unlimited
temp_space_cap_kb | unlimited
run_time_cap | unlimited
all_roles |
default_roles |
search_path | "$user", public, v_catalog, v_monitor, v_internal
-- try to alter user test password
dbadmin= alter user test identified by '12345A';
ROLLBACK 4923: That password is not acceptable
DETAIL: The password is too short
HINT: Minimum password length is 10 characters
-- try again
dbadmin= alter user test identified by '12345Aabcd';
ROLLBACK 4923: That password is not acceptable
DETAIL: The password must have at least 2 uppercase letters
-- try now using the hints it gives you
dbadmin= alter user test identified by '12345AabcD';
ALTER USER