Here you can find tips, tutorials and short examples about the database. You will found many short examples of coding containing a ready-made database especially regarding the use of general SQL, Oracle PL/SQL and Oracle APEX.

Sunday, October 30, 2016

Function is a special syntax in DML (Data Manipulation Language) queries to perform data processing in accordance with user needs. There are some ready-made function in Oracle Database. Here are some purposes of function in SQL query:

data manipulation

menerima argumen dan mengembalikan satu nilai

accept arguments and return a value

able to modified column data type

can perform nested functions

There are three categories of basic functions, which are character function, numeric functions and date function.

Tuesday, October 25, 2016

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.

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