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

Syntax

ALTER USER 'username'
  [NAME 'fullname']
  [EMAIL 'user@domain.com']
  [LIKE 'sourceuser' [WITHOUT (DATABASE CREDENTIALS | , ROLES | , PRIVILEGES) ]]
  [IDENTIFIED BY {'password' | NONE }]
  [ authenticated_by_clause ]
  [VALID
    (FROM | BETWEEN | UNTIL ) 'YYYY-MM-DD HH:MM:SS'
    ( AND 'YYYY-MM-DD HH:MM:SS')
    WITH TIME ZONE 'timezone'
  ]
  [LOCK | UNLOCK]

where authenticated_by_clause can be one of:

cirrototp OPTIONS (SECRET 'key')

yubikey OPTIONS (USER_ID 'PublicId', SECRET 'SecretKey')

duoname [OPTIONS (USERNAME 'duo_username')]

oktaname [OPTIONS (USERNAME 'Okta_username')]

pingidname [OPTIONS (USERNAME 'Okta_username')]

saaspass_otpname [OPTIONS (USERNAME 'Okta_username')]

saaspass_pushname [OPTIONS (USERNAME 'Okta_username')]

unloqname [OPTIONS (USERNAME 'Okta_username')]

Arguments

  • username - The name of the user account.

  • fullname - An additional description for the user such as their full name.

  • EMAIL - the email address for the user account.

  • LIKE ‘sourceuser’ - allows duplication of ‘sourceuser’ datasource credentials, roles and privileges from ‘targetuser’ account.

  • WITHOUT - exclude datasource credentials, roles and privileges from ‘sourceuser’ account.

  • IDENTIFIED BY

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

    • NONE - revokes any existing password on the specified user account.

  • 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. Use 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.

AUTHENTICATED BY arguments

AUTHENTICATED BY arguments are used to enable different Multifactor Authentication providers on the account.

There are two built-in providers:

PROVIDER TYPE OPTIONS Description Mandatory SECRET
cirrototp SECRET TOTP Secret key true true
yubikey USER_ID Private ID false false
  SECRET Secret key true true

The other providers are added using ADD AUTHENTICATION PROVIDER.

PROVIDER TYPE OPTIONS Description Mandatory SECRET
duo duoname name of duo provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME DUO username (if different to Cirro username) false false
okta oktaname name of okta provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME Okta username (if different to Cirro username). false false
pingid pingidname name of pingid provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME PingID username (if different to Cirro username). false false
saaspass_otp saaspass_otpname Name of saaspass_otp provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME saaspass username (if different to Cirro username). false false
saaspass_push saaspass_pushname Name of saaspass_push provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME saaspass username (if different to Cirro username). false false
unloq unloqname Name of unloq provider set in ADD AUTHENTICATION PROVIDER. true false
  USERNAME unloq username (if different to Cirro username). false false

Additional Information

  • username

    • usernames are case-insensitive like email addresses and presented to the user in lower case. They comply with SQL username requirements.

    • No spaces or special characters are permitted other than @ and .(period) if setting username as an email address (such as for Multifactor Authentication accounts).

  • LIKE

    • LIKE must immediately follow CREATE ‘username’.

    • Follow with the WITHOUT clause to exclude database credentials, roles or privileges.

    • Requires COPY USER privilege on the account executing the SQL.

  • IDENTIFIED BY

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

    • NONE - Required if using AUTHENTICATED BY without an IDENTIFIED BY password.

  • EMAIL - Account alteration emails are sent to this address if alterations are made via the user interface.

  • AUTHENTICATED BY - include IDENTIFIED BY NONE if not including a password.

  • 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.

  • LOCK/UNLOCK - No further arguments are permitted after this argument.

  • UNLOCK - Not required for new user accounts as they are unlocked by default.

Examples

Alter email address and lock the user.

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

Alter user to duplicate existing but exclude privileges and set a new password.

ALTER USER 'cirrouser_copy'
  LIKE 'cirrouser'
    WITHOUT privileges
  IDENTIFIED BY 'mysecretpassword';

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');

Add Yubikey as a cirro-managed one-time password to a user account.

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

See also