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.
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.
If a password includes characters that are nonalphanumeric, it must be enclosed in single quotes when logging in to heavysql. For example:
$HEAVYAI_PATH/bin/heavysql heavyai -u admin -p '77Heavy!9Ai'
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.
Property
Value
password
User's password.
is_super
Set to true if user is a superuser. Default is false.
default_db
User's default database on login.
can_login
Set 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 HEAVY.AI. Deactivated users who try to log in receive the error message "Unauthorized Access: User is deactivated."
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.
Property
Value
password
User's password.
is_super
Set to true if user is a superuser. Default is false.
default_db
User's default database on login.
can_login
Set 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 HEAVY.AI. Deactivated users who try to log in receive the error message "Unauthorized Access: User is deactivated."
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.
Property
Value
owner
User name of the database owner.
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;
Only superusers can run the ALTER DATABASE OWNER TO command.
REASSIGN OWNED
REASSIGN 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 in the current database to a different user.
Example: Reassign database objects owned by jason and mike to joe.
REASSIGN OWNED BY jason, mike TO joe;
Database object ownership changes only for the currently connected database; objects in other databases are not affected. 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.

Datatypes

Tables

Last modified 2mo ago

Export as PDF

Copy link

On this page

Nomenclature Constraints

CREATE USER

DROP USER

ALTER USER

CREATE DATABASE

DROP DATABASE

ALTER DATABASE

ALTER DATABASE OWNER TO

REASSIGN OWNED

Database Security Example