# 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.

{% hint style="info" %}
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.
{% endhint %}

{% hint style="info" %}
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'`
{% endhint %}

For more information about users, roles, and privileges, see [DDL - Roles and Privileges](https://docs.heavy.ai/v8.3.0/installation-and-configuration/security/roles).

## Nomenclature Constraints

The following are naming convention requirements for HEAVY.AI objects, described in [regex](https://en.wikipedia.org/wiki/Regular_expression) 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`  | <p>Set to <em>true</em> (default/implicit) to activate a user.<br></p><p>When <em>false</em>, 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."</p> |

Examples:

```sql
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:

```sql
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`  | <p>Set to <em>true</em> (default/implicit) to activate a user.</p><p>When <em>false</em>, 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."</p> |

Example:

```sql
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.

{% hint style="info" %}
In Release 6.3.0 and later, database names are case insensitive. Duplicate database names will cause a failure when attempting to start HeavyDB 6.3.0 or higher. Check database names and revise as necessary to avoid duplicate names.
{% endhint %}

| Property | Value                            |
| -------- | -------------------------------- |
| `owner`  | User name of the database owner. |

Example:

```sql
CREATE DATABASE test (owner = 'jason');
```

## DROP DATABASE

```
DROP DATABASE [IF EXISTS] ;
```

Example:

```sql
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:

```sql
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;
```

{% hint style="info" %}
Only superusers can run the ALTER DATABASE OWNER TO command.
{% endhint %}

## 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`.

```sql
REASSIGN OWNED BY jason, mike TO joe;
```

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

```sql
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](https://docs.heavy.ai/v8.3.0/installation-and-configuration/security/roles#data-security-example) in [DDL - Roles and Privileges](https://docs.heavy.ai/v8.3.0/installation-and-configuration/security/roles) for a database security example.