DDL - Users and Databases

OmniSci ships with a default super user named omnisci with default password HyperInteractive.

When you create or alter a user, you can grant super user privileges by setting the is_super property.

You can also specify a default database when you create or alter a user by using the default_db property. During login, if a database is not specified, the server uses the default database assigned to that user. If no default database is assigned to the user and no database is specified during login, the mapd database is used.

For more information about users, roles, and privileges, see DDL - Roles and Privileges.

Nomenclature Constraints

The following are naming convention requirements for OmniSci objects, described in regex notation:

  • A NAME is [A-Za-z_][A-Za-z0-9\$_]*
  • A DASHEDNAME is [A-Za-z_][A-Za-z0-9\$_\-]*
  • An EMAIL is ([^[:space:]\"]+|\".+\")@[A-Za-z0-9][A-Za-z0-9\-\.]*\.[A-Za-z]+

User objects can use NAME, DASHEDNAME, or EMAIL format.

Role objects must use either NAME or DASHEDNAME format.

Database and column objects must use NAME format.

CREATE USER

CREATE USER <name> (<property> = value,...);

Example:

CREATE USER jason (password = 'OmniSciRocks!', is_super = 'true', default_db='tweets');

DROP USER

DROP USER <name>;

Example:

DROP USER jason;

ALTER USER

ALTER USER <name> (<property> = value, ...);

Example:

ALTER USER omnisci (password = 'OmniSciIsFast!');
ALTER USER jason (is_super = 'false', password = 'SilkySmooth', default_db='traffic');

CREATE DATABASE

CREATE DATABASE <name> (<property> = value, ...);

Database names cannot include quotes, spaces, or special characters.

Example:

CREATE DATABASE test (owner = 'jason');

DROP DATABASE

DROP DATABASE <name>;

Example:

DROP DATABASE test;

Database Security Example

See Example: Data Security in DDL - Roles and Privileges for a database security example.