ΠΠΎΡΡΠΈΠΊ Π·Π° ΠΏΠΈΡΡΠ²Π°ΡΠ΅ Π½Π° ΠΎΠ²Π°Π° ΡΠΊΠΈΡΠ° Π±Π΅ΡΠ΅ ΡΡΠ°ΡΠΈΡΠ°ΡΠ°
Π‘Π΅ ΡΠΈΠ½Π΅ΡΠ΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎ ΡΡΠΎ ΠΈΡΡΠ°ΡΠ° ΠΌΠΈΡΠ»Π° - "ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠΈΡΠ°ΡΡΠ΅ Π΄Π΅Π»ΠΎΠ²Π½Π° Π»ΠΎΠ³ΠΈΠΊΠ° Π²ΠΎ Π±Π°Π·Π°ΡΠ° Π½Π° ΠΏΠΎΠ΄Π°ΡΠΎΡΠΈ".
ΠΠ΅ ΠΌΠΈ ΠΏΠ°Π΄Π½Π° Π½Π° ΠΏΠ°ΠΌΠ΅Ρ ΡΠ°ΠΌΠΎ ΠΌΠ΅Π½Π΅.
ΠΡΡΠΎ ΡΠ°ΠΊΠ°, Π·Π° Π²ΠΎ ΠΈΠ΄Π½ΠΈΠ½Π°, ΡΠ°ΠΊΠ°Π² Π΄Π° Π³ΠΈ Π·Π°ΡΡΠ²Π°ΠΌ, ΠΏΡΠ΅Π΄ ΡΓ¨, Π·Π° ΡΠ΅Π±Π΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΈΡΠ΅ ΡΠ»ΡΡΡΠ²Π°ΡΠ° ΡΡΠΎ ΡΠ΅ ΠΏΠΎΡΠ°Π²ΠΈΡΠ° ΠΏΡΠΈ ΡΠΏΡΠΎΠ²Π΅Π΄ΡΠ²Π°ΡΠ΅ΡΠΎ. ΠΡΠΎΠ±Π΅Π½ΠΎ Π°ΠΊΠΎ ΡΠ΅ Π·Π΅ΠΌΠ΅ ΠΏΡΠ΅Π΄Π²ΠΈΠ΄ Π΄Π΅ΠΊΠ° ΡΠ΅Π»Π°ΡΠΈΠ²Π½ΠΎ Π½Π΅ΠΎΠ΄Π°ΠΌΠ½Π° Π±Π΅ΡΠ΅ Π΄ΠΎΠ½Π΅ΡΠ΅Π½Π° ΡΡΡΠ°ΡΠ΅ΡΠΊΠ° ΠΎΠ΄Π»ΡΠΊΠ° Π·Π° ΠΏΡΠΎΠΌΠ΅Π½Π° Π½Π° Π°ΡΡ ΠΈΡΠ΅ΠΊΡΡΡΠ°ΡΠ° ΠΈ ΡΡΠ°Π½ΡΡΠ΅Ρ Π½Π° Π΄Π΅Π»ΠΎΠ²Π½Π°ΡΠ° Π»ΠΎΠ³ΠΈΠΊΠ° Π½Π° Π½ΠΈΠ²ΠΎ Π½Π° Π·Π°Π΄Π½ΠΈΠ½Π°. Π’Π°ΠΊΠ° ΡΡΠΎ ΡΓ¨ ΡΡΠΎ Π΅ ΡΠ°Π·Π²ΠΈΠ΅Π½ΠΎ Π½Π°ΡΠΊΠΎΡΠΎ Π½ΠΈΠΊΠΎΠΌΡ Π½Π΅ΠΌΠ° Π΄Π° Π±ΠΈΠ΄Π΅ ΠΎΠ΄ ΠΊΠΎΡΠΈΡΡ ΠΈ Π½ΠΈΠΊΠΎΠ³ΠΎ Π½Π΅ΠΌΠ° Π΄Π° Π±ΠΈΠ΄Π΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎ.
ΠΠΏΠΈΡΠ°Π½ΠΈΡΠ΅ ΠΌΠ΅ΡΠΎΠ΄ΠΈ Π½Π΅ ΡΠ΅ Π½Π΅ΠΊΠ°ΠΊΠ²ΠΎ ΠΎΡΠΊΡΠΈΡΠΈΠ΅ ΠΈΠ»ΠΈ ΠΈΡΠΊΠ»ΡΡΠΈΡΠ΅Π»Π½ΠΈ. Π·Π½Π°ΠΌ ΠΊΠ°ΠΊΠΎ, ΡΠ΅ Π΅ ΠΊΠ»Π°ΡΠΈΡΠ½ΠΎ ΠΈ Π΅ ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠΈΡΠ°Π½ΠΎ Π½Π΅ΠΊΠΎΠ»ΠΊΡ ΠΏΠ°ΡΠΈ (Π½Π° ΠΏΡΠΈΠΌΠ΅Ρ, ΡΠ°Ρ ΠΊΠΎΡΠΈΡΡΠ΅Π² ΡΠ»ΠΈΡΠ΅Π½ ΠΏΡΠΈΡΡΠ°ΠΏ ΠΏΡΠ΅Π΄ 20 Π³ΠΎΠ΄ΠΈΠ½ΠΈ Π½Π° Oracle) Π‘Π°ΠΌΠΎ ΡΠ΅ΡΠΈΠ² Π΄Π° ΡΠΎΠ±Π΅ΡΠ°ΠΌ ΡΠ΅ Π½Π° Π΅Π΄Π½ΠΎ ΠΌΠ΅ΡΡΠΎ. ΠΠΎ ΡΠ»ΡΡΠ°Ρ Π½Π΅ΠΊΠΎΠΌΡ Π΄ΠΎΠ±ΡΠΎ Π΄Π° ΠΌΡ Π΄ΠΎΡΠ΄Π΅. ΠΠ°ΠΊΠΎ ΡΡΠΎ ΠΏΠΎΠΊΠ°ΠΆΠ° ΠΏΡΠ°ΠΊΡΠΈΠΊΠ°ΡΠ°, Π΄ΠΎΡΡΠ° ΡΠ΅ΡΡΠΎ ΠΈΡΡΠ° ΠΈΠ΄Π΅ΡΠ° Π΄ΠΎΠ°ΡΠ° Π΄ΠΎ ΡΠ°Π·Π»ΠΈΡΠ½ΠΈ Π»ΡΡΠ΅ Π½Π΅Π·Π°Π²ΠΈΡΠ½ΠΎ. Π ΠΊΠΎΡΠΈΡΠ½ΠΎ Π΅ Π΄Π° Π³ΠΎ ΡΡΠ²Π°ΡΠ΅ Π·Π° ΡΠ΅Π±Π΅ ΠΊΠ°ΠΊΠΎ ΡΡΠ²Π΅Π½ΠΈΡ.
Π‘Π΅ ΡΠ°Π·Π±ΠΈΡΠ°, Π½ΠΈΡΡΠΎ Π½Π° ΠΎΠ²ΠΎΡ ΡΠ²Π΅Ρ Π½Π΅ Π΅ ΡΠΎΠ²ΡΡΠ΅Π½ΠΎ, Π³ΡΠ΅ΡΠΊΠΈ ΠΈ ΠΏΠ΅ΡΠ°ΡΠ½ΠΈ Π³ΡΠ΅ΡΠΊΠΈ Π·Π° ΠΆΠ°Π» ΡΠ΅ ΠΌΠΎΠΆΠ½ΠΈ. ΠΡΠΈΡΠΈΠΊΠΈΡΠ΅ ΠΈ ΠΊΠΎΠΌΠ΅Π½ΡΠ°ΡΠΈΡΠ΅ ΡΠ΅ ΡΠΈΠ»Π½ΠΎ Π΄ΠΎΠ±ΡΠ΅Π΄ΠΎΡΠ΄Π΅Π½ΠΈ ΠΈ ΠΎΡΠ΅ΠΊΡΠ²Π°Π½ΠΈ, Π° ΡΡΡΠ΅ Π΅Π΄Π΅Π½ ΠΌΠ°Π» Π΄Π΅ΡΠ°Ρ - ΠΊΠΎΠ½ΠΊΡΠ΅ΡΠ½ΠΈ Π΄Π΅ΡΠ°Π»ΠΈ Π·Π° ΠΈΠΌΠΏΠ»Π΅ΠΌΠ΅Π½ΡΠ°ΡΠΈΡΠ°ΡΠ° ΡΠ΅ ΠΈΡΠΏΡΡΡΠ΅Π½ΠΈ. Π‘Π΅ΠΏΠ°ΠΊ, ΡΓ¨ ΡΡΡΠ΅ ΡΠ΅ ΠΊΠΎΡΠΈΡΡΠΈ Π²ΠΎ Π²ΠΈΡΡΠΈΠ½ΡΠΊΠΈ ΡΠ°Π±ΠΎΡΠ΅Π½ ΠΏΡΠΎΠ΅ΠΊΡ. ΠΠ½Π°ΡΠΈ, Π½Π°ΠΏΠΈΡΠΎΡ Π΅ ΡΠ°ΠΌΠΎ ΡΠΊΠΈΡΠ° ΠΈ ΠΎΠΏΠΈΡ Π½Π° ΠΎΠΏΡΡΠΈΠΎΡ ΠΊΠΎΠ½ΡΠ΅ΠΏΡ, Π½ΠΈΡΡΠΎ ΠΏΠΎΠ²Π΅ΡΠ΅. Π‘Π΅ Π½Π°Π΄Π΅Π²Π°ΠΌ Π΄Π΅ΠΊΠ° ΠΈΠΌΠ° Π΄ΠΎΠ²ΠΎΠ»Π½ΠΎ Π΄Π΅ΡΠ°Π»ΠΈ Π·Π° Π΄Π° ΡΠ΅ ΡΠ°Π·Π±Π΅ΡΠ΅ ΡΠ΅Π»ΠΎΠΊΡΠΏΠ½Π°ΡΠ° ΡΠ»ΠΈΠΊΠ°.
ΠΠΏΡΡΠ°ΡΠ° ΠΈΠ΄Π΅ΡΠ° Π΅ βΡΠ°Π·Π΄Π΅Π»ΠΈ ΠΈ Π²Π»Π°Π΄Π΅Ρ, ΡΠΊΡΠΈΡ ΠΈ ΠΏΠΎΡΠ΅Π΄ΡΠ²Π°Ρβ
ΠΠ΄Π΅ΡΠ°ΡΠ° Π΅ ΠΊΠ»Π°ΡΠΈΡΠ½Π° - ΠΏΠΎΡΠ΅Π±Π½Π° ΡΠ΅ΠΌΠ° Π·Π° ΡΠ°Π±Π΅Π»ΠΈ, ΠΏΠΎΡΠ΅Π±Π½Π° ΡΠ΅ΠΌΠ° Π·Π° ΡΠΊΠ»Π°Π΄ΠΈΡΠ°Π½ΠΈ ΡΡΠ½ΠΊΡΠΈΠΈ.
ΠΠ»ΠΈΠ΅Π½ΡΠΎΡ Π½Π΅ΠΌΠ° Π΄ΠΈΡΠ΅ΠΊΡΠ½ΠΎ ΠΏΡΠΈΡΡΠ°ΠΏ Π΄ΠΎ ΠΏΠΎΠ΄Π°ΡΠΎΡΠΈΡΠ΅. Π‘Γ¨ ΡΡΠΎ ΠΊΠ»ΠΈΠ΅Π½ΡΠΎΡ ΠΌΠΎΠΆΠ΅ Π΄Π° Π½Π°ΠΏΡΠ°Π²ΠΈ Π΅ Π΄Π° ΡΠ° ΠΏΠΎΠ²ΠΈΠΊΠ° Π·Π°ΡΡΠ²Π°Π½Π°ΡΠ° ΡΡΠ½ΠΊΡΠΈΡΠ° ΠΈ Π΄Π° Π³ΠΎ ΠΎΠ±ΡΠ°Π±ΠΎΡΠΈ Π΄ΠΎΠ±ΠΈΠ΅Π½ΠΈΠΎΡ ΠΎΠ΄Π³ΠΎΠ²ΠΎΡ.
Π£Π»ΠΎΠ³ΠΈ
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;
ΠΡΠ°Π²Π° Π½Π° ΠΏΡΠΈΡΡΠ°ΠΏ
Π£Π»ΠΎΠ³Π° - ΠΠ΅Π»ΠΈΠΊΠ° ΠΡΠΈΡΠ°Π½ΠΈΡΠ° ΠΈΠΌΠ° ΡΠ΅Π»ΠΎΡΠ΅Π½ ΠΏΡΠΈΡΡΠ°ΠΏ Π΄ΠΎ ΡΠΈΡΠ΅ ΡΠ΅ΠΌΠΈ (ΠΎΠ΄Π΄Π΅Π»Π΅Π½ΠΈ ΠΎΠ΄ ΡΠ»ΠΎΠ³Π°ΡΠ° Π½Π° ΡΠΎΠΏΡΡΠ²Π΅Π½ΠΈΠΊ Π½Π° DB).
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 ...
ΠΠΎΠΌΠ΅Π½ΡΠ°ΡΠΈ Π·Π° Π‘ΠΈΡΠ΅ ΠΏΡΠ΅Π΄ΠΌΠ΅ΡΠΈ, Π±Π΅Π· ΠΈΡΠΊΠ»ΡΡΠΎΠΊ.
ΠΠΎΠΊΠ°Π»Π½Π° ΡΠ΅Π²ΠΈΠ·ΠΈΡΠ°
ΠΠ° Π΄Π° ΡΠ΅ Π΅Π²ΠΈΠ΄Π΅Π½ΡΠΈΡΠ° ΠΈΠ·Π²ΡΡΡΠ²Π°ΡΠ΅ΡΠΎ Π½Π° Π·Π°ΡΡΠ²Π°Π½ΠΈΡΠ΅ ΡΡΠ½ΠΊΡΠΈΠΈ ΠΈ ΠΏΡΠΎΠΌΠ΅Π½ΠΈΡΠ΅ Π½Π° ΡΠ΅Π»Π½ΠΈΡΠ΅ ΡΠ°Π±Π΅Π»ΠΈ, ΡΠ΅ ΠΊΠΎΡΠΈΡΡΠΈ Π»ΠΎΠΊΠ°Π»Π½Π° ΡΠ΅Π²ΠΈΠ·ΠΎΡΡΠΊΠ° ΡΠ°Π±Π΅Π»Π°, ΠΊΠΎΡΠ°, ΠΌΠ΅ΡΡ Π΄ΡΡΠ³ΠΎΡΠΎ, Π²ΠΊΠ»ΡΡΡΠ²Π° Π΄Π΅ΡΠ°Π»ΠΈ Π·Π° Π²ΡΡΠΊΠ°ΡΠ° ΡΠΎ ΠΊΠ»ΠΈΠ΅Π½ΡΠΎΡ, ΠΎΠ·Π½Π°ΠΊΠ°ΡΠ° Π½Π° ΠΏΠΎΠ²ΠΈΠΊΠ°Π½ΠΈΠΎΡ ΠΌΠΎΠ΄ΡΠ» ΠΈ Π²ΠΈΡΡΠΈΠ½ΡΠΊΠΈΡΠ΅ Π²ΡΠ΅Π΄Π½ΠΎΡΡΠΈ Π½Π° Π²Π»Π΅Π·ΠΎΡ ΠΈ ΠΈΠ·Π»Π΅Π·Π½ΠΈ ΠΏΠ°ΡΠ°ΠΌΠ΅ΡΡΠΈ Π²ΠΎ ΡΠΎΡΠΌΠ° Π½Π° 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;
ΠΠΊΡΠΏΠ½ΠΎ
ΠΠ° Π΄Π° ΡΠ° ΠΎΠΏΠΈΡΠ°ΠΌ ΠΎΠΏΡΡΠ°ΡΠ° ΡΠ»ΠΈΠΊΠ°, ΠΌΠΈΡΠ»Π°ΠΌ Π΄Π΅ΠΊΠ° Π΅ ΡΠΎΡΠ΅ΠΌΠ° Π΄ΠΎΠ²ΠΎΠ»Π½ΠΎ. ΠΠΊΠΎ Π½Π΅ΠΊΠΎΡ Π΅ Π·Π°ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠΈΡΠ°Π½ Π·Π° Π΄Π΅ΡΠ°Π»ΠΈ ΠΈ ΡΠ΅Π·ΡΠ»ΡΠ°ΡΠΈ, ΠΏΠΈΡΠ΅ΡΠ΅ ΠΊΠΎΠΌΠ΅Π½ΡΠ°ΡΠΈ, ΡΠΎ Π·Π°Π΄ΠΎΠ²ΠΎΠ»ΡΡΠ²ΠΎ ΡΠ΅ Π΄ΠΎΠ΄Π°Π΄Π°ΠΌ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡΠ΅Π»Π½ΠΈ Π΄ΠΎΠΏΠΈΡΠΈ Π½Π° ΡΠ»ΠΈΠΊΠ°ΡΠ°.
PS
ΠΡΠΈΡΠ°Π²ΡΠ²Π°ΡΠ΅ Π΅Π΄Π½ΠΎΡΡΠ°Π²Π½Π° Π³ΡΠ΅ΡΠΊΠ° - ΡΠΈΠΏ Π½Π° Π²Π»Π΅Π·Π΅Π½ ΠΏΠ°ΡΠ°ΠΌΠ΅ΡΠ°Ρ
-[ 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