# REST-Enabling the HR Schema in Oracle ORDS: A Complete Guide

## **Introduction**

Oracle REST Data Services (ORDS) allows you to expose database objects as RESTful web services. In this guide, we will enable RESTful services for the **HR schema** and create a **fully functional REST API** that supports **GET, POST, PUT, PATCH, and DELETE** operations for the **Employees table**.

This blog will take you through the **end-to-end process**, including:

* Enabling RESTful services for **HR schema**
    
* Creating a **module**, **templates**, and **handlers**
    
* Implementing all major **HTTP methods**
    
* Testing the APIs
    

---

## **1\. Enabling RESTful Services for HR Schema**

Before creating RESTful endpoints, we must enable ORDS for the **HR schema**.

### **Step 1: Enable ORDS for HR Schema**

Execute the following PL/SQL block in **SQLcl or SQL\*Plus**:

```sql
  BEGIN
        ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api', -- Base path for the schema
        p_auto_rest_auth => FALSE  -- Set to TRUE if authentication is required
    );
    COMMIT;
 END;
/
```

✅ **Now, the HR schema is REST-enabled!** All endpoints will be accessible under `/ords/hr_api/`.

---

## **2\. Creating the RESTful API Module**

A **module** acts as a logical container for multiple endpoints under a **base path**.

### **Step 2: Define a Module (**`hr_module`)

```sql
 BEGIN
        ORDS.DEFINE_MODULE(
        p_module_name => 'hr_module',
        p_base_path => 'employees/',  -- Base path for all employee-related APIs
        p_items_per_page => 25
    );
    COMMIT;
 END;
/
```

✅ **All employee-related endpoints will be accessible under** `/ords/hr_api/employees/`.

---

## **3\. Creating REST Endpoints for Employees Table**

### **Step 3: Define Templates and Handlers**

#### **📌 GET - Fetch All Employees**

```sql
    BEGIN
        ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '' -- Base pattern: `/ords/hr_api/employees/`
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '',
        p_method => 'GET',
        p_source_type => ORDS.SOURCE_TYPE_QUERY,
        p_source => 'SELECT employee_id, first_name, last_name, job_id, salary FROM hr.employees'
    );

    COMMIT;
END;
/
```

✅ **Access:** `GET /ords/hr_api/employees/`  
✅ **Returns:** List of all employees in JSON format.

---

#### **📌 GET - Fetch a Single Employee by ID**

```sql
BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}' -- `/ords/hr_api/employees/:employee_id`
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'GET',
        p_source_type => ORDS.SOURCE_TYPE_QUERY,
        p_source => 'SELECT employee_id, first_name, last_name, job_id, salary 
                     FROM hr.employees 
                     WHERE employee_id = :employee_id'
    );

    COMMIT;
END;
/
```

✅ **Access:** `GET /ords/hr_api/employees/101`  
✅ **Returns:** Employee details for `employee_id = 101`.

---

#### **📌 POST - Insert a New Employee**

```sql
BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => ''
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '',
        p_method => 'POST',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            INSERT INTO hr.employees (employee_id, first_name, last_name, job_id, salary) 
            VALUES (:employee_id, :first_name, :last_name, :job_id, :salary);
            COMMIT;'
    );

    COMMIT;
END;
/
```

✅ **Access:** `POST /ords/hr_api/employees/`  
✅ **Body (JSON Example):**

```plaintext
jsonCopyEdit{
    "employee_id": 999,
    "first_name": "John",
    "last_name": "Doe",
    "job_id": "IT_PROG",
    "salary": 6000
}
```

✅ **Effect:** Inserts a new employee.

---

#### **📌 PUT - Update an Entire Employee Record**

```sql
BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'PUT',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            UPDATE hr.employees 
            SET first_name = :first_name, last_name = :last_name, 
                job_id = :job_id, salary = :salary 
            WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/
```

✅ **Access:** `PUT /ords/hr_api/employees/999`  
✅ **Body (JSON Example):**

```plaintext
jsonCopyEdit{
    "first_name": "Jane",
    "last_name": "Doe",
    "job_id": "SA_REP",
    "salary": 7000
}
```

✅ **Effect:** Updates all fields of the employee.

---

#### **📌 PATCH - Partially Update an Employee**

```sql
BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'PATCH',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            UPDATE hr.employees 
            SET salary = :salary 
            WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/
```

✅ **Access:** `PATCH /ords/hr_api/employees/999`  
✅ **Body (JSON Example):**

```plaintext
jsonCopyEdit{
    "salary": 8000
}
```

✅ **Effect:** Updates only the salary of the employee.

---

#### **📌 DELETE - Remove an Employee**

```sql
BEGIN
    ORDS.DEFINE_TEMPLATE(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}'
    );

    ORDS.DEFINE_HANDLER(
        p_module_name => 'hr_module',
        p_pattern => '{employee_id}',
        p_method => 'DELETE',
        p_source_type => ORDS.SOURCE_TYPE_PLSQL,
        p_source => '
            DELETE FROM hr.employees WHERE employee_id = :employee_id;
            COMMIT;'
    );

    COMMIT;
END;
/
```

✅ **Access:** `DELETE /ords/hr_api/employees/999`  
✅ **Effect:** Removes employee with `employee_id = 999`.

---

## **Testing the REST API**

Once everything is set up, test the APIs using:

* **Postman**
    
* **cURL**
    
* **Web browser (for GET requests)**
    

---

## **Conclusion**

In this guide, we:  
✔ Enabled RESTful services for **HR schema**  
✔ Created an **ORDS module (**`hr_module`)  
✔ Implemented **GET, POST, PUT, PATCH, and DELETE** requests  
✔ Provided **real examples with HR schema**

Now, you have a fully functional REST API for managing employees! 🚀

## **Securing ORDS REST APIs with Basic Authentication in APEX**

By default, ORDS allows anonymous access if `p_auto_rest_auth` is set to `FALSE`. However, for security, we should enable **authentication** using APEX **roles and privileges**.

---

## **1\. Enable Authentication for HR Schema**

Modify the `ORDS.ENABLE_SCHEMA` command to **force authentication**:

```plaintext
sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE  -- Enforce authentication
    );
    COMMIT;
END;
/
```

✅ Now, authentication is required for all endpoints.

---

## **2\. Create an APEX User for API Access**

1. Log in to **APEX Administration**.
    
2. Navigate to **Manage Users and Groups** → **Create User**.
    
3. Provide a **Username**, **Password**, and assign **RESTful Service** role.
    
4. Save the user.
    

✅ The user can now authenticate with **Basic Authentication (username & password).**

---

## **3\. Create an ORDS Role and Privilege**

To enforce API access restrictions, define a **role** and **privilege** in ORDS.

### **Step 1: Create a Role (**`hr_api_role`)

```plaintext
sqlCopyEditBEGIN
    ORDS.CREATE_ROLE(
        p_role_name => 'hr_api_role'
    );
    COMMIT;
END;
/
```

✅ `hr_api_role` is now available in ORDS.

---

### **Step 2: Assign the Role to APEX Users**

```plaintext
sqlCopyEditBEGIN
    ORDS.GRANT_ROLE(
        p_role_name => 'hr_api_role',
        p_grantee => 'APEX_REST_PUBLIC_USER'
    );
    COMMIT;
END;
/
```

✅ Now, the APEX user must authenticate to access the API.

---

### **Step 3: Create a Privilege and Assign the Role**

```plaintext
sqlCopyEditBEGIN
    ORDS.DEFINE_PRIVILEGE(
        p_privilege_name => 'hr_api_access',
        p_role => 'hr_api_role',
        p_description => 'Access to HR API endpoints'
    );
    COMMIT;
END;
/
```

✅ The privilege `hr_api_access` is linked to `hr_api_role`.

---

### **Step 4: Secure the API Endpoints**

Modify the existing **ORDS module** to enforce authentication.

```plaintext
sqlCopyEditBEGIN
    ORDS.DEFINE_MODULE(
        p_module_name => 'hr_module',
        p_base_path => 'employees/',
        p_items_per_page => 25,
        p_status => 'PUBLISHED',
        p_comments => 'HR Employees API'
    );

    -- Secure the module with the privilege
    ORDS.DEFINE_MODULE_PRIVILEGE(
        p_module_name => 'hr_module',
        p_privilege_name => 'hr_api_access'
    );

    COMMIT;
END;
/
```

✅ Now, all requests to `/ords/hr_api/employees/` require **authentication**.

---

## **4\. Testing Secure API with Authentication**

Now, if you try to access the API **without authentication**, you will get:

```plaintext
jsonCopyEdit{
    "error": "401 Unauthorized"
}
```

### **Use Basic Authentication in Postman or cURL:**

#### **Postman:**

1. Go to **Authorization** tab → Select **Basic Auth**.
    
2. Enter **APEX username & password**.
    
3. Send the request.
    

#### **cURL:**

```plaintext
bashCopyEditcurl -u apex_user:your_password -X GET https://yourserver/ords/hr_api/employees/
```

✅ **Now, only authenticated users can access the HR API.**

---

## **Conclusion**

✔ Enforced authentication using **ORDS roles and privileges**  
✔ Restricted API access to **APEX users**  
✔ Secured all **RESTful endpoints**

## **OAuth2 and JWT Authentication in ORDS (Oracle REST Data Services)**

When exposing Oracle database objects as RESTful services using **ORDS**, securing them with **OAuth2 and JWT (JSON Web Token)** is recommended for better security and control. Below, I’ll explain both authentication methods and provide **detailed implementation steps**.

---

# **1\. OAuth2 Authentication in ORDS**

## **Step 1: Enable OAuth2 in ORDS**

To use OAuth2, ensure **ORDS is configured for OAuth2**.  
Run the following command in SQL Developer (as `ORDS_METADATA` user):

```plaintext
sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE
    );
    COMMIT;
END;
/
```

✅ This enforces authentication for all endpoints under `hr_api/`.

---

## **Step 2: Create an OAuth2 Client**

Run the following command to create an OAuth2 client in ORDS:

```plaintext
sqlCopyEditBEGIN
    ORDS.CREATE_OAUTH_CLIENT(
        p_name            => 'hr_client',
        p_grant_type      => 'client_credentials',
        p_owner           => 'HR',
        p_description     => 'HR API OAuth2 Client'
    );
    COMMIT;
END;
/
```

✅ This creates an OAuth2 client (`hr_client`) with **client credentials** authentication.

---

## **Step 3: Get the Client Secret**

Run the following query to retrieve the **client secret**:

```plaintext
sqlCopyEditSELECT name, client_id, client_secret FROM user_ords_clients;
```

✅ Note the **client\_id** and **client\_secret**, as they will be used for authentication.

---

## **Step 4: Obtain an Access Token**

Use `curl` or **Postman** to request an **OAuth2 access token**:

```plaintext
bashCopyEditcurl -X POST https://yourserver/ords/hr/oauth/token \
    -H "Content-Type: application/x-www-form-urlencoded" \
    -d "grant_type=client_credentials" \
    -d "client_id=hr_client" \
    -d "client_secret=your_client_secret"
```

✅ This returns a response like:

```plaintext
jsonCopyEdit{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "token_type": "bearer",
    "expires_in": 3600
}
```

🔹 **Use this** `access_token` in API requests.

---

## **Step 5: Use Access Token for API Calls**

Once you have the access token, send API requests using **Bearer Token Authentication**:

```plaintext
bashCopyEditcurl -X GET https://yourserver/ords/hr_api/employees/ \
    -H "Authorization: Bearer eyJhbGciOiJIUzI1Ni..."
```

✅ Now, access is granted only if a **valid token** is provided.

---

# **2\. JWT Authentication in ORDS**

Instead of OAuth2, you can use **JWT (JSON Web Token)** authentication for a **stateless** and **secure API**.

## **Step 1: Enable JWT Authentication**

Run the following command to enable JWT authentication in ORDS:

```plaintext
sqlCopyEditBEGIN
    ORDS.ENABLE_SCHEMA(
        p_enabled => TRUE,
        p_schema => 'HR',
        p_url_mapping_type => 'BASE_PATH',
        p_url_mapping_pattern => 'hr_api',
        p_auto_rest_auth => TRUE
    );
    COMMIT;
END;
/
```

✅ This ensures that JWT is required for API access.

---

## **Step 2: Create a JWT-Based Role and Privilege**

```plaintext
sqlCopyEditBEGIN
    ORDS.CREATE_ROLE('hr_jwt_role');
    COMMIT;
END;
/
```

Then, create a **privilege** that requires a JWT:

```plaintext
sqlCopyEditBEGIN
    ORDS.DEFINE_PRIVILEGE(
        p_privilege_name => 'hr_jwt_access',
        p_role => 'hr_jwt_role',
        p_description => 'Access to HR API via JWT'
    );
    COMMIT;
END;
/
```

✅ Now, only users with valid JWT tokens can access the API.

---

## **Step 3: Obtain a JWT Token**

1. Create a **JWT provider** (e.g., Keycloak, Auth0, Oracle IDCS).
    
2. Use the **client\_id** and **client\_secret** to request a JWT.
    
3. ORDS will validate the JWT token before granting access.
    

Example JWT token request:

```plaintext
bashCopyEditcurl -X POST https://your-auth-server/oauth/token \
    -H "Content-Type: application/json" \
    -d '{
        "grant_type": "password",
        "client_id": "your_client_id",
        "client_secret": "your_client_secret",
        "username": "your_username",
        "password": "your_password"
    }'
```

✅ This returns a JWT token similar to OAuth2.

---

## **Step 4: Use JWT for API Calls**

```plaintext
bashCopyEditcurl -X GET https://yourserver/ords/hr_api/employees/ \
    -H "Authorization: Bearer eyJhbGciOiJIUzI1Ni..."
```

✅ ORDS verifies the JWT and grants or denies access.

---

# **Conclusion**

| Authentication Type | Use Case |
| --- | --- |
| **OAuth2** | Best for applications needing **token expiration & refresh** (e.g., external apps). |
| **JWT Authentication** | Best for **stateless** APIs with third-party authentication providers (e.g., Auth0, Keycloak). |

✅ **OAuth2** is ideal for external applications.  
✅ **JWT** is best for **microservices & secure APIs**.