Basic User Privilege Management Example On Oracle Database

In case we will create user schemes namely MI123, Tamu1 and Tamu2. User MI123 have two tables namely T1 and T2. Here are some examples for managing user objects for those user schemes.

1. Create new user scheme (MI123)

CREATE USER mi123 IDENTIFIED BY mi123;  

2. Grant user privileges to user for login and create user!

GRANT CONNECT, RESOURCE TO mi123; GRANT CONNECT TO mi123; GRANT RESOURCE TO mi123;  

3. Create 2 database objects (table T1 & table T2)

T1                                                   T2

ID        Nama                   KODE     NILAI     ID
-------------------                 ----------------------------------
1          Satu                      N1          70           1
2          Dua                      N2          80           1
3          Tiga                      N3          70           2
-------------------                    N4          90           3
                                      -------------------------------

CREATE TABLE T1(
ID NUMBER PRIMARY KEY,
NAMA VARCHAR2(24)
);

INSERT INTO T1(id, nama) VALUES(1, ‘Satu’);
INSERT INTO T1(id, nama) VALUES(2, ‘Dua’);
INSERT INTO T1(id, nama) VALUES(3, ‘Tiga’);
COMMIT;

CREATE TABLE T2(
KODE VARCHAR2(4),
NILAI NUMBER,
ID NUMBER,
CONSTRAINT fk1 FOREIGN KEY(ID) REFERENCES T1(ID)
);

INSERT INTO T2 VALUES(‘N1’, 70, 1);
INSERT INTO T2 VALUES(‘N2’, 80, 1);
INSERT INTO T2 VALUES(‘N3’, 70, 2);
INSERT INTO T2 VALUES(‘N4’, 90, 3);
COMMIT;

4. Create new user namely Tamu1 and Tamu2!

CREATE USER Tamu1 IDENTIFIED BY tamu1; CREATE USER Tamu2 IDENTIFIED BY tamu2;  

5. Create privilege to Tamu1 user for viewing T1 table!

GRANT SELECT ON mi123.T1 TO Tamu1;  

6. Create privilege for inputing data (INSERT) on Tabel T1 to Tamu2 user!

GRANT INSERT ON mi123.t1 TO Tamu2;  

7. Show data on T1 Table while login with Tamu1 user! Is it works?

SELECT * FROM mi123.T1; yes it works!  

8. Show data on T1 Table while login with Tamu2 user! Is it works?

SELECT * FROM mi123.T1; No!   GRANT SELECT ON mi123.T2 TO Tamu1 WITH GRANT OPTION;  

10. Login as Tamu1 user, type this sentence:

GRANT SELECT ON mi123.T2 To Tamu2;  

11. Login as Tamu2 user, show the data of T2 table!

SQL> conn tamu2/tamu2
Connected.

SQL> select * from mi123.t2;

KODE   NILAI    ID
----       ---------- ------
N1           70          1
N2           80          1
N3           70          2
N4           90          3