10.4 Creating a Self Service Schema

Note:

Available only if you have the DBA and PDB_DBA roles assigned.

The Self Service Schema page enables you to create a database schema on user request by generating a secure URL that is automatically sent by email to the user. Users can access a public page to request for a schema.

The steps are broken down into the following sections:

• Activate the Self Service Schema in Oracle REST Data Services (ORDS)
• Enable the Self Service Schema feature in Database Actions
• Request database schema by user
• Manage schema requests on the Self Service Schema Page

Activate the Self Service Schema in ORDS

1. By default, this feature is not available unless activated. To activate, set the configuration property in ORDS by using the command line or by adding the property to the XML configuration file.
   • From the command line, use the following command:

     ords config set feature.sdw.selfServiceSchema true

   • In the configuration XML file, add the property as shown below:

     <?xml version="1.0" encoding="UTF-8"?>
     <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
     <properties>
     <comment>Saved on Wed Nov 23 16:57:17 UTC 2022</comment>
     <entry key="database.api.enabled">true</entry>
     <entry key="feature.sdw.selfServiceSchema">true</entry>
     <entry key="sdw.dev.doc.root">C:\Users\jsmith\Desktop\static-resources</entry>
     <entry key="standalone.context.path">/ords</entry>
     <entry key="standalone.http.port">8080</entry>
     </properties>

   After the ORDS configuration property is set to true, you can enable or disable the feature from the Database Users page in the Database Actions user interface.

Enable the Self Service Schema Feature in Database Actions

2. In the Database Users screen, click Self Service Schema Settings, which is located next to Create User.
3. In the Self Service Schema settings slider:

   1. Select Enable Feature to enable the Self Service Schema feature.

      The URL to the Request Schema page appears, which can be shared with the user.

   2. Click Save.

   The Self Service Schema tab appears in the header next to User Management on the Database Users screen. Navigate to the Self Service Schema page using this tab.

   Figure 10-1 Self Service Schema Tab

   

Request Database Schema by User

4. In the Request a Schema page, a user has to enter details such as Requested User Name, Full Name, Email, and Justification, click Next and then click Submit.

   Note:

   Click towards the top right corner in the Self Schema Service page (see Figure 10-1) to access the Request page URL.

   

   A confirmation message appears. The user request becomes available on the Self Service Schema page in a card format.

Manage Schema Requests on the Self Service Schema Page

5. You can see and manage user requests on the Schema Requests section in the Self Service Schema page.

   You can set up an SMTP server to automatically send emails to users to notify whether the schema request has been approved or rejected. To define SMTP settings, click on the top right corner of the Schema Requests page. Enter fields such as Server Host, Server Port, Sender Name and Server Encryption to set up the server.

   
6. After SMTP settings are set, you can approve or reject the request by clicking the appropriate button on the card.

   • If you click Approve, a panel with two tabs appears: Request Details and Initial Grants. You can make changes to the schema name and customize the initial roles for the requested schema. By default, the Resource and Connect roles are pre-selected. Click Approve.

     

   • If you click Reject, you are prompted to specify a reason for rejection, which will be sent to the user.

   The schema card is updated and shows the status of the request when it is approved or rejected.

   Note:

   • If you cannot see the request card on the Schema page, check if any filters are set and clear them.
   • After the schema is approved, you can still reject the schema. To do that, click the Actions icon on the card and select Open Request Details. Click Reject, enter a reason for rejection, and click Submit. The status of the card changes to Rejected and an email notification is sent to the user.

   When you click Approve, a notification mail is sent to the user's email address.

   
7. Click Get Started. You are taken to a page where you need to set the password for the new schema. Enter Password and Confirm Password and click Submit.
8. Another mail is sent to the user notifying that the schema has been created and providing a Start Using Schema link. The user can use the link to log in to the schema after providing the new password.
   

   After the schema is created, the status of the user request card on the Schema Requests page changes to Fulfilled.

   Note:

   If you want to remove a schema request after the status changes to Fulfilled, you will need to use queries in the SQL worksheet.