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.
Pickleball spielen 
#1 von Noons am Mai 24, 2018 - 13:47
Some very cool features right there!
Thanks for the description, very useful.