SECURITY dalam ORACLE

Untuk membuat sebuah security di dalam oracle, langkah pertama yang harus kita lakukan yaitu :
1. Buka C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
2. Tambahkan script dibawah ini kedalam tnsnames.ora
DISTANT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 169.254.16.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
HOST = 169.254.16.91 merupakan alamat IP ADDRESS milik SERVER
3. Kemudian connect dengan begini.
sqlplus nama_user@distant;
password user;

KATA KUNCI

SQLPLUS / AS SYSDBA; --> untuk mengganti user dba lama dan harus di mesin server

SELECT TABLE_NAME, GRANTOR, PRIVILEGE, GRANTEE, GRANTABLE FROM DBA_TAB_PRIVS WHERE OWNER = 'POLTEK' ORDER BY TABLE_NAME; ---> digunakan untuk melihat hak akses apa saja yang telah diberikan kepada semua user.

with admin option; ----> digunakan untuk memberikan hak akses kepada seorang user, dimana hak tersebut bisa di bagi - bagikan kepada user yang lainnya.

with grant option; ---> digunakan pada setiap script yang memiliki kata ON dan digunakan untuk memberikan hak akses kepada seorang user, dimana hak tersebut bisa di bagi - bagikan kepada user yang lainnya.

1. grant connect to user1; -----> hanya memberikan hak connect kepada user1
2. grant connect to user1, user2, user3; -----> hanya memberikan hak connect kepada user1, user2, user3.
3. grant select on nama_user.nama_tabel to user1; -----> hanya memberikan hak untuk melihat data saja kepada user1 saja.
4. grant select on nama_user.nama_tabel to user1, user2, user3; -----> hanya memberikan hak untuk melihat data saja kepada user1, user2, user3.
5. grant insert on nama_user.nama_tabel to user1; -----> hanya memberikan hak untuk memasukkan data saja kepada user1 saja.
6.grant insert on nama_user.nama_tabel to user1, user2, user3; -----> hanya memberikan hak untuk memasukkan data saja kepada user1, user2, dan user3.
7. grant insert, update, delete, on nama_user.nama_tabel to user1, user2, user3; ---> untuk memberikan hak untuk memasukkan data, mengubah data, menghapus data kepada banyak user
8. revoke select on nama_user.nama_tabel FROM user3; ---> untuk remove hak akses milik user3
9. alter user user3 account lock; ---> untuk mengunci user3
10. drop user user3; ---> untuk menghapus user3
11. alter user user3 identified by passwordbaru password expire; ---> untuk mengganti password sendiri secara otomatis
12. alter user user3 identified by passwordbaru; ---> untuk mengubah password oleh DBA
13. grant dba to user3; ---> untuk memberikan hak akses yang sama dengan DBA
14. revoke connect from user1, user2; ---> untuk mencabut hak akses connect user user1 dan user2
15. lock table hr.regions in exclusive mode; ---> untuk mengunci tabel regions sehingga tabel regions tidak bisa di tambah, delete, ubah, oleh user yang lain
16. roolback ---> untuk melepaskan kunci
17. lock table hr.regions in share mode; ---> untuk mengunci tabel regions secara bersamaan.
18. select * from all_users; ---> untuk melihat semua user
19. grant resource to user1; ---> hak akses untuk bisa connect dan bisa buat tabel sendiri
20. grant execute on nama_prosedure to user1; ---> untuk memberikan hak untuk mengeksekusi prosedure tertentu kepada user1
21. grant select, insert, update, delete on nama_user.nama_tabel to user1 with grant option; -----> sistem memberikan hak kepada user1 dan user1 boleh membagi - bagikan haknya kepada user lain.
22. create role nama_group; ---> cara membuat group
contoh :
create role mahasiswa; ---> cara membuat group mahasiswa
grant mahasiswa to user1, user2, user3;
grant select on poltek.mahasiswa to mahasiswa;
23. revoke select, insert, update, delete on poltek.nilai from user1; -----> sistem mencabut haknya user1
24. revoke mahasiswa from user1; ----> mencabut hak user1 dari group mahasiswa;

TRIGGER dalam ORACLE

Aturan Penulisan Trigger

CREATE OR REPLACE TRIGGER nama_trigger
timing
event1 OR event2 OR event3
ON nama_objek
FOR EACH ROW
begin
end;
/
Note:
timing : BEFORE, AFTER, INSTEAD OF
event INSERT, UPDATE, DELETE, LOGON, LOGOFF
objek : nama table, nama user, database

Contoh Trigger
Pertama saya akan membuat sebuah tabel dengan nama rekaman, tabel ini berfungsi untuk mencatat semua aktivitas yang kita lakukan sesuai dengan trigger yang kita buat selama kita memanipulasi data dalam oracle.

Langkah pertama yaitu membuat tabel
CREATE TABLE rekaman
(pelaku varchar(10),
tanggal date,
jam date,
aksi varchar(100)
)
/

langkah Kedua yaitu kita mulai dengan membuat sebuah trigger untuk user yang login
CREATE OR REPLACE TRIGGER masuk
AFTER
LOGON
ON DATABASE
begin
insert into rekaman values (
user, sysdate, sysdate,
'login ke database'
);
end;
/
setelah kita menjalankan Trigger diatas, untuk mengecek apakah triggernya sudah jalan atau tidak, hal pertama yang kita lakukan yaitu :
- disconn;
- conn nama_user/pass_user;

Trigger ini kita gunakan untuk mencatat siapa saja user yang login ke system dan hasilnya akan dicatat didalam tabel rekaman.
Kita bisa melihatnya dengan menggunakan perintah dibawah ini.
select pelaku, tanggal, to_char(jam, 'HH:MI:SS'), aksi from rekaman;

langkah Ketiga yaitu kita mulai dengan membuat sebuah trigger untuk user yang logoff
CREATE OR REPLACE TRIGGER keluar
BEFORE
LOGOFF
ON DATABASE
begin
insert into rekaman values (
user, sysdate, sysdate,
'logout ke database');
end;
/
Trigger ini kita gunakan untuk mencatat siapa saja user yang sudah logoff dari dalam system dan hasilnya akan dicatat didalam tabel rekaman.
Kita bisa melihatnya dengan menggunakan perintah dibawah ini.
select pelaku, tanggal, to_char(jam, 'HH:MI:SS'), aksi from rekaman;

Langkah Yang Keempat yaitu kita akan membuat sebuah trigger yang kita gunakan untuk mengupdate data yang ada dalam tabel REGIONS

CREATE OR REPLACE TRIGGER updateanu
AFTER
UPDATE
ON regions
FOR EACH ROW
begin
insert into rekaman values (
user,sysdate,sysdate,
'mengubah data ke regions dengan nama '||:old.region_name||' menjadi '||:new.region_name);
end;
/
perintah untuk mengecek triggernya
select pelaku, tanggal, to_char(jam, 'HH:MI:SS') Jam, aksi from rekaman;

Langkah Yang Keenamt yaitu kita akan membuat sebuah trigger yang kita gunakan untuk menghapus data yang ada dalam tabel REGIONS
CREATE OR REPLACE TRIGGER deleteanu
AFTER
DELETE
ON regions
FOR EACH ROW
begin
insert into rekaman values (
user,sysdate,sysdate,
'mendelete table regions dengan id '||:new.region_id);
end;
/

Contoh Trigger yang digunakan untuk keamanan data, sehingga jika ada yang berusaha menghapus isi tabel rekaman, maka akan proses penghapusan akan digagalkan dana dalam tabel rekaman dicatat "berusaha menghapus rekaman data"
Pertama, sebelum kita membuat triggernya, kita terlebih dahulu membuat sebuah VIEW

CREATE VIEW v_rekaman
as
select pelaku, tanggal, to_char(jam, 'HH:MI:SS') jam, aksi from rekaman;
kemudian kita membuat triggernya
CREATE OR REPLACE TRIGGER awas
INSTEAD OF
DELETE
ON v_rekaman
FOR EACH ROW
begin
dbms_output.put_line('berusaha menghapus rekaman data');
end;
/

CATATAN PENTING

delete from rekaman; --------> menghapus data di tabel rekaman
alter trigger masuk; --------> menonaktifkan semua trigger

prosedure yang kita gunakan untuk memasukkan data kedalam tabel rekaman dengan parameternya adalah keterangan.
create or replace procedure rekamanp
(keterangan IN varchar)
IS
BEGIN
insert into rekaman (pelaku, tanggal, jam, aksi) values (
user,sysdate,sysdate, keterangan);
END;
/

TRIGGER yang digunakan untuk mencatat hasil insert, update, dan delete dalam tabel REGIONS, dimana kita memanggil procedure rekamanp yang diatas sebagai acuan.

CREATE OR REPLACE TRIGGER gabung
AFTER
INSERT OR UPDATE OR DELETE
ON regions
FOR EACH ROW
begin
if inserting then
rekamanp('BERHASIL MENGINSERT DATA KEDALAM TABEL REGIONS');
elsif updating then
rekamanp('BERHASIL MENGUPDATE DATA KEDALAM TABEL REGIONS');
elsif deleting then
rekamanp('BERHASIL MENDELETE DATA KEDALAM TABEL REGIONS');
end if;
end;
/

FUNCTION dalam ORACLE

Dibawah ini adalah beberapa contoh function.

cara memanggil FUNCTION *
select jumlah(10,20) from dual;

1. FUNCTION untuk penjumlahan

CREATE OR REPLACE FUNCTION jumlah
(x in number, y in number)
RETURN number
IS
Begin
return x+y;
end;

declare
a number(10);
b number(10);
hasil number(10);
begin
a:=&nilai_a;
b:=&nilai_b;
hasil:=jumlah(a,b);
dbms_output.put_line('Output diluar procedure : ' ||hasil);
end;

2. FUNCITION untuk menentukan gajinya besar ataukecil

CREATE OR REPLACE FUNCTION gaji
(g in number)
RETURN varchar
IS

Begin
if g > 5000 then
RETURN ' Gaji Besar';
else
RETURN ' Gaji Kecil';

end if;
end;

Cara menjalankan FUNCTIONNYA
SELECT gaji(10000) from dual;
SELECT first_name, salary, gaji(salary) "keterangan" from employees;


3. FUNCTION untuk menghitung jumlah rekan kerja

create or replace function NO2
(id employees.department_id%type)

idkar employees.department_id%type;
nama employees.first_name%type;
jml number(10);
IS
select first_name
into nama
from employees
where employee_id = (select count(department_id)
into jml
from employees
where department_id=idkar);
dbms_output.put_line(' Nama Karyawan : '||nama);
dbms_output.put_line(' Rekan Kerja : '||jml);

end;
Cara Memanggilnya
select first_name, NO2(employee_id) from employees;

3. contoh function

CREATE OR REPLACE FUNCTION JMLSA
(id in countries.country_id%type)
RETURN number
IS
sa locations.street_address%type;
BEGIN
select count(street_address)
into sa
from locations JOIN countries ON (locations.country_id=countries.country_id)
where locations.country_id=id;
return(sa);
END;
/

CREATE OR REPLACE FUNCTION UPDT
(id IN employees.employee_id%type)
RETURN number
IS
gaji employees.salary%type;
BEGIN
update employees
set salary = gaji
where id = id;
return(gaji);
END;
/

PROCEDURE dalam ORACLE

Beberapa perintah atau sintaks procedure dalam oracle.

Melihat daftar prosedur, function, dan triger yang kita punya.
select distinct name, type from user_source;

Perintah yang kita gunakan untuk melihat bagian yang error saat procedure dijalankan.
show error;

Untuk melihat source code atau bagian yang ada dalam sebuah procedure, function, maupun trigger.
contoh pengecekan sourcode atau bagian yang ada dalam sebuah procedure
select text from user_source where name = 'SECURE_EMPLOYEES'
Keterangan
SECURE_EMPLOYEES : nama prosedur yang mau kita cek sourcode nya.

Contoh procedure yang digunakan untuk menghitung jumlah procedure, function, dan trigger dengan menggunakan cursor explicit.


CREATE OR REPLACE PROCEDURE BONUS IS
a user_source.type%type;
b number(10);

cursor coba is
select type, count(distinct name)
from user_source group by type;

begin
open coba;
loop
fetch coba into a, b;
exit when coba%notfound;
dbms_output.put_line(a|| ' : ' ||b|| ' buah ');

end loop;
close coba;
end;
Cara memanggil atau menjalankan procedure diatas
exec BONUS();

Contoh procedure yang digunakan untuk menghitung jumlah procedure, function, dan trigger.
CREATE OR REPLACE PROCEDURE bonus IS
p number(5);
f number(5);
t number(5);
BEGIN
select count(distinct name) into p from user_source where type = 'PROCEDURE';
select count(distinct name) into f from user_source where type = 'FUNCTION';
select count(distinct name) into t from user_source where type = 'TRIGGER';

dbms_output.put_line('Procedure :'|| p ||' biji');
dbms_output.put_line('Function :'|| f ||' biji');
dbms_output.put_line('Trigger :'|| t ||' biji');
END;
/
Cara memanggil atau menjalankan procedure diatas
exec bonus();

cara membuat prosedure insert tanpa parameter

create or replace procedure add_dept IS
dept_id departments.department_id%type;
dept_name departments.department_name%type;

BEGIN
dept_id:=280;
dept_name:='ST-Currriculum';

INSERT INTO departments(department_id, department_name)
VALUES(dept_id, dept_name);

DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert'||SQL%ROWCOUNT||'baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure insert tanpa parameter inputan dengan nama procedurenya adalah add_dept. dan isinya yaitu memasukkan data kedalam tabel departments dengan dept_id:=280; dan dept_name:='ST-Currriculum';

Ada beberapa cara memanggil prosedur, diantaranya :
1. exec add_dept();
2. call add_dept();
3. begin
add_dept();
end;

Cara membuat prosedure insert dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_pake_param
(dept_id IN departments.department_id%type,
dept_name departments.department_name%type)
IS

BEGIN
INSERT INTO departments (department_id,department_name)
VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure insert dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_pake_param.

Cara Memanggil procedure ini yaitu

exec add_dept_pake_param(09,'contoh procedure dengan meminta parameter');


cara membuat prosedure delete dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_del
(dept_id IN departments.department_id%type)
IS
BEGIN
DELETE FROM departments where department_id = dept_id;
DBMS_OUTPUT.PUT_LINE('Berhasil Menghapus ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH TERHAPUS');
END;

script diatas adalah sebuah contoh untuk membuat procedure delete dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_del.


cara membuat prosedure update dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_update
(dept_id IN departments.department_id%type,
dept_name departments.department_name%type)
IS

BEGIN
UPDATE departments
SET department_name = dept_name
where department_id = dept_id;

DBMS_OUTPUT.PUT_LINE('Berhasil Mengupdate ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH TERUPDATE');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure update dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_update.

CREATE OR REPLACE PROCEDURE upadate
(gaji IN employees.salary%type,
emp_id IN employees.employee_id%type)
IS

BEGIN
UPDATE employees
SET salary = gaji
where employee_id = emp_id;

DBMS_OUTPUT.PUT_LINE('Berhasil Mengupdate ' ||SQL%ROWCOUNT|| ' baris');
END;

keterangan
procedure diatas adalah sebuah procedure update dengan hasil setelah di eksekusi yaitu memberitahukan berapa banyak jumlah baris yang diupdate dalam tabel employees.

Membuat procedure dengan fungsi akar

create or replace procedure proc_akar
(a number)
IS
b number(8,3);
begin
b:=sqrt(a);
dbms_output.put_line('Hasil Akar dari ' ||a|| ' adalah ' ||b );
end;

Procedure diatas adalah contoh procedure untuk menghitung hasil akar dari sebuah nilai. nama procedure diatas adalah proc_akar.
cara memanggil procedure ini yaitu

exec proc_akar(5);

CONTOH SOAL UNTUK PROCEDURE

1. Buatlah prosedur dengan parameter input X = employee_id.
Untuk menampilkan first_name, last_name, email, salary

Jawaban:
CREATE OR REPLACE PROCEDURE emp
(x employees.employee_id%type)
is
first employees.first_name%type;
last employees.last_name%type;
mail employees.email%type;
gaji employees.salary%type;

BEGIN
select first_name, last_name, email, salary
into first, last, mail, gaji
from employees
where employee_id = x;
DBMS_OUTPUT.PUT_LINE('First Name : '||first);
DBMS_OUTPUT.PUT_LINE('Last Name : '||last);
DBMS_OUTPUT.PUT_LINE('Email : '||mail);
DBMS_OUTPUT.PUT_LINE('Salary : '||gaji);
END;

Cara memanggil atau menjalankan procedurenya
exec emp(100);

2. buatlah prosedur untuk memasukkan pada tabel region dengan value yang dimasukkan id_region dan region_name yang dimasukkan dengan value id_region dan region_name, region_name dan region_id dijadikan parameter.

Jawaban:

CREATE OR REPLACE PROCEDURE reg
(id IN regions.region_id%type,
reg_name regions.region_name%type)
IS

BEGIN
INSERT INTO regions (region_id,region_name)
VALUES(id,reg_name);
DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Cara Memanggilnya:
exec reg();

3. buatlah prosedur dengan 1 parameter IN x dan 1 parameter OUT y.
x = jari-jari lingkaran
y = hasil untuk menghitung luas lingkaran.

Jawaban:

create or replace procedure kali
(x IN number)
IS
y number(10,2);
begin
y:=3.14*(x*x);
dbms_output.put_line('Luas Lingkarannya yaitu : ' ||y);
end;

cara memanggil atau menjalankan procedure diatas
exec kali(10);

atau

create or replace procedure kali_param
(x IN number, y OUT number)
IS
begin
y:=3.14*(x*x);
end;

cara memanggil atau menjalankan procedurenya

declare
x number(10);
y number(10,2);
begin
x:=&nilai_x;
kali_param(x,y);
dbms_output.put_line('Luas Lingkarannya dengan jari-jari ' ||x|| ' yaitu : ' ||y);
end;