Rabu, 27 Juli 2011

Hak Akses (Privileges) pada MySQL

Informasi hak akses disimpan pada tabel user, db, host, tables_priv, dan columns_priv pada database mysql (yaitu database bernama mysql). Server MySQL membaca isi dari tabel-tabel ini ketika mulai dijalankan dan pada waktu-waktu tertentu.
Privilege
Column
Context
select
Select_priv
tables
insert
Insert_priv
tables
update
Update_priv
tables
delete
Delete_priv
tables
index
Index_priv
tables
alter
Alter_priv
tables
create
Create_priv
databases, tables or indexes
drop
Drop_priv
databases or tables
grant
Grant_priv
databases or tables
references
References_priv
databases or tables
reload
Reload_priv
server administration
shutdown
Shutdown_priv
server administration
process
Process_priv
server administration
file
File_priv
file access on server
Privileges select, insert, update, dan delete mengijinkan anda untuk melakukan operasi pada baris-baris yang terdapat pada tabel dalam sebuah database.
Pernyataan SELECT memerlukan privilege select hanya jika benar-benar mengambil data dari tabel. Anda dapat mengeksekusi pernyataan SELECT tertentu bahkan tanpa hak akses untuk mengakses sembarang database di server. Sebagai contoh, anda dapat menggunakan program mysql klien sebagai kalkulator sederhana :
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
Privilege index mengijinkan anda untuk membuat atau menghapus index.
Privilege alter mengijinkan anda untuk menggunakan ALTER TABLE.
Privilege create dan drop mengijinkan anda untuk membuat database dan tabel baru, atau untuk menghapus (drop) database dan tabel yang sudah ada.
Ingat bahwa jika anda memberi privilege drop ke database mysql ke seorang user, user tersebut dapat menghapus database dimana hak akses MySQL disimpan.!
Privilege grant mengijinkan anda untuk memberikan user lain privilege yang dimiliki oleh anda sendiri.
Privilege file memberikan anda ijin untuk membaca dan menulis file di server menggunakan pernyataan LOAD DATA INFILE dan SELECT … INTO OUTFILE. User yang diberi privilege ini dapat membaca atau menulis sembarang file yang dapat dibaca atau ditulis oleh server MySQL.
Privilege sisanya digunakan untuk operasi administratif, yang dapat dilakukan menggunakan program mysqladmin. Tabel di bawah menunjukkan perintah mysqladmin yang diijinkan oleh privilaege administratif untuk anda eksekusi:
Privilege
Commands permitted to privilege holders
reload
reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables
shutdown
shutdown
process
processlist, kill
Perintah reload menyuruh server untuk membaca ulang tabel hak akses. Perintah refresh akan mem-flush semua tabel dan membuka dan menutup file log. flush-privileges adalah sinonim untuk reload.
Perintah shutdown akan mematikan server.
Perintah processlist akan menampilkan informasi mengenai threaad yang dieksekusi di server. Perintah kill akan menghentikan thread server.

Cara kerja sistem privilege
Sistem privilege MySQL memastikan bahwa user dapat melakukan hanya hal-hal yang diperbolehkan untuk mereka. Ketika anda connect ke server MySQL, identitas anda ditentukan oleh host tempat anda melakukan koneksi dan username yang anda ingin gunakan. Sistem memberi privilege sesuai dengan identitas anda dan apa yang ingin ada lakukan.
MySQL mempertimbangkan baik hostname dan username dalam mengidentifikasi karena mungkin ada alasan untuk menganggap bahwa username yang diberikan adalah milik orang yang sama dimanapun di Internet. Sebagai contoh, user bill yang connect dari whitehouse.gov tidak harus orang yang sama dengan user bill yang connect dari microsoft.com. MySQL menangani hal ini dengan mengijinkan anda un tuk menentukan user dari host yang berbeda yang mungkin namanya sama : anda dapat memberi bill sebuah himpunan privilege untuk koneksi dari whitehouse.gov, dan himpunan privilege yang lain untuk koneksi dari microsoft.com.
Kendali akses MySQL melibatkan dua tingkat:
·         Tingkat 1: server mengecek apakah anda diijinkan untuk connect ke server.
·         Tingkat 2: Dianggap anda dapat connect, server mengecek tiap permintaan yang anda jalankan untuk melihat apakah privilege anda cukup untuk menjalankannya. Contohnya, jika anda mencoba untuk memilih baris dari tabel dalam database atau menghapus sebuah tabel dari database, server memastikan bahwa anda memiliki privilege select untuk tabel tersebut atau privilege drop untuk database.
Server menggunakan tabel user, db dan host dalam database mysql pada kedua tingkat kendali akses.
Field-field pada tabel grant diberikan berikut:
Table name
user
db
host
Scope fields
Host
Host
Host

User
Db
Db

Password
User

Privilege fields
Select_priv
Select_priv
Select_priv

Insert_priv
Insert_priv
Insert_priv

Update_priv
Update_priv
Update_priv

Delete_priv
Delete_priv
Delete_priv

Index_priv
Index_priv
Index_priv

Alter_priv
Alter_priv
Alter_priv

Create_priv
Create_priv
Create_priv

Drop_priv
Drop_priv
Drop_priv

Grant_priv
Grant_priv
Grant_priv

References_priv



Reload_priv



Shutdown_priv



Process_priv



File_priv


Untuk tingkat kedua dari kendali akses (verifikasi permintaan), jika permintaan melibatkan tabel, ditambah dengan konsultasi ke tabel tables_priv dan columns_priv. Field-field dari tabel tersebut ditunjukkan berikut:
Table name
tables_priv
columns_priv
Scope fields
Host
Host

Db
Db

User
User

Table_name
Table_name


Column_name
Privilege fields
Table_priv
Column_priv

Column_priv

Other fields
Timestamp
Timestamp

Grantor

Tiap tabel grant memiliki field jangkauan dan field privilege.
Field jangkauan menentukan jangkauan dari tiap entry dalam tabel. Contohnya, jika sebuah entry tabel user dengan nilai Host dan User ‘thomas.loc.gov’ dan ‘bob’ akan digunakan untuk mengauttentifikasi koneksi yang dibuat ke server oleh bob dari host thomas.loc.gov.  Misalnya, ada entry di tabel db dengan field Host, User dan Db ‘thomas.loc.gov’, ’bob’ dan ‘reports’ akan digunakan ketika bob connect dari host thomas.loc.gov untuk mengakses database reports. Tabel tables_priv dan columns_priv berisi jangkauan fields yang mengindikasikan kombinasi tabel atau tabel/kolom dimana tiap entry berlaku. Untuk tujuan pengecekan akses, pembandingan nilai host adalah case insensitive. Nilai User, Password, Db dan Tabel_name adalah case sensitive. Nilai Column_name adalah case insensitive pada MySQL 3.22.12 atau selanjutnya.
Field privilege menunjukkan privilege yang diberikan oleh sebuah entry tabel, yaitu operasi apa yang dapat dilakukan. Server mengkombinasikan informasi dari bermacam tabel grant untuk membuat deskripsi komplet privilee user.
Field jangkauan adalah string, deklarasinya ditunjukkan di bawah, nilai default untuk tiap kolomnya adalah string kosong:
Field name
Type

Host
CHAR(60)

User
CHAR(16)

Password
CHAR(16)

Db
CHAR(64)
(CHAR(60) for the tables_priv and columns_priv tables)
Table_name
CHAR(60)

Column_name
CHAR(60)

Di dalam tabel user, db dan host, semua field privilege dideklarasikan sebagai ENUM(‘N’,’Y’) – masing-masing dapat memiliki nilai ‘N’ atau ‘Y’, dan nilai defaultnya adalah ‘N’.
Dalam tabel tables_priv dan columns_priv, field privilege dideklarasikan sebagai field himpunan:
Table name
Field name
Possible set elements
tables_priv
Table_priv
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_priv
Column_priv
'Select', 'Insert', 'Update', 'References'
columns_priv
Column_priv
'Select', 'Insert', 'Update', 'References'

Singkatnya, server menggunakan tabel grant sebagai berikut:
·         Field jangkauan tabel user menentukan aapakah koneksi yang diminta diijinkan atau ditolak. Untuk mengijinkan koneksi, sembarang privilege yang diberikan dalam tabel user menunjukkan privilege global user. Privilege ini berlaku untuk semua database di server.
·         Tabel db dan host digunakan bersama:
·         Field jangkauan tabel db menentukan user mana yang dapat mengakses database mana dari host mana. Field privilege menentukan operasi apa saja yang diijinkan.
·         Tabel host digunakan sebagai ekstensi dari tabel db jika anda ingin entry tabel db tertentu untuk berlaku bagi beberapa host. Contohnya, jika anda ingin user untuk bisa menggunakan database dari beberapa host di network anda, biarkan nilai Host kosong dalam entry tabel db bagi user, lalu isilah tabel host dengan entry dari host-host tersebut.
·         Tabel tables_priv dan columns_priv mirip dengan tabel db, tetapi untuk hal yang lebih detail: mereka berlaku untuk level tabel dan kolom dan bukannya pada level database.
Ingat bahwa privilege administratif (reload, shutdown, dll) hanya khusus ada di dalam tabel user. Hal ini karena operasi administratif adalah operasi di server itu sendiri dan tidak khusus untuk sebuah database, jadi tidak ada alasan untuk memasukkan privilege ini ke tabel grant yang lain. Bahkan, hanya tabel user yang butuh untuk dilihat untuk menentukan apakah boleh anda melakukan operasi administratif.
Privilege file juga khusus hanya di tabel user. Ini bukan privilege administratif, tetapi kemampuan anda untuk membaca dan menulis file di host server itu tidak terkait dengan database yang anda akses.

Tentang order by, group by, fungsi agregasi

Mengurutkan Data dengan ORDER BY
Mengurutkan data ascending maupun descending

select*from mhs order by nim asc;
select*from mhs order by nim desc;
Mengelompokkan data dengan GROUP BY
Ingin mengelompokkan data berdasarkan kota

select kota from mhs group by kota;
Menentukan kondisi pada GROUP BY
Perintah having hanya dapat digunakan jika group by di gunakan.
contoh :
akan dikelompokkan data pegawai berdasarkan pekerjaan, kecuali pegawai yang pekerjaanya 'tukang sapu'

select pekerjaan from pegawai group by pekerjaan having pekerjaan > 'tukang sapu' ;
Menggunakan Fungsi Agregasi
Fungsi agregrasi adalah fungsi yang disediakan oleh MySQL untuk menghasilkan sebuah nilai berdasarkan sejumlah data. Adapun beberapa fungsi agregasi pada MySQL adalah :
AVG -> Rata-rata
COUNT -> Jumlah Data
MAX -> Terbesar
MIN -> Terkecil
SUM -> Jumlah Nilai
Fungsi AVG
menghitung rata-rata gaji pegawai

select avg(gaji) from pegawai;
menghitung rata-rata pegawai per pekerjaan

select pekerjaan, avg(gaji) from pegawai group by pekerjaan;
menghitung rata-rata gaji pegawai yang pekerjaannya SALESMAN

select pekerjaan,avg(gaji) from pegawai group by pekerjaan having job='salesman';
Fungsi COUNT
menghapus jumlah pegawai di tabel begawai.

select count(*) from pegawai;
menghitung jumlah pegawai per pekerjaan

select pekerjaan,count(*) from pegawai group by pekerjaan;
Fungsi MAX
menampilkan informasi gaji terbesar

select max(gaji) from pegawai;
menampilkan informasi gaji terbesar pegawai per pekerjaan.

select pekerjaan, max(gaji) from pegawai group by pekerjaan;
Fungsi MIN
menampilkan informasi gaji terkecil

select min(gaji) from pegawai;
menampilkan informasi gaji terkecil pegawai per pekerjaan.

select pekerjaan, min(gaji) from pegawai group by pekerjaan;
Fungsi SUM
menghitung total gaji pegawai.

select sum(gaji) from pegawai;
menghitung jumlah gaji pegawai per pekerjaan dengan tampilan urut naik berdasarkan pekerjaan.

select pekerjaan, sum(gaji) from pegawai group by pekerjaan order by pekerjaan asc;
menghitung jumlah gaji pegawai per pekerjaan dengan tampilan urut menurun berdasarkan jumlah gaji.

select pekerjaan, sum(gaji) as bayaran from pegawai group by pekerjaan order by bayaran desc;

Tentang select, update, delete, distinct, between, like, regexp, IN, AND, OR dan NOT

Menampilkan Informasi (Retrieving Information)
menggunakan SELECT
ingin menampilkan seluruh tabel mhs.

select * from mhs;
menampilkan beberapa field dari tabel mhs.

select nim,nama from mhs;
menampilkan data yang nim = '0001'

select nim,nama from mhs where nim='0001';
Memasukkan Nilai Null(The Null Value)
Apabila kita bermaksud untuk tidak mengisi suatu field, maka kita dapat memberikan nilai NULL kedalam field tersebut. nilai null tidak dapat digunakan untuk operasi perbandingan aritmatika.
conoh :

insert into mhs (nim,nama,alamat) values ('0001','ali',NULL);
Mengubah data dengan UPDATE
Akan mengubah nama mahasiswa yang NIMnya = 0001 menjadi agus :

update mhs set nama='agus' where nim = '0001';
Menghapus data dengan DELETE
Untuk menghapus record, maka digunakan pernyataan DELETE, dengan syntax sebagai berikut :

delete from mhs where nim='0001';
Memperoleh data yang unik
(tidak ada yang kembar) DISTINCT

select distinct (sex) from mhs;
Menggunakan Operator BETWEEN
operator between digunakanuntuk mengangani operasi jangkauan.
contoh :


select nama from mhs where nim between '0001' and '0005';
Menggunakan Operator LIKE
digunakan untuk mencari data yang difatnya mirip.
(-) mewakili sebuah karakter
(%) mewakili beberapa karakter
Menampilkan nama yang diawali huruf 'S' :

select nama from mhs where nama like 'S%';
Menampilkan nama yang diawali huruf 'S' huruf keempatnya adalah 'i' :

select nama from mhs where nama like 's_i%';
Menampilkan nama mengandung huruf 'S' :

select nama from mhs where nama like '%S%';
Menggunakan Operator REGEXP(Regular Expressions)
Operator ini memiliki kesamaan fungsi dengan like. Ada beberapa symbol khusus yang biasa digunakan bersama-sama dengan operator Regexp, seperti :
. satu tanda titik (.) untuk mewakili satu karakter
[....] untuk mewakili beberapa karakter atau range yang ditentukan
^ untuk posisi awak dari sebuah criteria yang ditentukan
$ untuk posisi akhir dari sebuah criterua yang ditentukan

akan menampilkan nama,alamat, mhs yang berahiran huruf 'a' :

select nama,alamat from mhs where nama regexp 'a$';
menampilkan nama,alamat berakhiran huruf 'a' atau 'i' :


select nama,alamat from mhs where nama regexp '[ai]$';
Menggunakan Operator IN
akan menampilkan nama dan alamat yang namanya dedy atau budi atau sulis...

select nama,alamat from mhs where nama='dedy sutanta' or nama='budi satria' or nama='sulis andriani';
select nama,alamat from mhs where nama IN ('dedy sutanta','budi satria','sulis');
Menggunakan Operator AND, OR, dan NOT
akan menampilkan nama mahasiswa yang tinggal dikota semarang dan sex = pria


select nama from mhs where kota='semarang' and sex='P'; 
akan menampilkan nama mahasiswa yang namanya mengandung huruf 'i' dan jenis kelamin selain wanita :

select nama from mhs where nama like '%i%' and sex !='w';

Pengaturan Hak Akses


Untuk Apa Hak Akses Perlu Diatur
Mengontrol basis data merupakan hal yang sangat perlu diperhatikandalam rangka menjaga keamanan dari orang-orang yang tidak berhak. Data yang bersifat rahasia tidak dapat disemunyikan dan bahkan kemungkinan data menjadi sampah karena setiap orang bisa melakukan apa saja sesuka hati.

Cara Mengatur Hak Akses
SQL menyediakan perintah GRANT untuk mengatur hak akses seseorang terhadap suatu tabel. Bentuk petintahnya adalah :

Grant hak_akses on nama_tabel_atau_view to pemakai;

Keterangan

  • Hak akses merupakan hal akses yang akan diberikan (misalnya select atau insert atau gabungannya). Jika hak akses lebih dari satu, antar hak akes perlu dipisahkan dengan koma.
  • Nama_tabel_atau_view menyatakan tabel atau pandangan (view) yang hak aksesnya akan diatur.
  • Pemakai adalah nama pemakai yang telah didargarkan pada sistem bases data sejumlah pemakai dapat disebutkan dengan antar pemakai dipisah daengan koma

Macam-macam hak akses :
Hak Akses
Keterangan
Select
Hak akses yang memungkinkan operasi Select
Insert
Hak akses yang memungkinkan operasi Insert
Update
Hak akses yang memungkinkan operasi update
Delete
Hak akses yang memungkinkan operasi Delete
Index
Hak akses yang memungkinkan operasi membuat Index
Drop
Hak akses yang memungkinkan operasi Drop
Create
Hak akses yang memungkinkan operasi create database maupun table
Insert
Hak akses yang memungkinkan operasi penyisipan record baru ke table
File
Hak akses yang memungkinkan operasi  baca dan tulis file

Contoh 1 :

grant select on pegawai to terra;
Contoh 2 :

grant select, insert on pegawai to terra;
Mambatasi Hak Akses pada Field tertentu
SQL memungkinkan pemberian hak akses hanya terbatas pada kolom tertentu.
Contoh :

grant select, Update (no_pegawai, nama, pekerjaan) on pegawai to dewi;

Memberikan Segala Hak
Jka semua hak akan di berikan kepada seorang pemakai, maka dapat dituliskan ALL PRIVILEGES pada bagian hak akses.
Contoh :

grant all privileges on pegawai to dewi;