Membatasi Hak Akses User MySQL Pada Level Basis Data, Tabel, Kolom dan Baris

Pembatasan hak akses merupakan salah satu faktor keamanan di dalam basis data. Pembatasan pada basis data MySQL dapat dilakukan pada level basis data/skema, tabel, kolom dan baris.

Daftar Isi

Pembatasan Hak Akses Pada MySQL

Simulasi

≡

Pembatasan Hak Akses Pada MySQL

GRANT, CREATE VIEW, CREATE TRIGGER BEFORE INSERT, CREATE TRIGGER BEFORE UPDATE, USER() dan SIGNAL dapat digunakan untuk membatasi hak akses pengguna MySQL pada level basis data, tabel, kolom dan baris.
— Membatasi Hak Akses User MySQL Pada Level Basis Data, Tabel, Kolom dan Baris

https://bptsi.unisayogya.ac.id/membatasi-hak-akses-user-mysql-pada-level-basis-data-tabel-kolom-dan-baris/ 2022-12-11 00:03:44

CREATE SCHEMA `db` ;

CREATE TABLE `db`.`pembayaran` (
  `idpembayaran` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `kodebilling` CHAR(9) NULL,
  `nama` VARCHAR(50) NULL,
  `statustagihan` ENUM('tagihan', 'lunas', 'dispensasi') NULL,
  `channel` VARCHAR(45) NULL,
  `noreferensi` VARCHAR(45) NULL,
  `norekening` ENUM('giro1', 'giro2', 'giro3') NULL,
  `pengguna` ENUM('john', 'paimo') NULL,
  `nominal` INT UNSIGNED NULL,
  PRIMARY KEY (`idpembayaran`),
  INDEX `idx_statustagihan` (`statustagihan` ASC),
  INDEX `idx_norekening` (`norekening` ASC),
  INDEX `idx_pengguna` (`pengguna` ASC));

CREATE TABLE `db`.`informasi` (
  `idinformasi` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `informasi` TEXT NULL,
  PRIMARY KEY (`idinformasi`));

User dan Hak Akses MySQL

Pengguna yang diperbolehkan untuk mengakses suatu basis data sebaiknya berbeda-beda untuk setiap aplikasi atau sistem informasi, misalnya user basis data untuk Sistem Informasi A berbeda dengan user basis data untuk aplikasi mobile B.

Adapun sintaks yang umum digunakan untuk membuat user di MySQL adalah sebagai berikut

#CREATE USER `<namauser>`@`<host>` IDENTIFIED BY `<sandi>`;
#contoh:
CREATE USER `john`@`1.2.3.4` IDENTIFIED BY "1234567890!@#$ABdef";
CREATE USER `paimo`@`1.2.3.4` IDENTIFIED BY "!@#$ABdef123456";
CREATE USER `admin_`@`1.2.3.4` IDENTIFIED BY "ABdef!@#$12345678";
FLUSH PRIVILEGES;

Hal terpenting yang harus dilakukan adalah menghindari menggunakan % sebagai host. Sekali lagi, harus dihindari.

sedangkan user dapat diberi hak akses untuk melakukan semua (ALL) atau kombinasi dari hak akses berikut ini

#hak akses melakukan semua
ALL
#hak akses
ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES, PROCESS, PROXY, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGE

Pembatasan Hak Akses Pada Level Basis Data, Tabel dan Kolom

Secara umum, sintaks pembatasan hak akses adalah sebagai berikut

#level basis data dan tabel
#GRANT <hakakses> ON `<basisdata>`.`<tabel>` TO `<namauser>`@`<host>`;
#contoh:
#pembatasan level basis data
GRANT ALL ON `db`.* TO `admin_`@`1.2.3.4`;
#pembatasan level tabel
GRANT SELECT ON `db`.`informasi` TO `john`@`1.2.3.4`, `paimo`@`1.2.3.4`;

#tidak disarankan
#GRANT ALL ON *.* TO `admin_`@`1.2.3.4`;

#level kolom
#GRANT <hakakses> (`<kolom>`) ON `<basisdata>`.`<tabel>` TO `<namauser>`@`<host>`;
#contoh:
GRANT SELECT (kodebilling, nama, statustagihan, channel, noreferensi, nominal, norekening), UPDATE (statustagihan, channel, noreferensi) ON `db`.`pembayaran` TO `john`@`1.2.3.4`, `paimo`@`1.2.3.4`;

FLUSH PRIVILEGES;

Hal terpenting yang harus dilakukan adalah menghindari menggunakan GRANT ALL ON *.* karena user dapat melihat semua isi basis data. Sekali lagi, harus dihindari.

GRANT ALL ON db.* TO admin_@1.2.3.4; maknanya bahwa user admin_@1.2.3.4 diberi semua hak akses pada semua tabel di basis data db, sehingga admin_ memiliki kuasa penuh pada semua data yang ada di basis data tersebut.

GRANT EXECUTE ON db.pembayaran TO john@1.2.3.4, paimo@1.2.3.4; maknanya bahwa john@1.2.3.4 dan paimo@1.2.3.4 hanya diberi hak akses untuk menjalankan stored procedure (EXECUTE) pada tabel pembayaran di basis data db. Kedua user tersebut, john dan paimo, tidak dapat mengakses tabel lain di basis data tersebut maupun basis data lainnya.

GRANT SELECT (kodebilling, nama, statustagihan, channel, noreferensi), UPDATE (kodebank, statustagihan, channel, noreferensi) ON db.pembayaran TO john@1.2.3.4, paimo@1.2.3.4; maknanya bahwa john@1.2.3.4 dan paimo@1.2.3.4 diberi tambahan hak akses untuk:

melihat data (SELECT) hanya pada kolom kodebilling, nama, statustagihan, channel dan noreferensi saja
melakukan pembaruan data (UPDATE) hanya pada kolom kodebank, statustagihan, channel dan noreferensi saja

Pembatasan Pada Level Baris

Fungsi user() berguna untuk mendapatkan informasi siapakah pengguna yang sedang menjalankan perintah. Pembatasan pada level baris di MySQL dapat diakali dengan menggunakan view & trigger (CRUD) dan/atau signal (CUD), di mana:

C = create (insert)
R = read (select, call)
U = update
D = delete

Pemahaman Awal

Sebelumnya, pahami dulu REGEXP dan SUBSTRING_INDEX.

REGEXP akan mengembalikan nilai true apabila ada bagian dari string yang cocok dengan regular expression (regex) yang disediakan. Perhatikan contoh di bawah ini:

select "user@1.2.3.4" regexp "^(user|pengguna)@"; #-> true
select "pengguna@1.2.3.4" regexp "^(user|pengguna)@"; #-> true
select "puser@1.2.3.4" regexp "^(user|pengguna)@"; #-> false
select "users@1.2.3.4" regexp "^(user|pengguna)@"; #-> false

"^(user|pengguna)@" bermakna bahwa teks harus diawali dengan salah satu dari user atau pengguna dan dilanjutkan dengan @.

user@1.2.3.4 bernilai true karena diawali dengan user dan dilanjutkan dengan @, sedangkan pengguna@1.2.3.4 bernilai true karena diawali dengan pengguna dan dilanjutkan dengan @.

puser@1.2.3.4 bernilai false karena diawali dengan puser, bukan salah satu dari user atau pengguna.

users@1.2.3.4 bernilai false karena diawali dengan user, tetapi setelah itu dilanjutkan dengan s, bukan @.

SUBSTRING_INDEX akan mengembalikan bagian string sesuai dengan urutan pemisahan berdasarkan tanda pemisah. Perhatikan contoh di bawah ini:

select substring_index("user@1.2.3.4", "@", 1); #-> user
select substring_index("user@1.2.3.4", "@", 2); #-> 1.2.3.4

substring_index("user@1.2.3.4", "@", ...) akan menyebabkan string user@1.2.3.4 dipisah berdasarkan pemisah @ ke dalam

user (urutan 1)
1.2.3.4 (urutan 2)

Implementasi Pembatasan Level Baris

Di bawah ini adalah sintaks minimal untuk membatasi hak akses dalam level baris dengan view dan trigger

#DELIMITER $$

#CREATE FUNCTION `<basisdata>`.`<namafungsifiltertambahan>`(`<pengguna>_` VARCHAR(100), `<filtertambahan>_` VARCHAR(100), `<isnot>_` BOOLEAN) RETURNS BOOLEAN
#BEGIN
#  jika tidak ada pengaturan tambahan, maka gunakan RETURN NOT `<isnot>_`;
#  RETURN NOT `<isnot>_` AND ((`<pengguna>_` = "<pengguna>" AND `<filtertambahan>_` regexp "<rule1>") OR (`<pengguna>_` = "<pengguna2>" AND `<filtertambahan>_` regexp "<rule2>"));
#END $$

#CREATE ALGORITHM=MERGE VIEW `<basisdata>`.`<namaview>` AS SELECT * FROM `<basisdata>`.`<tabel>` WHERE ((`<kolomuser>`=substring_index(user(), '@', 1) OR isnull(`<kolomuser>`) AND `<basisdata>`.`<namafungsifiltertambahan>`(substring_index(user(), '@', 1), `<kolompengaturantambahan>`, false)))$$ #dapat ditambahkan dengan filter lain

#CREATE TRIGGER `<basisdata>`.`<namatrigger_beforeinsert>`
BEFORE INSERT ON `<basisdata>`.`<tabel>` FOR EACH ROW
#BEGIN
#  DECLARE user_ VARCHAR(100);
#  DECLARE forbid_ CONDITION FOR SQLSTATE "45000";
#  SET user_ = substring_index(user(), '@', 1);
#  IF user_ <> 'admin_' THEN
#    IF isnull(NEW.`<kolomuser>`) THEN
#      SET NEW.`<kolomuser>`=user_;
#    ELSEIF OLD.`<kolomuser>`<>user_ THEN
#      SET NEW.`<kolomuser>`=OLD.`<kolomuser>`;
#      #mungkin perlu kondisi tambahan
#      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    IF `<basisdata>`.`<namafungsifiltertambahan>`(user_, NEW.`<kolomfiltertambahan>`, true) THEN
#      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    ...
#  END IF;
#END$$

#CREATE TRIGGER `<basisdata>`.`<namatrigger_beforeupdate>`
BEFORE UPDATE ON `<basisdata>`.`<tabel>` FOR EACH ROW
#BEGIN
#  DECLARE user_ VARCHAR(100);
#  DECLARE forbid_ CONDITION FOR SQLSTATE "45000";
#  SET user_ = substring_index(user(), '@', 1);
#  IF user_ <> 'admin_' THEN
#    IF isnull(NEW.`<kolomuser>`) THEN
#      SET NEW.`<kolomuser>`=user_;
#    ELSEIF OLD.`<kolomuser>`<>user_ THEN
#      SET NEW.`<kolomuser>`=OLD.`<kolomuser>`;
#      #mungkin perlu kondisi tambahan
#      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    IF `<basisdata>`.`<namafungsifiltertambahan>`(user_, NEW.`<kolomfiltertambahan>`, true) THEN
#    SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    ...
#  END IF;
#END$$

#CREATE TRIGGER `<basisdata>`.`<namatrigger_beforedelete>`
BEFORE DELETE ON `<basisdata>`.`<tabel>` FOR EACH ROW
#BEGIN
#  DECLARE user_ VARCHAR(100);
#  DECLARE forbid_ CONDITION FOR SQLSTATE "45000";
#  SET user_ = substring_index(user(), '@', 1);
#  IF user_ <> 'admin_' THEN
#    IF OLD.`<kolomuser>`<>user_ THEN
#      #mungkin perlu kondisi tambahan
#      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    IF `<basisdata>`.`<namafungsifiltertambahan>`(user_, NEW.`<kolomfiltertambahan>`, true) THEN
#      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
#    END IF;
#    ...
#  END IF;
#END$$

#hapus privileges dari `<basisdata>`.`<tabel>`
#REVOKE <hakakses> ON `<basisdata>`.`<tabel>` FROM `<namauser>`@`<host>`; atau REVOKE <hakakses> (`<kolom>`) ON `<basisdata>`.`<tabel>` FROM `<namauser>`@`<host>`;
#tambahkan privileges dari `<basisdata>`.`<namaview>`
#REVOKE <hakakses> ON `<basisdata>`.`<namaview>` TO `<namauser>`@`<host>`; atau REVOKE <hakakses> (`<kolom>`) ON `<basisdata>`.`<namaview>` TO `<namauser>`@`<host>`;

FLUSH PRIVILEGES

Contoh di bawah ini tidak ada trigger before insert dan trigger before delete karena pengguna tidak diperkenankan untuk memasukkan (INSERT) dan menghapus (DELETE) data

DELIMITER $$

CREATE FUNCTION `db`.`cekhakaksesrekening`(`pengguna_` VARCHAR(100), `norekening_` VARCHAR(100), isnot_ BOOLEAN) RETURNS BOOLEAN
#BEGIN
#  jika tidak ada pengaturan tambahan, maka gunakan RETURN isnot_ TRUE;
  RETURN NOT isnot_ AND ((`pengguna_` = "john" AND `norekening_` regexp "(giro1|giro2)") OR (`pengguna_` = "paimo" AND `norekening_` regexp "(giro1|giro3)"));
END $$

CREATE ALGORITHM=MERGE VIEW `db`.`viewpembayaran` AS SELECT * FROM `db`.`pembayaran` WHERE (((`pengguna`=substring_index(user(), '@', 1) OR isnull(`pengguna`)) AND `db`.`cekhakaksesrekening`(substring_index(user(), '@', 1), `norekening`, false))) $$ #dapat ditambahkan dengan filter lain

#CREATE TRIGGER `db`.`pembayaran_beforeinsert` BEFORE INSERT ON `db`.`pembayaran` FOR EACH ROW tidak digunakan dalam contoh ini

CREATE TRIGGER `db`.`pembayaran_beforeupdate`
BEFORE UPDATE ON `db`.`pembayaran` FOR EACH ROW
BEGIN
  DECLARE user_ VARCHAR(100);
  DECLARE forbid_ CONDITION FOR SQLSTATE "45000";
  SET user_ = substring_index(user(), '@', 1);
  IF user_ <> 'admin_' THEN
    IF isnull(NEW.`pengguna`) THEN
      SET NEW.`pengguna`=user_;
    ELSEIF OLD.`pengguna` <> user_ THEN
      SET NEW.`pengguna`=OLD.`pengguna`;
      #mungkin perlu kondisi tambahan
      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
    END IF;
    IF `db`.`cekhakaksesrekening`(user_, NEW.`norekening`, true) THEN
      SIGNAL forbid_ SET MESSAGE_TEXT = 'An error occurred. Permission denied.', MYSQL_ERRNO = 1142;
    END IF;
  #  ...
  END IF;
END$$

#CREATE TRIGGER `db`.`pembayaran_beforedelete` BEFORE DELETE ON `db`.`pembayaran` FOR EACH ROW tidak digunakan dalam contoh ini

#hapus privileges dari `<basisdata>`.`<tabel>`
REVOKE SELECT (kodebilling, nama, statustagihan, channel, noreferensi, nominal, norekening), UPDATE (statustagihan, channel, noreferensi) ON `db`.`pembayaran` FROM `john`@`1.2.3.4`, `paimo`@`1.2.3.4`;
#tambahkan privileges dari `<basisdata>`.`<namaview>`
GRANT SELECT (kodebilling, nama, statustagihan, channel, noreferensi, norekening), UPDATE (statustagihan, channel, noreferensi) ON `db`.`viewpembayaran` TO `john`@`1.2.3.4`, `paimo`@`1.2.3.4`;

FLUSH PRIVILEGES

`pengguna`=substring_index(user(), '@', 1) dan isnull(pengguna) pada view viewpembayaran memungkinkan untuk pengguna mendapatkan data sesuai dengan data untuk dirinya dan data yang belum ada pemiliknya.

(`pengguna`= "john" AND `norekening` regexp "(giro1|giro2)") OR (`pengguna`= "paimo" AND `norekening` regexp "(giro1|giro3)")) memungkinkan pengguna john hanya dapat mengakses data dengan rekening giro1 atau giro2, sedangkan pengguna paimo hanya dapat mengakses data dengan rekening giro1 atau giro3.

IF isnull(NEW.`pengguna`) THEN ... dibuat untuk mengisi pengguna dengan nama pengguna yang aktif apabila belum kolom pengguna masih NULL. Apabila kolom pengguna sudah terisi, maka tidak dapat diganti oleh pengguna lain dan akan memicu pesan kesalahan.

Sebagai tambahan, apabila pengguna mengubah data yang tidak sesuai rekeningnya, maka akan memicu pesan kesalahan juga.