Netezza: enable CROSS JOIN control set ENABLE_CROSSJOIN_CONTROL

Enable CROSS JOIN control feature by adding enable_crossjoin_control parameter to /nz/data/postgresql.conf file:

###

### In the file: /nz/data/postgresql.conf

###

enable_crossjoin_control = true

---

BEFORE NPS REBOOT: 

/* NOTICE:  Cross join control not enabled, at runtime option will be ignored. */

/* Start time 5/11/2015 4:55:02 PM, end time 5/11/2015 4:55:02 PM. */

/* Duration 0.0312002 sec. */

alter user USERNAME  allow cross join FALSE;

---

AFTER NPS REBOOT: 

Testing with different user setting for ALLOW CROSS JOIN . See: ALTER USER.

Default: _V_USER.CROSS_JOIN_ALLOWED IS NULL	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE _2; /* ERROR:  Execution of explicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;
alter user MYUSER allow cross join FALSE;	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE_2; /* ERROR:  Execution of explicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;
alter user MYUSER allow cross join TRUE;	/* ERROR:  Execution of implicit cross joins is not permitted */ SELECT count (* ) FROM TABLE_1, TABLE_2; /* Success. Ok !*/ SELECT count (* ) FROM TABLE_1 cross join  TABLE_2;

  

---

SET CONTROL AT DATABASE GROUP AND USER LEVEL:

Replace NULL with TRUE|FALSE:   ALTER GROUP xyz ALLOW CROSS JOIN TRUE;  If still left NULL, the user defaults to FALSE.

  

--- fine grained control at GROUP level --- select groupname, cross_joins_allowed  from _v_group;	--- fine username control at USER level --- select username, cross_joins_allowed  from SYSTEM ..v_user;