GRANT Statement - Informix to PostgreSQL Migration

Both Informix and PostgreSQL provide the GRANT statement to assign access privileges to users and roles, but there are differences in the syntax.

• Execute a Procedure or Function
• Use Languages

Grant execute permission on a stored procedure or function in Informix:

Informix:

  -- Procedure without parameters
  GRANT EXECUTE ON PROCEDURE sp_stat() TO public AS informix;
 
  -- Procedure with a parameter
  GRANT EXECUTE ON PROCEDURE sp_get_order(int) TO public AS informix;

Since Informix stored procedurs are converted to functions with RETURNS VOID in PostgreSQL (there is no CREATE PROCEDURE statement), you have to grant EXECUTE ON FUNCTION in PostgreSQL:

PostgreSQL:

  -- Function without parameters
  GRANT EXECUTE ON FUNCTION sp_stat() TO public;
 
  -- Function with a parameter
  GRANT EXECUTE ON FUNCTION sp_get_order(int) TO public;

Note that PostgreSQL does not support AS grantor clause.

Grant a privileges to use SPL, C or Java language for stored procedures and user-defined functions in Informix:

Informix:

  -- Grant a language privilege
  GRANT USAGE ON LANGUAGE SPL TO public;
  GRANT USAGE ON LANGUAGE C TO public;
  GRANT USAGE ON LANGUAGE JAVA TO public;

Since Informix SPL stored procedurs and functions are converted to PLPGSQL language in PostgreSQL, you have to grant LANGUAGE PLPGSQL in PostgreSQL:

PostgreSQL:

  -- Grant a language privilege
  GRANT USAGE ON LANGUAGE PLPGSQL TO public;
  GRANT USAGE ON LANGUAGE C TO public;
  GRANT USAGE ON LANGUAGE JAVA TO public;

• Informix to PostgreSQL Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2013.