Setup your users using SQL commands.

Passwords

Cirro provides four account password combinations.

Password Type Description Value
Standard Standard user password. You can set your user security policy (SQL) with SQL commands. password
One Time Password Time limited One Time Password generated with a system-issued QR code added to an Authenticator application such as Google Authenticator totp
Combined Standard password & one-time password A combination of the above. both
Two Factor Authentication Can be applied to any combination of the above. The system uses either Duo or Saaspass to push a notification to the account holder’s device (usually a smartphone or tablet) Two-Factor Authentication is set up with the ADD AUTHENTICATION SQL commands. Duo (or) Saaspass

Create User

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

Where

  • username - The name of the user account.

  • EMAIL ‘address’: the email address for the user account.

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

  • LIKE ‘sourceuser’ WITHOUT ()

    • Allows duplication of ‘sourceuser’ roles and privileges in ‘targetuser’ account.

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

  • 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 OPTIONS SECRET ‘key’

    • Use the Cirro Timed One-Time Password to authenticate.

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

  • AUTHENTICATED BY yubikey OPTIONS USER ID ‘value’, SECRET ‘secretkeyvalue’

    • Use yubikey as an authentication provider.

    • USER_ID ‘value’ - This is the Yubikey PRIVATE ID.

    • SECRET ‘secretkeyvalue’ - this is the Yubikey SECRET KEY.

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

Drop Users


DROP USER userName;

Where:

  • userName: The name of an existing user.

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 IDENTIFIED BY 'password' EMAIL 'cirrouser@cirro.com' AUTHENTICATED BY cirrototp OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ')

Account with Cirro one-time password alone

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

CREATE USER wecoyote LIKE rrunner EMAIL 'wecoyote@cirro.com' IDENTIFIED BY 'acme' WITHOUT PRIVILEGES

Account with set expiry

CREATE USER myusername 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 cirrouser AUTHENTICATED BY yubikey OPTIONS (
  USER_ID 'PublicId',
  SECRET 'SecretKey'
)
[ IDENTIFIED BY 'password' ]

DROP USER wecoyote;