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>