DDL - Users and Databases

OmniSci ships with a default super user named admin 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 omnisci database is used.

Note When an administrator, superuser, or owner drops or renames a database, all current active sessions for users logged in to that database are invalidated. The users must log in again.

Similarly, when an administrator or superuser drops or renames a user, all active sessions for that user are immediately invalidated.
Note If a password includes characters that are nonalphanumeric, it must be enclosed in single quotes when logging in to omnisql. For example: 
$OMNISCI_PATH/bin/omnisql omnisci -u admin -p '77Omni!9Sci'

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,...);

OmniSci accepts (almost) any string enclosed in optional double quotation marks as the user name.

PropertyValue
passwordUser's password.
is_superSet to true if user is a superuser. Default is false.
default_dbUser's default database on login.
can_loginSet to true (default/implicit) to activate a user.

When false, the user still retains all defined privileges and configuration settings, but cannot log in to OmniSci. Deactivated users who try to log in receive the error message "Unauthorized Access: User <username> is deactivated."

Examples:

CREATE USER jason (password = 'OmniSciRocks!', is_super = 'true', default_db='tweets');
CREATE USER "pembroke.q.aloysius" (password= 'OmniSciRolls!', default_db='omnisci');

DROP USER

DROP USER ["]<name>["];

Example:

DROP USER jason;
DROP USER "pemboke.q.aloysius";

ALTER USER

ALTER USER ["]<name>["] (<property> = value, ...);
ALTER USER ["]<oldUserName>["] RENAME TO ["]<newUserName>["];

OmniSci accepts (almost) any string enclosed in optional double quotation marks as the old or new user name.

PropertyValue
passwordUser's password.
is_superSet to true if user is a superuser. Default is false.
default_dbUser's default database on login.
can_loginSet to true (default/implicit) to activate a user.

When false, the user still retains all defined privileges and configuration settings, but cannot log in to OmniSci. Deactivated users who try to log in receive the error message "Unauthorized Access: User <username> is deactivated."

Example:

ALTER USER admin (password = 'OmniSciIsFast!');
ALTER USER jason (is_super = 'false', password = 'SilkySmooth', default_db='traffic');
ALTER USER methuselah RENAME TO aurora;
ALTER USER "pembroke.q.aloysius" RENAME TO "pembroke.q.murgatroyd";
ALTER USER chumley (can_login='false');

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] <name> (<property> = value, ...);

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

PropertyValue
ownerUser name of the database owner.

Example:

CREATE DATABASE test (owner = 'jason');

DROP DATABASE

DROP DATABASE [IF EXISTS] <name>;

Example:

DROP DATABASE IF EXISTS test;

ALTER DATABASE

ALTER DATABASE <oldName> RENAME TO <newName>;

To alter a table, you must be the owner of the table or an OmniSciDB superuser.

Example:

ALTER DATABASE curmudgeonlyOldDatabase RENAME TO ingenuousNewDatabase;

Database Security Example

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