Users, schemas & privileges in #Exasol

Exasol Logo

In Exasol, a database user may own multiple schemas – or even none at all. I connect to my Community Edition to show that:

C:\Users\uh>cd \Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus>exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN

EXAplus 6.0.8 (c) EXASOL AG

Wednesday, May 23, 2018 3:28:29 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create user adam identified by adam;
EXA: create user adam identified by adam;

Rows affected: 0

SQL_EXA> grant dba to adam;
EXA: grant dba to adam;

Rows affected: 0

SQL_EXA> select user_name from exa_dba_users;
EXA: select user_name from exa_dba_users;

USER_NAME
------------------------------------------------------------
SYS
ADAM

2 rows in resultset.

SQL_EXA> select schema_owner,schema_name from exa_schemas;
EXA: select schema_owner,schema_name from exa_schemas;

SCHEMA_OWNER
-------------------------------------------------------------
SCHEMA_NAME
-------------------------------------------------------------
SYS
RETAIL

1 row in resultset.

SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:34:42 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);
Error: [42000] no schema specified or opened or current schema has been dropped [line 1, column 27] (Session: 1601269589413551548)
SQL_EXA> open schema adam;
EXA: open schema adam;
Error: [42000] schema ADAM not found [line 1, column 13] (Session: 1601269589413551548)

Demo user adam has the DBA role granted but there is no adam schema yet. I need to create it first:

EXA: create schema adam;

Rows affected: 0

SQL_EXA> open schema adam;
EXA: open schema adam;

Rows affected: 0

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);

Rows affected: 0

SQL_EXA> create schema adam2;
EXA: create schema adam2;

Rows affected: 0

SQL_EXA> create table adam2.t2 (n number);
EXA: create table adam2.t2 (n number);

Rows affected: 0

SQL_EXA> select table_schema,table_name from exa_user_tables;
EXA: select table_schema,table_name from exa_user_tables;

TABLE_SCHEMA
--------------------------------------------------------
TABLE_NAME
--------------------------------------------------------
ADAM
T1
ADAM2
T2

2 rows in resultset.

As you see, user adam has now two schemas with different tables in them. Now briefly to privileges:

SQL_EXA> create user fred identified by fred;
EXA: create user fred identified by fred;

Rows affected: 0

SQL_EXA> grant create session to fred;
EXA: grant create session to fred;

Rows affected: 0

SQL_EXA> grant select on adam.t1 to fred;
EXA: grant select on adam.t1 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:53:34 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam.t1;
EXA: select * from adam.t1;

N
-----------------

0 rows in resultset.

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;
Error: [42500] insufficient privileges: SELECT on table T2 (Session: 1601270776421928841)
SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:54:33 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create role allonadam2;
EXA: create role allonadam2;

Rows affected: 0

SQL_EXA> grant all on adam2 to allonadam2;
EXA: grant all on adam2 to allonadam2;

Rows affected: 0

SQL_EXA> grant allonadam2 to fred;
EXA: grant allonadam2 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:55:54 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;

N
-----------------

0 rows in resultset.

SQL_EXA> drop table adam2.t2;
EXA: drop table adam2.t2;
Error: [42500] insufficient privileges for dropping table (Session: 1601270923042332982)

That’s because ALL contains ALTER, DELETE, EXECUTE, INSERT, SELECT and UPDATE but not DROP which can be confirmed using EXA_DBA_OBJ_PRIVS.

Leave a comment

Getting started with #Exasol

One nice and easy way to make yourself familiar with Exasol – the leading In-Memory Analytic Database – is the Community Edition. It’s free and can be downloaded here as a virtual machine running on VirtualBox.

A good description how to install the Community Edition can be found here.

There’s an Exasol SQL Client called EXAplus. You can use it as GUI, then it looks like this:

EXAplus GUI

A command line version of EXAplus is also available. I will use it for my articles subsequently because it works better than pictures from the GUI for that purpose. You will be able to copy & paste commands from the article that way, for example. If you install the Community Edition on a Windows host like I did, you get to the command line EXAplus this way: Open a cmd shell. Then

C:\>cd \Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus>exaplusx64 -help

That gives you a basic help for the EXAplus CLI. I connect now as superuser to my Community Edition:

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus>exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN
EXAplus 6.0.8 (c) EXASOL AG

Tuesday, May 15, 2018 2:13:50 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA>

For those of you who are used to SQL*Plus, EXAplus will appear quite familiar:

SQL_EXA> col column_name for a40;
COLUMN   column_name ON
FORMAT   a40
SQL_EXA> desc exa_user_users;
EXA: desc exa_user_users;

COLUMN_NAME                              SQL_TYPE                                 NULLABLE DISTRIBUTION_KEY
---------------------------------------- ---------------------------------------- -------- ----------------
USER_NAME                                VARCHAR(128) UTF8
CREATED                                  TIMESTAMP
USER_PRIORITY                            VARCHAR(128) UTF8
USER_COMMENT                             VARCHAR(2000) UTF8

4 rows in resultset.

SQL_EXA> @c:/blogpostings/whoami;
EXA: select user_name from exa_user_users;

USER_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS

1 row in resultset.

I placed a file named whoami.sql in c:/blogpostings containing the SELECT command. Notice the mandatory ; at the end of each command.

Oh, and CURSOR UP and DOWN scrolls you through the history of commands out of the box 🙂

Leave a comment

What is #Exasol?

Exasol is the name of a company based in Nuremberg, Germany and the name of its major product: An in-memory analytic database.

That database leads the recent TPC-H benchmarks, delivering outstanding performance while running on commodity hardware.

Technically, Exasol is a clustered RDBMS using a shared nothing architecture. Availability is achieved with spare nodes, like in this diagram:

Exasol Cluster 2+1That shows a 2+1 cluster with two active nodes and one spare node. The nodes are running on Linux CentOS with an additional layer on top of it named EXAClusterOS that acts as a clusterware. Each active node stores its own data as well as replicated data from neighbor nodes, so node failure can be tolerated without losing data, the data being stored on EXAStorage – think of it as a database filesystem.

Data is stored in compressed columnar format on disk and loaded into the memory of the nodes in that format, to the effect that popular data can be processed in memory in parallel with very high speed. Exasol doesn’t require much tuning if any. One key feature in that area is that indexes are being generated automatically if required by queries and these indexes are also dropped automatically when they do no longer contribute to query performance.

Exasol follows the ACID standard and always operates in transaction isolation level SERIALIZABLE. Historically, Exasol started as an accelerator layer on top of Oracle – which is why being Oracle compatible has been a priority from the start. Meanwhile, Exasol is an autonomous RDBMS and technology leader for analytic in-memory databases. Due to its growing popularity among customers worldwide, the company is growing at high speed.

That goes out to you who wonder ‘What company is that where Uwe went to?’ Expect some more articles about Exasol to come. But don’t worry: I’m still following Oracle technology news as well and will write about that also in my spare time 🙂

2 Comments

%d bloggers like this: