Netezza: list users that own objects

PROBLEM:
Dropping a database user renders error:

ERROR:  DROP USER: user "JOHN.DOUGH" owns objects, cannot be removed

What objects does user own?

SOLUTION:

SELECT distinct OWNER, THE_OBJECT_TYPE, OBJNAME, DBNAME
FROM (
   SELECT
               usename AS OWNER,
               (CASE   WHEN objclass = 4902 then 'database'
                       WHEN objclass = 4903 then 'group'
                       WHEN objclass = 4904 then 'user'       END ) AS the_object_type,
               objname, null as dbname 
       FROM
               _t_object,
               _t_user
       WHERE   objowner = usesysid
        AND    objclass IN (4902, 4903, 4904)
        AND    objdb = 0
        AND    objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN')
   UNION ALL
       SELECT
               _t_user.usename AS OWNER,
               lower(the_class.objname) AS the_object_type,
               the_object.objname    as objname,
               the_database.objname  AS dbname
       FROM
               _t_object as the_object
                       LEFT OUTER JOIN _t_object the_class ON
                               (the_object.objclass = the_class.objid),
               _t_object the_database,
               _t_user
       WHERE   the_object.objowner = _t_user.usesysid
        AND    the_object.objclass NOT IN (4902, 4903, 4904)
        AND    the_object.objdb = the_database.objid
        AND    the_object.objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN')
) tab
WHERE 1=1
  and OWNER in 
     ( 
         'JOHN.DOUGH'
        ,'SUSAN.SMITH'
        ,'JUAN.RODRIGES'
     )
ORDER BY 1,2,3
;

Snowflake: snowsql !options

Related links:

• Snowflake: Using SnowSQL
• Unloading SnowSQL command results to a file
• Snowflake: the snowsql option OUTPUT_FORMAT to output results delimited and formatted.

Show snowsqlcommand line options.

snowsql -q '!options'

+----------------------------+------------------------+--------------------------------------------------------------------------------------+
| Name                       | Value                  | Help                                                                                 |
|----------------------------+------------------------+--------------------------------------------------------------------------------------|
| auto_completion            | True                   | Displays auto-completion suggestions for commands and Snowflake objects              |
| client_session_keep_alive  | False                  | Keeps the session active indefinitely, even if there is no activity from the user.   |
| echo                       | False                  | Outputs the SQL command to the terminal when it is executed                          |
| editor                     | vim                    | Changes the editor to use for the !edit command                                      |
| empty_for_null_in_tsv      | False                  | Outputs an empty string for NULL values in TSV format                                |
| environment_variables      | []                     | Specifies the environment variables to be set in the SnowSQL variables.              |
|                            |                        | The variable names should be comma separated.                                        |
| execution_only             | False                  | Executes queries only. No data will be fetched                                       |
| exit_on_error              | False                  | Quits when SnowSQL encounters an error                                               |
| force_put_overwrite        | False                  | Force OVERWRITE=true for PUT. This is to mitigate S3's eventually consistent issue.  |
| friendly                   | False                  | Shows the splash text and goodbye messages                                           |
| header                     | True                   | Outputs the header in query results                                                  |
| insecure_mode              | False                  | Turns off OSCP certificate checks                                                    |
| key_bindings               | emacs                  | Changes keybindings for navigating the prompt to emacs or vi                         |
| log_bootstrap_file         | ~/.snowsql/log_boots.. | SnowSQL bootstrap log file location                                                  |
| log_file                   | ~/.snowsql/log         | SnowSQL main log file location                                                       |
| log_level                  | CRITICAL               | Changes the log level (critical, debug, info, error, warning)                        |
| login_timeout              | 120                    | Login timeout in seconds.                                                            |
| noup                       | False                  | Turns off auto upgrading Snowsql                                                     |
| ocsp_fail_open             | True                   | Sets the fail open mode for OCSP Failures. For help please refer the documentation.  |
| output_file                | None                   | Writes output to the specified file in addition to the terminal                      |
| output_format              | psql                   | Sets the output format for query results.                                            |
| paging                     | False                  | Enables paging to pause output per screen height.                                    |
| progress_bar               | True                   | Shows progress bar while transferring data.                                          |
| prompt_format              | [user]#[warehouse]@[.. | Sets the prompt format. For help, see the documentation                              |
| quiet                      | False                  | Hides all output                                                                     |
| remove_comments            | False                  | Removes comments before sending query to Snowflake                                   |
| remove_trailing_semicolons | False                  | Removes trailing semicolons from SQL text before sending queries to Snowflake        |
| results                    | True                   | If set to off, queries will be sent asynchronously, but no results will be fetched.  |
|                            |                        | Use !queries to check the status.                                                    |
| rowset_size                | 1000                   | Sets the size of rowsets to fetch from the server.                                   |
|                            |                        | Set the option low for smooth output, high for fast output.                          |
| sfqid                      | True                   | Turns on/off Snowflake query id in the summary.                                      |
| sfqid_in_error             | False                  | Turns on/off Snowflake query id in the error message                                 |
| stop_on_error              | False                  | Stops all queries yet to run when SnowSQL encounters an error                        |
| syntax_style               | default                | Sets the colors for the text of SnowSQL.                                             |
| timing                     | True                   | Turns on/off timing for each query                                                   |
| timing_in_output_file      | False                  | Includes timing in the output file.                                                  |
| variable_substitution      | False                  | Substitutes variables (starting with '&') with values                                |
| version                    | 1.1.84                 | SnowSQL version                                                                      |
| wrap                       | True                   | Truncates lines at the width of the terminal screen                                  |
+----------------------------+------------------------+--------------------------------------------------------------------------------------+

Usage example for snowsql command line !options:

ubuntu@server:~$ snowsql -o friendly=false -o sfqid=on -o echo=True -o progress_bar=false  -q 'SELECT CURRENT_TIMESTAMP'
SELECT CURRENT_TIMESTAMP
+-------------------------------+
| CURRENT_TIMESTAMP             |
|-------------------------------|
| 2019-09-09 16:05:10.176 -0700 |
+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.130s, Query ID: 018ec7e9-9999-9999-9999-83f10025e61a

Set options when at snowsql session:

--- Success ---
USR#WH@DBNAME.PUBLIC>   !set echo=false   --- Case-insensetive
USR#WH@DBNAME.PUBLIC>   !set echo=False
USR#WH@DBNAME.PUBLIC>   !set echo=TRUE
USR#WH@DBNAME.PUBLIC>   !set echo =TRUE

--- These will FAIL ---

USR#WH@DBNAME.PUBLIC>   !set echo=1;      --- ERROR: not acceptable value
1 is not an acceptable value for echo     

1 is not an acceptable value for echo
USR#WH@DBNAME.PUBLIC>   !set echo= TRUE   --- ERROR: white space is part of a value after '=' sign
 TRUE is not an acceptable value for echo

.

Snowflake: SHOW PARAMETERS

Related links:

• Snowflake: SHOW PARAMETERS
• Snowflake: get all context CURRENT_* session functions
• Snowflake: snowsql !options

Show session parameters for Snowflake session.

SHOW PARAMETERS ;

USER#WH@MAINDB.PUBLIC>SHOW PARAMETERS;
+-----------------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
| key                                           | value                            | default                          | level   | description                                                                                                                                                                         | type    |
|-----------------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------|
| ABORT_DETACHED_QUERY                          | false                            | false                            | SESSION | If true, Snowflake will automatically abort queries when it detects that the client has disappeared.                                                                                | BOOLEAN |
| AUTOCOMMIT                                    | true                             | true                             | SESSION | The autocommit property determines whether is statement should to be implicitly                                                                                                     | BOOLEAN |
|                                               |                                  |                                  |         | wrapped within a transaction or not. If autocommit is set to true, then a                                                                                                           |         |
|                                               |                                  |                                  |         | statement that requires a transaction is executed within a transaction                                                                                                              |         |
|                                               |                                  |                                  |         | implicitly. If autocommit is off then an explicit commit or rollback is required                                                                                                    |         |
|                                               |                                  |                                  |         | to close a transaction. The default autocommit value is true.                                                                                                                       |         |
| AUTOCOMMIT_API_SUPPORTED                      | true                             | true                             |         | Whether autocommit feature is enabled for this client. This parameter is for                                                                                                        | BOOLEAN |
|                                               |                                  |                                  |         | Snowflake use only.                                                                                                                                                                 |         |
| BINARY_INPUT_FORMAT                           | HEX                              | HEX                              |         | input format for binary                                                                                                                                                             | STRING  |
| BINARY_OUTPUT_FORMAT                          | HEX                              | HEX                              |         | display format for binary                                                                                                                                                           | STRING  |
| CLIENT_MEMORY_LIMIT                           | 1536                             | 1536                             |         | Limit the amount of memory used by the client in MB                                                                                                                                 | NUMBER  |
| CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX    | false                            | false                            |         | For client metadata request(getTables()), use session catalog and schema if set to true                                                                                             | BOOLEAN |
| CLIENT_PREFETCH_THREADS                       | 4                                | 4                                | SESSION | Customer parameter to control threads, 0=auto                                                                                                                                       | NUMBER  |
| CLIENT_RESULT_CHUNK_SIZE                      | 160                              | 160                              |         | Set the max result chunk size in MB on the client side                                                                                                                              | NUMBER  |
| CLIENT_RESULT_COLUMN_CASE_INSENSITIVE         | false                            | false                            |         | Column name search is case insensitive in clients                                                                                                                                   | BOOLEAN |
| CLIENT_SESSION_CLONE                          | false                            | false                            |         | If true, client clones a new session from the previously used tokens for the account and user.                                                                                      | BOOLEAN |
| CLIENT_SESSION_KEEP_ALIVE                     | false                            | false                            |         | If true, client session will not expire automatically                                                                                                                               | BOOLEAN |
| CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY | 900                              | 3600                             | SESSION | Heartbeat frequency in seconds for CLIENT_SESSION_KEEP_ALIVE.                                                                                                                       | NUMBER  |
| CLIENT_TIMESTAMP_TYPE_MAPPING                 | TIMESTAMP_LTZ                    | TIMESTAMP_LTZ                    |         | If a variable is bound to the TIMESTAMP data type using the bind API, determines which TIMESTAMP* type it should map to:                                                            | STRING  |
|                                               |                                  |                                  |         |   TIMESTAMP_LTZ (default), TIMESTAMP_NTZ                                                                                                                                            |         |
| DATE_INPUT_FORMAT                             | AUTO                             | AUTO                             |         | input format for date                                                                                                                                                               | STRING  |
| DATE_OUTPUT_FORMAT                            | YYYY-MM-DD                       | YYYY-MM-DD                       |         | display format for date                                                                                                                                                             | STRING  |
| ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION      | true                             | true                             |         | Enable physical-type optimizations used in Snowflake to affect the Parquet output                                                                                                   | BOOLEAN |
| ERROR_ON_NONDETERMINISTIC_MERGE               | true                             | true                             |         | raise an error when attempting to merge-update a row that joins many rows                                                                                                           | BOOLEAN |
| ERROR_ON_NONDETERMINISTIC_UPDATE              | false                            | false                            |         | raise an error when attempting to update a row that joins many rows                                                                                                                 | BOOLEAN |
| JDBC_TREAT_DECIMAL_AS_INT                     | true                             | true                             |         | When scale is 0, whether to treat Decimal as Int in JDBC                                                                                                                            | BOOLEAN |
| JSON_INDENT                                   | 2                                | 2                                |         | Width of indentation in JSON output (0 for compact)                                                                                                                                 | NUMBER  |
| JS_TREAT_INTEGER_AS_BIGINT                    | false                            | false                            |         | If true, the nodejs client will convert all integer columns to bigint type                                                                                                          | BOOLEAN |
| LOCK_TIMEOUT                                  | 43200                            | 43200                            |         | Number of seconds to wait while trying to lock a resource, before timing out                                                                                                        | NUMBER  |
|                                               |                                  |                                  |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the                                                                                                            |         |
|                                               |                                  |                                  |         | statement must acquire the lock immediately or abort. If multiple resources                                                                                                         |         |
|                                               |                                  |                                  |         | need to be locked by the statement, the timeout applies separately to each                                                                                                          |         |
|                                               |                                  |                                  |         | lock attempt.                                                                                                                                                                       |         |
| MULTI_STATEMENT_COUNT                         | 1                                | 1                                |         | Number of statement included in submitted query text.                                                                                                                               | NUMBER  |
|                                               |                                  |                                  |         | This parameter is submitted by user to avoid sql injection.                                                                                                                         |         |
|                                               |                                  |                                  |         | Value 1 means one statement, value > 1 means n statement                                                                                                                            |         |
|                                               |                                  |                                  |         | can be executed, if not equal to the value will raise the                                                                                                                           |         |
|                                               |                                  |                                  |         | exception. Value 0 means any number of statement can be                                                                                                                             |         |
|                                               |                                  |                                  |         |  executed                                                                                                                                                                           |         |
| ODBC_USE_CUSTOM_SQL_DATA_TYPES                | false                            | false                            |         | ODBC return snowflake specific sql data types in result set metadata                                                                                                                | BOOLEAN |
| QUERY_TAG                                     |                                  |                                  |         | String (up to 2000 characters) used to tag statements executed by the session                                                                                                       | STRING  |
| QUOTED_IDENTIFIERS_IGNORE_CASE                | false                            | false                            |         | If true, the case of quoted identifiers is ignored                                                                                                                                  | BOOLEAN |
| ROWS_PER_RESULTSET                            | 0                                | 0                                |         | maxium number of rows in a result set                                                                                                                                               | NUMBER  |
| SEARCH_PATH                                   | $current, $public                | $current, $public                |         | Search path for unqualified object references.                                                                                                                                      | STRING  |
| SIMULATED_DATA_SHARING_CONSUMER               |                                  |                                  |         | Data sharing views will return rows as if executed in the specificed consumer account.                                                                                              | STRING  |
| STATEMENT_QUEUED_TIMEOUT_IN_SECONDS           | 0                                | 0                                |         | Timeout in seconds for queued statements: statements will automatically be canceled if they are queued on a warehouse for longer than this amount of time; disabled if set to zero. | NUMBER  |
| STATEMENT_TIMEOUT_IN_SECONDS                  | 172800                           | 172800                           |         | Timeout in seconds for statements: statements are automatically canceled if they run for longer; if set to zero, max value (604800) is enforced.                                    | NUMBER  |
| STRICT_JSON_OUTPUT                            | false                            | false                            |         | JSON output is strictly compliant to the specification                                                                                                                              | BOOLEAN |
| TIMESTAMP_DAY_IS_ALWAYS_24H                   | false                            | false                            |         | If set, arithmetic on days always uses 24 hours per day,                                                                                                                            | BOOLEAN |
|                                               |                                  |                                  |         | possibly not preserving the time (due to DST changes)                                                                                                                               |         |
| TIMESTAMP_INPUT_FORMAT                        | AUTO                             | AUTO                             |         | input format for timestamp                                                                                                                                                          | STRING  |
| TIMESTAMP_LTZ_OUTPUT_FORMAT                   |                                  |                                  |         | Display format for TIMESTAMP_LTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.                                                                                                 | STRING  |
| TIMESTAMP_NTZ_OUTPUT_FORMAT                   | YYYY-MM-DD HH24:MI:SS.FF3        | YYYY-MM-DD HH24:MI:SS.FF3        |         | Display format for TIMESTAMP_NTZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.                                                                                                 | STRING  |
| TIMESTAMP_OUTPUT_FORMAT                       | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM |         | Default display format for all timestamp types.                                                                                                                                     | STRING  |
| TIMESTAMP_TYPE_MAPPING                        | TIMESTAMP_NTZ                    | TIMESTAMP_NTZ                    |         | If TIMESTAMP type is used, what specific TIMESTAMP* type it should map to:                                                                                                          | STRING  |
|                                               |                                  |                                  |         |   TIMESTAMP_LTZ, TIMESTAMP_NTZ (default) or TIMESTAMP_TZ                                                                                                                            |         |
| TIMESTAMP_TZ_OUTPUT_FORMAT                    |                                  |                                  |         | Display format for TIMESTAMP_TZ values. If empty, TIMESTAMP_OUTPUT_FORMAT is used.                                                                                                  | STRING  |
| TIMEZONE                                      | America/Los_Angeles              | America/Los_Angeles              |         | time zone                                                                                                                                                                           | STRING  |
| TIME_INPUT_FORMAT                             | AUTO                             | AUTO                             |         | input format for time                                                                                                                                                               | STRING  |
| TIME_OUTPUT_FORMAT                            | HH24:MI:SS                       | HH24:MI:SS                       |         | display format for time                                                                                                                                                             | STRING  |
| TRANSACTION_ABORT_ON_ERROR                    | false                            | false                            |         | If this parameter is true, and a statement issued within a non-autocommit                                                                                                           | BOOLEAN |
|                                               |                                  |                                  |         | transaction returns with an error, then the non-autocommit transaction is                                                                                                           |         |
|                                               |                                  |                                  |         | aborted. All statements issued inside that transaction will fail until an                                                                                                           |         |
|                                               |                                  |                                  |         | commit or rollback statement is executed to close that transaction.                                                                                                                 |         |
| TRANSACTION_DEFAULT_ISOLATION_LEVEL           | READ COMMITTED                   | READ COMMITTED                   |         | The default isolation level when starting a starting a transaction, when no                                                                                                         | STRING  |
|                                               |                                  |                                  |         | isolation level was specified                                                                                                                                                       |         |
| TWO_DIGIT_CENTURY_START                       | 1970                             | 1970                             |         | For 2-digit dates, defines a century-start year.                                                                                                                                    | NUMBER  |
|                                               |                                  |                                  |         | For example, when set to 1980:                                                                                                                                                      |         |
|                                               |                                  |                                  |         |   - parsing a string '79' will produce 2079                                                                                                                                         |         |
|                                               |                                  |                                  |         |   - parsing a string '80' will produce 1980                                                                                                                                         |         |
| UNSUPPORTED_DDL_ACTION                        | ignore                           | ignore                           |         | The action to take upon encountering an unsupported ddl statement                                                                                                                   | STRING  |
| USE_CACHED_RESULT                             | true                             | true                             |         | If enabled, query results can be reused between successive invocations of the same query as long as the original result has not expired                                             | BOOLEAN |
| WEEK_OF_YEAR_POLICY                           | 0                                | 0                                |         | Defines the policy of assigning weeks to years:                                                                                                                                     | NUMBER  |
|                                               |                                  |                                  |         | 0: the week needs to have 4 days in a given year;                                                                                                                                   |         |
|                                               |                                  |                                  |         | 1: a week with January 1st always belongs to a given year.                                                                                                                          |         |
| WEEK_START                                    | 0                                | 0                                |         | Defines the first day of the week:                                                                                                                                                  | NUMBER  |
|                                               |                                  |                                  |         | 0: legacy Snowflake behavior; 1: Monday .. 7: Sunday.                                                                                                                               |         |
+-----------------------------------------------+----------------------------------+----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
50 Row(s) produced. Time Elapsed: 0.142s, Query ID: 018ec708-9999-9999-9999-83f10025d402