Seperti yang telah kita ketahui bersama, Interbase dengan dukungan fitur keamanan yang baru yaitu Embedded User Authentication (EUA) yang telah ada sejak Interbase versi 7.5, sebenarnya data kita sudah cukup aman.
Namun mungkin kita belum puas dan belum merasa aman dengan yang ada, berikut ini ada sedikit resep agar data kita lebih aman dan lebih kebal dari tangan orang-orang yang “mungkin” jahil/iseng.
Seperti yang kita ketahui juga sebuah object (table, procedure dll) , hanya dapat dibaca atau dijalankan oleh SYSDBA, user yang membuat atau user lain yang diberi hak, namun tidak ada batasan atau larangan bagi user lain untuk membuat object baru.
Pada “ramuan jamu” kali ini kita akan membuat agar data Interbase kita lebih aman diantaranya membatasi hanya SYSDBA yang dapat membuat object (table, procedure, trigger, view, ganerator dll)Â baru, hanya SYSDBA dan user yang bersangkutan yang dapat melihat daftar user.
Pada postingan sebelumnya telah dibahas cara untuk menghapus source code pada procedure, trigger atau view.
Pada kesempatan kali ini kita bukan menghapus source codenya namun kita akan menyembunyikan daftar procedurenya sehingga user tidak tahu ada procedure apa aja dalam database.
Rasanya untuk uraian gak perlu panjang lebar langsung saja ini resepnya …
/*-------------------------------------------------------------*
* STANDAR SECURITY FOR INTERBASE PROJECT *
* BY IMAM CHALIMI FEBRUARY, 2010 *
* Last Update January 22, 2013 *
* 29/10/2012 - Prevent Other user create/delete generator *
* 22/01/2012 - Add DATA field for users *
*-------------------------------------------------------------*/
/*--------------------------------------------------------------
* Revoke all access on system tables from PUBLIC
*--------------------------------------------------------------
*/
REVOKE ALL ON RDB$CHARACTER_SETS FROM PUBLIC;
REVOKE ALL ON RDB$COLLATIONS FROM PUBLIC;
REVOKE ALL ON RDB$CHECK_CONSTRAINTS FROM PUBLIC;
REVOKE ALL ON RDB$DATABASE FROM PUBLIC;
REVOKE ALL ON RDB$DEPENDENCIES FROM PUBLIC;
REVOKE ALL ON RDB$EXCEPTIONS FROM PUBLIC;
REVOKE ALL ON RDB$FIELD_DIMENSIONS FROM PUBLIC;
REVOKE ALL ON RDB$FIELDS FROM PUBLIC;
REVOKE ALL ON RDB$FILES FROM PUBLIC;
REVOKE ALL ON RDB$FILTERS FROM PUBLIC;
REVOKE ALL ON RDB$FORMATS FROM PUBLIC;
REVOKE ALL ON RDB$FUNCTION_ARGUMENTS FROM PUBLIC;
REVOKE ALL ON RDB$FUNCTIONS FROM PUBLIC;
REVOKE ALL ON RDB$GENERATORS FROM PUBLIC;
REVOKE ALL ON RDB$INDEX_SEGMENTS FROM PUBLIC;
REVOKE ALL ON RDB$INDICES FROM PUBLIC;
REVOKE ALL ON RDB$LOG_FILES FROM PUBLIC;
REVOKE ALL ON RDB$PAGES FROM PUBLIC;
REVOKE ALL ON RDB$PROCEDURE_PARAMETERS FROM PUBLIC;
REVOKE ALL ON RDB$PROCEDURES FROM PUBLIC;
REVOKE ALL ON RDB$REF_CONSTRAINTS FROM PUBLIC;
REVOKE ALL ON RDB$RELATION_CONSTRAINTS FROM PUBLIC;
REVOKE ALL ON RDB$RELATION_FIELDS FROM PUBLIC;
REVOKE ALL ON RDB$RELATIONS FROM PUBLIC;
REVOKE ALL ON RDB$ROLES FROM PUBLIC;
REVOKE ALL ON RDB$SECURITY_CLASSES FROM PUBLIC;
REVOKE ALL ON RDB$TRANSACTIONS FROM PUBLIC;
REVOKE ALL ON RDB$TRIGGER_MESSAGES FROM PUBLIC;
REVOKE ALL ON RDB$TRIGGERS FROM PUBLIC;
REVOKE ALL ON RDB$TYPES FROM PUBLIC;
REVOKE ALL ON RDB$USER_PRIVILEGES FROM PUBLIC;
REVOKE ALL ON RDB$USERS FROM PUBLIC;
REVOKE ALL ON RDB$VIEW_RELATIONS FROM PUBLIC;
/*--------------------------------------------------------------
* Read only access on many system tables from PUBLIC
*--------------------------------------------------------------
*/
GRANT SELECT ON RDB$DATABASE TO PUBLIC;
GRANT SELECT ON RDB$CHARACTER_SETS TO PUBLIC;
GRANT SELECT ON RDB$FIELD_DIMENSIONS TO PUBLIC;
GRANT SELECT ON RDB$FIELDS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_FIELDS TO PUBLIC;
GRANT SELECT ON RDB$RELATIONS TO PUBLIC;
GRANT SELECT ON RDB$USER_PRIVILEGES TO PUBLIC;
GRANT SELECT ON RDB$PROCEDURE_PARAMETERS TO PUBLIC;
GRANT SELECT ON RDB$FILES TO PUBLIC;
GRANT SELECT ON RDB$LOG_FILES TO PUBLIC;
/* Required by IB-Expert */
GRANT SELECT ON RDB$DEPENDENCIES TO PUBLIC;
GRANT SELECT ON RDB$COLLATIONS TO PUBLIC;
GRANT SELECT ON RDB$INDEX_SEGMENTS TO PUBLIC;
GRANT SELECT ON RDB$RELATION_CONSTRAINTS TO PUBLIC;
GRANT SELECT ON RDB$INDICES TO PUBLIC;
GRANT SELECT ON RDB$REF_CONSTRAINTS TO PUBLIC;
/*--------------------------------------------------------------
* Allow Access on Temporary Table to Public
*--------------------------------------------------------------
*/
GRANT SELECT ON TMP$DATABASE TO PUBLIC;
GRANT SELECT ON TMP$ATTACHMENTS TO PUBLIC;
/*--------------------------------------------------------------
* Prevent public user to create new object
*--------------------------------------------------------------
*/
CREATE EXCEPTION EX_SYSDBA_ONLY 'Only SYSDBA can create/delete object';
SET TERM ^ ;
CREATE TRIGGER RDB$DATABASE_BU FOR RDB$DATABASE
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
IF (USER<>'SYSDBA') THEN
EXCEPTION EX_SYSDBA_ONLY;
END
^
CREATE TRIGGER RDB$PROCEDURES_BI FOR RDB$PROCEDURES
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (USER<>'SYSDBA') THEN
EXCEPTION EX_SYSDBA_ONLY;
END
^
CREATE TRIGGER RDB$TRIGGERS_BI FOR RDB$TRIGGERS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (USER<>'SYSDBA') THEN
EXCEPTION EX_SYSDBA_ONLY;
END
^
CREATE TRIGGER RDB$GENERATORS_BD FOR RDB$GENERATORS
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
IF (USER<>'SYSDBA') THEN
EXCEPTION EX_SYSDBA_ONLY;
END
^
CREATE TRIGGER RDB$GENERATORS_BI FOR RDB$GENERATORS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (USER<>'SYSDBA') THEN
EXCEPTION EX_SYSDBA_ONLY;
END
^
SET TERM ; ^
ALTER TABLE RDB$USERS
ADD DATA BLOB SUB_TYPE 1 SEGMENT SIZE 80;
CREATE VIEW RDB$VW_USERS
AS
SELECT *
FROM RDB$USERS
WHERE
USER='SYSDBA' OR
USER=RDB$USER_NAME;
GRANT SELECT ON RDB$VW_USERS TO PUBLIC;
GRANT UPDATE(RDB$FIRST_NAME,RDB$MIDDLE_NAME,RDB$LAST_NAME,DATA) ON RDB$VW_USERS TO PUBLIC;
COMMIT;
/* ---- */
Script SQLnya dapat didownload di https://www.box.com/s/0dxp1pf9ficxxf2rm5x6
Catatan:
Resep ini hanya (lebih) cocok untuk Interbase dengan fitur Embedded User Authentication (EUA) Â diaktifkan