DDL - Users and Databases

OmniSci ships with a default super user named omnisci with default password HyperInteractive. You can grant super user privileges when you create or alter a user by setting the is_super property.

CREATE USER <name> (<property> = value,...);

Example:

CREATE USER jason (password = 'OmniSciRocks!', is_super = 'true');

DROP USER <name>;

Example:

DROP USER jason;

ALTER USER

ALTER USER <name> (<property> = value, ...);

CREATE DATABASE

CREATE DATABASE <name> (<property> = value, ...);

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

Example:

CREATE DATABASE test (owner = 'jason');

DROP DATABASE <name>;

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>