MSC-PG0000

User privileges must be handled manually

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added because currently Snowconvert is not automatically migrating data control privileges, user privileges must be handled manually.

Code Example

Input Code:

Grant

GRANT ALL ON FUNCTION f_name() TO PUBLIC;

Revoke

REVOKE ALL ON FUNCTION f_name() FROM PUBLIC;

Alter External Table Owner

ALTER EXTERNAL TABLE Table1 
OWNER TO new_owner;

Alter Function Owner

ALTER FUNCTION public.pg_schema_size(text) OWNER TO gpadmin;

Output Code:

Grant

-- ** MSC-WARNING - MSC-PG0000 - USER PRIVILEGES MUST BE HANDLED MANUALLY **
--GRANT ALL ON FUNCTION f_name() TO PUBLIC
                                                   ;

Revoke

-- ** MSC-WARNING - MSC-PG0000 - USER PRIVILEGES MUST BE HANDLED MANUALLY **
--REVOKE ALL ON FUNCTION f_name() FROM PUBLIC
                                                        ;

Alter External Table Owner

-- ** MSC-WARNING - MSC-PG0000 - USER PRIVILEGES MUST BE HANDLED MANUALLY **
--ALTER EXTERNAL TABLE Table1
--OWNER TO new_owner
                  ;

Alter Function Owner

-- ** MSC-WARNING - MSC-PG0000 - USER PRIVILEGES MUST BE HANDLED MANUALLY **
--ALTER FUNCTION public.pg_schema_size(text) OWNER TO gpadmin
                                                           ;

Recommendations

• There are several types of objects that are not compatible with Snowflake so there is no possible transformation. The unsupported objects are the following: DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, ROUTINE, LANGUAGE, LARGE OBJECT, PARAMETER, TABLESPACE, AND TYPE.

• In PostgreSQL, you can use the GRANTED BY to indicate who gave the privilege, but Snowflake performs that function automatically.

• In the case of the GRANT CREATE in PostgreSQL all the privileges of the CREATE statement are granted, but for Snowflake, you must specify each privilege of the CREATE statement in a sequence.

• In the case of the ON FUNCTION/PROCEDURE, the type of function parameters must be specified for it to work in Snowflake.

• In the function specification part, the PostgreSQL USER_SESSION should be transformed to a CURRENT_USER in Snowflake.

• In the case of the Greenplum Alter External Table with Owner To clause, it could be transformed into a Grant Ownership of the table.

• In the case of the Greenplum Alter Function with Owner To clause, it could be transformed into a Grant Ownership of the function.

• If you need more support, you can email us at [e-mail]