āĻāĻ āĻ¸ā§āĻā§āĻ āĻ˛ā§āĻāĻžāĻ° āĻ
āĻ¨ā§āĻĒā§āĻ°ā§āĻ°āĻŖāĻž āĻāĻŋāĻ˛ āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻāĻŋ
āĻŽāĻāĻžāĻ° āĻŽāĻ¨ā§ āĻšāĻ˛ āĻāĻāĻ āĻāĻžāĻŦāĻ¨āĻž - "āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻŦā§āĻ¯āĻŦāĻ¸āĻžāĻ¯āĻŧāĻŋāĻ āĻ¯ā§āĻā§āĻ¤āĻŋ āĻĒā§āĻ°āĻ¯āĻŧā§āĻ āĻāĻ°ā§āĻ¨".
āĻāĻāĻž āĻļā§āĻ§ā§ āĻāĻŽāĻžāĻ° āĻŽāĻ¨ā§ āĻāĻ¸ā§āĻ¨āĻŋāĨ¤
āĻāĻāĻžāĻĄāĻŧāĻžāĻ, āĻāĻŦāĻŋāĻˇā§āĻ¯āĻ¤ā§āĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻŋ āĻĒā§āĻ°āĻĨāĻŽāĻ¤, āĻ¨āĻŋāĻā§āĻ° āĻāĻ¨ā§āĻ¯, āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻĻā§āĻā§āĻ¤ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻŦāĻŋāĻāĻžāĻļāĻā§āĻ˛āĻŋ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻ¤ā§ āĻā§āĻ¯āĻŧā§āĻāĻŋāĻ˛āĻžāĻŽāĨ¤ āĻŦāĻŋāĻļā§āĻˇ āĻāĻ°ā§ āĻŦāĻŋāĻŦā§āĻāĻ¨āĻž āĻāĻ°ā§ āĻ¯ā§ āĻ¤ā§āĻ˛āĻ¨āĻžāĻŽā§āĻ˛āĻāĻāĻžāĻŦā§ āĻ¸āĻŽā§āĻĒā§āĻ°āĻ¤āĻŋ āĻ¸ā§āĻĨāĻžāĻĒāĻ¤ā§āĻ¯ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ āĻāĻŦāĻ āĻŦā§āĻ¯āĻžāĻāĻāĻ¨ā§āĻĄ āĻ¸ā§āĻ¤āĻ°ā§ āĻŦā§āĻ¯āĻŦāĻ¸āĻžāĻ° āĻ¯ā§āĻā§āĻ¤āĻŋ āĻ¸ā§āĻĨāĻžāĻ¨āĻžāĻ¨ā§āĻ¤āĻ° āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻā§āĻļāĻ˛āĻāĻ¤ āĻ¸āĻŋāĻĻā§āĻ§āĻžāĻ¨ā§āĻ¤ āĻ¨ā§āĻāĻ¯āĻŧāĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛āĨ¤ āĻ¯āĻžāĻ¤ā§ āĻŦāĻŋāĻāĻļāĻŋāĻ¤ āĻ¸āĻŦāĻāĻŋāĻā§āĻ āĻļā§āĻā§āĻ°āĻ āĻāĻžāĻ°āĻ āĻāĻžāĻā§ āĻāĻ¸āĻŦā§ āĻ¨āĻž āĻāĻŦāĻ āĻāĻžāĻ°āĻ āĻ¸ā§āĻŦāĻžāĻ°ā§āĻĨā§ āĻĨāĻžāĻāĻŦā§ āĻ¨āĻžāĨ¤
āĻŦāĻ°ā§āĻŖāĻŋāĻ¤ āĻĒāĻĻā§āĻ§āĻ¤āĻŋāĻā§āĻ˛ā§ āĻā§āĻ¨ā§ āĻ§āĻ°āĻ¨ā§āĻ° āĻāĻŦāĻŋāĻˇā§āĻāĻžāĻ° āĻŦāĻž āĻŦā§āĻ¯āĻ¤āĻŋāĻā§āĻ°āĻŽā§ āĻ¨āĻ¯āĻŧāĨ¤ āĻāĻžāĻ¨āĻŋ āĻāĻŋāĻāĻžāĻŦā§, āĻ¸āĻŦāĻāĻŋāĻā§āĻ āĻā§āĻ˛āĻžāĻ¸āĻŋāĻ āĻāĻŦāĻ āĻŦā§āĻļ āĻāĻ¯āĻŧā§āĻāĻŦāĻžāĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ (āĻāĻĻāĻžāĻšāĻ°āĻŖāĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻāĻŽāĻŋ 20 āĻŦāĻāĻ° āĻāĻā§ āĻāĻ°āĻžāĻā§āĻ˛ā§ āĻāĻāĻ āĻĒāĻĻā§āĻ§āĻ¤āĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĻāĻŋ)āĨ¤ āĻāĻŽāĻŋ āĻ¸āĻŦ āĻāĻŋāĻā§ āĻāĻ āĻāĻžāĻ¯āĻŧāĻāĻžāĻ¯āĻŧ āĻ¸āĻāĻā§āĻ°āĻš āĻāĻ°āĻžāĻ° āĻ¸āĻŋāĻĻā§āĻ§āĻžāĻ¨ā§āĻ¤ āĻ¨āĻŋāĻ¯āĻŧā§āĻāĻŋāĨ¤ āĻ¯āĻĻāĻŋ āĻāĻāĻž āĻāĻžāĻ°ā§ āĻāĻžāĻā§ āĻ˛āĻžāĻā§āĨ¤ āĻ āĻ¨ā§āĻļā§āĻ˛āĻ¨ āĻĻā§āĻāĻžāĻ¨ā§ āĻšāĻ¯āĻŧā§āĻā§, āĻĒā§āĻ°āĻžāĻ¯āĻŧāĻ āĻāĻāĻ āĻ§āĻžāĻ°āĻŖāĻž āĻ¸ā§āĻŦāĻžāĻ§ā§āĻ¨āĻāĻžāĻŦā§ āĻŦāĻŋāĻāĻŋāĻ¨ā§āĻ¨ āĻŽāĻžāĻ¨ā§āĻˇā§āĻ° āĻāĻžāĻā§ āĻāĻ¸ā§āĨ¤ āĻāĻŦāĻ āĻāĻāĻŋ āĻāĻāĻāĻŋ āĻ¸ā§āĻ¯ā§āĻā§āĻ¨āĻŋāĻ° āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻ¨āĻŋāĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ°āĻžāĻāĻž āĻĻāĻ°āĻāĻžāĻ°ā§āĨ¤
āĻ āĻŦāĻļā§āĻ¯āĻ, āĻāĻ āĻĒā§āĻĨāĻŋāĻŦā§āĻ¤ā§ āĻāĻŋāĻā§āĻ āĻ¨āĻŋāĻā§āĻāĻ¤ āĻ¨āĻ¯āĻŧ, āĻā§āĻ˛ āĻāĻŦāĻ āĻāĻžāĻāĻĒā§ āĻĻā§āĻ°ā§āĻāĻžāĻā§āĻ¯āĻŦāĻļāĻ¤ āĻ¸āĻŽā§āĻāĻŦāĨ¤ āĻ¸āĻŽāĻžāĻ˛ā§āĻāĻ¨āĻž āĻāĻŦāĻ āĻŽāĻ¨ā§āĻ¤āĻŦā§āĻ¯ āĻĻā§āĻĸāĻŧāĻāĻžāĻŦā§ āĻ¸ā§āĻŦāĻžāĻāĻ¤ āĻāĻŦāĻ āĻĒā§āĻ°āĻ¤ā§āĻ¯āĻžāĻļāĻŋāĻ¤āĨ¤ āĻāĻŦāĻ āĻāĻ°āĻ āĻāĻāĻāĻŋ āĻā§āĻ āĻŦāĻŋāĻŦāĻ°āĻŖ - āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ āĻŦāĻŋāĻŦāĻ°āĻŖ āĻŦāĻžāĻĻ āĻĻā§āĻāĻ¯āĻŧāĻž āĻšāĻ¯āĻŧāĨ¤ āĻāĻāĻ¨āĻ, āĻ¸āĻŦāĻāĻŋāĻā§ āĻāĻāĻ¨āĻ āĻāĻāĻāĻŋ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦ āĻāĻžāĻā§āĻ° āĻĒā§āĻ°āĻāĻ˛ā§āĻĒā§ āĻŦā§āĻ¯āĻŦāĻšā§āĻ¤ āĻšāĻ¯āĻŧāĨ¤ āĻ¸ā§āĻ¤āĻ°āĻžāĻ, āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻāĻŋ āĻā§āĻŦāĻ˛ āĻāĻāĻāĻŋ āĻ¸ā§āĻā§āĻ āĻāĻŦāĻ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻ§āĻžāĻ°āĻŖāĻžāĻ° āĻāĻāĻāĻŋ āĻŦāĻŋāĻŦāĻ°āĻŖ, āĻāĻ° āĻŦā§āĻļāĻŋ āĻāĻŋāĻā§ āĻ¨āĻ¯āĻŧāĨ¤ āĻāĻŽāĻŋ āĻāĻļāĻž āĻāĻ°āĻŋ āĻ¸āĻžāĻŽāĻā§āĻ°āĻŋāĻ āĻāĻŦāĻŋ āĻŦā§āĻāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¯āĻĨā§āĻˇā§āĻ āĻŦāĻŋāĻŦāĻ°āĻŖ āĻāĻā§āĨ¤
āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻ§āĻžāĻ°āĻŖāĻž āĻšāĻ˛ "āĻŦāĻŋāĻāĻžāĻāĻ¨ āĻāĻŦāĻ āĻāĻ¯āĻŧ, āĻ˛ā§āĻāĻžāĻ¨ āĻāĻŦāĻ āĻ¨āĻŋāĻā§āĻ°"
āĻ§āĻžāĻ°āĻŖāĻžāĻāĻŋ āĻā§āĻ˛āĻžāĻ¸āĻŋāĻ - āĻā§āĻŦāĻŋāĻ˛ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻĒā§āĻĨāĻ āĻ¸ā§āĻāĻŋāĻŽāĻž, āĻ¸āĻā§āĻāĻŋāĻ¤ āĻĢāĻžāĻāĻļāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻĒā§āĻĨāĻ āĻ¸ā§āĻāĻŋāĻŽāĻžāĨ¤
āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻā§āĻ° āĻ¸āĻ°āĻžāĻ¸āĻ°āĻŋ āĻĄā§āĻāĻžāĻ¤ā§ āĻ
ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻ¨ā§āĻāĨ¤ āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ āĻ¯āĻž āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻ¤āĻž āĻšāĻ˛ āĻ¸āĻā§āĻāĻŋāĻ¤ āĻĢāĻžāĻāĻļāĻ¨āĻā§ āĻāĻ˛ āĻāĻ°āĻž āĻāĻŦāĻ āĻĒā§āĻ°āĻžāĻĒā§āĻ¤ āĻĒā§āĻ°āĻ¤āĻŋāĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻāĻ°āĻžāĨ¤
āĻā§āĻŽāĻŋāĻāĻž
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
āĻ¸ā§āĻāĻŋāĻŽ
āĻā§āĻŦāĻŋāĻ˛ āĻ¸ā§āĻā§āĻ°ā§āĻ āĻ¸ā§āĻāĻŋāĻŽ
āĻ˛āĻā§āĻˇā§āĻ¯ āĻ¸āĻžāĻ°āĻŖā§ āĻ¯āĻž āĻŦāĻŋāĻˇāĻ¯āĻŧ āĻ¸āĻ¤ā§āĻ¤āĻž āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ āĻāĻ°ā§āĨ¤
CREATE SCHEMA store AUTHORIZATION store ;
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĢāĻžāĻāĻļāĻ¨ āĻĄāĻžāĻ¯āĻŧāĻžāĻā§āĻ°āĻžāĻŽ
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĢāĻžāĻāĻļāĻ¨, āĻŦāĻŋāĻļā§āĻˇ āĻāĻ°ā§ āĻ˛āĻāĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯āĨ¤
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;
āĻ¸ā§āĻĨāĻžāĻ¨ā§āĻ¯āĻŧ āĻ āĻĄāĻŋāĻ āĻ¸ā§āĻāĻŋāĻŽ
āĻ¸āĻā§āĻāĻŋāĻ¤ āĻĢāĻžāĻāĻļāĻ¨ āĻāĻŦāĻ āĻāĻžāĻ°ā§āĻā§āĻ āĻā§āĻŦāĻŋāĻ˛ā§āĻ° āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ā§āĻ° āĻ¸ā§āĻĨāĻžāĻ¨ā§āĻ¯āĻŧ āĻ āĻĄāĻŋāĻāĻŋāĻ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĢāĻžāĻāĻļāĻ¨ āĻāĻŦāĻ āĻā§āĻŦāĻŋāĻ˛āĨ¤
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
āĻ¸āĻžāĻ°ā§āĻāĻŋāĻ¸ āĻĢāĻžāĻāĻļāĻ¨ āĻĄāĻžāĻ¯āĻŧāĻžāĻā§āĻ°āĻžāĻŽ
āĻ¸ā§āĻŦāĻž āĻāĻŦāĻ DML āĻĢāĻžāĻāĻļāĻ¨ āĻāĻ¨ā§āĻ¯ āĻĢāĻžāĻāĻļāĻ¨.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
āĻŦā§āĻ¯āĻŦāĻ¸āĻž āĻĢāĻžāĻāĻļāĻ¨ āĻĄāĻžāĻ¯āĻŧāĻžāĻā§āĻ°āĻžāĻŽ
āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ āĻĻā§āĻŦāĻžāĻ°āĻž āĻŦāĻ˛āĻž āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻŦā§āĻ¯āĻŦāĻ¸āĻž āĻĢāĻžāĻāĻļāĻ¨ āĻāĻ¨ā§āĻ¯ āĻĢāĻžāĻāĻļāĻ¨.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻ āĻ§āĻŋāĻāĻžāĻ°
āĻā§āĻŽāĻŋāĻāĻž - DBA āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻ¸ā§āĻāĻŋāĻŽāĻžāĻā§āĻ˛āĻŋāĻ¤ā§ āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻ°āĻ¯āĻŧā§āĻā§ (āĻĄāĻŋāĻŦāĻŋ āĻŽāĻžāĻ˛āĻŋāĻā§āĻ° āĻā§āĻŽāĻŋāĻāĻž āĻĨā§āĻā§ āĻĒā§āĻĨāĻ)ā§ˇ
CREATE ROLE dba_role;
GRANT store TO dba_role;
GRANT sys_functions TO dba_role;
GRANT loc_audit_functions TO dba_role;
GRANT service_functions TO dba_role;
GRANT business_functions TO dba_role;
āĻā§āĻŽāĻŋāĻāĻž - USER āĻā§ āĻŦāĻŋāĻļā§āĻˇāĻžāĻ§āĻŋāĻāĻžāĻ° āĻāĻā§ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻ āĻāĻŋāĻ¤ā§āĻ°ā§ āĻŦā§āĻ¯āĻŦāĻ¸āĻžāĻ¯āĻŧāĻŋāĻ_āĻĢāĻžāĻāĻļāĻ¨.
CREATE ROLE user_role;
āĻ¸ā§āĻāĻŋāĻŽ āĻŽāĻ§ā§āĻ¯ā§ āĻŦāĻŋāĻļā§āĻˇāĻžāĻ§āĻŋāĻāĻžāĻ°
āĻĒā§āĻ°āĻĻāĻžāĻ¨
āĻ¯ā§āĻšā§āĻ¤ā§ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻĢāĻžāĻāĻļāĻ¨ āĻ
ā§āĻ¯āĻžāĻā§āĻ°āĻŋāĻŦāĻŋāĻāĻ āĻĻāĻŋāĻ¯āĻŧā§ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻž āĻšāĻ¯āĻŧ āĻ¨āĻŋāĻ°āĻžāĻĒāĻ¤ā§āĻ¤āĻž āĻ¸āĻāĻā§āĻāĻžāĻāĻžāĻ°ā§ āĻ¨āĻŋāĻ°ā§āĻĻā§āĻļāĻžāĻŦāĻ˛ā§ āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻāĻ¨āĻ¸āĻžāĻ§āĻžāĻ°āĻŖā§āĻ° āĻāĻžāĻ āĻĨā§āĻā§... āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻĢāĻžāĻāĻļāĻ¨ āĻĒā§āĻ°āĻ¤ā§āĻ¯āĻžāĻšāĻžāĻ° āĻāĻ°ā§āĻ¨;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA sys_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA loc_audit_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA service_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA business_functions FROM public ;
GRANT USAGE ON SCHEMA sys_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sys_functions TO dba_role ;
GRANT USAGE ON SCHEMA loc_audit_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA loc_audit_functions TO dba_role ;
GRANT USAGE ON SCHEMA service_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_functions TO dba_role ;
GRANT USAGE ON SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO user_role ;
GRANT ALL PRIVILEGES ON SCHEMA store TO GROUP business_functions ;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA store TO business_functions ;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA store TO business_functions ;
āĻ¤āĻžāĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻ¸ā§āĻāĻŋāĻŽāĻž āĻĒā§āĻ°āĻ¸ā§āĻ¤ā§āĻ¤āĨ¤ āĻāĻĒāĻ¨āĻŋ āĻĄā§āĻāĻž āĻĒā§āĻ°āĻŖ āĻāĻ°āĻž āĻļā§āĻ°ā§ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨āĨ¤
āĻāĻžāĻ°ā§āĻā§āĻ āĻā§āĻŦāĻŋāĻ˛
āĻā§āĻŦāĻŋāĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻž āĻ¤ā§āĻā§āĻāĨ¤ āĻā§āĻ¨ āĻŦāĻŋāĻļā§āĻˇ āĻŦā§āĻļāĻŋāĻˇā§āĻā§āĻ¯ āĻāĻžāĻĄāĻŧāĻž āĻāĻāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻ¨āĻž āĻāĻ°āĻžāĻ° āĻ¸āĻŋāĻĻā§āĻ§āĻžāĻ¨ā§āĻ¤ āĻ¨ā§āĻāĻ¯āĻŧāĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛ āĻā§āĻ°āĻŽāĻŋāĻ āĻāĻŦāĻ āĻ¸ā§āĻ¸ā§āĻĒāĻˇā§āĻāĻāĻžāĻŦā§ āĻ¸āĻŋāĻā§āĻ¯āĻŧā§āĻ¨ā§āĻ¸ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĨ¤ āĻĒā§āĻ˛āĻžāĻ¸, āĻ āĻŦāĻļā§āĻ¯āĻ, āĻ¨āĻŋāĻ°ā§āĻĻā§āĻļāĻžāĻŦāĻ˛ā§ āĻ¸āĻ°ā§āĻŦāĻžāĻ§āĻŋāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°
COMMENT ON ...
āĻāĻ¨ā§āĻ¯ āĻŽāĻ¨ā§āĻ¤āĻŦā§āĻ¯ вŅĐĩŅ āĻŦāĻ¸ā§āĻ¤ā§, āĻŦā§āĻ¯āĻ¤āĻŋāĻā§āĻ°āĻŽ āĻāĻžāĻĄāĻŧāĻžāĨ¤
āĻ¸ā§āĻĨāĻžāĻ¨ā§āĻ¯āĻŧ āĻ¨āĻŋāĻ°ā§āĻā§āĻˇāĻž
āĻ¸āĻā§āĻāĻŋāĻ¤ āĻĢāĻžāĻāĻļāĻ¨ āĻāĻŦāĻ āĻāĻžāĻ°ā§āĻā§āĻ āĻā§āĻŦāĻŋāĻ˛ā§ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ˛āĻ āĻāĻ¨ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻāĻāĻŋ āĻ¸ā§āĻĨāĻžāĻ¨ā§āĻ¯āĻŧ āĻ āĻĄāĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧ, āĻ¯āĻžāĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻ āĻ¨ā§āĻ¯āĻžāĻ¨ā§āĻ¯ āĻāĻŋāĻ¨āĻŋāĻ¸āĻā§āĻ˛āĻŋāĻ° āĻŽāĻ§ā§āĻ¯ā§, āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ āĻ¸āĻāĻ¯ā§āĻā§āĻ° āĻŦāĻŋāĻļāĻĻ āĻŦāĻŋāĻŦāĻ°āĻŖ, āĻŦāĻ˛āĻž āĻŽāĻĄāĻŋāĻāĻ˛ā§āĻ° āĻ˛ā§āĻŦā§āĻ˛ āĻāĻŦāĻ āĻāĻ¨āĻĒā§āĻā§āĻ° āĻĒā§āĻ°āĻā§āĻ¤ āĻŽāĻžāĻ¨ āĻ āĻ¨ā§āĻ¤āĻ°ā§āĻā§āĻā§āĻ¤ āĻĨāĻžāĻā§āĨ¤ JSON āĻāĻāĻžāĻ°ā§ āĻāĻāĻāĻĒā§āĻ āĻĒāĻ°āĻžāĻŽāĻŋāĻ¤āĻŋāĨ¤
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĢāĻžāĻāĻļāĻ¨
āĻ˛āĻā§āĻˇā§āĻ¯ āĻā§āĻŦāĻŋāĻ˛ā§ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ āĻ˛āĻāĻŋāĻ āĻāĻ¨ā§āĻ¯ āĻĄāĻŋāĻāĻžāĻāĻ¨ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§. āĻ¤āĻžāĻ°āĻž āĻā§āĻ°āĻŋāĻāĻžāĻ° āĻĢāĻžāĻāĻļāĻ¨.
āĻā§āĻŽāĻĒā§āĻ˛ā§āĻ - āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĢāĻžāĻāĻļāĻ¨
---------------------------------------------------------
-- INSERT
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_insert AFTER INSERT ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_insert_log();
---------------------------------------------------------
-- UPDATE
CREATE OR REPLACE FUNCTION sys_functions.table_update_log ()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.column != NEW.column
THEN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column' , NEW.column ) );
END IF ;
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_update AFTER UPDATE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_update_log ();
---------------------------------------------------------
-- DELETE
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_delete AFTER DELETE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_delete_log ();
āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻĢāĻžāĻāĻļāĻ¨
āĻāĻžāĻ°ā§āĻā§āĻ āĻā§āĻŦāĻŋāĻ˛ā§ āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻāĻŦāĻ DML āĻ āĻĒāĻžāĻ°ā§āĻļāĻ¨ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĄāĻŋāĻāĻžāĻāĻ¨ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§āĨ¤
āĻā§āĻŽāĻĒā§āĻ˛ā§āĻ - āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻĢāĻžāĻāĻļāĻ¨
--INSERT
--RETURN id OF NEW ROW
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Generate new id
new_id = nextval('store.table.seq');
-- Insert into table
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--DELETE
--RETURN ROW NUMBERS DELETED
CREATE OR REPLACE FUNCTION service_functions.table_delete ( current_id integer )
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
DELETE FROM store.table WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- UPDATE DETAILS
-- RETURN ROW NUMBERS UPDATED
CREATE OR REPLACE FUNCTION service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
UPDATE store.table
SET
column = new_column
WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
āĻŦā§āĻ¯āĻŦāĻ¸āĻž āĻĢāĻžāĻāĻļāĻ¨
āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ āĻĻā§āĻŦāĻžāĻ°āĻž āĻŦāĻ˛āĻž āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻŦā§āĻ¯āĻŦāĻ¸āĻž āĻĢāĻžāĻāĻļāĻ¨ āĻāĻ¨ā§āĻ¯ āĻĄāĻŋāĻāĻžāĻāĻ¨. āĻ¤āĻžāĻ°āĻž āĻ¸āĻŦāĻ¸āĻŽāĻ¯āĻŧ āĻĢāĻŋāĻ°ā§ āĻāĻ¸ā§ - āĻ¤āĻžāĻĻā§āĻ°āĻā§ JSON. āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻ¤ā§āĻ°ā§āĻāĻŋāĻā§āĻ˛āĻŋ āĻāĻāĻāĻžāĻ¤ā§ āĻāĻŦāĻ āĻ˛āĻ āĻāĻ°āĻ¤ā§, āĻŦā§āĻ˛āĻāĻāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĻ¨ āĻāĻžāĻĄāĻŧ.
āĻā§āĻŽāĻĒā§āĻ˛ā§āĻ - āĻŦā§āĻ¯āĻŦāĻ¸āĻž āĻĢāĻžāĻāĻļāĻ¨
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Input parameters
)
RETURNS JSON AS $$
DECLARE
------------------------
--for exception catching
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
--LOGGING
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--IN Parameters
)
);
PERFORM business_functions.notice('business_function_template');
--START BUSINESS PART
--END BUSINESS PART
-- SUCCESFULLY RESULT
PERFORM business_functions.notice('result');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'FINISHED',
json_build_object( 'result',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- EXCEPTION CATCHING
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--IN Parameters
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = COLUMN_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-COLUMN_NAME',
json_build_object('COLUMN_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_DATATYPE_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(s
'business_function_template',
' ERROR-SCHEMA_NAME',json_build_object('SCHEMA_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_DETAIL ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_HINT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_CONTEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
RAISE WARNING 'ALARM: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
āĻĢāĻ˛āĻžāĻĢāĻ˛
āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻāĻŋāĻ¤ā§āĻ°āĻāĻŋ āĻŦāĻ°ā§āĻŖāĻ¨āĻž āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻŋ āĻŽāĻ¨ā§ āĻāĻ°āĻŋ āĻāĻāĻŋ āĻ¯āĻĨā§āĻˇā§āĻāĨ¤ āĻ¯āĻĻāĻŋ āĻā§āĻ āĻŦāĻŋāĻļāĻĻ āĻāĻŦāĻ āĻĢāĻ˛āĻžāĻĢāĻ˛ā§ āĻāĻā§āĻ°āĻšā§ āĻšāĻ¨, āĻŽāĻ¨ā§āĻ¤āĻŦā§āĻ¯ āĻ˛āĻŋāĻā§āĻ¨, āĻāĻŽāĻŋ āĻāĻŦāĻŋāĻ¤ā§ āĻ āĻ¤āĻŋāĻ°āĻŋāĻā§āĻ¤ āĻ¸ā§āĻĒāĻ°ā§āĻļ āĻ¯ā§āĻ āĻāĻ°āĻ¤ā§ āĻĒā§āĻ°ā§ āĻā§āĻļāĻŋ āĻšāĻŦāĨ¤
āĻĻā§āĻ°āĻˇā§āĻāĻŦā§āĻ¯
āĻāĻāĻāĻŋ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻ¤ā§āĻ°ā§āĻāĻŋ āĻ˛āĻāĻŋāĻ - āĻāĻ¨āĻĒā§āĻ āĻĒāĻ°āĻžāĻŽāĻŋāĻ¤āĻŋ āĻĒā§āĻ°āĻāĻžāĻ°
-[ RECORD 1 ]- date_trunc | 2020-08-19 13:15:46 id | 1072 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | STARTED jsonb_pretty | { | "dko": { | "id": 4, | "type": "Type1", | "title": "CREATED BY addKD", | "Weight": 10, | "Tr": "300", | "reduction": 10, | "isTrud": "TRUE", | "description": "decription", | "lowerTr": "100", | "measurement": "measurement1", | "methodology": "m1", | "passportUrl": "files", | "upperTr": "200", | "weightingFactor": 100.123, | "actualTrValue": null, | "upperTrCalcNumber": "120" | }, | "CardId": 3 | } -[ RECORD 2 ]- date_trunc | 2020-08-19 13:15:46 id | 1073 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR jsonb_pretty | { | "SQLSTATE": "22P02" | } -[ RECORD 3 ]- date_trunc | 2020-08-19 13:15:46 id | 1074 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR jsonb_pretty | { | "SQLERRM": "invalid input syntax for type numeric: "null"" | } -[ RECORD 4 ]- date_trunc | 2020-08-19 13:15:46 id | 1075 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-RETURNED_SQLSTATE jsonb_pretty | { | "RETURNED_SQLSTATE": "22P02" | } -[ RECORD 5 ]- date_trunc | 2020-08-19 13:15:46 id | 1076 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-COLUMN_NAME jsonb_pretty | { | "COLUMN_NAME": "" | } -[ RECORD 6 ]- date_trunc | 2020-08-19 13:15:46 id | 1077 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-CONSTRAINT_NAME jsonb_pretty | { | "CONSTRAINT_NAME": "" | } -[ RECORD 7 ]- date_trunc | 2020-08-19 13:15:46 id | 1078 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_DATATYPE_NAME jsonb_pretty | { | "PG_DATATYPE_NAME": "" | } -[ RECORD 8 ]- date_trunc | 2020-08-19 13:15:46 id | 1079 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: "null"" | } -[ RECORD 9 ]- date_trunc | 2020-08-19 13:15:46 id | 1080 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-SCHEMA_NAME jsonb_pretty | { | "SCHEMA_NAME": "" | } -[ RECORD 10 ]- date_trunc | 2020-08-19 13:15:46 id | 1081 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_DETAIL jsonb_pretty | { | "PG_EXCEPTION_DETAIL": "" | } -[ RECORD 11 ]- date_trunc | 2020-08-19 13:15:46 id | 1082 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_HINT jsonb_pretty | { | "PG_EXCEPTION_HINT": "" | } -[ RECORD 12 ]- date_trunc | 2020-08-19 13:15:46 id | 1083 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_CONTEXT jsonb_pretty | { usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: "null"" | }
āĻāĻ¤ā§āĻ¸: www.habr.com