Syntax Examples for USER

  1. CREATE USER

Example 1. Basic create user

USE ROLE USERADMIN;

CREATE USER sophie PASSWORD='SnowDBA123';
User SOPHIE successfully created.

Example 2. Create user with a default role and force a password change

USE ROLE USERADMIN;


CREATE USER paul PASSWORD='SnowDBA123' DEFAULT_ROLE = account_admin MUST_CHANGE_PASSWORD=true;
User PAUL successfully created.

Example 3. Create user with default DB.SCHEMA namespace and specify their TIMEZONE

USE ROLE USERADMIN;


CREATE USER sammy PASSWORD='SnowDBA123' DEFAULT_NAMESPACE=DEMO_DB.PUBLIC TIMEZONE='Europe/London';
User SAMMY successfully created.

2. ALTER USER

Example 4. Reset a users password and requirement to change on first login

USE ROLE USERADMIN;


ALTER USER sammy SET PASSWORD='OLAFisReal2021!' MUST_CHANGE_PASSWORD=true;
Statement executed successfully.

Example 5. Rename a user

USE ROLE USERADMIN;

ALTER USER SAMMY RENAME TO MICHAEL;
Statement executed successfully.

Example 6. Abort a users queries

USE ROLE USERADMIN;


ALTER USER sophie ABORT ALL QUERIES;
Statement executed successfully.

Example 7. Disable a user (lock them out)

USE ROLE USERADMIN;


ALTER USER michael SET DISABLED = true;
Statement executed successfully.

3. DROP USER

Example 8. Drop a user

USE ROLE USERADMIN;

-- Does not seem to be an UNDROP USER
DROP USER michael;
MICHAEL successfully dropped.

4. DESCRIBE USER

Example 9. Describe a user

USE ROLE USERADMIN;


DESCRIBE USER PAUL;

5. SHOW USERS

Example 10. Show users

USE ROLE SECURITYADMIN;

SHOW USERS;

-- Also Pattern match
SHOW USERS LIKE '%SOPHIE%';