Create users and grant roles and privileges using these 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

Connect

  • SSH to Cirro as root.

sudo su

Create User


CREATE USER 'userName' IDENTIFIED BY 'password';


CREATE USER username;

VALUES GENERATE_PASSWORD;


CREATE USER username IDENTIFIED BY 'password' EMAIL 'username@cirro.com' AUTHENTICATED BY method OPTIONS (SECRET 'HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ')


CREATE USER targetuser LIKE sourceuser EMAIL 'targetuser@host.com' IDENTIFIED BY 'password'


CREATE USER myusername IDENTIFIED BY 'supersecretpassword' 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 for the new user, enclose in single quotes. userName is a case-sensitive VARCHAR(128) which complies with SQL username requirements and therefore does not include spaces or special characters.

  • LIKE: Allows duplication of ‘sourceuser’ roles and privileges in ‘targetuser’ account. Requires COPY USER privilege. If used, this must precede any other commands.

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

  • VALUES GENERATE_PASSWORD: Generates a random character password. See GENERATE_PASSWORD

  • EMAIL: the email address for the user account. Cirro sends a message to this user containing any of password, one time password link to QR code for Authenticator app setup, or two factor authentication link to Duo or Saaspass.

  • AUTHENTICATED BY method: Sets authentication method. Default is cirrootp (Cirro One-Time Password). Requires email address so user can receive account details.

  • OPTIONS (SECRET ‘HXDMVJECJJWSRB3HWIZR4IFUGFTMXBOZ’): A 32 character 160 bit number formatted using base 32. This is generated outside Cirro by the secure application of your choice.

  • WITHOUT options: exclude three options from any LIKE clause: DATABASE CREDENTIALS, ROLES or PRIVILEGES. WITHOUT must immediately follow LIKE argument.

  • VALID BETWEEN: start and end date and time the user account will function. Uses AND to separate start and end date. Requires TIME ZONE. If time not specified, it defaults to midnight (00:00:00)

  • VALID FROM: The start date and time the user account will begin to function in form YYYY-MM-DD HH:MM:SS. Requires TIME ZONE. If time not specified, it defaults to midnight (00:00:00)

  • VALID UNTIL: The end date and time the user account will stop functioning in form YYYY-MM-DD HH:MM:SS. Requires TIME ZONE. If time not specified, it defaults to midnight (00:00:00)

  • TIME ZONE - the time zone your user operates in. See Valid Time Zones. Used with VALID (FROM BETWEEN UNTIL).

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 system generated password


CREATE USER cirrouser;

VALUES GENERATE_PASSWORD;

This returns a value, for example:

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


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

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’


DROP USER wecoyote;

See Also