Netezza does not have schema’s per-se, unlike Oracle. Here a database can not contain more than one object of the same name, be it tables and users or materialized views. So an object does have an owner, which relates in a similar manner to schema’s, but now not in particular require adding when enabling and managing cross database access.
Here’s an example.
I have a couple of database’s here, and some tables. For the second user I’ll create the whole thing and show permissions required to define this for the minimum requirements.
SYSTEM(ADMIN)=> c gc gary gary
You are now connected to database gc as user gary.
GC(GARY)=> d t1
Table “T1″
Attribute | Type | Modifier | Default Value
———–+————–+———-+—————
ID | BIGINT | |
MF | CHARACTER(1) | |
Distributed on hash: “MF”
Organized on: “ID”
SYSTEM(ADMIN)=> create database gc2;
CREATE DATABASE
SYSTEM(ADMIN)=> create user gary2 with password ‘gary2′;
CREATE USER
Currently we cannot use this database as the new user.
LABDB(ADMIN)=> c gc2 gary2 gary2
FATAL 1: database connection refused
Previous connection kept
SYSTEM(ADMIN)=> grant list on gc2 to gary2;
GRANT
Now we can connect.
LABDB(ADMIN)=> c gc2 gary2 gary2
You are now connected to database gc2 as user gary2.
GC2(GARY2)=> select count(*) from gc.gary.t1;
ERROR: Permission denied on “T1″.
The following allows access.
GC2(ADMIN)=> c gc admin password
You are now connected to database gc as user admin.
GC(ADMIN)=> grant select on t1 to gary2;
GRANT
Which is the same as
GC(ADMIN)=> grant select on gary.t1 to gary2;
GRANT
Now we can access across the database.
GC2(GARY2)=> select count(*) from gc.gary.t1;
COUNT
———–
134217728
(1 row)
We still cannot create a table in the database though.
GC2(GARY2)=> create table t1 as select * from gc.gary.t1 limit 10;
ERROR: CREATE TABLE: permission denied.
GC(ADMIN)=> c gc2 admin password
You are now connected to database gc2 as user admin.
GC2(ADMIN)=> grant create table to gary2;
GRANT
You will note that there is no requirement to reconnect to the database to obtain these permissions.
GC2(GARY2)=> create table t1 as select * from gc.gary.t1 limit 10;
INSERT 0 10
GC(ADMIN)=> c gc gary gary
You are now connected to database gc as user gary.
GC(GARY)=> create table t4 as select * from t1 limit 0;’
INSERT 0 0
GC(GARY)=> c gc admin password
You are now connected to database gc as user admin.
GC(ADMIN)=> grant insert on t4 to gary2;
GRANT
GC2(GARY2)=> insert into gc.gary.t4 select * from t1;
ERROR: Cross Database Access not supported for this type of command
Bearing in mind that you cannot have two tables the same in a single database, there is no reason to place the schema name of the table in the command. Therefore the following are identical.
GC2(GARY2)=> insert into t1 select * from gc.gary.t1 limit 10;
INSERT 0 10
GC2(GARY2)=> insert into t1 select * from gc..t1 limit 10;
INSERT 0 10
GC2(GARY2)=> insert into t1 select * from gc.t1 limit 10;
INSERT 0 10
But there is another option which can be set.
GC2(GARY2)=> show enable_schema_dbo_check;
NOTICE: ENABLE_SCHEMA_DBO_CHECK is 0
SHOW VARIABLE
This changes the behaviour of using the schema name when referencing a table. 0 raises no message, 1 produces a warning whilst 2 denies access.
GC(ADMIN)=> c gc2 gary2 gary2
You are now connected to database gc2 as user gary2.
GC2(GARY2)=> insert into t1 select * from gc.gary.t1 limit 10;
INSERT 0 10
GC2(GARY2)=> set enable_schema_dbo_check = 1;
SET VARIABLE
GC2(GARY2)=> insert into t1 select * from gc.gary.t1 limit 10;
NOTICE: Schema ‘GARY’ does not exist
INSERT 0 10
GC2(GARY2)=> set enable_schema_dbo_check = 2;
SET VARIABLE
GC2(GARY2)=> insert into t1 select * from gc.gary.t1 limit 10;
ERROR: Schema ‘GARY’ does not exist
The setting can be made permenant.
[nz@netezza data]$ pwd
/nz/data
[nz@netezza data]$ more postgresql.conf | grep schema
#
# Cross Database Access Settings
#
# enable_schema_dbo_check = 0
So it is originally hashed out and not read when the database starts. Change it in the configuration file.
#
# Cross Database Access Settings
#
enable_schema_dbo_check = 2
Now connect and try the same.
GC2(GARY2)=> insert into t1 select * from gc.gary.t1 limit 10;
INSERT 0 10
Nothing. So the default is still in place. Restart the system to enable the change.
[nz@netezza data]$ nzstop
[nz@netezza data]$ nzstart
Now it takes effect.
[nz@netezza data]$ nzsql gc2 gary2 gary2
Welcome to nzsql, the Netezza SQL interactive terminal.
Type: h for help with SQL commands
? for help on internal slash commands
g or terminate with semicolon to execute query
q to quit
GC2(GARY2)=> show enable_schema_dbo_check;
NOTICE: ENABLE_SCHEMA_DBO_CHECK is 2
SHOW VARIABLE
GC2(GARY2)=> insert into t1 select * from gc.t1 limit 10;
ERROR: Schema ‘GC’ does not exist
As expected.
