How to disallow users from connecting to DB temporarily

[debfawn]

Hello - What is the best way to disallow users from connecting to a Vertica database temporarily while we refresh tables in the DB? We want to not have folks connect for about a 5 hour period. We're sending out a global email, but people will probably still try to log in during the outage.

Thanks!

[JimKnicely]

Hi,

Refer to the "Single User Mode" topic here:

http://www.vertica-forums.com/viewtopic.php?f=5&t=146

You can also lock all non-admin user accounts, do your work, and then unlock all the users...

First, generate a script that will re-lock and currently locked users users:

Code: Select all

SELECT 'ALTER USER ' || user_name || ' ACCOUNT UNLOCK;' FROM users WHERE is_locked = 't';

Save the script until later.

Next, you can generate a script to lock all non admin users using the query:

Code: Select all

SELECT 'ALTER USER ' || user_name || ' ACCOUNT LOCK;' FROM users WHERE is_super_user = 'f';

After you perform your DB work, you can generate a script to unlock all non admin users using the query:

Code: Select all

SELECT 'ALTER USER ' || user_name || ' ACCOUNT UNLOCK;' FROM users WHERE is_super_user = 'f';

Finally, if you had any users that were locked prior to locking/unlocking all users, you'll need to re-lock them using the script you generated in the first step