ΠΠΎΠ΄ΡΡΡΠ΅ΠΊ Π·Π° ΠΏΠΈΡΠ°ΡΠ΅ ΠΎΠ²Π΅ ΡΠΊΠΈΡΠ΅ Π±ΠΈΠΎ ΡΠ΅ ΡΠ»Π°Π½Π°ΠΊ
Π§ΠΈΠ½ΠΈΠ»ΠΎ ΡΠ΅ Π·Π°Π½ΠΈΠΌΡΠΈΠ²ΠΈΠΌ Π΄Π° ΠΈΡΡΠ° ΠΌΠΈΡΠ°ΠΎ - "ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠΈΡΠ°ΡΠΈ ΠΏΠΎΡΠ»ΠΎΠ²Π½Ρ Π»ΠΎΠ³ΠΈΠΊΡ Ρ Π±Π°Π·ΠΈ ΠΏΠΎΠ΄Π°ΡΠ°ΠΊΠ°".
ΠΠΈΡΠ°ΠΌ ΡΠ°ΠΌΠΎ ΠΌΠ΅Π½ΠΈ ΠΏΠ°ΠΎ Π½Π° ΠΏΠ°ΠΌΠ΅Ρ.
Π’Π°ΠΊΠΎΡΠ΅, Π·Π° Π±ΡΠ΄ΡΡΠ½ΠΎΡΡ ΡΠ°ΠΌ ΠΆΠ΅Π»Π΅ΠΎ Π΄Π° ΡΠ°ΡΡΠ²Π°ΠΌ, ΠΏΡΠ΅ ΡΠ²Π΅Π³Π°, Π·Π° ΡΠ΅Π±Π΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ°Π½ΡΠ½Π΅ Π΄ΠΎΠ³Π°ΡΠ°ΡΠ΅ ΠΊΠΎΡΠΈ ΡΡ Π½Π°ΡΡΠ°Π»ΠΈ ΡΠΎΠΊΠΎΠΌ ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠ°ΡΠΈΡΠ΅. ΠΠΎΠ³ΠΎΡΠΎΠ²ΠΎ ΠΈΠΌΠ°ΡΡΡΠΈ Ρ Π²ΠΈΠ΄Ρ Π΄Π° ΡΠ΅ ΡΠ΅Π»Π°ΡΠΈΠ²Π½ΠΎ Π½Π΅Π΄Π°Π²Π½ΠΎ Π΄ΠΎΠ½Π΅ΡΠ° ΡΡΡΠ°ΡΠ΅ΡΠΊΠ° ΠΎΠ΄Π»ΡΠΊΠ° Π΄Π° ΡΠ΅ ΠΏΡΠΎΠΌΠ΅Π½ΠΈ Π°ΡΡ ΠΈΡΠ΅ΠΊΡΡΡΠ° ΠΈ ΠΏΠΎΡΠ»ΠΎΠ²Π½Π° Π»ΠΎΠ³ΠΈΠΊΠ° ΠΏΡΠ΅Π½Π΅ΡΠ΅ Π½Π° Π±Π°ΡΠΊΠ΅Π½Π΄ Π½ΠΈΠ²ΠΎ. Π’Π°ΠΊΠΎ Π΄Π° ΡΠ²Π΅ ΡΡΠΎ ΡΠ΅ ΡΠ°Π·Π²ΠΈΡΠ΅Π½ΠΎ ΡΡΠΊΠΎΡΠΎ Π½ΠΈΠΊΠΎΠΌΠ΅ Π½Π΅ΡΠ΅ ΠΊΠΎΡΠΈΡΡΠΈΡΠΈ ΠΈ Π½ΠΈΠΊΠΎΠ³Π° Π½Π΅ΡΠ΅ Π·Π°Π½ΠΈΠΌΠ°ΡΠΈ.
ΠΠΏΠΈΡΠ°Π½ΠΈ ΠΌΠ΅ΡΠΎΠ΄ΠΈ Π½ΠΈΡΡ Π½Π΅ΠΊΠ° Π²ΡΡΡΠ° ΠΎΡΠΊΡΠΈΡΠ° ΠΈΠ»ΠΈ ΠΈΠ·ΡΠ·Π΅ΡΠ½Π°. ΠΊΠ½ΠΎΠ² Ρ ΠΎΠ², ΡΠ²Π΅ ΡΠ΅ ΠΊΠ»Π°ΡΠΈΡΠ½ΠΎ ΠΈ ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠΈΡΠ°Π½ΠΎ ΡΠ΅ Π½Π΅ΠΊΠΎΠ»ΠΈΠΊΠΎ ΠΏΡΡΠ° (Π½Π° ΠΏΡΠΈΠΌΠ΅Ρ, ΠΊΠΎΡΠΈΡΡΠΈΠΎ ΡΠ°ΠΌ ΡΠ»ΠΈΡΠ°Π½ ΠΏΡΠΈΡΡΡΠΏ ΠΏΡΠ΅ 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;
ΠΠΈΡΠ°Π³ΡΠ°ΠΌ ΡΠ΅ΡΠ²ΠΈΡΠ½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅
Π€ΡΠ½ΠΊΡΠΈΡΠ΅ Π·Π° ΡΠ΅ΡΠ²ΠΈΡ ΠΈ ΠΠΠ ΡΡΠ½ΠΊΡΠΈΡΠ΅.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
ΠΠΈΡΠ°Π³ΡΠ°ΠΌ ΠΏΠΎΡΠ»ΠΎΠ²Π½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅
Π€ΡΠ½ΠΊΡΠΈΡΠ΅ Π·Π° Π·Π°Π²ΡΡΠ½Π΅ ΠΏΠΎΡΠ»ΠΎΠ²Π½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅ ΠΊΠΎΡΠ΅ ΠΏΠΎΠ·ΠΈΠ²Π° ΠΊΠ»ΠΈΡΠ΅Π½Ρ.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
ΠΡΠ°Π²Π° ΠΏΡΠΈΡΡΡΠΏΠ°
Π£Π»ΠΎΠ³Π° - ΠΠΠ ΠΈΠΌΠ° ΠΏΡΠ½ ΠΏΡΠΈΡΡΡΠΏ ΡΠ²ΠΈΠΌ ΡΠ΅ΠΌΠ°ΠΌΠ° (ΠΎΠ΄Π²ΠΎΡΠ΅Π½ΠΎ ΠΎΠ΄ ΡΠ»ΠΎΠ³Π΅ Π²Π»Π°ΡΠ½ΠΈΠΊΠ° ΠΠ-Π°).
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;
Π£Π»ΠΎΠ³Π° - ΠΠΠ ΠΠ‘ΠΠΠ ΠΈΠΌΠ° ΠΏΡΠΈΠ²ΠΈΠ»Π΅Π³ΠΈΡΡ ΠΠΠΠ Π¨ΠΠ’Π Ρ Π΄ΠΈΡΠ°Π³ΡΠ°ΠΌΡ ΠΏΠΎΡΠ»ΠΎΠ²Π½Π΅_ΡΡΠ½ΠΊΡΠΈΡΠ΅.
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 ...
ΠΠΎΠΌΠ΅Π½ΡΠ°ΡΠΈ Π·Π° ΡΠ²Π΅ ΠΎΠ±ΡΠ΅ΠΊΠ°ΡΠ°, Π±Π΅Π· ΠΈΠ·ΡΠ·Π΅ΡΠΊΠ°.
ΠΠΎΠΊΠ°Π»Π½Π° ΡΠ΅Π²ΠΈΠ·ΠΈΡΠ°
ΠΠ° Π΅Π²ΠΈΠ΄Π΅Π½ΡΠΈΡΠ°ΡΠ΅ ΠΈΠ·Π²ΡΡΠ΅ΡΠ° ΡΡΠΊΠ»Π°Π΄ΠΈΡΡΠ΅Π½ΠΈΡ ΡΡΠ½ΠΊΡΠΈΡΠ° ΠΈ ΠΏΡΠΎΠΌΠ΅Π½Π° ΡΠΈΡΠ½ΠΈΡ ΡΠ°Π±Π΅Π»Π° ΠΊΠΎΡΠΈΡΡΠΈ ΡΠ΅ Π»ΠΎΠΊΠ°Π»Π½Π° ΡΠ°Π±Π΅Π»Π° ΡΠ΅Π²ΠΈΠ·ΠΈΡΠ΅ ΠΊΠΎΡΠ° ΡΠΊΡΡΡΡΡΠ΅, ΠΈΠ·ΠΌΠ΅ΡΡ ΠΎΡΡΠ°Π»ΠΎΠ³, Π΄Π΅ΡΠ°ΡΠ΅ ΠΎ Π²Π΅Π·ΠΈ ΡΠ° ΠΊΠ»ΠΈΡΠ΅Π½ΡΠΎΠΌ, ΠΎΠ·Π½Π°ΠΊΡ ΠΏΠΎΠ·Π²Π°Π½ΠΎΠ³ ΠΌΠΎΠ΄ΡΠ»Π° ΠΈ ΡΡΠ²Π°ΡΠ½Π΅ Π²ΡΠ΅Π΄Π½ΠΎΡΡΠΈ ΡΠ»Π°Π·Π° ΠΈ ΠΈΠ·Π»Π°Π·Π½ΠΈ ΠΏΠ°ΡΠ°ΠΌΠ΅ΡΡΠΈ Ρ ΠΎΠ±Π»ΠΈΠΊΡ ΠΠ‘ΠΠ-Π°.
Π€ΡΠ½ΠΊΡΠΈΡΠ΅ ΡΠΈΡΡΠ΅ΠΌΠ°
ΠΠΈΠ·Π°ΡΠ½ΠΈΡΠ°Π½ Π·Π° Π΅Π²ΠΈΠ΄Π΅Π½ΡΠΈΡΠ°ΡΠ΅ ΠΏΡΠΎΠΌΠ΅Π½Π° Ρ ΡΠΈΡΠ½ΠΈΠΌ ΡΠ°Π±Π΅Π»Π°ΠΌΠ°. ΠΠ½Π΅ ΡΡ ΡΡΠ½ΠΊΡΠΈΡΠ΅ ΠΎΠΊΠΈΠ΄Π°ΡΠ°.
Π¨Π°Π±Π»ΠΎΠ½ β ΡΠΈΡΡΠ΅ΠΌΡΠΊΠ° ΡΡΠ½ΠΊΡΠΈΡΠ°
---------------------------------------------------------
-- 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 ();
Π‘Π΅ΡΠ²ΠΈΡΠ½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅
ΠΠΈΠ·Π°ΡΠ½ΠΈΡΠ°Π½ Π·Π° ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠ°ΡΠΈΡΡ ΡΡΠ»ΡΠ³Π° ΠΈ ΠΠΠ ΠΎΠΏΠ΅ΡΠ°ΡΠΈΡΠ° Π½Π° ΡΠΈΡΠ½ΠΈΠΌ ΡΠ°Π±Π΅Π»Π°ΠΌΠ°.
Π¨Π°Π±Π»ΠΎΠ½ β ΡΠ΅ΡΠ²ΠΈΡΠ½Π° ΡΡΠ½ΠΊΡΠΈΡΠ°
--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;
ΠΠΎΡΠ»ΠΎΠ²Π½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅
ΠΠΈΠ·Π°ΡΠ½ΠΈΡΠ°Π½ Π·Π° Π·Π°Π²ΡΡΠ½Π΅ ΠΏΠΎΡΠ»ΠΎΠ²Π½Π΅ ΡΡΠ½ΠΊΡΠΈΡΠ΅ ΠΊΠΎΡΠ΅ ΠΏΠΎΠ·ΠΈΠ²Π° ΠΊΠ»ΠΈΡΠ΅Π½Ρ. Π£Π²Π΅ΠΊ ΡΠ΅ Π²ΡΠ°ΡΠ°ΡΡ - ΠΠ‘ΠΠ. ΠΠ° ΠΏΡΠ΅ΡΡΠ΅ΡΠ°ΡΠ΅ ΠΈ Π΅Π²ΠΈΠ΄Π΅Π½ΡΠΈΡΠ°ΡΠ΅ Π³ΡΠ΅ΡΠ°ΠΊΠ° Ρ ΠΈΠ·Π²ΡΡΠ΅ΡΡ, ΠΊΠΎΡΠΈΡΡΠΈΡΠ΅ Π±Π»ΠΎΠΊ ΠΠΠ£ΠΠΠ’ΠΠ.
Π¨Π°Π±Π»ΠΎΠ½ β ΠΏΠΎΡΠ»ΠΎΠ²Π½Π° ΡΡΠ½ΠΊΡΠΈΡΠ°
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"" | }
ΠΠ·Π²ΠΎΡ: Π²Π²Π².Ρ Π°Π±Ρ.ΡΠΎΠΌ