Make alterations to existing Cirro user accounts. All Cirro users have the PUBLIC role by default which enables login.

Syntax

ALTER 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

Alter email address and lock the user.

ALTER USER 'cirrouser' EMAIL 'cirrouser@cirro.com' LOCK;

Set up account duplicating another with standard password, email address but no privileges.

ALTER USER 'cirrouser_copy'
LIKE 'cirrouser'
WITHOUT PRIVILEGES
EMAIL 'cirrouser_copy@cirro.com'
IDENTIFIED BY 'password';

Alter a user and add Cirro Timed one-time password.

ALTER USER 'cirrouser'
AUTHENTICATED BY cirrototp
OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ');

Alter a user, remove standard password and add Cirro Timed one-time password

ALTER USER 'cirrouser'
IDENTIFIED BY NONE
AUTHENTICATED BY cirrototp
OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ');

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

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

See also