Have a request where users would like to be able to create their own table objects.  
They would like to run CREATE TABLE FROM or import their own data into Netezza to work with.  

With these caveats,  what's the best approach to granting a user their own personal functional "sandbox" 

• Obviously,  I don't want to grant them full access to the entire database
• I'm not able to create an additional schema since our NPS has "enable_schema_dbo_check=0" and afraid to touch that.  
• I setup a new database,  but encountered issues with "Cross Database Access not supported for this type of command"

Does Netezza have a way to GRANT permissions on objects given a set prefix?
Something like "GRANT ALL ON 'ABC_%'.* TO GROUPABC" as a way to say they can do anything to tables in the database that start with "ABC_"

Appreciate any advice,  thank you!

------------------------------
Greg Brown
------------------------------
#NetezzaPerformanceServer

>> Have a request where users would like to be able to create their own table objects.  
>> They would like to run CREATE TABLE FROM or import their own data into Netezza to work with.  

GRANT CREATE TABLE TO that_user;

They will be able to create their own tables (and do anything to THOSE tables that they want).
This (by itself) does not give them any access to any other objects in that database.
You can easily see who created/owns each table.


>> Does Netezza have a way to GRANT permissions on objects given a set prefix?

No.  You can do it for specific objects, or for all objects in that class (e.g., grant list,select on TABLE to that_user; )

------------------------------
MARK Fraase
------------------------------

>> GRANT CREATE TABLE TO that_user;

This makes sense,  thank you!  That will help them create their own tables in the same environment as the tables they'd like to CTAS source or import their own data.   Then, it would be a training task to ask them to create all of their tables as "ABC_",  for example,  if I want them to stay in that prefix.   Also tested to make sure after CREATE,  they're able to TRUNCATE, DELETE, INSERT, DROP - and they will.   Main concern was ensuring they could do no harm to other existing objects.  Will give this a try - we have backups :)  thank you!

------------------------------
Greg Brown
------------------------------

Original Message:
Sent: Tue January 17, 2023 10:24 AM
From: MARK Fraase
Subject: How Best to Setup User Sandbox?

>> Have a request where users would like to be able to create their own table objects.  
>> They would like to run CREATE TABLE FROM or import their own data into Netezza to work with.  

GRANT CREATE TABLE TO that_user;

They will be able to create their own tables (and do anything to THOSE tables that they want).
This (by itself) does not give them any access to any other objects in that database.
You can easily see who created/owns each table.


>> Does Netezza have a way to GRANT permissions on objects given a set prefix?

No.  You can do it for specific objects, or for all objects in that class (e.g., grant list,select on TABLE to that_user; )

------------------------------
MARK Fraase
------------------------------