Users and Databases

HEAVY.AI has a default superuser named admin with default password HyperInteractive.

When you create or alter a user, you can grant superuser 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 heavyai 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 HEAVY.AI 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,...);

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

Examples:

CREATE USER jason (password = 'HeavyaiRocks!', is_super = 'true', default_db='tweets');
CREATE USER "pembroke.q.aloysius" (password= 'HeavyaiRolls!', default_db='heavyai');

DROP USER

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

Example:

DROP USER [IF EXISTS] jason;
DROP USER "pemboke.q.aloysius";

ALTER USER

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

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

Example:

ALTER USER admin (password = 'HeavyaiIsFast!');
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.

Example:

CREATE DATABASE test (owner = 'jason');

DROP DATABASE

DROP DATABASE [IF EXISTS] ;

Example:

DROP DATABASE IF EXISTS test;

ALTER DATABASE

ALTER DATABASE <name> RENAME TO <name>;

To alter a database, you must be the owner of the database or an HeavyDB superuser.

Example:

ALTER DATABASE curmudgeonlyOldDatabase RENAME TO ingenuousNewDatabase;

ALTER DATABASE OWNER TO

Enable super users to change the owner of a database.

ALTER DATABASE <database name> OWNER TO <new_owner>;

Example

Change the owner of my_database to user Joe:

ALTER DATABASE my_database OWNER TO Joe;

REASSIGN OWNED

REASSIGN [ALL] OWNED BY <old_owner>, <old_owner>, ... TO <new_owner>

Changes ownership of database objects (tables, views, dashboards, etc.) from a user or set of users to a different user. When the ALL keyword is specified, ownership change would apply to database objects across all databases. Otherwise, ownership change only applies to database objects in the current database.

Example: Reassign database objects owned by jason and mike in the current database to joe.

REASSIGN OWNED BY jason, mike TO joe;

Example: Reassign database objects owned by jason and mike across all databases to joe.

REASSIGN ALL OWNED BY jason, mike TO joe;

Database object ownership changes only for objects within the database; ownership of the database itself is not affected. You must be a superuser to run this command.

Database Security Example

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