Basic User Role Management Example on Oracle Database

On previous post explained that before user able to manage objects, user must possessed some privileges. Minimum privileges for a user are connect and resource.  Every user in the Oracle Database will have one scheme. A scheme will have objects such as tables, views and procedures.  User can access other user-owned scheme if granted permissions or privileges. Some privileges can be grouped into a role.
Here will be given an example how to create role on Oracle Database. For example there are 3 user schemes (User1, User2, User3). User1 scheme has 1 table called T1 with id (Primary Key), nama and nilai attributes.

User Schemes: User1 User2 User3
Object: Tabel T1 (#id, nama, nilai)
• CREATE TABLE t1(id NUMBER PRIMARY KEY, nama VARCHAR2(24), nilai NUMBER);

Here are the scenarios for creating User Role:
1. Login with SYSTEM user then create user1, user2, user3
CREATE USER user4 IDENTIFIED BY user4
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

2. Create ROLE namely MASUK, MENULIS, MEMBACA and BACA_TULIS
CREATE ROLE masuk;
CREATE ROLE menulis;
CREATE ROLE membaca;
CREATE ROLE baca_tulis;

3. Assign the required privileges to the ROLE
GRANT connect TO masuk;
GRANT insert, update, delete ON user1.t1 TO menulis;
GRANT select ON user1.t1 TO membaca;
GRANT masuk, menulis, membaca TO baca_tulis;

4. Assign ROLE baca_tulis to user1
GRANT baca_tulis TO user1;

5. Here is an example for granting System Privileges to a Role
CREATE ROLE intip;
GRANT select any table TO intip;
GRANT masuk, intip TO user2;
• Login as user2, show data of T1 table owned by user1.

6. Here is an example for granting Object Privileges to a Role
CREATE ROLE atur_t1;
GRANT select, insert, update, delete ON user1.t1 TO atur_t1;
GRANT atur_t1 TO user3;
• Login as user3, execute select/insert/update/delete on tabel T1 owned by user1

7. Create new role namely buat_table. Assign RESOURCE system privilege to that role (WITH ADMIN OPTION). Assign that role to user3.
• Login as user3 then create T2 table (#kode, ket) on user2 scheme.
• Fill some data to that table

8. Create user4 user by SYSTEM user.

9. Login as user3 then assign RESOURCE privilege to user4