Resep “Jamu manjur” agar data Interbase lebih sakti

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

2 Comments to Resep “Jamu manjur” agar data Interbase lebih sakti

  1. Joko Rivai says:

    Ajiiibb jamunya….

Leave a Reply

Your email address will not be published.