DCL

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