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.

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;

Basic Database Security Example

The system db is omnisci.

The superuser is omnisci.

There are two users: Michael and Nagesh.

There are two Databases: db1 and db2.

Only user Michael can see db1.

Only user Nagesh can see db2.

admin@hal:~$ bin/omnisql omnisci -u omnisci -p HyperInteractive
omnisci> create user Michael (password = 'Michael');
omnisci> create user Nagesh (password = 'Nagesh');
omnisci> create database db1 (owner = 'Michael');
omnisci> create database db2 (owner = 'Nagesh');
omnisci> \q
User omnisci disconnected from database omnisci
admin@hal:~$ bin/omnisql db1 -u Nagesh -p Nagesh
User Nagesh is not authorized to access database db1
omnisci> \q
admin@hal:~$ bin/omnisql db2 -u Nagesh -p Nagesh
User Nagesh connected to database db2
omnisci> \q
User Nagesh disconnected from database db2
admin@hal:~$ bin/omnisql db1 -u Michael -p Michael
User Michael connected to database db1
omnisci> \q
User Michael disconnected from database db1
admin@hal:~$ bin/omnisql db2 -u Michael -p Michael
User Michael is not authorized to access database db2
omnisci>