DATA CONTROL LANGUAGE Teori Data Control Language DCL (Data Control Language) meupakan bagian dari Oracle yang dpergunakan untuk merubah hak akses, memberikan roles, dan isu lain yang berhubungan dengan keamanan database A. CREATE USER - Membuat user CREATE USER namausername IDENTIFIED BY password; - Melihat user yang ada didalam oracle select * from all_users; B. REVOKE AND GRANT Merupakan komponnen dari bahasa SQL yang dipergunakan untuk mengatur control akses yang disimpan dalam sebuah data base. Terdapat dua statement utama dalam control akese yaitu GRANT AND REVOKE. GRANT dipergunakan untuk memberikan akses sedangkan REVOKE dipergunakan untuk mencabut hak akses 1. MEMBUAT PREVILAGE SYSTEM DI USER GRANT namaprevilage TO username; 2. MENCABUT PREVILAGE SYSTEM DI USER REVOKE namaprevilage FROM username; 3. MEMBUAT PREVILAGE DI TABLE GRANT namaprevilage ON namatabel TO username; 4. MENCABUT PREVILAGE DI TABLE REVOKE namaprevilage ON namatabel FROM username; C. DAFTAR PREVILAGE TABEL PREVILAGE SUSTEM Privilege Description ADMIN Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion. ALTER ANY CACHE GROUP Enables a user to alter any cache group in the database. Privilege Description ALTER ANY INDEX Enables a user to alter any index in the database. Note: There is no ALTER INDEX statement. ALTER ANY MATERIALIZED VIEW Enables a user to alter any materialized view in the database. Note: There is no ALTER MATERIALIZED VIEW statement. ALTER ANY PROCEDURE Enables a user to alter any PL/SQL procedure, function or package in the database. ALTER ANY SEQUENCE Enables a user to alter any sequence in the database. Note: There is no ALTER SEQUENCE statement. ALTER ANY TABLE Enables a user to alter any table in the database. ALTER ANY VIEW Enables a user to alter any view in the database. Note: There is no ALTER VIEW statement. CACHE_MANAGER Enables a user to perform operations related to cache groups. CREATE ANY CACHE GROUP Enables a user to create a cache group owned by any user in the database. CREATE ANY INDEX Enables a user to create an index on any table or materialized view in the database. CREATE ANY MATERIALIZED VIEW Enables a user to create a materialized view owned by any user in the database. CREATE ANY PROCEDURE Enables a user to create a PL/SQL procedure, function or package owned by any user in the database. CREATE ANY SEQUENCE Enables a user to create a sequence owned by any user in the database. CREATE ANY SYNONYM Enables a user to create a private synonym owned by any user in the database. CREATE ANY TABLE Enables a user to create a table owned by any user in the database. CREATE ANY VIEW Enables a user to create a view owned by any user in the database. Privilege Description CREATE CACHE GROUP Enables a user to create a cache group owned by that user. CREATE MATERIALIZED VIEW Enables a user to create a materialized view owned by that user. CREATE PROCEDURE Enables a user to create a PL/SQL procedure, function or package owned by that user. CREATE PUBLIC SYNONYM Enables a user to create a public synonym. CREATE SEQUENCE Enables a user to create a sequence owned by that user. CREATE SESSION Enables a user to create a connection to the database. CREATE SYNONYM Enables a user to create a private synonym. CREATE TABLE Enables a user to create a table owned by that user. CREATE VIEW Enables a user to create a view owned by that user. DELETE ANY TABLE Enables a user to delete from any table in the database. DROP ANY CACHE GROUP Enables a user to drop any cache group in the database. DROP ANY INDEX Enables a user to drop any index in the database. DROP ANY MATERIALIZED VIEW Enables a user to drop any materialized view in the database. DROP ANY PROCEDURE Enables a user to drop any PL/SQL procedure, function or package in the database. DROP ANY SEQUENCE Enables a user to drop any sequence in the database. DROP ANY SYNONYM Enables a user to drop a synonym owned by any user in the database. DROP ANY TABLE Enables a user to drop any table in the database. DROP ANY VIEW Enables a user to drop any view in the database. DROP PUBLIC SYNONYM Enables a user to drop a public synonym. Privilege Description EXECUTE ANY PROCEDURE Enables a user to execute any PL/SQL procedure, function or package in the database. FLUSH ANY CACHE GROUP Enables a user to flush any cache group in the database. INSERT ANY TABLE Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table. LOAD ANY CACHE GROUP Enables a user to load any cache group in the database. REFRESH ANY CACHE GROUP Enables a user to flush any cache group in the database. SELECT ANY SEQUENCE Enables a user to select from any sequence or synonym on a sequence in the database. SELECT ANY TABLE Enables a user to select from any table, view, materialized view, or synonym in the database. UNLOAD ANY CACHE GROUP Enables a user to unload any cache group in the database. UPDATE ANY TABLE Enables a user to update any table or synonym in the database. PREVILAGE TABLE Privilege Description SELECT Ability to perform SELECT statements on the table. INSERT Ability to perform INSERT statements on the table. UPDATE Ability to perform UPDATE statements on the table. DELETE Ability to perform DELETE statements on the table. REFERENCES Ability to create a constraint that refers to the table. ALTER Ability to perform ALTER TABLE statements to change the table definition INDEX Ability to create an index on the table with the create index statement Privilege Description ALL All privileges on table Kegiatan Praktikum Kegiatan praktikum pada pertemuan hari ini akan difokuskan ke pembuatan username dan memberikan hak akses kepada user tersebut. Adapun lankah-langkah nya adalah sebagai berikut: 1. Untuk membuat username baru kita harus login dulu sebagai administrator database karena yang memiliki akses penuh adalah administrator. Buka SQL plus. Masuk sebagai system, dan untuk password nya ketikkan sama seperti yang kita ketikkan sewaktu proses instalasi oracle 2. Kita akan membuat user dengan nama oracleUser dan password oraclePassword. Ketikkan perintah berikut CREATE USER oracleUser IDENTIFIED BY oraclePassword; Jika muncul pesan User Created. Berarti proses pembuatan user berhasil 3. Cek didalam tabel all_users untuk melihat user yang dibuat telah berhasil 4. select * from all_users; 5. Buka SQL Plus pada jendela baru untuk mencoba login user baru. Masuk sebagai oracleUser dan ketikkan password sesuai dengan yang telah di tentukan sebelumnya Akan muncul warning bahwasanya user tidak bisa login karen belum mempunyai akses session previlage. Karena secara default didalam oracle user yang pertama kalinya dibuat tidak mempunyai hak ases sama sekali. Untuk itu perlu ditambahkan previlage. Yang berhak untuk menambahkan previlage adalah administrator database. 6. Kita kembali ke login sebagai system. Kita akan menambahkan previlage session ketikkan perintah GRANT CREATE SESSION TO oracleUser; Jika muncul pesan grant succeeds berarti proses penambahan previlage telah berhasil 7. Untuk menguji apakah penambahan previlage berhasil dilakukan. Lakukan login terhadap oracleUser, login berhasil jika tidak ada pesan error atau warning; 8. Pencabuatan Previlage bisa dilakukan dengan REVOKE. Misalkan kita akan mencabut previlage create session dari user userOracle. Maka kita bisa ketikan perintah sebagai berikut diSQL 9. REVOKE create session FROM oracleUser; 10. Cara diatas dapat digunakan untuk memberikan atau mengilangkan PREVILAGE yang lain. Tugas Praktikum 1. Buatlah user di oracle sebanyak anggota kelompok Masing-masing user berikanlah previlage sebagai berikut a. Superuser (system) b. User 1 : create table, dan alter tabel c. User 2 : Insert, update row d. User 3 : select table 2. Buatlah rancangan database (ER-Diagram) sistem informasi kesehatan yang didalamnya terdapat minimal satu buah relasi one to one, one to many, many to many