/* *************************************************************************
 * Copyright 2020 VMware, Inc.   All rights reserved. -- VMware Confidential
 * *************************************************************************/

/*==============================================================================*/
/* VCDB_DB_SCHEMA_PERMISSIONS.sql                                               */
/*==============================================================================*/
/*                                                                              */
/* Description:                                                                 */
/*                                                                              */
/* Grants the nessesary permissions for other schemas and users to be able to   */
/* run the procedures in the build in library for Expand & Contract.            */
/*                                                                              */
/*==============================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_db_schema_permissions()
 LANGUAGE plpgsql
AS $vcdb_ndu_db_schema_permissions$

DECLARE
  r record;
  k record;
BEGIN
  BEGIN
    GRANT USAGE ON SCHEMA vc TO cns, vstatsuser;
    GRANT SELECT, INSERT, UPDATE ON TABLE vc.vpx_vcdb_ndu_exp TO cns, vstatsuser;
    GRANT SELECT, INSERT, UPDATE ON TABLE vc.vpx_vcdb_ndu_rvt TO cns, vstatsuser;
    GRANT SELECT, INSERT, UPDATE ON TABLE vc.vpx_vcdb_proc_trk TO cns, vstatsuser;
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE vc.vpx_vcdb_ndu_exp_app TO cns, vstatsuser;
	RAISE LOG 'Permissions to use NDU track tables added to CNS and VSTATSUSER';
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to grant permissions for NDU track tables to CNS and VSTATSUSER - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   BEGIN
     FOR r IN SELECT n.nspname,
                     p.proname,
                     CASE p.prokind
                          WHEN 'f' THEN 'FUNCTION'
                     END AS kind
              FROM pg_proc p
              LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
              LEFT JOIN pg_language l ON p.prolang = l.oid
              LEFT JOIN pg_type t ON t.oid = p.prorettype
              WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
              AND p.proname LIKE 'vcdb_ndu%'
              AND p.prokind = 'f'
              ORDER BY n.nspname,
                       p.proname
      LOOP
       EXECUTE 'GRANT EXECUTE ON FUNCTION "' || r.nspname || '"."' || r.proname || '" TO cns, vstatsuser;';
       RAISE LOG 'Execute permission granted to CNS and VSTATSUSER for NDU function %.%', r.nspname, r.proname;
      END LOOP;
      EXCEPTION
        WHEN OTHERS THEN
           RAISE LOG 'Failed to grant execute to CNS and VSTATSUSER on function %.% - sqlstate: %, sqlerrm: %', r.nspname, r.proname, SQLSTATE, SQLERRM;
   END;

   BEGIN
     FOR k IN SELECT n.nspname,
                     p.proname,
                     CASE p.prokind
                          WHEN 'p' THEN 'PROCEDURE'
                     END AS kind
              FROM pg_proc p
              LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
              LEFT JOIN pg_language l ON p.prolang = l.oid
              LEFT JOIN pg_type t ON t.oid = p.prorettype
              WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
              AND p.proname LIKE 'vcdb_ndu%'
              AND p.prokind = 'p'
              ORDER BY n.nspname,
                       p.proname
      LOOP
       EXECUTE 'GRANT EXECUTE ON PROCEDURE "' || k.nspname || '"."' || k.proname || '" TO cns, vstatsuser;';
       RAISE LOG 'Execute permission granted to CNS and VSTATSUSER for NDU procedure %.%', k.nspname, k.proname;
      END LOOP;
      EXCEPTION
        WHEN OTHERS THEN
           RAISE LOG 'Failed to grant execute to CNS and VSTATSUSER on procedure %.% - sqlstate: %, sqlerrm: %', r.nspname, r.proname, SQLSTATE, SQLERRM;
   END;
END;
$vcdb_ndu_db_schema_permissions$;

-----------------------------------------------------------------------------
-- END OF FILE
-----------------------------------------------------------------------------