Create a Cirro user account that can connect to the application. All Cirro users have the PUBLIC role by default which enables login. Once created, GRANT roles and privileges.

Syntax

CREATE USER 'username' EMAIL 'user@domain.com'
  [LIKE 'sourceuser']
  [IDENTIFIED BY {'password' | NONE }]
  [AUTHENTICATED BY method OPTIONS(name value[,...])]
  [WITHOUT (DATABASE CREDENTIALS | ROLES | PRIVILEGES) ]
  [VALID
    (FROM | BETWEEN | Until ) 'YYYY-MM-DD HH:MM:SS'
    ( AND 'YYYY-MM-DD HH:MM:SS')
    WITH TIME ZONE 'timezone'
  ]
  [LOCK | UNLOCK]

Arguments

  • username - The name of the user account.

  • EMAIL - the email address for the user account.

  • LIKE ‘sourceuser’ - allows duplication of ‘sourceuser’ roles and privileges in ‘targetuser’ account.

  • IDENTIFIED BY

    • ‘password’: Sets ‘password’ to access the account. Always enclose the password in single quotes.

    • NONE - revokes any existing password on the specified user account. Required if using AUTHENTICATED BY without an IDENTIFIED BY password.

  • AUTHENTICATED BY - cirrototp or yubikey.

  • WITHOUT - exclude source user DATABASE CREDENTIALS, ROLES and/or PRIVILEGES

  • VALID FROM - The start date and time the user account will begin to function.

  • VALID BETWEEN - start and end date and time the user account will function. Uses AND to separate start and end date.

  • VALID UNTIL: The end date and time the user account will stop functioning.

  • TIME ZONE - the time zone your user operates in.

  • LOCK | UNLOCK - lock or unlock the account to allow or prevent login. Use of these arguments concludes the syntax. No further arguments are permitted.

AUTHENTICATED BY cirrototp

cirrototp OPTIONS SECRET 'key'
  • SECRET ‘key’ - Supply a 32 character 160 bit number, formatted using base 32.

AUTHENTICATED BY Yubikey

AUTHENTICATED BY yubikey OPTIONS USER ID 'value', SECRET 'secretkeyvalue'
  • USER_ID ‘value’ - the Yubikey PRIVATE ID.

  • SECRET ‘secretkeyvalue’ - the Yubikey SECRET KEY.

Additional Information

  • username - Cirro usernames are case-sensitive VARCHAR(128) comply with SQL username requirements. No spaces or special characters are permitted.

  • UNLOCK - only required if altering a user. Cirro user accounts are unlocked by default.

  • LIKE

    • LIKE must immediately follow CREATE ‘username’.

    • Follow LIKE with the WITHOUT clause if you want to exclude database credentials, roles or privileges.

    • The COPY USER privilege is required for the user executing the SQL.

  • IDENTIFIED BY ‘password’ - Password requirements may differ based on the user security policy set on your installation.

  • AUTHENTICATED BY

    • Use account can use both password and authentication.

    • If using without password, requires IDENTIFIED BY NONE.

    • For authenticator app setup, use domainname:username (e.g., cirroserver.com:username) as the Account Name.

  • AUTHENTICATED BY Yubikey

    • If authenticating only with Yubikey, touch the device when prompted for your password

    • If authenticating with both a password and Yubikey, enter your password (don’t type ENTER/RETURN) then touch your Yubikey to authenticate.

  • VALID

    • all values use YYYY-MM-DD HH:MM:SS date format.

    • If time not specified, it defaults to midnight (00:00:00).

    • VALID arguments require TIME ZONE. Use worldtimeserver to find your timezone.

Examples

Account with standard password

CREATE USER 'cirrouser' IDENTIFIED BY 'p@ssword';

Account with standard password, email address and cirro one time password

CREATE USER 'cirrouser_pass_totp'
 IDENTIFIED BY 'password'
 EMAIL 'cirrouser@cirro.com'
 AUTHENTICATED BY cirrototp
 OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ')

Account with Cirro one-time password alone

CREATE USER 'cirrouser_totp'
IDENTIFIED BY NONE
EMAIL 'cirrouser@cirro.com'
AUTHENTICATED BY cirrototp
OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ')

Account duplicating another with standard password, email address but no privileges

CREATE USER 'cirrouser_copy'
LIKE cirrouser
WITHOUT PRIVILEGES
EMAIL 'cirrouser_copy@cirro.com'
IDENTIFIED BY 'mypassword';

Account with set expiry

CREATE USER 'user_expiry'
IDENTIFIED BY 'supersecretpassword'
VALID BETWEEN '2018-01-01 00:00:00'
AND '2019-01-01 00:00:00'
WITH TIME ZONE 'Australia/Melbourne';

Set up Yubikey as a cirro-managed one-time password.

CREATE USER cirro_yubikey
AUTHENTICATED BY yubikey
OPTIONS
(USER_ID 'PublicId',
SECRET 'SecretKey')
IDENTIFIED BY 'password';

See Also