| Server IP : 185.208.173.17 / Your IP : 87.236.161.98 Web Server : Microsoft-IIS/10.0 System : Windows NT SRV8576125506 10.0 build 26100 (Windows Server 2016) AMD64 User : IUSR ( 0) PHP Version : 7.4.13 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/Program Files/MySQL/MySQL Workbench 8.0/swb/shell/lib/mysqlsh/plugins/mrs_plugin/docs/ |
Upload File : |
<!DOCTYPE html>
<html lang="en" dir="ltr">
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.-->
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>MySQL REST Service - SQL Reference</title>
<link rel="shortcut icon" href="images/favicon.png" type="image/x-icon">
<link rel="stylesheet" href="style/style.css">
<!-- cSpell:ignore pagetitle -->
<title>MySQL REST Service - SQL Reference</title>
<style type="text/css">
code {
white-space: pre;
}
</style>
<script>
window.addEventListener('DOMContentLoaded', () => {
window.visibleItems = [];
window.lastVisible = null;
const observer = new IntersectionObserver((entries) => {
// Add active css class to first match
entries.forEach((entry) => {
const id = entry.target.getAttribute('id');
const querySel = document.querySelector(`nav li a[href="#` + id + `"]`);
if (querySel) {
const el = querySel.parentElement;
if (entry.isIntersecting) {
if (!window.visibleItems.includes(el)) {
if (window.visibleItems.length === 0 && window.lastVisible !== null) {
window.lastVisible.classList.remove('active');
window.lastVisible = el;
}
if (window.lastVisible === null) {
window.lastVisible = el;
}
window.visibleItems.push(el);
}
if (!el.classList.contains('active')) {
el.classList.add('active');
}
history.pushState({}, "", "#" + id);
} else {
if (window.visibleItems.includes(el)) {
if (window.visibleItems.length === 1) {
window.lastVisible = el;
} else {
el.classList.remove('active');
}
const index = window.visibleItems.indexOf(el);
if (index > -1) {
window.visibleItems.splice(index, 1);
}
if (window.lastVisible === el && window.visibleItems.length > 0) {
window.lastVisible === window.visibleItems[0];
}
}
}
}
});
}/*, { threshold: 0.1, rootMargin: '0px 0px -100% 0px', root: document.body }*/);
// Track all h that have an `id` applied
document.querySelectorAll('.content h1[id],h2[id]').forEach((section) => {
observer.observe(section);
});
document.querySelectorAll('a[href^="#"]').forEach(anchor => {
anchor.addEventListener('click', function (e) {
e.preventDefault();
document.querySelector(this.getAttribute('href')).scrollIntoView({
behavior: 'smooth'
});
});
});
if (typeof acquireVsCodeApi === "function") {
document.vscode = acquireVsCodeApi();
const addVsCodeDecorations = () => {
const addVsActionBtn = (e, command, caption) => {
const d = document.createElement("button");
d.innerHTML = '<i class="arrow right"></i><p>' + caption + '</p>';
d.onclick = () => {
document.vscode.postMessage({ command, path: e.innerHTML });
}
e.parentNode.insertBefore(d, e.nextSibling)
}
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.endsWith(".sql")) {
addVsActionBtn(e, "openSqlFile", "Open in MySQL Shell");
} else if (e.innerHTML.endsWith(".mrs.json")) {
addVsActionBtn(e, "loadMrsDump", "Load MRS Dump");
} else if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
addVsActionBtn(e, "saveProject", "Save Project");
}
});
}
addVsCodeDecorations();
} else {
document.querySelectorAll("code").forEach((e) => {
if (e.innerHTML.startsWith("VSCodeProject:") > 0) {
e.innerHTML = e.innerHTML.slice("VSCodeProject:".length);
}
});
}
});
// Handle the message inside the webview
window.addEventListener('message', event => {
const message = event.data; // The JSON data our extension sent
switch (message.command) {
case 'goToId':
if (message.id) {
window.location = "#" + message.id;
}
break;
}
});
</script>
</head>
<body>
<div class="page">
<div class="sidebar">
<div class="bookOverview">
<h1>MySQL REST Service</h1>
<h2>
<div class="icon bookIcon"></div> <a href="index.html#document-top">Reference Manual</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="quickstart.html#document-top">Quickstart Guide</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sql.html#document-top">SQL Reference</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="restApi.html#document-top">Core REST APIs</a>
</h2>
<h2>
<div class="icon bookIcon"></div> <a href="sdk.html#document-top">SDK Reference</a>
</h2>
</div>
<div class="sidebarContent">
<nav class="section-nav">
<div class="bookToc">
<br>
<h3>MySQL REST Service - SQL
Reference 2026.3.0+9.6.1</h3>
<ul>
<li><a
href="#introduction"
id="toc-introduction"><span
class="toc-section-number">1</span>
Introduction</a></li>
<li><a
href="#configure-and-create"
id="toc-configure-and-create"><span
class="toc-section-number">2</span>
CONFIGURE and CREATE</a>
<ul>
<li><a
href="#configure-rest-metadata"
id="toc-configure-rest-metadata"><span
class="toc-section-number">2.1</span>
CONFIGURE REST
METADATA</a></li>
<li><a
href="#create-rest-service"
id="toc-create-rest-service"><span
class="toc-section-number">2.2</span>
CREATE REST
SERVICE</a></li>
<li><a
href="#clone-rest-service"
id="toc-clone-rest-service"><span
class="toc-section-number">2.3</span>
CLONE REST
SERVICE</a></li>
<li><a
href="#create-rest-schema"
id="toc-create-rest-schema"><span
class="toc-section-number">2.4</span>
CREATE REST
SCHEMA</a></li>
<li><a
href="#create-rest-view"
id="toc-create-rest-view"><span
class="toc-section-number">2.5</span>
CREATE REST
VIEW</a></li>
<li><a
href="#create-rest-procedure"
id="toc-create-rest-procedure"><span
class="toc-section-number">2.6</span>
CREATE REST
PROCEDURE</a></li>
<li><a
href="#create-rest-function"
id="toc-create-rest-function"><span
class="toc-section-number">2.7</span>
CREATE REST
FUNCTION</a></li>
<li><a
href="#create-rest-content-set"
id="toc-create-rest-content-set"><span
class="toc-section-number">2.8</span>
CREATE REST CONTENT
SET</a></li>
<li><a
href="#create-rest-content-file"
id="toc-create-rest-content-file"><span
class="toc-section-number">2.9</span>
CREATE REST CONTENT
FILE</a></li>
<li><a
href="#create-rest-auth-app"
id="toc-create-rest-auth-app"><span
class="toc-section-number">2.10</span>
CREATE REST AUTH
APP</a></li>
<li><a
href="#create-rest-user"
id="toc-create-rest-user"><span
class="toc-section-number">2.11</span>
CREATE REST
USER</a></li>
<li><a
href="#create-rest-role"
id="toc-create-rest-role"><span
class="toc-section-number">2.12</span>
CREATE REST
ROLE</a></li>
</ul></li>
<li><a href="#alter"
id="toc-alter"><span
class="toc-section-number">3</span>
ALTER</a>
<ul>
<li><a
href="#alter-rest-service"
id="toc-alter-rest-service"><span
class="toc-section-number">3.1</span>
ALTER REST
SERVICE</a></li>
<li><a
href="#alter-rest-auth-app"
id="toc-alter-rest-auth-app"><span
class="toc-section-number">3.2</span>
ALTER REST AUTH
APP</a></li>
<li><a
href="#alter-rest-schema"
id="toc-alter-rest-schema"><span
class="toc-section-number">3.3</span>
ALTER REST
SCHEMA</a></li>
<li><a
href="#alter-rest-view"
id="toc-alter-rest-view"><span
class="toc-section-number">3.4</span>
ALTER REST VIEW</a></li>
<li><a
href="#alter-rest-procedure"
id="toc-alter-rest-procedure"><span
class="toc-section-number">3.5</span>
ALTER REST
PROCEDURE</a></li>
<li><a
href="#alter-rest-function"
id="toc-alter-rest-function"><span
class="toc-section-number">3.6</span>
ALTER REST
FUNCTION</a></li>
<li><a
href="#alter-rest-content-set"
id="toc-alter-rest-content-set"><span
class="toc-section-number">3.7</span>
ALTER REST CONTENT
SET</a></li>
<li><a
href="#alter-rest-user"
id="toc-alter-rest-user"><span
class="toc-section-number">3.8</span>
ALTER REST USER</a></li>
</ul></li>
<li><a href="#drop"
id="toc-drop"><span
class="toc-section-number">4</span>
DROP</a>
<ul>
<li><a
href="#drop-rest-service"
id="toc-drop-rest-service"><span
class="toc-section-number">4.1</span>
DROP REST
SERVICE</a></li>
<li><a
href="#drop-rest-schema"
id="toc-drop-rest-schema"><span
class="toc-section-number">4.2</span>
DROP REST
SCHEMA</a></li>
<li><a
href="#drop-rest-view"
id="toc-drop-rest-view"><span
class="toc-section-number">4.3</span>
DROP REST VIEW</a></li>
<li><a
href="#drop-rest-procedure"
id="toc-drop-rest-procedure"><span
class="toc-section-number">4.4</span>
DROP REST
PROCEDURE</a></li>
<li><a
href="#drop-rest-function"
id="toc-drop-rest-function"><span
class="toc-section-number">4.5</span>
DROP REST
FUNCTION</a></li>
<li><a
href="#drop-rest-content-set"
id="toc-drop-rest-content-set"><span
class="toc-section-number">4.6</span>
DROP REST CONTENT
SET</a></li>
<li><a
href="#drop-rest-content-file"
id="toc-drop-rest-content-file"><span
class="toc-section-number">4.7</span>
DROP REST CONTENT
FILE</a></li>
<li><a
href="#drop-rest-auth-app"
id="toc-drop-rest-auth-app"><span
class="toc-section-number">4.8</span>
DROP REST AUTH
APP</a></li>
<li><a
href="#drop-rest-user"
id="toc-drop-rest-user"><span
class="toc-section-number">4.9</span>
DROP REST USER</a></li>
<li><a
href="#drop-rest-role"
id="toc-drop-rest-role"><span
class="toc-section-number">4.10</span>
DROP REST ROLE</a></li>
</ul></li>
<li><a
href="#grant-and-revoke"
id="toc-grant-and-revoke"><span
class="toc-section-number">5</span>
GRANT and REVOKE</a>
<ul>
<li><a
href="#grant-rest"
id="toc-grant-rest"><span
class="toc-section-number">5.1</span>
GRANT REST</a></li>
<li><a
href="#grant-rest-role"
id="toc-grant-rest-role"><span
class="toc-section-number">5.2</span>
GRANT REST ROLE</a></li>
<li><a
href="#revoke-rest"
id="toc-revoke-rest"><span
class="toc-section-number">5.3</span>
REVOKE REST</a></li>
<li><a
href="#revoke-rest-role"
id="toc-revoke-rest-role"><span
class="toc-section-number">5.4</span>
REVOKE REST
ROLE</a></li>
</ul></li>
<li><a
href="#use-and-show"
id="toc-use-and-show"><span
class="toc-section-number">6</span>
USE and SHOW</a>
<ul>
<li><a href="#use"
id="toc-use"><span
class="toc-section-number">6.1</span>
USE</a></li>
<li><a
href="#show-rest-status"
id="toc-show-rest-status"><span
class="toc-section-number">6.2</span>
SHOW REST
STATUS</a></li>
<li><a
href="#show-rest-services"
id="toc-show-rest-services"><span
class="toc-section-number">6.3</span>
SHOW REST
SERVICES</a></li>
<li><a
href="#show-rest-schemas"
id="toc-show-rest-schemas"><span
class="toc-section-number">6.4</span>
SHOW REST
SCHEMAS</a></li>
<li><a
href="#show-rest-views"
id="toc-show-rest-views"><span
class="toc-section-number">6.5</span>
SHOW REST VIEWS</a></li>
<li><a
href="#show-rest-procedures"
id="toc-show-rest-procedures"><span
class="toc-section-number">6.6</span>
SHOW REST
PROCEDURES</a></li>
<li><a
href="#show-rest-functions"
id="toc-show-rest-functions"><span
class="toc-section-number">6.7</span>
SHOW REST
FUNCTIONS</a></li>
<li><a
href="#show-rest-content-sets"
id="toc-show-rest-content-sets"><span
class="toc-section-number">6.8</span>
SHOW REST CONTENT
SETS</a></li>
<li><a
href="#show-rest-content-files"
id="toc-show-rest-content-files"><span
class="toc-section-number">6.9</span>
SHOW REST CONTENT
FILES</a></li>
<li><a
href="#show-create-rest-content-set"
id="toc-show-create-rest-content-set"><span
class="toc-section-number">6.10</span>
SHOW CREATE REST CONTENT
SET</a></li>
<li><a
href="#show-create-rest-content-file"
id="toc-show-create-rest-content-file"><span
class="toc-section-number">6.11</span>
SHOW CREATE REST CONTENT
FILE</a></li>
<li><a
href="#show-rest-auth-apps"
id="toc-show-rest-auth-apps"><span
class="toc-section-number">6.12</span>
SHOW REST AUTH
APPS</a></li>
<li><a
href="#show-create-rest-service"
id="toc-show-create-rest-service"><span
class="toc-section-number">6.13</span>
SHOW CREATE REST
SERVICE</a></li>
<li><a
href="#show-create-rest-schema"
id="toc-show-create-rest-schema"><span
class="toc-section-number">6.14</span>
SHOW CREATE REST
SCHEMA</a></li>
<li><a
href="#show-create-rest-view"
id="toc-show-create-rest-view"><span
class="toc-section-number">6.15</span>
SHOW CREATE REST
VIEW</a></li>
<li><a
href="#show-create-rest-procedure"
id="toc-show-create-rest-procedure"><span
class="toc-section-number">6.16</span>
SHOW CREATE REST
PROCEDURE</a></li>
<li><a
href="#show-create-rest-function"
id="toc-show-create-rest-function"><span
class="toc-section-number">6.17</span>
SHOW CREATE REST
FUNCTION</a></li>
<li><a
href="#show-create-rest-auth-app"
id="toc-show-create-rest-auth-app"><span
class="toc-section-number">6.18</span>
SHOW CREATE REST AUTH
APP</a></li>
<li><a
href="#show-create-rest-role"
id="toc-show-create-rest-role"><span
class="toc-section-number">6.19</span>
SHOW CREATE REST
ROLE</a></li>
<li><a
href="#show-create-rest-user"
id="toc-show-create-rest-user"><span
class="toc-section-number">6.20</span>
SHOW CREATE REST
USER</a></li>
<li><a
href="#show-rest-roles"
id="toc-show-rest-roles"><span
class="toc-section-number">6.21</span>
SHOW REST ROLES</a></li>
<li><a
href="#show-rest-grants"
id="toc-show-rest-grants"><span
class="toc-section-number">6.22</span>
SHOW REST
GRANTS</a></li>
</ul></li>
<li><a href="#dump"
id="toc-dump"><span
class="toc-section-number">7</span>
DUMP</a>
<ul>
<li><a
href="#dump-rest-project"
id="toc-dump-rest-project"><span
class="toc-section-number">7.1</span>
DUMP REST
PROJECT</a></li>
<li><a
href="#dump-rest-service"
id="toc-dump-rest-service"><span
class="toc-section-number">7.2</span>
DUMP REST
SERVICE</a></li>
</ul></li>
</ul>
</div>
</nav>
</div>
</div>
<div class="content">
<div class="header" id="document-top">
<h1>
<div class="iconBig bookIcon"></div> MySQL REST
Service - SQL Reference
</h1>
</div>
<div class="main">
<p>This document discusses the MySQL REST Service (MRS)
DDL - SQL Extension. The MRS DDL - SQL Extension adds a
set of MRS specific DDL statements to the MySQL Shell
SQL handling in order to provide a familiar management
interface to MySQL developers and DBAs.</p>
<p><strong>Chapter Overview</strong></p>
<ul>
<li><a href="#introduction">Introduction to the MRS DDL
Extension</a></li>
<li><a href="#configure-and-create">MRS SQL Reference:
CONFIGURE and CREATE</a></li>
<li><a href="#alter">MRS SQL Reference: ALTER</a></li>
<li><a href="#drop">MRS SQL Reference: DROP</a></li>
<li><a href="#grant-and-revoke">MRS SQL Reference: GRANT
and REVOKE</a></li>
<li><a href="#use-and-show">MRS SQL Reference: USE and
SHOW</a></li>
</ul>
<hr />
<p>Please also see</p>
<ul>
<li><strong><a href="index.html">MySQL REST Service -
Developer’s Guide</a></strong> - This book explains how
to install and configure the MySQL REST Service (MRS) as
well as how to access the data through REST calls.</li>
<li><strong><a href="quickstart.html">MySQL REST Service
- Quickstart Guide</a></strong> - This book provides a
hands-on approach to learning how to use the MySQL REST
service.</li>
<li><strong><a href="restApi.html">MySQL REST Service -
Core REST APIs</a></strong> - This book provides
examples of using the MySQL REST Service queries and
other operations against tables and views after you have
REST-enabled them.</li>
<li><strong><a href="sdk.html">MySQL REST Service - SDK
Reference</a></strong> - This book explains how to work
with the MRS Software Development Kit and discusses the
Client APIs.</li>
</ul>
<hr />
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="1" id="introduction"><span
class="header-section-number">1</span> Introduction</h1>
<p>It has been an essential goal of the MySQL REST
Service (MRS) to provide a management interface that
feels familiar to MySQL developers and DBAs and
integrates well into their existing processes.</p>
<p>For this purpose the MySQL REST Service plugins
directly into the <a
href="https://dev.mysql.com/downloads/shell/">MySQL
Shell</a> and <a
href="https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code">MySQL
Shell for VS Code</a>. It extends the available SQL
commands to include DDL (Data Definition Language)
statements that allow managing the MySQL REST Service in
an easy and seamless way.</p>
<p>This makes the process of creating a MySQL REST
Service for your application as easy as creating a
database schema or table.</p>
<p><strong><em>Example</em></strong></p>
<p>The following script configures the MySQL REST
Service, creates a new REST service
<code>/myService</code> and adds a REST schema
<code>/sakila</code> and a REST data mapping view
<code>/actor</code> that lists all actors and their film
titles.</p>
<div class="sourceCode" id="cb1"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a>CONFIGURE REST METADATA;</span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-3"><a href="#cb1-3" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST SERVICE <span class="op">/</span>myService;</span>
<span id="cb1-4"><a href="#cb1-4" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST SERVICE <span class="op">/</span>myService;</span>
<span id="cb1-5"><a href="#cb1-5" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-6"><a href="#cb1-6" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">FROM</span> `sakila`;</span>
<span id="cb1-7"><a href="#cb1-7" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span>
<span id="cb1-8"><a href="#cb1-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-9"><a href="#cb1-9" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>actor</span>
<span id="cb1-10"><a href="#cb1-10" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`actor` {</span>
<span id="cb1-11"><a href="#cb1-11" aria-hidden="true" tabindex="-1"></a> actorId: actor_id @SORTABLE,</span>
<span id="cb1-12"><a href="#cb1-12" aria-hidden="true" tabindex="-1"></a> firstName: first_name,</span>
<span id="cb1-13"><a href="#cb1-13" aria-hidden="true" tabindex="-1"></a> lastName: last_name,</span>
<span id="cb1-14"><a href="#cb1-14" aria-hidden="true" tabindex="-1"></a> lastUpdate: last_update,</span>
<span id="cb1-15"><a href="#cb1-15" aria-hidden="true" tabindex="-1"></a> filmActor: sakila.film_actor @UNNEST {</span>
<span id="cb1-16"><a href="#cb1-16" aria-hidden="true" tabindex="-1"></a> film: sakila.film @UNNEST {</span>
<span id="cb1-17"><a href="#cb1-17" aria-hidden="true" tabindex="-1"></a> title: title</span>
<span id="cb1-18"><a href="#cb1-18" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb1-19"><a href="#cb1-19" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb1-20"><a href="#cb1-20" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb1-21"><a href="#cb1-21" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<blockquote>
<p>Note: Please ensure to install the <a
href="https://downloads.mysql.com/docs/sakila-db.zip">MySQL
sakila example database schema</a> before running the
MRS DDL script above.</p>
</blockquote>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="2" id="configure-and-create"><span
class="header-section-number">2</span> CONFIGURE and
CREATE</h1>
<h2 data-number="2.1" id="configure-rest-metadata"><span
class="header-section-number">2.1</span> CONFIGURE REST
METADATA</h2>
<p>The CONFIGURE REST METADATA statement is used to
perform the initial configuration of the MySQL REST
Service on a MySQL Server instance or InnoDB
Cluster/Set.</p>
<p>It will create the
<code>mysql_rest_service_metadata</code> database
schema.</p>
<p>Please note that the MySQL account used to execute
the statement needs the required privileges to create
database schemas.</p>
<p><strong><em>Syntax</em></strong></p>
<pre class="antlr"><code>configureRestMetadataStatement:
CONFIGURE REST METADATA restMetadataOptions?
;
restMetadataOptions: (
enabledDisabled
| jsonOptions
| updateIfAvailable
)+
;</code></pre>
<p>configureRestMetadataStatement ::= <img
src="sections/sql/../../images/sql/configureRestMetadataStatement.svg"
title="configureRestMetadataStatement"
alt="configureRestMetadataStatement" /></p>
<p>restMetadataOptions ::= <img
src="sections/sql/../../images/sql/restMetadataOptions.svg"
title="restMetadataOptions"
alt="restMetadataOptions" /></p>
<p><strong><em>Example</em></strong></p>
<div class="sourceCode" id="cb3"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb3-1"><a href="#cb3-1" aria-hidden="true" tabindex="-1"></a>CONFIGURE REST METADATA;</span></code></pre></div>
<h3 data-number="2.1.1"
id="enable-or-disable-the-mysql-rest-service"><span
class="header-section-number">2.1.1</span> Enable or
Disable the MySQL REST Service</h3>
<p>The enabledDisabled option specifies if the MySQL
REST Service should be enabled or disabled after the
configuration operation. The default is set to enable
the MySQL REST Service.</p>
<pre class="antlr"><code>enabledDisabled:
ENABLED
| DISABLED
;</code></pre>
<p>enabledDisabled ::= <img
src="sections/sql/../../images/sql/enabledDisabled.svg"
title="enabledDisabled" alt="enabledDisabled" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example configures the MySQL REST
Service and enables it and updates the metadata schema,
if possible.</p>
<div class="sourceCode" id="cb5"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a>CONFIGURE REST METADATA</span>
<span id="cb5-2"><a href="#cb5-2" aria-hidden="true" tabindex="-1"></a> ENABLED</span>
<span id="cb5-3"><a href="#cb5-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">UPDATE</span> <span class="cf">IF</span> AVAILABLE;</span></code></pre></div>
<p>The following example configures the MySQL REST
Service and enables the GTID cache and sets
authentication options.</p>
<div class="sourceCode" id="cb6"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a>CONFIGURE REST METADATA</span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a> ENABLED</span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a> OPTIONS {</span>
<span id="cb6-4"><a href="#cb6-4" aria-hidden="true" tabindex="-1"></a> <span class="ot">"gtid"</span>: {</span>
<span id="cb6-5"><a href="#cb6-5" aria-hidden="true" tabindex="-1"></a> <span class="ot">"cache"</span>: {</span>
<span id="cb6-6"><a href="#cb6-6" aria-hidden="true" tabindex="-1"></a> <span class="ot">"enable"</span>: <span class="kw">true</span>,</span>
<span id="cb6-7"><a href="#cb6-7" aria-hidden="true" tabindex="-1"></a> <span class="ot">"refreshRate"</span>: <span class="dv">5</span>,</span>
<span id="cb6-8"><a href="#cb6-8" aria-hidden="true" tabindex="-1"></a> <span class="ot">"refreshWhenIncreasesBy"</span>: <span class="dv">500</span></span>
<span id="cb6-9"><a href="#cb6-9" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb6-10"><a href="#cb6-10" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb6-11"><a href="#cb6-11" aria-hidden="true" tabindex="-1"></a> <span class="ot">"authentication"</span>: {</span>
<span id="cb6-12"><a href="#cb6-12" aria-hidden="true" tabindex="-1"></a> <span class="ot">"throttling"</span>: {</span>
<span id="cb6-13"><a href="#cb6-13" aria-hidden="true" tabindex="-1"></a> <span class="ot">"perAccount"</span>: {</span>
<span id="cb6-14"><a href="#cb6-14" aria-hidden="true" tabindex="-1"></a> <span class="ot">"minimumTimeBetweenRequestsInMs"</span>: <span class="dv">1500</span>,</span>
<span id="cb6-15"><a href="#cb6-15" aria-hidden="true" tabindex="-1"></a> <span class="ot">"maximumAttemptsPerMinute"</span>: <span class="dv">5</span></span>
<span id="cb6-16"><a href="#cb6-16" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb6-17"><a href="#cb6-17" aria-hidden="true" tabindex="-1"></a> <span class="ot">"perHost"</span>: {</span>
<span id="cb6-18"><a href="#cb6-18" aria-hidden="true" tabindex="-1"></a> <span class="ot">"minimumTimeBetweenRequestsInMs"</span>: <span class="dv">1500</span>,</span>
<span id="cb6-19"><a href="#cb6-19" aria-hidden="true" tabindex="-1"></a> <span class="ot">"maximumAttemptsPerMinute"</span>: <span class="dv">5</span></span>
<span id="cb6-20"><a href="#cb6-20" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb6-21"><a href="#cb6-21" aria-hidden="true" tabindex="-1"></a> <span class="ot">"blockWhenAttemptsExceededInSeconds"</span>: <span class="dv">120</span></span>
<span id="cb6-22"><a href="#cb6-22" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb6-23"><a href="#cb6-23" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb6-24"><a href="#cb6-24" aria-hidden="true" tabindex="-1"></a> };</span></code></pre></div>
<h3 data-number="2.1.2"
id="rest-configuration-json-options"><span
class="header-section-number">2.1.2</span> REST
Configuration Json Options</h3>
<p>The jsonOptions allow to set a number of specific
options for the MySQL REST Service. Specify the
<code>MERGE</code> keyword to merge the given options
with the existing options. If <code>MERGE</code> is
omitted, all existing options will be overwritten with
the specified options.</p>
<pre class="antlr"><code>jsonOptions:
MERGE? OPTIONS jsonValue
;</code></pre>
<p>jsonOptions ::= <img
src="sections/sql/../../images/sql/jsonOptions.svg"
title="jsonOptions" alt="jsonOptions" /></p>
<p>These options can include the following JSON
keys.</p>
<ul>
<li><code>authentication</code>
<ul>
<li>Defines global authentication parameters valid for
all MySQL Routers</li>
<li><code>throttling</code>
<ul>
<li>Used to limit the authentication attempts to prevent
brute force attacks on account information</li>
<li><code>perAccount</code>
<ul>
<li>Settings that apply per MRS account</li>
<li><code>minimumTimeBetweenRequestsInMs</code>
<ul>
<li>Sets the minimum time between connection attempts.
If a client tries to authenticate faster than that the
request will be rejected. The value is given in
milliseconds.</li>
</ul></li>
<li><code>maximumAttemptsPerMinute</code>
<ul>
<li>Sets the maximum amount of attempts per minute. If a
client tries to authenticate more often that that
further attempts will be blocked for the amount of
seconds specified in the
<code>blockWhenAttemptsExceededInSeconds</code>
value.</li>
</ul></li>
</ul></li>
<li><code>perHost</code>
<ul>
<li>Settings that apply per host from where a client
tries to connect</li>
<li><code>minimumTimeBetweenRequestsInMs</code></li>
<li><code>maximumAttemptsPerMinute</code></li>
</ul></li>
<li><code>blockWhenAttemptsExceededInSeconds</code>
<ul>
<li>Sets the amount of time the account or client host
will be blocked from authentication. The value is given
in seconds.</li>
</ul></li>
</ul></li>
</ul></li>
<li><code>gtid</code>
<ul>
<li>Defines global settings for the MySQL GTID handling,
using the following fields.</li>
<li><code>cache</code>
<ul>
<li>Is used to configure the MySQL Router’s GTID
cache.</li>
<li><code>enable</code>
<ul>
<li>If set to <code>true</code> GTIDs will be cached by
the MySQL Router.</li>
</ul></li>
<li><code>refreshRate</code>
<ul>
<li>Defines how often the GTID cache will be refreshed.
Set seconds, e.g. 5.</li>
</ul></li>
<li><code>refreshWhenIncreasesBy</code>
<ul>
<li>In addition to the time based refresh, the GTID
cache can also be refreshed based on the number of
transactions that happened since the last refresh. Set
in number of transactions, e.g. 500.</li>
</ul></li>
</ul></li>
</ul></li>
<li><code>responseCache</code>
<ul>
<li>Global options for the REST endpoint response cache,
which keeps an in-memory cache of responses to GET
requests on tables, views, procedures and functions. To
enable caching of an endpoint, you must also set the
<code>cacheTimeToLive</code> option for each object to
be cached.</li>
<li><code>maxCacheSize</code>
<ul>
<li>Maximum size of the cache. Default is 1M.</li>
</ul></li>
</ul></li>
<li><code>fileCache</code>
<ul>
<li>Global options for the static file data cache, which
keeps an in-memory cache of responses to GET requests on
content set files.</li>
<li><code>maxCacheSize</code>
<ul>
<li>Maximum size of the cache. Default is 1M.</li>
</ul></li>
</ul></li>
<li><code>defaultStaticContent</code>
<ul>
<li>Allows the definition of static content for the root
path <code>/</code> that will be returned for file paths
matching the given JSON keys. A JSON key
<code>index.html</code> will be served as
<code>/index.html</code> by the MySQL Router. The file
content needs to be Base64 encoded. If the same JSON key
is used for <code>defaultStaticContent</code> as well as
for <code>defaultRedirects</code>, the redirect is
prioritized.</li>
</ul></li>
<li><code>defaultRedirects</code>
<ul>
<li>Is used to define internal redirects performed by
the MySQL Router. This can be used to expose content of
a REST service on the root path <code>/</code>. A JSON
key <code>index.html</code> holding the value
<code>/myService/myContentSet/index.html</code> will
exposed the corresponding file from the given path as
<code>/index.html</code>.</li>
</ul></li>
<li><code>directoryIndexDirective</code>
<ul>
<li>Holds an ordered list of files that should be
returned when a directory path has been requested. The
first matching file that is available will be returned.
The <code>directoryIndexDirective</code> is recursively
applies to all directory paths exposed by the MySQL
Router. To change the
<code>directoryIndexDirective</code> for a given REST
service or REST static content set, the corresponding
option needs to be set for those objects.</li>
</ul></li>
</ul>
<p>All other keys will be ignored and can be used to
store custom metadata about the service. It is a good
practice to include a unique prefix when adding custom
keys to avoid them be overwritten by future MRS
options.</p>
<p><strong><em>Examples</em></strong></p>
<p>The following JsonValue will define the static
content for <code>/index.html</code>,
<code>/favicon.ico</code> and <code>/favicon.svg</code>.
It will also direct the MySQL Router to return the
contents of <code>/index.html</code> if the root path
<code>/</code> is requested,
e.g. <code>https://my.example.com/</code></p>
<div class="sourceCode" id="cb8"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb8-1"><a href="#cb8-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb8-2"><a href="#cb8-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"defaultStaticContent"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb8-3"><a href="#cb8-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"index.html"</span><span class="fu">:</span> <span class="st">"PCFET0NUW..."</span><span class="fu">,</span></span>
<span id="cb8-4"><a href="#cb8-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"favicon.ico"</span><span class="fu">:</span> <span class="st">"AAABAAMAM..."</span><span class="fu">,</span></span>
<span id="cb8-5"><a href="#cb8-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"favicon.svg"</span><span class="fu">:</span> <span class="st">"PD94bWwmV..."</span></span>
<span id="cb8-6"><a href="#cb8-6" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb8-7"><a href="#cb8-7" aria-hidden="true" tabindex="-1"></a> <span class="dt">"directoryIndexDirective"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb8-8"><a href="#cb8-8" aria-hidden="true" tabindex="-1"></a> <span class="st">"index.html"</span></span>
<span id="cb8-9"><a href="#cb8-9" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span></span>
<span id="cb8-10"><a href="#cb8-10" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>In this example an internal redirect of
<code>/index.html</code> to
<code>/myService/myContentSet/index.html</code> is
performed, directly serving the <code>index.html</code>
page of <code>/myService/myContentSet</code>. This
overwrites the <code>index.html</code> definition in
<code>defaultStaticContent</code>.</p>
<p>This is useful to directly serve a specific app on
the root path <code>/</code>.</p>
<div class="sourceCode" id="cb9"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"defaultStaticContent"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"index.html"</span><span class="fu">:</span> <span class="st">"PCFET0NUW..."</span><span class="fu">,</span></span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"favicon.ico"</span><span class="fu">:</span> <span class="st">"AAABAAMAM..."</span><span class="fu">,</span></span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"favicon.svg"</span><span class="fu">:</span> <span class="st">"PD94bWwmV..."</span></span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a> <span class="dt">"defaultRedirects"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb9-8"><a href="#cb9-8" aria-hidden="true" tabindex="-1"></a> <span class="dt">"index.html"</span><span class="fu">:</span> <span class="st">"/myService/myContentSet/index.html"</span></span>
<span id="cb9-9"><a href="#cb9-9" aria-hidden="true" tabindex="-1"></a> <span class="fu">},</span></span>
<span id="cb9-10"><a href="#cb9-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"directoryIndexDirective"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb9-11"><a href="#cb9-11" aria-hidden="true" tabindex="-1"></a> <span class="st">"index.html"</span></span>
<span id="cb9-12"><a href="#cb9-12" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span></span>
<span id="cb9-13"><a href="#cb9-13" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h3 data-number="2.1.3"
id="updating-the-mysql-rest-service-metadata-schema"><span
class="header-section-number">2.1.3</span> Updating the
MySQL REST Service Metadata Schema</h3>
<p>If the updateIfAvailable is defined, the configure
operation will include an update of the
<code>mysql_rest_service_metadata</code> database
schema.</p>
<pre class="antlr"><code>updateIfAvailable:
UPDATE (IF AVAILABLE)?
;</code></pre>
<p>updateIfAvailable ::= <img
src="sections/sql/../../images/sql/updateIfAvailable.svg"
title="updateIfAvailable" alt="updateIfAvailable" /></p>
<h2 data-number="2.2" id="create-rest-service"><span
class="header-section-number">2.2</span> CREATE REST
SERVICE</h2>
<p>The CREATE REST SERVICE statement is used to create a
new or replace an existing REST service.</p>
<p>The MySQL REST Service supports the creation of many
individual REST services. It is good practice to create
a separate REST service for each REST application.</p>
<p>Each REST service can have its own options,
authentication apps and supports a different set of
authentication users.</p>
<p>Please note that service will not be published at
creation time by default. They will only be served by
MySQL Routers that are bootstrapped for development
purposes. To publish a REST service after all REST
schemas and REST objects have been created please use
the <a
href="#alter-rest-service"><code>ALTER REST SERVICE</code></a>
statement and set the PUBLISHED option.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestServiceStatement: (
CREATE OR REPLACE REST SERVICE
| CREATE REST SERVICE (
IF NOT EXISTS
)?
) serviceRequestPath restServiceOptions?
;
serviceRequestPath:
serviceDevelopersIdentifier? requestPathIdentifier
;
restServiceOptions: (
enabledDisabled
| publishedUnpublished
| restProtocol
| restAuthentication
| jsonOptions
| comments
| metadata
| addAuthApp
| removeAuthApp
)+
;</code></pre>
<p>createRestServiceStatement ::= <img
src="sections/sql/../../images/sql/createRestServiceStatement.svg"
title="CREATE REST SERVICE Statement"
alt="CREATE REST SERVICE Statement" /></p>
<p>serviceRequestPath ::= <img
src="sections/sql/../../images/sql/serviceRequestPath.svg"
title="serviceRequestPath"
alt="serviceRequestPath" /></p>
<p>restServiceOptions ::= <img
src="sections/sql/../../images/sql/restServiceOptions.svg"
title="restServiceOptions"
alt="restServiceOptions" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example creates a REST service
<code>/myService</code>. Please note that you need to
set the <code>PUBLISHED</code> option to make the REST
service publicly available.</p>
<div class="sourceCode" id="cb12"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST SERVICE <span class="op">/</span>myService</span>
<span id="cb12-2"><a href="#cb12-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">COMMENT</span> <span class="ot">"A simple REST service"</span>;</span></code></pre></div>
<p>The next example shows how to set the REST service
options.</p>
<div class="sourceCode" id="cb13"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST SERVICE <span class="op">/</span>myTestService</span>
<span id="cb13-2"><a href="#cb13-2" aria-hidden="true" tabindex="-1"></a> COMMENTS <span class="ot">"A simple REST service"</span></span>
<span id="cb13-3"><a href="#cb13-3" aria-hidden="true" tabindex="-1"></a> AUTHENTICATION</span>
<span id="cb13-4"><a href="#cb13-4" aria-hidden="true" tabindex="-1"></a> PATH <span class="ot">"/authentication"</span></span>
<span id="cb13-5"><a href="#cb13-5" aria-hidden="true" tabindex="-1"></a> REDIRECTION <span class="kw">DEFAULT</span></span>
<span id="cb13-6"><a href="#cb13-6" aria-hidden="true" tabindex="-1"></a> <span class="kw">VALIDATION</span> <span class="kw">DEFAULT</span></span>
<span id="cb13-7"><a href="#cb13-7" aria-hidden="true" tabindex="-1"></a> PAGE CONTENT <span class="kw">DEFAULT</span></span>
<span id="cb13-8"><a href="#cb13-8" aria-hidden="true" tabindex="-1"></a> OPTIONS {</span>
<span id="cb13-9"><a href="#cb13-9" aria-hidden="true" tabindex="-1"></a> <span class="ot">"headers"</span>: {</span>
<span id="cb13-10"><a href="#cb13-10" aria-hidden="true" tabindex="-1"></a> <span class="ot">"Access-Control-Allow-Credentials"</span>: <span class="ot">"true"</span>,</span>
<span id="cb13-11"><a href="#cb13-11" aria-hidden="true" tabindex="-1"></a> <span class="ot">"Access-Control-Allow-Headers"</span>: <span class="ot">"Content-Type, Authorization, X-Requested-With, Origin, X-Auth-Token"</span>,</span>
<span id="cb13-12"><a href="#cb13-12" aria-hidden="true" tabindex="-1"></a> <span class="ot">"Access-Control-Allow-Methods"</span>: <span class="ot">"GET, POST, PUT, DELETE, OPTIONS"</span></span>
<span id="cb13-13"><a href="#cb13-13" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb13-14"><a href="#cb13-14" aria-hidden="true" tabindex="-1"></a> <span class="ot">"http"</span>: {</span>
<span id="cb13-15"><a href="#cb13-15" aria-hidden="true" tabindex="-1"></a> <span class="ot">"allowedOrigin"</span>: <span class="ot">"auto"</span></span>
<span id="cb13-16"><a href="#cb13-16" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb13-17"><a href="#cb13-17" aria-hidden="true" tabindex="-1"></a> <span class="ot">"logging"</span>: {</span>
<span id="cb13-18"><a href="#cb13-18" aria-hidden="true" tabindex="-1"></a> <span class="ot">"exceptions"</span>: <span class="kw">true</span>,</span>
<span id="cb13-19"><a href="#cb13-19" aria-hidden="true" tabindex="-1"></a> <span class="ot">"request"</span>: {</span>
<span id="cb13-20"><a href="#cb13-20" aria-hidden="true" tabindex="-1"></a> <span class="ot">"body"</span>: <span class="kw">true</span>,</span>
<span id="cb13-21"><a href="#cb13-21" aria-hidden="true" tabindex="-1"></a> <span class="ot">"headers"</span>: <span class="kw">true</span></span>
<span id="cb13-22"><a href="#cb13-22" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb13-23"><a href="#cb13-23" aria-hidden="true" tabindex="-1"></a> <span class="ot">"response"</span>: {</span>
<span id="cb13-24"><a href="#cb13-24" aria-hidden="true" tabindex="-1"></a> <span class="ot">"body"</span>: <span class="kw">true</span>,</span>
<span id="cb13-25"><a href="#cb13-25" aria-hidden="true" tabindex="-1"></a> <span class="ot">"headers"</span>: <span class="kw">true</span></span>
<span id="cb13-26"><a href="#cb13-26" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb13-27"><a href="#cb13-27" aria-hidden="true" tabindex="-1"></a> },</span>
<span id="cb13-28"><a href="#cb13-28" aria-hidden="true" tabindex="-1"></a> <span class="ot">"returnInternalErrorDetails"</span>: <span class="kw">true</span>,</span>
<span id="cb13-29"><a href="#cb13-29" aria-hidden="true" tabindex="-1"></a> <span class="ot">"includeLinksInResults"</span>: <span class="kw">false</span></span>
<span id="cb13-30"><a href="#cb13-30" aria-hidden="true" tabindex="-1"></a> };</span></code></pre></div>
<h3 data-number="2.2.1"
id="building-a-servicerequestpath"><span
class="header-section-number">2.2.1</span> Building a
ServiceRequestPath</h3>
<p>When creating or accessing a REST service a
<code>serviceRequestPath</code> has to be specified
which uniquely identifies the REST service across the
MySQL REST Service.</p>
<p>It consists of three components.</p>
<ul>
<li><code>serviceDevelopersIdentifier</code> (optional)
- When set, the REST service is only available to the
developers listed.</li>
<li><code>requestPathIdentifier</code> - The URL context
root path the REST service will be served from.</li>
</ul>
<p>In many cases setting the
<code>requestPathIdentifier</code> will be
sufficient.</p>
<p>The <code>serviceDevelopersIdentifier</code> will be
set automatically when a REST service is cloned for
development by a given developer. Should such a REST
service be made available to more developers, the list
of developers can be extended via an <a
href="#alter-rest-service"><code>ALTER REST SERVICE</code></a>
command.</p>
<pre class="antlr"><code>serviceDevelopersIdentifier:
serviceDeveloperIdentifier (
COMMA serviceDeveloperIdentifier
)* AT_SIGN
;
requestPathIdentifier:
REST_REQUEST_PATH
| BACK_TICK_QUOTED_ID
| {if AnsiQuotes} DOUBLE_QUOTED_TEXT
;</code></pre>
<p>serviceDevelopersIdentifier ::= <img
src="sections/sql/../../images/sql/serviceDevelopersIdentifier.svg"
title="serviceDevelopersIdentifier"
alt="serviceDevelopersIdentifier" /></p>
<p>requestPathIdentifier ::= <img
src="sections/sql/../../images/sql/requestPathIdentifier.svg"
title="requestPathIdentifier"
alt="requestPathIdentifier" /></p>
<h3 data-number="2.2.2"
id="enabling-or-disabling-a-rest-service-at-creation-time"><span
class="header-section-number">2.2.2</span> Enabling or
Disabling a REST Service at Creation Time</h3>
<p>The <code>enabledDisabled</code> option specifies
whether the REST service should be enabled or disabled.
REST services are created in the ENABLED state by
default. The state can be changed via the <a
href="#alter-rest-service"><code>ALTER REST SERVICE</code></a>
statement.</p>
<p>Please also see the <code>publishedUnpublished</code>
option which determines if a REST service is served by
MySQL Routers.</p>
<pre class="antlr"><code>enabledDisabled:
ENABLED
| DISABLED
;</code></pre>
<p>enabledDisabled ::= <img
src="sections/sql/../../images/sql/enabledDisabled.svg"
title="enabledDisabled" alt="enabledDisabled" /></p>
<h3 data-number="2.2.3"
id="publishing-a-rest-service-at-creation-time"><span
class="header-section-number">2.2.3</span> Publishing a
REST Service at Creation Time</h3>
<p>The <code>publishedUnpublished</code> option
specifies whether the REST service should be in
<code>PUBLISHED</code> or <code>UNPUBLISHED</code>
state. REST services are created in the
<code>UNPUBLISHED</code> state by default.</p>
<p>A REST service in <code>UNPUBLISHED</code> state will
only be served by MySQL Routers that are bootstrapped
for development using the
<code>--mrs-development <user></code> option. To
make a REST service publicly available on all MySQL
Routers it needs to be set to the <code>PUBLISHED</code>
state using the <a
href="#alter-rest-service"><code>ALTER REST SERVICE</code></a>
statement.</p>
<pre class="antlr"><code>publishedUnpublished:
PUBLISHED
| UNPUBLISHED
;</code></pre>
<p>publishedUnpublished ::= <img
src="sections/sql/../../images/sql/publishedUnpublished.svg"
title="publishedUnpublished"
alt="publishedUnpublished" /></p>
<h3 data-number="2.2.4"
id="setting-the-rest-service-protocol"><span
class="header-section-number">2.2.4</span> Setting the
REST Service Protocol</h3>
<p>In general it is advised to run the MySQL REST
Service using HTTPS only and changing the REST service
protocol default (HTTPS) is not required.</p>
<p>There might still be special use cases when
configuring the MySQL Router using HTTP is acceptable,
e.g. when using a reverse proxy on the same machine that
is handling the HTTPS part and not using MySQL internal
authentication which requires passwords to be
transferred in plain text. But even in that specific
example the REST service protocol must be set to HTTPS
as the reverse proxy offers the REST service via
HTTPS.</p>
<p>Should there still be a configuration setup that
requires the REST service to be accessible by clients
via HTTP, the REST service protocol can be switched to
HTTP.</p>
<p>This setting is used in one place.</p>
<ul>
<li>When performing an OAuth2 authentication request,
the protocol is used to build the redirect URL parameter
in the first authentication request to the OAuth2
server. The protocol used in the redirect URL parameter
must match the external protocol the REST service is
reachable on. In case of using a reverse proxy, the
<code>X-Forwarded-Proto</code> request header will
overwrite this setting when made available by the
proxy.</li>
</ul>
<pre class="antlr"><code>restProtocol:
PROTOCOL (HTTP | HTTPS)
;</code></pre>
<h3 data-number="2.2.5"
id="assigning-a-rest-authentication-app-to-a-rest-service"><span
class="header-section-number">2.2.5</span> Assigning a
REST Authentication App to a REST Service</h3>
<p>To enable authentication for a REST service, a REST
authentication app needs to be linked to the REST
service. REST authentication apps are created using the
<a href="#create-rest-auth-app">CREATE REST AUTH APP</a>
statement.</p>
<p>REST authentication apps can be linked while creating
the REST service or they can be added alter using the <a
href="#alter-rest-service">ALTER REST SERVICE</a>
statement.</p>
<pre class="antlr"><code>addAuthApp:
ADD AUTH APP authAppName (IF EXISTS)?
;</code></pre>
<p>addAuthApp ::= <img
src="sections/sql/../../images/sql/addAuthApp.svg"
title="addAuthApp" alt="addAuthApp" /></p>
<h3 data-number="2.2.6"
id="rest-service-authentication-settings"><span
class="header-section-number">2.2.6</span> REST Service
Authentication Settings</h3>
<p>Each REST service can be configured with specific
authentication settings.</p>
<pre class="antlr"><code>restAuthentication:
AUTHENTICATION (
authPath
| authRedirection
| authValidation
| authPageContent
)*
;
authPath:
PATH quotedTextOrDefault
;
authRedirection:
REDIRECTION quotedTextOrDefault
;
authValidation:
VALIDATION quotedTextOrDefault
;
authPageContent:
PAGE CONTENT quotedTextOrDefault
;</code></pre>
<p>restAuthentication ::= <img
src="sections/sql/../../images/sql/restAuthentication.svg"
title="restAuthentication"
alt="restAuthentication" /></p>
<p>authPath ::= <img
src="sections/sql/../../images/sql/authPath.svg"
title="authPath" alt="authPath" /></p>
<p>authRedirection ::= <img
src="sections/sql/../../images/sql/authRedirection.svg"
title="authRedirection" alt="authRedirection" /></p>
<p>authValidation ::= <img
src="sections/sql/../../images/sql/authValidation.svg"
title="authValidation" alt="authValidation" /></p>
<p>authPageContent ::= <img
src="sections/sql/../../images/sql/authPageContent.svg"
title="authPageContent" alt="authPageContent" /></p>
<ul>
<li>AUTHENTICATION PATH
<ul>
<li>The html path used for authentication handling for
this REST service. Specified as a sub-path to the REST
service path. If not explicitly set, the default is path
is <code>/authentication</code> is used.</li>
<li>The following endpoints will be made available for
<code><service_path>/<auth_path></code>
<ul>
<li><code>/login</code></li>
<li><code>/status</code></li>
<li><code>/logout</code></li>
<li><code>/completed</code></li>
</ul></li>
</ul></li>
<li>AUTHENTICATION REDIRECTION
<ul>
<li>The authentication workflow will redirect to this
URL after successful- or failed login. Specified as a
sub-path to the REST service path. If this option is not
set explicitly, the workflow will redirect to
<code><service_path>/<auth_path>/completed</code>
if the
<code><service_path>/<auth_path>/login?onCompletionRedirect</code>
parameter has not been set.</li>
</ul></li>
<li>AUTHENTICATION VALIDATION
<ul>
<li>A regular expression to validate the
<code><service_path>/<auth_path>/login?onCompletionRedirect</code>
parameter. If set, this allows to limit the possible
URLs an application can specify for this parameter.</li>
</ul></li>
<li>AUTHENTICATION PAGE CONTENT
<ul>
<li>If this option is set its content will replace the
page content of the
<code><service_path>/<auth_path>/completed</code>
page.</li>
</ul></li>
</ul>
<h3 data-number="2.2.7"
id="rest-service-json-options"><span
class="header-section-number">2.2.7</span> REST Service
Json Options</h3>
<p>The jsonOptions allow to set a number of specific
options for the service. Specify the <code>MERGE</code>
keyword to merge the given options with the existing
options. If <code>MERGE</code> is omitted, all existing
options will be overwritten with the specified
options.</p>
<pre class="antlr"><code>jsonOptions:
MERGE? OPTIONS jsonValue
;
jsonOptions ::=
These options can include the following JSON keys.
- `headers` - Allows the specification of HTTP headers. Please refer to the HTTP header documentation for details.
- `http`
- `allowedOrigin` - The setting for Access-Control-Allow-Origin HTTP header. Can either be set to `*`, `null`, `<origin>` or `auto`. When set to `auto`, the MySQL Routers will return the origin of the specific client making the request.
- `httpMethodsAllowedForUnauthorizedAccess` - If a REST objects is configured to not require authentication, only GET is allowed by default. In a testing environment it might be desirable to allow all HTTP methods. In that case this option can be set to a list of allowed methods, e.g. ["GET", "POST", "PUT", "DELETE"]
- `logging`
- `exceptions` - If exceptions should be logged.
- `requests`
- `body` - If the content of request bodies should be logged.
- `headers` - If the content of request headers should be logged.
- `response`
- `body` - If the content of response bodies should be logged.
- `headers` - If the content of response headers should be logged.
- `returnInternalErrorDetails` - If internal errors should be returned. This is useful for application development but should be turned off for production deployments.
- `includeLinksInResults` - If set to false, the results do not include navigation links.
- `defaultStaticContent` - Allows the definition of static content for `request path` of the REST service that will be returned for file paths matching the given JSON keys. A JSON key `index.html` will be served as `/myService/index.html` by the MySQL Router if the `request path` of the REST service has been set to `/myService`. The file content needs to be Base64 encoded. If the same JSON key is used for `defaultStaticContent` as well as for `defaultRedirects`, the redirect is prioritized.
- `defaultRedirects` - Is used to define internal redirects performed by the MySQL Router. This can be used to expose content on the `request path` of the of a REST service. A JSON key `index.html` holding the value `/myService/myContentSet/index.html` will exposed the corresponding file from the given path as `/myService/index.html` if the `request path` of the REST service has been set to `/myService`.
- `directoryIndexDirective` - Holds an ordered list of files that should be returned when a directory path has been requested. The first matching file that is available will be returned. The `directoryIndexDirective` is recursively applies to all directory paths exposed by the MySQL Router. To change the `directoryIndexDirective` for a given REST object, the corresponding option needs to be set for that object.
- `sqlQuery`
- `timeout`
- Number of milliseconds to allow for DB operations while serving an endpoint. DB requests taking longer than that time will be interrupted and an error 504 returned. Default 2000. Can be overridden at a per-endpoint basis.
All other keys will be ignored and can be used to store custom metadata about the service. It is a good practice to include a unique prefix when adding custom keys to avoid them be overwritten by future MRS options.
**_Examples_**
```json
{
"headers": {
"Access-Control-Allow-Credentials": "true",
"Access-Control-Allow-Headers": "Content-Type, Authorization, X-Requested-With, Origin, X-Auth-Token",
"Access-Control-Allow-Methods": "GET, POST, PUT, DELETE, OPTIONS"
},
"http": {
"allowedOrigin": "auto"
},
"logging": {
"exceptions": true,
"request": {
"body": true,
"headers": true
},
"response": {
"body": true,
"headers": true
}
},
"returnInternalErrorDetails": true,
"includeLinksInResults": false
}</code></pre>
<h3 data-number="2.2.8" id="rest-service-comments"><span
class="header-section-number">2.2.8</span> REST Service
Comments</h3>
<p>The comments can hold a description of the REST
service. The maximal length is of the comments string is
512 characters.</p>
<pre class="antlr"><code>comments:
COMMENT textStringLiteral
;</code></pre>
<p>comments ::= <img
src="sections/sql/../../images/sql/comments.svg"
title="comments" alt="comments" /></p>
<h3 data-number="2.2.9" id="rest-service-metadata"><span
class="header-section-number">2.2.9</span> REST Service
Metadata</h3>
<p>The metadata can hold any JSON data. It can later be
consumed by a front end implementation to dynamically
render certain attributes, like a specific icon or a
color.</p>
<pre class="antlr"><code>metadata:
METADATA jsonValue
;</code></pre>
<h2 data-number="2.3" id="clone-rest-service"><span
class="header-section-number">2.3</span> CLONE REST
SERVICE</h2>
<p>Duplicates the contents of a REST service to a newly
created one.</p>
<p>All endpoints and roles belonging to the given
service are copied.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>cloneRestServiceStatement:
CLONE REST SERVICE serviceRequestPath NEW REQUEST PATH
newServiceRequestPath
;</code></pre>
<p>cloneRestServiceStatement ::= <img
src="sections/sql/../../images/sql/cloneRestServiceStatement.svg"
title="cloneRestServiceStatement"
alt="cloneRestServiceStatement" /></p>
<h2 data-number="2.4" id="create-rest-schema"><span
class="header-section-number">2.4</span> CREATE REST
SCHEMA</h2>
<p>The CREATE REST SCHEMA statement is used to create a
new or replace an existing REST schema. Each REST schema
directly maps to a database schema and allows the
database schema objects (tables, views and stored
procedures) to be exposed via REST endpoints.</p>
<blockquote>
<p>Note: Adding a REST schema to a REST service does not
automatically expose any database schema objects via
REST. The corresponding
<code>CREATE REST DATA MAPPING VIEW</code>,
<code>CREATE REST PROCEDURE</code>,
<code>CREATE REST FUNCTION</code> ddl commands need to
be called to explicitly expose a database schema
object.</p>
</blockquote>
<p>Each REST schema belongs to a REST service, which has
to be created first. One REST service can hold many REST
schemas.</p>
<p>Each REST schema can have its own options,
authentication apps and supports a different set of
authentication users.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestSchemaStatement: (
CREATE OR REPLACE REST SCHEMA
| CREATE REST SCHEMA (
IF NOT EXISTS
)?
) schemaRequestPath? (
ON SERVICE? serviceRequestPath
)? FROM schemaName restSchemaOptions?
;
restSchemaOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
)+
;</code></pre>
<p>createRestSchemaStatement ::= <img
src="sections/sql/../../images/sql/createRestSchemaStatement.svg"
title="createRestSchemaStatement"
alt="createRestSchemaStatement" /></p>
<p>restSchemaOptions ::= <img
src="sections/sql/../../images/sql/restSchemaOptions.svg"
title="restSchemaOptions" alt="restSchemaOptions" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example creates a REST schema
<code>/sakila</code> on the REST service
<code>/myService</code>.</p>
<div class="sourceCode" id="cb25"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb25-1"><a href="#cb25-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">ON</span> SERVICE <span class="op">/</span>myService</span>
<span id="cb25-2"><a href="#cb25-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">FROM</span> `sakila`</span>
<span id="cb25-3"><a href="#cb25-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">COMMENT</span> <span class="ot">"The sakila schema"</span>;</span></code></pre></div>
<h3 data-number="2.4.1"
id="enabling-or-disabling-a-rest-schema-at-creation-time"><span
class="header-section-number">2.4.1</span> Enabling or
Disabling a REST Schema at Creation Time</h3>
<p>The <code>enabledDisabledPrivate</code> option
specifies whether the REST schema should be enabled,
disabled or private when it is created.</p>
<p>Setting a schema to private disables public access
via HTTPS but keeps the schema available for private
access from MRS scripts.</p>
<pre class="antlr"><code>enabledDisabledPrivate:
ENABLED
| DISABLED
| PRIVATE
;</code></pre>
<p>enabledDisabledPrivate ::= <img
src="sections/sql/../../images/sql/enabledDisabledPrivate.svg"
title="enabledDisabledPrivate"
alt="enabledDisabledPrivate" /></p>
<h3 data-number="2.4.2"
id="specifying-the-default-page-count"><span
class="header-section-number">2.4.2</span> Specifying
the Default Page Count</h3>
<p>The <code>itemsPerPage</code> option can be used to
specify the default number of items returned for queries
run against this REST schema.</p>
<pre class="antlr"><code>itemsPerPage:
ITEMS PER PAGE itemsPerPageNumber
;</code></pre>
<p>itemsPerPage ::= <img
src="sections/sql/../../images/sql/itemsPerPage.svg"
title="itemsPerPage" alt="itemsPerPage" /></p>
<p>The number of items per page can also be specified
for each REST object individually.</p>
<h3 data-number="2.4.3"
id="rest-schema-json-options"><span
class="header-section-number">2.4.3</span> REST Schema
Json Options</h3>
<p>The jsonOptions allow to set a number of specific
options for the schema. Specify the <code>MERGE</code>
keyword to merge the given options with the existing
options. If <code>MERGE</code> is omitted, all existing
options will be overwritten with the specified
options.</p>
<pre class="antlr"><code>jsonOptions:
MERGE? OPTIONS jsonValue
;
jsonOptions ::=
These options can include the following JSON keys.
- `defaultStaticContent` - This option serves the same purpose as described in the [REST Configuration Json Options](#rest-configuration-json-options).
- `defaultRedirects` - This option serves the same purpose as described in the [REST Configuration Json Options](#rest-configuration-json-options).
- `directoryIndexDirective` - This option serves the same purpose as described in the [REST Configuration Json Options](#rest-configuration-json-options).
- `sqlQuery` - see [REST Service JSON Options](#rest-service-json-options)
All other keys will be ignored and can be used to store custom metadata about the schema. It is a good practice to include a unique prefix when adding custom keys to avoid them be overwritten by future MRS options.
### REST Schema Comments
The comments can hold a description of the REST schema. The maximal length is of the comments string is 512 characters.
```antlr
comments:
COMMENT textStringLiteral
;</code></pre>
<p>comments ::= <img
src="sections/sql/../../images/sql/comments.svg"
title="comments" alt="comments" /></p>
<h3 data-number="2.4.4" id="rest-schema-metadata"><span
class="header-section-number">2.4.4</span> REST Schema
Metadata</h3>
<p>The metadata can hold any JSON data. It can later be
consumed by a front end implementation to dynamically
render certain attributes, like a specific icon or a
color.</p>
<pre class="antlr"><code>metadata:
METADATA jsonValue
;</code></pre>
<h2 data-number="2.5" id="create-rest-view"><span
class="header-section-number">2.5</span> CREATE REST
VIEW</h2>
<p>The <code>CREATE REST DATA MAPPING VIEW</code>
statement is used to add REST endpoints for database
schema tables or views. Their data will be served as
JSON documents.</p>
<p>The structure of the served JSON documents is defined
using an <a
href="#defining-the-graphql-definition-for-a-rest-view">extended
GraphQL syntax</a>. This allows to define even complex
REST data mapping views in a simple and human readable
way. Please see the corresponding <a
href="#defining-the-graphql-definition-for-a-rest-view">GraphQL
section</a> about how to design the GraphQL definition
for a REST data mapping view.</p>
<p>Please see the MRS Reference Manual to learn more
about <a href="index.html#json-data-mapping-views">JSON
data mapping views</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestViewStatement: (
CREATE OR REPLACE REST DATA? MAPPING? VIEW
| CREATE REST DATA? MAPPING? VIEW (
IF NOT EXISTS
)?
) viewRequestPath (ON serviceSchemaSelector)? AS qualifiedIdentifier (
CLASS restObjectName
)? graphQlCrudOptions? graphQlObj? restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;</code></pre>
<p>createRestViewStatement ::= <img
src="sections/sql/../../images/sql/createRestViewStatement.svg"
title="createRestViewStatement"
alt="createRestViewStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example adds a REST data mapping view
for the <code>sakila.city</code> database schema
table.</p>
<div class="sourceCode" id="cb31"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb31-1"><a href="#cb31-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb31-2"><a href="#cb31-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb31-3"><a href="#cb31-3" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`city` {</span>
<span id="cb31-4"><a href="#cb31-4" aria-hidden="true" tabindex="-1"></a> cityId: city_id @SORTABLE,</span>
<span id="cb31-5"><a href="#cb31-5" aria-hidden="true" tabindex="-1"></a> city: city,</span>
<span id="cb31-6"><a href="#cb31-6" aria-hidden="true" tabindex="-1"></a> countryId: country_id,</span>
<span id="cb31-7"><a href="#cb31-7" aria-hidden="true" tabindex="-1"></a> lastUpdate: last_update</span>
<span id="cb31-8"><a href="#cb31-8" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb31-9"><a href="#cb31-9" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<p>Querying the REST data mapping view using the
TypeScript SDK returns the following JSON document.</p>
<div class="sourceCode" id="cb32"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb32-1"><a href="#cb32-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">sakila</span><span class="op">.</span><span class="at">city</span><span class="op">.</span><span class="fu">findFirst</span>()<span class="op">;</span></span>
<span id="cb32-2"><a href="#cb32-2" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb32-3"><a href="#cb32-3" aria-hidden="true" tabindex="-1"></a> <span class="st">"city"</span><span class="op">:</span> <span class="st">"A Corua (La Corua)"</span><span class="op">,</span></span>
<span id="cb32-4"><a href="#cb32-4" aria-hidden="true" tabindex="-1"></a> <span class="st">"links"</span><span class="op">:</span> [</span>
<span id="cb32-5"><a href="#cb32-5" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb32-6"><a href="#cb32-6" aria-hidden="true" tabindex="-1"></a> <span class="st">"rel"</span><span class="op">:</span> <span class="st">"self"</span><span class="op">,</span></span>
<span id="cb32-7"><a href="#cb32-7" aria-hidden="true" tabindex="-1"></a> <span class="st">"href"</span><span class="op">:</span> <span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb32-8"><a href="#cb32-8" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb32-9"><a href="#cb32-9" aria-hidden="true" tabindex="-1"></a> ]<span class="op">,</span></span>
<span id="cb32-10"><a href="#cb32-10" aria-hidden="true" tabindex="-1"></a> <span class="st">"cityId"</span><span class="op">:</span> 1<span class="op">,</span></span>
<span id="cb32-11"><a href="#cb32-11" aria-hidden="true" tabindex="-1"></a> <span class="st">"countryId"</span><span class="op">:</span> 87<span class="op">,</span></span>
<span id="cb32-12"><a href="#cb32-12" aria-hidden="true" tabindex="-1"></a> <span class="st">"lastUpdate"</span><span class="op">:</span> <span class="st">"2006-02-15 04:45:25.000000"</span><span class="op">,</span></span>
<span id="cb32-13"><a href="#cb32-13" aria-hidden="true" tabindex="-1"></a> <span class="st">"_metadata"</span><span class="op">:</span> {</span>
<span id="cb32-14"><a href="#cb32-14" aria-hidden="true" tabindex="-1"></a> <span class="st">"etag"</span><span class="op">:</span> <span class="st">"EE93452B41984F3F5BBB0395CCB2CED00F5C748FEEA4A36CCD749CC3F85B7CEA"</span></span>
<span id="cb32-15"><a href="#cb32-15" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb32-16"><a href="#cb32-16" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<p>The next example adds the referenced table
<code>sakila.country</code> to the REST data mapping
view.</p>
<div class="sourceCode" id="cb33"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb33-1"><a href="#cb33-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb33-2"><a href="#cb33-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb33-3"><a href="#cb33-3" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> `sakila`.`city` {</span>
<span id="cb33-4"><a href="#cb33-4" aria-hidden="true" tabindex="-1"></a> cityId: city_id @SORTABLE,</span>
<span id="cb33-5"><a href="#cb33-5" aria-hidden="true" tabindex="-1"></a> city: city,</span>
<span id="cb33-6"><a href="#cb33-6" aria-hidden="true" tabindex="-1"></a> countryId: country_id,</span>
<span id="cb33-7"><a href="#cb33-7" aria-hidden="true" tabindex="-1"></a> lastUpdate: last_update,</span>
<span id="cb33-8"><a href="#cb33-8" aria-hidden="true" tabindex="-1"></a> country: sakila.country {</span>
<span id="cb33-9"><a href="#cb33-9" aria-hidden="true" tabindex="-1"></a> countryId: country_id @SORTABLE,</span>
<span id="cb33-10"><a href="#cb33-10" aria-hidden="true" tabindex="-1"></a> country: country,</span>
<span id="cb33-11"><a href="#cb33-11" aria-hidden="true" tabindex="-1"></a> lastUpdate: last_update</span>
<span id="cb33-12"><a href="#cb33-12" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb33-13"><a href="#cb33-13" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb33-14"><a href="#cb33-14" aria-hidden="true" tabindex="-1"></a>AUTHENTICATION REQUIRED;</span></code></pre></div>
<p>This is what the REST data mapping view looks like in
the interactive MySQL REST Object Dialog in the MySQL
Shell for VS Code extension.</p>
<p><img
src="sections/sql/../../images/vsc-mrs-rest-object-editor-2-referenced-table.png"
title="Adding a Referenced Table"
alt="Adding a Referenced Table" />ı</p>
<p>Running a TypeScript SDK query against this new REST
endpoint returns the following JSON Document.</p>
<div class="sourceCode" id="cb34"><pre
class="sourceCode ts"><code class="sourceCode typescript"><span id="cb34-1"><a href="#cb34-1" aria-hidden="true" tabindex="-1"></a>ts<span class="op">></span> myService<span class="op">.</span><span class="at">sakila</span><span class="op">.</span><span class="at">city</span><span class="op">.</span><span class="fu">findFirst</span>()<span class="op">;</span></span>
<span id="cb34-2"><a href="#cb34-2" aria-hidden="true" tabindex="-1"></a>{</span>
<span id="cb34-3"><a href="#cb34-3" aria-hidden="true" tabindex="-1"></a> <span class="st">"city"</span><span class="op">:</span> <span class="st">"A Corua (La Corua)"</span><span class="op">,</span></span>
<span id="cb34-4"><a href="#cb34-4" aria-hidden="true" tabindex="-1"></a> <span class="st">"links"</span><span class="op">:</span> [</span>
<span id="cb34-5"><a href="#cb34-5" aria-hidden="true" tabindex="-1"></a> {</span>
<span id="cb34-6"><a href="#cb34-6" aria-hidden="true" tabindex="-1"></a> <span class="st">"rel"</span><span class="op">:</span> <span class="st">"self"</span><span class="op">,</span></span>
<span id="cb34-7"><a href="#cb34-7" aria-hidden="true" tabindex="-1"></a> <span class="st">"href"</span><span class="op">:</span> <span class="st">"/myService/sakila/city/1"</span></span>
<span id="cb34-8"><a href="#cb34-8" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb34-9"><a href="#cb34-9" aria-hidden="true" tabindex="-1"></a> ]<span class="op">,</span></span>
<span id="cb34-10"><a href="#cb34-10" aria-hidden="true" tabindex="-1"></a> <span class="st">"cityId"</span><span class="op">:</span> 1<span class="op">,</span></span>
<span id="cb34-11"><a href="#cb34-11" aria-hidden="true" tabindex="-1"></a> <span class="st">"country"</span><span class="op">:</span> {</span>
<span id="cb34-12"><a href="#cb34-12" aria-hidden="true" tabindex="-1"></a> <span class="st">"country"</span><span class="op">:</span> <span class="st">"Spain"</span><span class="op">,</span></span>
<span id="cb34-13"><a href="#cb34-13" aria-hidden="true" tabindex="-1"></a> <span class="st">"countryId"</span><span class="op">:</span> 87<span class="op">,</span></span>
<span id="cb34-14"><a href="#cb34-14" aria-hidden="true" tabindex="-1"></a> <span class="st">"lastUpdate"</span><span class="op">:</span> <span class="st">"2006-02-15 04:44:00.000000"</span></span>
<span id="cb34-15"><a href="#cb34-15" aria-hidden="true" tabindex="-1"></a> }<span class="op">,</span></span>
<span id="cb34-16"><a href="#cb34-16" aria-hidden="true" tabindex="-1"></a> <span class="st">"countryId"</span><span class="op">:</span> 87<span class="op">,</span></span>
<span id="cb34-17"><a href="#cb34-17" aria-hidden="true" tabindex="-1"></a> <span class="st">"lastUpdate"</span><span class="op">:</span> <span class="st">"2006-02-15 04:45:25.000000"</span><span class="op">,</span></span>
<span id="cb34-18"><a href="#cb34-18" aria-hidden="true" tabindex="-1"></a> <span class="st">"_metadata"</span><span class="op">:</span> {</span>
<span id="cb34-19"><a href="#cb34-19" aria-hidden="true" tabindex="-1"></a> <span class="st">"etag"</span><span class="op">:</span> <span class="st">"FFA2187AD4B98DF48EC40B3E807E0561A71D02C2F4F5A3B953AA6CB6E41CAD16"</span></span>
<span id="cb34-20"><a href="#cb34-20" aria-hidden="true" tabindex="-1"></a> }</span>
<span id="cb34-21"><a href="#cb34-21" aria-hidden="true" tabindex="-1"></a>}</span></code></pre></div>
<h3 data-number="2.5.1" id="preconditions"><span
class="header-section-number">2.5.1</span>
Preconditions</h3>
<p>You define a REST data mapping view against a set of
tables related by primary key (PK), foreign key (FK) or
unique key constraints (UK). The following rules
apply:</p>
<ul>
<li>The constraints must be declared in the
database.</li>
<li>The relationships type can be 1-to-1, 1-to-N and
N-to-M (using a mapping table with two FKs). The N-to-M
relationship can be thought of as the combination of
1-to-N and 1-to-1 relationship</li>
<li>Columns of two or more tables with 1-to-1 or N-to-1
relationships can be merged into the same JSON object
via UNNEST. Otherwise a nested JSON object is
created.</li>
<li>Tables with a 1-to-N relationship create a nested
JSON array.</li>
<li>Each item in the data mapping view is one JSON
object, which is typically a hierarchy of nested objects
and arrays.</li>
<li>Each application object is built from values
originating from one or multiple rows from the
underlying tables of that view. Typically, each table
contributes to one (nested) JSON object.</li>
</ul>
<h3 data-number="2.5.2"
id="enabling-or-disabling-a-rest-view-at-creation-time"><span
class="header-section-number">2.5.2</span> Enabling or
Disabling a REST View at Creation Time</h3>
<p>The <code>enabledDisabledPrivate</code> option
specifies whether the REST data mapping view should be
enabled, disabled or private when it is created.</p>
<p>Setting a REST data mapping view to private disables
public access via HTTPS but keeps the schema available
for private access from MRS scripts.</p>
<pre class="antlr"><code>enabledDisabledPrivate:
ENABLED
| DISABLED
| PRIVATE
;</code></pre>
<p>enabledDisabledPrivate ::= <img
src="sections/sql/../../images/sql/enabledDisabledPrivate.svg"
title="enabledDisabledPrivate"
alt="enabledDisabledPrivate" /></p>
<h3 data-number="2.5.3"
id="requiring-authentication-for-rest-views"><span
class="header-section-number">2.5.3</span> Requiring
Authentication for REST Views</h3>
<p>The <code>authenticationRequired</code> option
specifies if a REST data mapping view requires
authentication before accessing its REST endpoints.</p>
<pre class="antlr"><code>authenticationRequired:
AUTHENTICATION NOT? REQUIRED
;</code></pre>
<p>authenticationRequired ::= <img
src="sections/sql/../../images/sql/authenticationRequired.svg"
title="authenticationRequired"
alt="authenticationRequired" /></p>
<h3 data-number="2.5.4"
id="specifying-the-page-count-for-rest-views"><span
class="header-section-number">2.5.4</span> Specifying
the Page Count for REST Views</h3>
<p>The <code>itemsPerPage</code> option can be used to
specify the number of items returned for queries run
against the REST data mapping view.</p>
<pre class="antlr"><code>itemsPerPage:
ITEMS PER PAGE itemsPerPageNumber
;</code></pre>
<p>itemsPerPage ::= <img
src="sections/sql/../../images/sql/itemsPerPage.svg"
title="itemsPerPage" alt="itemsPerPage" /></p>
<p>The number of items per page can also be specified
for each REST object individually.</p>
<h3 data-number="2.5.5"
id="setting-the-media-type-for-rest-views"><span
class="header-section-number">2.5.5</span> Setting the
Media Type for REST Views</h3>
<p>If this REST data mapping view returns a specific
MIME type it can be set via the
<code>restViewMediaType</code> option. If MRS should try
to automatically detect the file type based on the
content of the file the <code>AUTODETECT</code> option
can be used.</p>
<pre class="antlr"><code>restViewMediaType:
MEDIA TYPE (
textStringLiteral
| AUTODETECT
)
;</code></pre>
<p>restViewMediaType ::= <img
src="sections/sql/../../images/sql/restViewMediaType.svg"
title="restViewMediaType" alt="restViewMediaType" /></p>
<h3 data-number="2.5.6"
id="setting-the-result-format-for-rest-views"><span
class="header-section-number">2.5.6</span> Setting the
Result Format for REST Views</h3>
<p>A REST data mapping view can return one of the
following formats which can be set with the
<code>restViewFormat</code> option.</p>
<ul>
<li>FEED: A list of result JSON objects</li>
<li>ITEM: A single result item</li>
<li>MEDIA: A single blob item. The
<code>restViewMediaType</code> option is used to set the
corresponding MIME type in this case.</li>
</ul>
<pre class="antlr"><code>restViewFormat:
FORMAT (FEED | ITEM | MEDIA)
;</code></pre>
<p>restViewFormat ::= <img
src="sections/sql/../../images/sql/restViewFormat.svg"
title="restViewFormat" alt="restViewFormat" /></p>
<h3 data-number="2.5.7"
id="using-a-custom-authentication-procedure-for-a-rest-view"><span
class="header-section-number">2.5.7</span> Using a
Custom Authentication Procedure for a REST View</h3>
<p>In case the built in authentication handling does not
cover the specific use case for a REST data mapping
view, a custom MySQL stored procedure can be used to
handle the authentication check for the given user and
the requested CRUD operation.</p>
<p>The referenced MySQL stored procedure has to be in
the same schema as the database schema object and it has
to accept the following parameters:
<code>(IN user_id BINARY(16), IN schema VARCHAR(255), IN object VARCHAR(255), IN crud_operation VARCHAR(4))</code>.
It needs to returns <code>true</code> or
<code>false</code>.</p>
<p>restViewAuthenticationProcedure ::= <img
src="sections/sql/../../images/sql/restViewAuthenticationProcedure.svg"
title="restViewAuthenticationProcedure"
alt="restViewAuthenticationProcedure" /></p>
<h3 data-number="2.5.8"
id="defining-the-graphql-definition-for-a-rest-view"><span
class="header-section-number">2.5.8</span> Defining the
GraphQL definition for a REST View</h3>
<pre class="antlr"><code>graphQlObj:
OPEN_CURLY graphQlPair (COMMA graphQlPair)* CLOSE_CURLY
| OPEN_CURLY CLOSE_CURLY
;
graphQlCrudOptions: (
AT_INSERT
| AT_NOINSERT
| AT_UPDATE
| AT_NOUPDATE
| AT_DELETE
| AT_NODELETE
| AT_CHECK
| AT_NOCHECK
)+
;
graphQlPair:
graphQlPairKey COLON graphQlPairValue (
AT_IN
| AT_OUT
| AT_INOUT
)? graphQlValueOptions? (
AT_DATATYPE OPEN_PAR graphQlDatatypeValue CLOSE_PAR
)? graphQlCrudOptions? graphQlValueJsonSchema? graphQlObj?
;
graphQlValueOptions: (
AT_NOCHECK
| AT_SORTABLE
| AT_NOFILTERING
| AT_ROWOWNERSHIP
| AT_UNNEST
| AT_KEY
)+
;
graphQlValue:
qualifiedIdentifier
| graphQlObj
;
graphQlValueJsonSchema:
JSON SCHEMA jsonValue
;</code></pre>
<p>graphQlObj ::= <img
src="sections/sql/../../images/sql/graphQlObj.svg"
title="graphQlObj" alt="graphQlObj" /></p>
<p>graphQlCrudOptions ::= <img
src="sections/sql/../../images/sql/graphQlCrudOptions.svg"
title="graphQlCrudOptions"
alt="graphQlCrudOptions" /></p>
<p>graphQlPair ::= <img
src="sections/sql/../../images/sql/graphQlPair.svg"
title="graphQlPair" alt="graphQlPair" /></p>
<p>graphQlValue ::= <img
src="sections/sql/../../images/sql/graphQlValue.svg"
title="graphQlValue" alt="graphQlValue" /></p>
<p>graphQlValueJsonSchema ::= <img
src="sections/sql/../../images/sql/graphQlValueJsonSchema.svg"
title="graphQlValueJsonSchema"
alt="graphQlValueJsonSchema" /></p>
<h3 data-number="2.5.9" id="rest-view-metadata"><span
class="header-section-number">2.5.9</span> REST View
Metadata</h3>
<p>The metadata can hold any JSON data. It can later be
consumed by a front end implementation to dynamically
render certain attributes, like a specific icon or a
color.</p>
<pre class="antlr"><code>metadata:
METADATA jsonValue
;</code></pre>
<h3 data-number="2.5.10"
id="json-options-for-views"><span
class="header-section-number">2.5.10</span> Json Options
for Views</h3>
<p>The jsonOptions allow to set a number of specific
options for the REST view. Specify the
<code>MERGE</code> keyword to merge the given options
with the existing options. If <code>MERGE</code> is
omitted, all existing options will be overwritten with
the specified options.</p>
<pre class="antlr"><code>jsonOptions:
MERGE? OPTIONS jsonValue
;
The following additional options can be configured for most database object endpoints in a JSON object through the OPTIONS clause (indentation means JSON object nesting):
- `http`
- see [REST Service JSON Options](#rest-service-json-options)
- `logging`
- see [REST Service JSON Options](#rest-service-json-options)
- `metadata`
- `gtid` (_bool_)
- if true, embeds the GTID assigned to DB change operations into the metadata of the response JSON object. Enable when using with replication topologies, to ensure that changes made to an object will be reflected when reading it,regardless of where the read happens. Default true.
- `sqlQuery`
- `embedWait` (_bool_)
- if true, performs the wait described in the `wait` option directly in the DB query, as opposed to executing it beforehand in a separate statement.
- `timeout` (_number_)
- number of milliseconds to wait for the DB query to execute before it's terminated. Default is taken from the global `sqlQuery`.`timeout` option.
- `wait` (_bool_)
- when querying data from a secondary server, controls whether to wait until the transaction GTID specified through the `asof` clause are applied. Effectively enables read-own-writes semantics.
- `result`
- `cacheTimeToLive` (_double_)
- enables caching for GET requests. Specifies the number of seconds (including sub-second values) to keep the response in the cache, after which it will be discarded until a new request comes in or when the cache fills up.
- `includeLinks` (_bool_)
- whether to include links in returned JSON objects (default true)
- `returnInternalErrorDetails` (_bool_)
- controls debug logging
- `disableAutomaticGrants` (_bool_)
- when creating or altering REST objects a list of privileges are automatically granted to the `mysql_rest_service_data_provider` role in order for the MySQL Router to be able to access the referenced data in the MySQL Server. In some cases it might be desirable to turn off this automatic grant of privileges and use the `grants` option to specify a custom list of grants.
- `grants` (_object_ | _list_)
- Either a single grant object or a list of grant objects that are executed in addition to the automatically generated grant statements for the REST object. These additional grants are needed when e.g. a stored PROCEDURE works with database TABLEs/VIEWs or calls other PROCEDUREs and FUNCTIONs.
- `privileges` (_string_ | _list_)
- Either a single privilege name or a list of privileges names or a list of privilege objects. The following MySQL privileges are allowed: "ALTER" | "ALTER ROUTINE" | "CREATE" | "CREATE ROUTINE" | "CREATE TEMPORARY TABLES" | "CREATE VIEW" | "DELETE" | "DROP" | "EVENT" | "EXECUTE" | "INDEX" | "INSERT" | "LOCK TABLES" | "REFERENCES" | "SELECT" | "SHOW DATABASES" | "SHOW VIEW" | "TRIGGER" | "UPDATE" | "USAGE". A privilege object has two fields, `privilege` and `columnList` with the latter holding a list of column names.
- `objectType` (_string_)
- Specifies an option object type, either "TABLE" | "FUNCTION" | "PROCEDURE".
- `schema` (_string_)
- The name of the database schema or `*`. The name needs to be provided without back ticks.
- `object` (_string_)
- The name of the database table/view/procedure/function or `*`. The name needs to be provided without back ticks.
**Examples for `grants`:**
The following example grants the SELECT privilege on the `mysql_rest_service_metadata`.`mrs_user` table.
```json
{
"grants": {
"privileges": "SELECT",
"schema": "mysql_rest_service_metadata",
"object": "mrs_user"
}
}</code></pre>
<p>This example grants the SELECT and UPDATE privileges
on the
<code>mysql_rest_service_metadata</code>.<code>mrs_user</code>
table.</p>
<div class="sourceCode" id="cb43"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb43-1"><a href="#cb43-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb43-2"><a href="#cb43-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"grants"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb43-3"><a href="#cb43-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"privileges"</span><span class="fu">:</span> <span class="ot">[</span> <span class="st">"SELECT"</span><span class="ot">,</span> <span class="st">"UPDATE"</span> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb43-4"><a href="#cb43-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"schema"</span><span class="fu">:</span> <span class="st">"mysql_rest_service_metadata"</span><span class="fu">,</span></span>
<span id="cb43-5"><a href="#cb43-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"object"</span><span class="fu">:</span> <span class="st">"mrs_user"</span> <span class="fu">}</span></span>
<span id="cb43-6"><a href="#cb43-6" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>The following example grants two privileges.</p>
<div class="sourceCode" id="cb44"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb44-1"><a href="#cb44-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb44-2"><a href="#cb44-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"grants"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb44-3"><a href="#cb44-3" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb44-4"><a href="#cb44-4" aria-hidden="true" tabindex="-1"></a> <span class="dt">"privileges"</span><span class="fu">:</span> <span class="st">"SELECT"</span><span class="fu">,</span></span>
<span id="cb44-5"><a href="#cb44-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"schema"</span><span class="fu">:</span> <span class="st">"mysql_rest_service_metadata"</span><span class="fu">,</span></span>
<span id="cb44-6"><a href="#cb44-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"object"</span><span class="fu">:</span> <span class="st">"msm_schema_version"</span></span>
<span id="cb44-7"><a href="#cb44-7" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span><span class="ot">,</span></span>
<span id="cb44-8"><a href="#cb44-8" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb44-9"><a href="#cb44-9" aria-hidden="true" tabindex="-1"></a> <span class="dt">"privileges"</span><span class="fu">:</span> <span class="st">"SELECT"</span><span class="fu">,</span></span>
<span id="cb44-10"><a href="#cb44-10" aria-hidden="true" tabindex="-1"></a> <span class="dt">"schema"</span><span class="fu">:</span> <span class="st">"mysql_rest_service_metadata"</span><span class="fu">,</span></span>
<span id="cb44-11"><a href="#cb44-11" aria-hidden="true" tabindex="-1"></a> <span class="dt">"object"</span><span class="fu">:</span> <span class="st">"mrs_user_schema_version"</span></span>
<span id="cb44-12"><a href="#cb44-12" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb44-13"><a href="#cb44-13" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span></span>
<span id="cb44-14"><a href="#cb44-14" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<p>This example grants the SELECT privilege only on the
<code>id</code> and <code>name</code> columns of the
<code>mysql_rest_service_metadata</code>.<code>mrs_user</code>
table.</p>
<div class="sourceCode" id="cb45"><pre
class="sourceCode json"><code class="sourceCode json"><span id="cb45-1"><a href="#cb45-1" aria-hidden="true" tabindex="-1"></a><span class="fu">{</span></span>
<span id="cb45-2"><a href="#cb45-2" aria-hidden="true" tabindex="-1"></a> <span class="dt">"grants"</span><span class="fu">:</span> <span class="fu">{</span></span>
<span id="cb45-3"><a href="#cb45-3" aria-hidden="true" tabindex="-1"></a> <span class="dt">"privileges"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb45-4"><a href="#cb45-4" aria-hidden="true" tabindex="-1"></a> <span class="fu">{</span></span>
<span id="cb45-5"><a href="#cb45-5" aria-hidden="true" tabindex="-1"></a> <span class="dt">"privilege"</span><span class="fu">:</span> <span class="st">"SELECT"</span><span class="fu">,</span></span>
<span id="cb45-6"><a href="#cb45-6" aria-hidden="true" tabindex="-1"></a> <span class="dt">"columnList"</span><span class="fu">:</span> <span class="ot">[</span></span>
<span id="cb45-7"><a href="#cb45-7" aria-hidden="true" tabindex="-1"></a> <span class="st">"id"</span><span class="ot">,</span></span>
<span id="cb45-8"><a href="#cb45-8" aria-hidden="true" tabindex="-1"></a> <span class="st">"name"</span></span>
<span id="cb45-9"><a href="#cb45-9" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span></span>
<span id="cb45-10"><a href="#cb45-10" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb45-11"><a href="#cb45-11" aria-hidden="true" tabindex="-1"></a> <span class="ot">]</span><span class="fu">,</span></span>
<span id="cb45-12"><a href="#cb45-12" aria-hidden="true" tabindex="-1"></a> <span class="dt">"schema"</span><span class="fu">:</span> <span class="st">"mysql_rest_service_metadata"</span><span class="fu">,</span></span>
<span id="cb45-13"><a href="#cb45-13" aria-hidden="true" tabindex="-1"></a> <span class="dt">"object"</span><span class="fu">:</span> <span class="st">"mrs_user"</span></span>
<span id="cb45-14"><a href="#cb45-14" aria-hidden="true" tabindex="-1"></a> <span class="fu">}</span></span>
<span id="cb45-15"><a href="#cb45-15" aria-hidden="true" tabindex="-1"></a><span class="fu">}</span></span></code></pre></div>
<h2 data-number="2.6" id="create-rest-procedure"><span
class="header-section-number">2.6</span> CREATE REST
PROCEDURE</h2>
<p>The <code>CREATE REST PROCEDURE</code> statement is
used to add REST endpoints for database schema stored
procedures. It uses the same <a
href="#defining-the-graphql-definition-for-a-rest-view">extended
GraphQL syntax</a> as defined for REST data mapping
views to describe the REST procedure’s parameters and
result sets. Please make sure to study the <a
href="#defining-the-graphql-definition-for-a-rest-view">corresponding
section</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestProcedureStatement: (
CREATE OR REPLACE REST PROCEDURE
| CREATE REST PROCEDURE (
IF NOT EXISTS
)?
) procedureRequestPath (ON serviceSchemaSelector)? AS qualifiedIdentifier
FORCE? (
PARAMETERS restObjectName? graphQlObj
)? restProcedureResult* restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;
restProcedureResult:
RESULT restResultName? graphQlObj
;</code></pre>
<p>createRestProcedureStatement ::= <img
src="sections/sql/../../images/sql/createRestProcedureStatement.svg"
title="createRestProcedureStatement"
alt="createRestProcedureStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<p>restProcedureResult ::= <img
src="sections/sql/../../images/sql/restProcedureResult.svg"
title="restProcedureResult"
alt="restProcedureResult" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example adds a REST procedure for the
<code>sakila.rewards_report</code> database schema
procedure. It is assumed that a REST service
<code>/myService</code> and a REST schema
<code>/sakila</code> have already been created.</p>
<div class="sourceCode" id="cb47"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb47-1"><a href="#cb47-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">PROCEDURE</span> <span class="op">/</span>filmInStock</span>
<span id="cb47-2"><a href="#cb47-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb47-3"><a href="#cb47-3" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> sakila.rewards_report;</span></code></pre></div>
<p>The following example adds a REST procedure for the
<code>sakila.filmInStock</code> database schema
procedure, while explicitly specifying the list of
parameters and the RESULT returned by the procedure.</p>
<div class="sourceCode" id="cb48"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb48-1"><a href="#cb48-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">OR</span> <span class="kw">REPLACE</span> REST <span class="kw">PROCEDURE</span> <span class="op">/</span>filmInStock</span>
<span id="cb48-2"><a href="#cb48-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb48-3"><a href="#cb48-3" aria-hidden="true" tabindex="-1"></a><span class="kw">AS</span> sakila.film_in_stock</span>
<span id="cb48-4"><a href="#cb48-4" aria-hidden="true" tabindex="-1"></a><span class="kw">PARAMETERS</span> MyServiceSakilaFilmInStockParams {</span>
<span id="cb48-5"><a href="#cb48-5" aria-hidden="true" tabindex="-1"></a> pFilmId: p_film_id @IN,</span>
<span id="cb48-6"><a href="#cb48-6" aria-hidden="true" tabindex="-1"></a> pStoreId: p_store_id @IN,</span>
<span id="cb48-7"><a href="#cb48-7" aria-hidden="true" tabindex="-1"></a> pFilmCount: p_film_count @OUT</span>
<span id="cb48-8"><a href="#cb48-8" aria-hidden="true" tabindex="-1"></a>}</span>
<span id="cb48-9"><a href="#cb48-9" aria-hidden="true" tabindex="-1"></a>RESULT MyServiceSakilaFilmInStock {</span>
<span id="cb48-10"><a href="#cb48-10" aria-hidden="true" tabindex="-1"></a> inventoryId: inventory_id @DATATYPE(<span class="ot">"int"</span>)</span>
<span id="cb48-11"><a href="#cb48-11" aria-hidden="true" tabindex="-1"></a>};</span></code></pre></div>
<h3 data-number="2.6.1"
id="the-force-flag-for-procedures"><span
class="header-section-number">2.6.1</span> The FORCE
Flag For Procedures</h3>
<p>In certain cases, a REST procedure may need to be
created even though the actual database schema procedure
is not yet available. To make the
<code>CREATE REST PROCEDURE</code> succeed in this case,
the <code>FORCE</code> flag must be specified.</p>
<h2 data-number="2.7" id="create-rest-function"><span
class="header-section-number">2.7</span> CREATE REST
FUNCTION</h2>
<p>The <code>CREATE REST FUNCTION</code> statement is
used to add REST endpoints for database schema stored
function. It uses the same <a
href="#defining-the-graphql-definition-for-a-rest-view">extended
GraphQL syntax</a> as defined for REST data mapping
views to describe the REST functions’s parameters and
result. Please make sure to study the <a
href="#defining-the-graphql-definition-for-a-rest-view">corresponding
section</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestFunctionStatement: (
CREATE OR REPLACE REST FUNCTION
| CREATE REST FUNCTION (
IF NOT EXISTS
)?
) functionRequestPath (ON serviceSchemaSelector)? AS qualifiedIdentifier
FORCE? (
PARAMETERS restObjectName? graphQlObj
)? restFunctionResult? restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;
restFunctionResult:
RESULT restResultName? graphQlObj
;</code></pre>
<p>createRestFunctionStatement ::= <img
src="sections/sql/../../images/sql/createRestFunctionStatement.svg"
title="createRestFunctionStatement"
alt="createRestFunctionStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<p>restFunctionResult ::= <img
src="sections/sql/../../images/sql/restFunctionResult.svg"
title="restFunctionResult"
alt="restFunctionResult" /></p>
<h3 data-number="2.7.1"
id="the-force-flag-for-functions"><span
class="header-section-number">2.7.1</span> The FORCE
Flag For Functions</h3>
<p>In certain cases, a REST function may need to be
created even though the actual database schema function
is not yet available. To make the
<code>CREATE REST FUNCTION</code> succeed in this case,
the <code>FORCE</code> flag must be specified.</p>
<p>See <a href="#json-options-for-views">JSON
Options</a></p>
<h2 data-number="2.8" id="create-rest-content-set"><span
class="header-section-number">2.8</span> CREATE REST
CONTENT SET</h2>
<p>The <code>CREATE REST CONTENT SET</code> statement is
used to add REST endpoints for static content.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestContentSetStatement: (
CREATE OR REPLACE REST CONTENT SET
| CREATE REST CONTENT SET (
IF NOT EXISTS
)?
) contentSetRequestPath (
ON SERVICE? serviceRequestPath
)? (FROM directoryFilePath)? restContentSetOptions?
;
restContentSetOptions: (
enabledDisabledPrivate
| authenticationRequired
| jsonOptions
| comments
| fileIgnoreList
| loadScripts
)+
;</code></pre>
<p>createRestContentSetStatement ::= <img
src="sections/sql/../../images/sql/createRestContentSetStatement.svg"
title="createRestContentSetStatement"
alt="createRestContentSetStatement" /></p>
<p>restContentSetOptions ::= <img
src="sections/sql/../../images/sql/restContentSetOptions.svg"
title="restContentSetOptions"
alt="restContentSetOptions" /></p>
<h2 data-number="2.9"
id="create-rest-content-file"><span
class="header-section-number">2.9</span> CREATE REST
CONTENT FILE</h2>
<p>Adds a file to a content set.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestContentFileStatement: (
CREATE OR REPLACE REST CONTENT FILE
| CREATE REST CONTENT FILE (
IF NOT EXISTS
)?
) contentFileRequestPath ON (
SERVICE? serviceRequestPath
)? CONTENT SET contentSetRequestPath (
(FROM directoryFilePath)
| (BINARY? CONTENT textStringLiteral)
) restContentFileOptions?
;
restContentFileOptions: (
enabledDisabledPrivate
| authenticationRequired
| jsonOptions
)+
;
directoryFilePath:
textStringLiteral
;</code></pre>
<p>createRestContentFileStatement ::= <img
src="sections/sql/../../images/sql/createRestContentFileStatement.svg"
title="createRestContentFileStatement"
alt="createRestContentFileStatement" /></p>
<p>restContentFileOptions ::= <img
src="sections/sql/../../images/sql/restContentFileOptions.svg"
title="restContentFileOptions"
alt="restContentFileOptions" /></p>
<h2 data-number="2.10" id="create-rest-auth-app"><span
class="header-section-number">2.10</span> CREATE REST
AUTH APP</h2>
<p>The <code>CREATE REST AUTH APP</code> statement is
used to create a new REST authentication app. The MySQL
REST Service supports a list of authentication vendors,
including dedicated MRS authentication, MYSQL user
account authentication as well as several OAuth2
vendors, like OCI OAuth2, Facebook and Google.</p>
<p>Once a REST authentication app has been created, it
can be linked to REST services to enable the required
authentication support.</p>
<p>The <a href="#create-rest-service">CREATE REST
SERVICE</a> and <a href="#alter-rest-service">ALTER REST
SERVICE</a> statements support the addition or removal
of REST authentication apps when using the ADD AUTH APP
and REMOVE AUTH APP clauses</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestAuthAppStatement: (
CREATE OR REPLACE REST (
AUTH
| AUTHENTICATION
) APP
| CREATE REST (
AUTH
| AUTHENTICATION
) APP (IF NOT EXISTS)?
) authAppName VENDOR (
MRS
| MYSQL
| vendorName
) restAuthAppOptions?
;
restAuthAppOptions: (
enabledDisabled
| comments
| allowNewUsersToRegister
| defaultRole
| appId
| appSecret
| url
)+
;
allowNewUsersToRegister:
(DO NOT)? ALLOW NEW USERS (
TO REGISTER
)?
;
defaultRole:
DEFAULT ROLE textOrIdentifier
;
appId:
(APP | CLIENT) ID textStringLiteral
;
appSecret:
(APP | CLIENT) SECRET textStringLiteral
;
url:
URL textStringLiteral
;
authAppName:
textOrIdentifier
;</code></pre>
<p>createRestAuthAppStatement ::= <img
src="sections/sql/../../images/sql/createRestAuthAppStatement.svg"
title="createRestAuthAppStatement"
alt="createRestAuthAppStatement" /></p>
<p>restAuthAppOptions ::= <img
src="sections/sql/../../images/sql/restAuthAppOptions.svg"
title="restAuthAppOptions"
alt="restAuthAppOptions" /></p>
<p>allowNewUsersToRegister ::= <img
src="sections/sql/../../images/sql/allowNewUsersToRegister.svg"
title="allowNewUsersToRegister"
alt="allowNewUsersToRegister" /></p>
<p>defaultRole ::= <img
src="sections/sql/../../images/sql/defaultRole.svg"
title="defaultRole" alt="defaultRole" /></p>
<p>appId ::= <img
src="sections/sql/../../images/sql/appId.svg"
title="appId" alt="appId" /></p>
<p>appSecret ::= <img
src="sections/sql/../../images/sql/appSecret.svg"
title="appSecret" alt="appSecret" /></p>
<p>url ::= <img
src="sections/sql/../../images/sql/url.svg" title="url"
alt="url" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example creates an REST authentication
app using the MRS authentication vendor and links it to
the REST service <code>/myService</code>.</p>
<div class="sourceCode" id="cb53"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb53-1"><a href="#cb53-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST AUTHENTICATION APP <span class="ot">"MRS"</span> <span class="kw">ON</span> SERVICE <span class="op">/</span>myService VENDOR MRS;</span>
<span id="cb53-2"><a href="#cb53-2" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb53-3"><a href="#cb53-3" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> REST SERVICE <span class="op">/</span>myService <span class="kw">ADD</span> AUTH APP <span class="ot">"MRS"</span>;</span></code></pre></div>
<p>The next example creates an REST authentication app
for the OCI OAuth2 service.</p>
<div class="sourceCode" id="cb54"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb54-1"><a href="#cb54-1" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> REST AUTHENTICATION APP <span class="ot">"OCI"</span></span>
<span id="cb54-2"><a href="#cb54-2" aria-hidden="true" tabindex="-1"></a> VENDOR <span class="ot">"OCI OAuth2"</span></span>
<span id="cb54-3"><a href="#cb54-3" aria-hidden="true" tabindex="-1"></a> URL <span class="ot">"https://idcs-f84866196ff54e3b93a15651865191da.identity.oraclecloud.com:443"</span>;</span>
<span id="cb54-4"><a href="#cb54-4" aria-hidden="true" tabindex="-1"></a> CLIENT <span class="kw">ID</span> <span class="ot">"f2abc2c0f19a4c40a1abc48edcdfe60b"</span></span>
<span id="cb54-5"><a href="#cb54-5" aria-hidden="true" tabindex="-1"></a> CLIENT SECRET <span class="ot">"**********************"</span></span></code></pre></div>
<h3 data-number="2.10.1"
id="rest-authentication-app-vendors"><span
class="header-section-number">2.10.1</span> REST
Authentication App Vendors</h3>
<p>The following settings for <code>VENDOR</code> are
currently supported.</p>
<table>
<caption>REST Authentication App Vendors</caption>
<colgroup>
<col style="width: 25%" />
<col style="width: 25%" />
<col style="width: 50%" />
</colgroup>
<thead>
<tr class="header">
<th>Vendor</th>
<th>Type</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td><code>MRS</code></td>
<td>MRS</td>
<td>Built in MRS authentication with dedicated MRS
account management.</td>
</tr>
<tr class="even">
<td><code>MYSQL</code></td>
<td>MySQL Server</td>
<td>MySQL server account authentication. This
authentication method makes most sense for tooling and
other applications with hardcoded accounts accessing the
MySQL REST Service.</td>
</tr>
<tr class="odd">
<td><code>Facebook</code></td>
<td>OAuth2</td>
<td>Performing authentication against the Facebook
OAuth2 servers using
<code>Login with Facebook</code>.</td>
</tr>
<tr class="even">
<td><code>Google</code></td>
<td>OAuth2</td>
<td>Performing authentication against the Google OAuth2
servers using <code>Login with Google</code>.</td>
</tr>
<tr class="odd">
<td><code>"OCI OAuth2"</code></td>
<td>OAuth2</td>
<td>Performing authentication against the OCI OAuth2
service. See <a
href="index.html#configuring-oci-oauth2">here</a> for
details on how to configure the OCI Auth2 service.</td>
</tr>
</tbody>
</table>
<h3 data-number="2.10.2"
id="configuring-a-rest-authentication-app-for-oauth2-access"><span
class="header-section-number">2.10.2</span> Configuring
a REST Authentication App for OAuth2 access</h3>
<p>Before creating a REST authentication app using an
OAuth2 vendor, it is required to register the
application at the OAuth2 vendor beforehand. Please
consult the documentation of the individual vendors for
more details.</p>
<p>For configuring the OCI OAuth2 service please see <a
href="index.html#configuring-oci-oauth2">here</a>.</p>
<p>Please note that the URL of the OAuth2 server is a
required setting and must be specified.</p>
<p>During the registration process an APP ID (also
called CLIENT ID) as well as an APP SECRET (also called
CLIENT SECRET) will be generated by the OAuth2 vendor to
uniquely identify the application. Both, the APP ID as
well as the APP SECRET need to be specified when
creating the REST authentication app.</p>
<p>In case of using OCI OAuth2 it is also required to
specify the OCI “Domain URL” using the URL option.</p>
<h2 data-number="2.11" id="create-rest-user"><span
class="header-section-number">2.11</span> CREATE REST
USER</h2>
<p>The <code>CREATE REST USER</code> statement is used
to add REST user to a REST authentication app.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestUserStatement: (
CREATE OR REPLACE REST USER
| CREATE REST USER (
IF NOT EXISTS
)?
) userName AT_SIGN authAppName (
IDENTIFIED BY userPassword
)? userOptions?
;
userName:
textOrIdentifier
;
userPassword:
textStringLiteral
;
userOptions:
(accountLock | appOptions | jsonOptions)+
;
appOptions:
APP OPTIONS jsonValue
;
accountLock:
ACCOUNT (LOCK | UNLOCK)
;
</code></pre>
<p>createRestUserStatement ::= <img
src="sections/sql/../../images/sql/createRestUserStatement.svg"
title="createRestUserStatement"
alt="createRestUserStatement" /></p>
<p>userOptions ::= <img
src="sections/sql/../../images/sql/userOptions.svg"
title="userOptions" alt="userOptions" /></p>
<p>appOptions ::= <img
src="sections/sql/../../images/sql/appOptions.svg"
title="appOptions" alt="appOptions" /></p>
<p>accountLock ::= <img
src="sections/sql/../../images/sql/accountLock.svg"
title="accountLock" alt="accountLock" /></p>
<h2 data-number="2.12" id="create-rest-role"><span
class="header-section-number">2.12</span> CREATE REST
ROLE</h2>
<p>Creates a REST role to the specified or currently
active REST service. Role names must be unique across
all services.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>createRestRoleStatement: (
CREATE OR REPLACE REST ROLE
| CREATE REST ROLE (
IF NOT EXISTS
)?
) roleName (EXTENDS parentRoleName)? roleService? restRoleOptions?
;
restRoleOptions:
(jsonOptions | comments)+
;</code></pre>
<p>createRestRoleStatement ::= <img
src="sections/sql/../../images/sql/createRestRoleStatement.svg"
title="createRestRoleStatement"
alt="createRestRoleStatement" /></p>
<p>restRoleOptions ::= <img
src="sections/sql/../../images/sql/restRoleOptions.svg"
title="restRoleOptions" alt="restRoleOptions" /></p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="3" id="alter"><span
class="header-section-number">3</span> ALTER</h1>
<h2 data-number="3.1" id="alter-rest-service"><span
class="header-section-number">3.1</span> ALTER REST
SERVICE</h2>
<p>An existing REST service can be altered by using the
<code>ALTER REST SERVICE</code> statement. It uses the
same <code>restServiceOptions</code> as used by the <a
href="#create-rest-service"><code>CREATE REST SERVICE</code></a>
statement. Please see the discussion of the options <a
href="#create-rest-service">there</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestServiceStatement:
ALTER REST SERVICE serviceRequestPath (
NEW REQUEST PATH newServiceRequestPath
)? restServiceOptions?
;
restServiceOptions: (
enabledDisabled
| publishedUnpublished
| restProtocol
| restAuthentication
| jsonOptions
| comments
| metadata
| addAuthApp
| removeAuthApp
)+
;
addAuthApp:
ADD AUTH APP authAppName (IF EXISTS)?
;
removeAuthApp:
REMOVE AUTH APP authAppName (IF EXISTS)?
;</code></pre>
<p>alterRestServiceStatement ::= <img
src="sections/sql/../../images/sql/alterRestServiceStatement.svg"
title="alterRestServiceStatement"
alt="alterRestServiceStatement" /></p>
<p>restServiceOptions ::= <img
src="sections/sql/../../images/sql/restServiceOptions.svg"
title="restServiceOptions"
alt="restServiceOptions" /></p>
<p>addAuthApp ::= <img
src="sections/sql/../../images/sql/addAuthApp.svg"
title="addAuthApp" alt="addAuthApp" /></p>
<p>removeAuthApp ::= <img
src="sections/sql/../../images/sql/removeAuthApp.svg"
title="removeAuthApp" alt="removeAuthApp" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example alters a REST service
<code>/myService</code> by setting a new comment.</p>
<div class="sourceCode" id="cb58"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb58-1"><a href="#cb58-1" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> REST SERVICE <span class="op">/</span>myService</span>
<span id="cb58-2"><a href="#cb58-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">COMMENT</span> <span class="ot">"A simple, improved REST service"</span>;</span></code></pre></div>
<h2 data-number="3.2" id="alter-rest-auth-app"><span
class="header-section-number">3.2</span> ALTER REST AUTH
APP</h2>
<p>Modifies attributes from an existing authentication
app. See <a
href="#create-rest-auth-app"><code>CREATE REST AUTH APP</code></a>
for details about supported options.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestAuthAppStatement:
ALTER REST (
AUTH
| AUTHENTICATION
) APP authAppName (
NEW NAME newAuthAppName
)? restAuthAppOptions?
;</code></pre>
<p>alterRestAuthAppStatement ::= <img
src="sections/sql/../../images/sql/alterRestAuthAppStatement.svg"
title="alterRestAuthAppStatement"
alt="alterRestAuthAppStatement" /></p>
<h2 data-number="3.3" id="alter-rest-schema"><span
class="header-section-number">3.3</span> ALTER REST
SCHEMA</h2>
<p>An existing REST schema can be altered by using the
<code>ALTER REST SCHEMA</code> statement. It uses the
same <code>restSchemaOptions</code> as used by the <a
href="#create-rest-schema"><code>CREATE REST SCHEMA</code></a>
statement. Please see the discussion of the options
there.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestSchemaStatement:
ALTER REST SCHEMA schemaRequestPath? (
ON SERVICE? serviceRequestPath
)? (
NEW REQUEST PATH newSchemaRequestPath
)? (FROM schemaName)? restSchemaOptions?
;
restSchemaOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
)+
;</code></pre>
<p>alterRestSchemaStatement ::= <img
src="sections/sql/../../images/sql/alterRestSchemaStatement.svg"
title="alterRestSchemaStatement"
alt="alterRestSchemaStatement" /></p>
<p>restSchemaOptions ::= <img
src="sections/sql/../../images/sql/restSchemaOptions.svg"
title="restSchemaOptions" alt="restSchemaOptions" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example alters a REST schema
<code>/myService</code> to use a new request path
<code>/myPublicService</code>.</p>
<div class="sourceCode" id="cb61"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb61-1"><a href="#cb61-1" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">ON</span> SERVICE <span class="op">/</span>myService</span>
<span id="cb61-2"><a href="#cb61-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">NEW</span> REQUEST PATH <span class="op">/</span>myPublicService;</span></code></pre></div>
<h2 data-number="3.4" id="alter-rest-view"><span
class="header-section-number">3.4</span> ALTER REST
VIEW</h2>
<p>The <code>ALTER REST DATA MAPPING VIEW</code>
statement is used to alter existing REST data mapping
views.</p>
<p>Please see the corresponding <a
href="#defining-the-graphql-definition-for-a-rest-view">GraphQL
section</a> about how to design the GraphQL definition
for a REST data mapping view.</p>
<p>Please see the MRS Reference Manual to learn more
about <a href="index.html#rest-views">JSON data mapping
views</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestViewStatement:
ALTER REST DATA? MAPPING? VIEW viewRequestPath (
ON serviceSchemaSelector
)? (NEW REQUEST PATH newViewRequestPath)? (
CLASS restObjectName graphQlCrudOptions? graphQlObj?
)? restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;</code></pre>
<p>alterRestViewStatement ::= <img
src="sections/sql/../../images/sql/alterRestViewStatement.svg"
title="alterRestViewStatement"
alt="alterRestViewStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example alters a REST data mapping view
for the <code>sakila.city</code> database schema table
and sets a new list of fields.</p>
<div class="sourceCode" id="cb63"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb63-1"><a href="#cb63-1" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb63-2"><a href="#cb63-2" aria-hidden="true" tabindex="-1"></a><span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila</span>
<span id="cb63-3"><a href="#cb63-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> `sakila`.`city` <span class="kw">AS</span> MyServiceSakilaCity {</span>
<span id="cb63-4"><a href="#cb63-4" aria-hidden="true" tabindex="-1"></a> cityId: city_id @SORTABLE,</span>
<span id="cb63-5"><a href="#cb63-5" aria-hidden="true" tabindex="-1"></a> city: city</span>
<span id="cb63-6"><a href="#cb63-6" aria-hidden="true" tabindex="-1"></a>};</span></code></pre></div>
<h2 data-number="3.5" id="alter-rest-procedure"><span
class="header-section-number">3.5</span> ALTER REST
PROCEDURE</h2>
<p>The <code>ALTER REST PROCEDURE</code> statement is
used to alter REST endpoints for database schema stored
procedures.</p>
<p>It uses the same <a
href="#defining-the-graphql-definition-for-a-rest-view">extended
GraphQL syntax</a> as defined for REST data mapping
views to describe the REST procedure’s parameters and
result sets. Please make sure to study the <a
href="#defining-the-graphql-definition-for-a-rest-view">corresponding
section</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestProcedureStatement:
ALTER REST PROCEDURE procedureRequestPath (
ON serviceSchemaSelector
)? (
NEW REQUEST PATH newProcedureRequestPath
)? (PARAMETERS restObjectName? graphQlObj)? restProcedureResult* restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;</code></pre>
<p>alterRestProcedureStatement ::= <img
src="sections/sql/../../images/sql/alterRestProcedureStatement.svg"
title="alterRestProcedureStatement"
alt="alterRestProcedureStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<h2 data-number="3.6" id="alter-rest-function"><span
class="header-section-number">3.6</span> ALTER REST
FUNCTION</h2>
<p>The <code>ALTER REST FUNCTION</code> statement is
used to alter REST endpoints for database schema stored
functions.</p>
<p>It uses the same <a
href="#defining-the-graphql-definition-for-a-rest-view">extended
GraphQL syntax</a> as defined for REST data mapping
views to describe the REST procedure’s parameters and
result sets. Please make sure to study the <a
href="#defining-the-graphql-definition-for-a-rest-view">corresponding
section</a>.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestFunctionStatement:
ALTER REST FUNCTION functionRequestPath (
ON serviceSchemaSelector
)? (
NEW REQUEST PATH newFunctionRequestPath
)? (PARAMETERS restObjectName? graphQlObj)? restFunctionResult* restObjectOptions?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
restObjectOptions: (
enabledDisabledPrivate
| authenticationRequired
| itemsPerPage
| jsonOptions
| comments
| metadata
| restViewMediaType
| restViewFormat
| restViewAuthenticationProcedure
)+
;</code></pre>
<p>alterRestFunctionStatement ::= <img
src="sections/sql/../../images/sql/alterRestFunctionStatement.svg"
title="alterRestFunctionStatement"
alt="alterRestFunctionStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p>restObjectOptions ::= <img
src="sections/sql/../../images/sql/restObjectOptions.svg"
title="restObjectOptions" alt="restObjectOptions" /></p>
<h2 data-number="3.7" id="alter-rest-content-set"><span
class="header-section-number">3.7</span> ALTER REST
CONTENT SET</h2>
<p>The <code>ALTER REST CONTENT SET</code> statement is
used to alter REST content sets.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestContentSetStatement:
ALTER REST CONTENT SET contentSetRequestPath (
ON SERVICE? serviceRequestPath
)? (
NEW REQUEST PATH newContentSetRequestPath
)? restContentSetOptions?
;
newContentSetRequestPath:
requestPathIdentifier
;</code></pre>
<p>alterRestContentSetStatement ::= <img
src="sections/sql/../../images/sql/alterRestContentSetStatement.svg"
title="alterRestContentSetStatement"
alt="alterRestContentSetStatement" /></p>
<h2 data-number="3.8" id="alter-rest-user"><span
class="header-section-number">3.8</span> ALTER REST
USER</h2>
<p>Change password and options for an existing REST user
account.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>alterRestUserStatement:
ALTER REST USER userName AT_SIGN authAppName (
IDENTIFIED BY userPassword
)? userOptions?
;</code></pre>
<p>createRestUserStatement ::= <img
src="sections/sql/../../images/sql/createRestUserStatement.svg"
title="createRestUserStatement"
alt="createRestUserStatement" /></p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="4" id="drop"><span
class="header-section-number">4</span> DROP</h1>
<h2 data-number="4.1" id="drop-rest-service"><span
class="header-section-number">4.1</span> DROP REST
SERVICE</h2>
<p>An existing REST service can be dropped by using the
<code>DROP REST SERVICE</code> statement.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestServiceStatement:
DROP REST SERVICE (
IF EXISTS
)? serviceRequestPath
;</code></pre>
<p>dropRestServiceStatement ::= <img
src="sections/sql/../../images/sql/dropRestServiceStatement.svg"
title="dropRestServiceStatement"
alt="dropRestServiceStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example drops a REST service with the
request path <code>/myService</code>.</p>
<div class="sourceCode" id="cb69"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb69-1"><a href="#cb69-1" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> REST SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="4.2" id="drop-rest-schema"><span
class="header-section-number">4.2</span> DROP REST
SCHEMA</h2>
<p>An existing REST schema can be dropped by using the
<code>DROP REST SCHEMA</code> statement.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestSchemaStatement:
DROP REST SCHEMA (
IF EXISTS
)? schemaRequestPath (
FROM SERVICE? serviceRequestPath
)?
;</code></pre>
<p>dropRestSchemaStatement ::= <img
src="sections/sql/../../images/sql/dropRestSchemaStatement.svg"
title="dropRestSchemaStatement"
alt="dropRestSchemaStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example drops a REST schema using the
request path <code>/myService</code>.</p>
<div class="sourceCode" id="cb71"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb71-1"><a href="#cb71-1" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">FROM</span> SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="4.3" id="drop-rest-view"><span
class="header-section-number">4.3</span> DROP REST
VIEW</h2>
<p>The <code>DROP REST DATA MAPPING VIEW</code>
statement is used to drop existing REST data mapping
views.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestViewStatement:
DROP REST DATA? MAPPING? VIEW (
IF EXISTS
)? viewRequestPath (FROM serviceSchemaSelector)?
;</code></pre>
<p>dropRestViewStatement ::= <img
src="sections/sql/../../images/sql/dropRestViewStatement.svg"
title="dropRestViewStatement"
alt="dropRestViewStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example drops a REST data mapping view
using the request path <code>/city</code>.</p>
<div class="sourceCode" id="cb73"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb73-1"><a href="#cb73-1" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city</span>
<span id="cb73-2"><a href="#cb73-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="4.4" id="drop-rest-procedure"><span
class="header-section-number">4.4</span> DROP REST
PROCEDURE</h2>
<p>The <code>DROP REST PROCEDURE</code> statement is
used to drop an existing REST procedures.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestProcedureStatement:
DROP REST PROCEDURE (
IF EXISTS
)? procedureRequestPath (FROM serviceSchemaSelector)?
;</code></pre>
<p>dropRestProcedureStatement ::= <img
src="sections/sql/../../images/sql/dropRestProcedureStatement.svg"
title="dropRestProcedureStatement"
alt="dropRestProcedureStatement" /></p>
<h2 data-number="4.5" id="drop-rest-function"><span
class="header-section-number">4.5</span> DROP REST
FUNCTION</h2>
<p>The <code>DROP REST FUNCTION</code> statement is used
to drop an existing REST functions.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestFunctionStatement:
DROP REST FUNCTION (
IF EXISTS
)? functionRequestPath (FROM serviceSchemaSelector)?
;</code></pre>
<p>dropRestFunctionStatement ::= <img
src="sections/sql/../../images/sql/dropRestFunctionStatement.svg"
title="dropRestFunctionStatement"
alt="dropRestFunctionStatement" /></p>
<h2 data-number="4.6" id="drop-rest-content-set"><span
class="header-section-number">4.6</span> DROP REST
CONTENT SET</h2>
<p>The <code>DROP REST CONTENT SET</code> statement is
used to drop an existing REST static content set.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestContentSetStatement:
DROP REST CONTENT SET (
IF EXISTS
)? contentSetRequestPath (
FROM SERVICE? serviceRequestPath
)?
;</code></pre>
<p>dropRestContentSetStatement ::= <img
src="sections/sql/../../images/sql/dropRestContentSetStatement.svg"
title="dropRestContentSetStatement"
alt="dropRestContentSetStatement" /></p>
<h2 data-number="4.7" id="drop-rest-content-file"><span
class="header-section-number">4.7</span> DROP REST
CONTENT FILE</h2>
<p>Removes a file from a REST static content set.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestContentFileStatement:
DROP REST CONTENT FILE (
IF EXISTS
)? contentFileRequestPath FROM (
SERVICE? serviceRequestPath
)? CONTENT SET contentSetRequestPath
;</code></pre>
<p>dropRestContentFileStatement ::= <img
src="sections/sql/../../images/sql/dropRestContentFileStatement.svg"
title="dropRestContentFileStatement"
alt="dropRestContentFileStatement" /></p>
<h2 data-number="4.8" id="drop-rest-auth-app"><span
class="header-section-number">4.8</span> DROP REST AUTH
APP</h2>
<p>The <code>DROP REST AUTH APP</code> statement is used
to drop an existing REST authentication app from a REST
service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestAuthAppStatement:
DROP REST (AUTH | AUTHENTICATION) APP (
IF EXISTS
)? authAppName
;</code></pre>
<p>dropRestAuthAppStatement ::= <img
src="sections/sql/../../images/sql/dropRestAuthAppStatement.svg"
title="dropRestAuthAppStatement"
alt="dropRestAuthAppStatement" /></p>
<h2 data-number="4.9" id="drop-rest-user"><span
class="header-section-number">4.9</span> DROP REST
USER</h2>
<p>The <code>DROP REST USER</code> statement is used to
drop an existing REST user from a REST authentication
app.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestUserStatement:
DROP REST USER (IF EXISTS)? userName AT_SIGN
authAppName
;</code></pre>
<p>dropRestUserStatement ::= <img
src="sections/sql/../../images/sql/dropRestUserStatement.svg"
title="dropRestUserStatement"
alt="dropRestUserStatement" /></p>
<h2 data-number="4.10" id="drop-rest-role"><span
class="header-section-number">4.10</span> DROP REST
ROLE</h2>
<p>Drops the named REST role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dropRestRoleStatement:
DROP REST ROLE (IF EXISTS)? roleName roleService?
;</code></pre>
<p>dropRestRoleStatement ::= <img
src="sections/sql/../../images/sql/dropRestRoleStatement.svg"
title="dropRestRoleStatement"
alt="dropRestRoleStatement" /></p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="5" id="grant-and-revoke"><span
class="header-section-number">5</span> GRANT and
REVOKE</h1>
<h2 data-number="5.1" id="grant-rest"><span
class="header-section-number">5.1</span> GRANT REST</h2>
<p>Grants REST privileges on endpoint objects to a
role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>grantRestPrivilegeStatement:
GRANT REST privilegeList (
(ON SERVICE? serviceRequestPathWildcard)
| (
ON serviceSchemaSelectorWildcard (
OBJECT objectRequestPathWildcard
)?
)
)? TO roleName roleService?
;
privilegeList:
privilegeName
| privilegeName COMMA privilegeList
;
privilegeName:
CREATE
| READ
| UPDATE
| DELETE
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>grantRestPrivilegeStatement ::= <img
src="sections/sql/../../images/sql/grantRestPrivilegeStatement.svg"
title="grantRestPrivilegeStatement"
alt="grantRestPrivilegeStatement" /></p>
<p>privilegeList ::= <img
src="sections/sql/../../images/sql/privilegeList.svg"
title="privilegeList" alt="privilegeList" /></p>
<p>privilegeName ::= <img
src="sections/sql/../../images/sql/privilegeName.svg"
title="privilegeName" alt="privilegeName" /></p>
<h2 data-number="5.2" id="grant-rest-role"><span
class="header-section-number">5.2</span> GRANT REST
ROLE</h2>
<p>Grants a REST role to a user account.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>grantRestRoleStatement:
GRANT REST ROLE roleName roleService? TO userName AT_SIGN
authAppName comments?
;</code></pre>
<p>grantRestRoleStatement ::= <img
src="sections/sql/../../images/sql/grantRestRoleStatement.svg"
title="grantRestRoleStatement"
alt="grantRestRoleStatement" /></p>
<h2 data-number="5.3" id="revoke-rest"><span
class="header-section-number">5.3</span> REVOKE
REST</h2>
<p>Revokes privileges on an REST endpoint object from a
role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>revokeRestPrivilegeStatement:
REVOKE REST privilegeList (
(ON SERVICE? serviceRequestPathWildcard)
| (
ON serviceSchemaSelectorWildcard (
OBJECT objectRequestPathWildcard
)?
)
)? FROM roleName roleService?
;</code></pre>
<p>revokeRestPrivilegeStatement ::= <img
src="sections/sql/../../images/sql/revokeRestPrivilegeStatement.svg"
title="revokeRestPrivilegeStatement"
alt="revokeRestPrivilegeStatement" /></p>
<h2 data-number="5.4" id="revoke-rest-role"><span
class="header-section-number">5.4</span> REVOKE REST
ROLE</h2>
<p>Revokes a role from a REST user.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>revokeRestRoleStatement:
REVOKE REST ROLE roleName roleService? FROM userName AT_SIGN
authAppName
;</code></pre>
<p>revokeRestRoleStatement ::= <img
src="sections/sql/../../images/sql/revokeRestRoleStatement.svg"
title="revokeRestRoleStatement"
alt="revokeRestRoleStatement" /></p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="6" id="use-and-show"><span
class="header-section-number">6</span> USE and SHOW</h1>
<h2 data-number="6.1" id="use"><span
class="header-section-number">6.1</span> USE</h2>
<p>An existing REST service can be dropped by using the
<code>DROP REST SERVICE</code> statement.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>useStatement:
USE REST serviceAndSchemaRequestPaths
;
serviceAndSchemaRequestPaths:
SERVICE serviceRequestPath
| serviceSchemaSelector
;</code></pre>
<p>useStatement ::= <img
src="sections/sql/../../images/sql/useStatement.svg"
title="useStatement" alt="useStatement" /></p>
<p>serviceAndSchemaRequestPaths ::= <img
src="sections/sql/../../images/sql/serviceAndSchemaRequestPaths.svg"
title="serviceAndSchemaRequestPaths"
alt="serviceAndSchemaRequestPaths" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example makes the REST service with the
request path <code>/myService</code> the new default
REST service.</p>
<div class="sourceCode" id="cb86"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb86-1"><a href="#cb86-1" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<p>After the default REST service has been set, the
following statement can be used to set the default REST
schema.</p>
<div class="sourceCode" id="cb87"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb87-1"><a href="#cb87-1" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<p>The next example shows how to set the default REST
service and REST schema in a single statement.</p>
<div class="sourceCode" id="cb88"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb88-1"><a href="#cb88-1" aria-hidden="true" tabindex="-1"></a><span class="kw">USE</span> REST SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="6.2" id="show-rest-status"><span
class="header-section-number">6.2</span> SHOW REST
STATUS</h2>
<p>The <code>SHOW REST STATUS</code> statement is used
to get basic information about the current status of the
MySQL REST Service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestMetadataStatusStatement:
SHOW REST METADATA? STATUS
;</code></pre>
<p>showRestMetadataStatusStatement ::= <img
src="sections/sql/../../images/sql/showRestMetadataStatusStatement.svg"
title="showRestMetadataStatusStatement"
alt="showRestMetadataStatusStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example drops a REST schema using the
request path <code>/myService</code>.</p>
<div class="sourceCode" id="cb90"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb90-1"><a href="#cb90-1" aria-hidden="true" tabindex="-1"></a>SHOW REST STATUS;</span></code></pre></div>
<h2 data-number="6.3" id="show-rest-services"><span
class="header-section-number">6.3</span> SHOW REST
SERVICES</h2>
<p>The <code>SHOW REST SERVICES</code> statement lists
all available REST services.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestServicesStatement:
SHOW REST SERVICES
;</code></pre>
<p>showRestServicesStatement ::= <img
src="sections/sql/../../images/sql/showRestServicesStatement.svg"
title="showRestServicesStatement"
alt="showRestServicesStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST services.</p>
<div class="sourceCode" id="cb92"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb92-1"><a href="#cb92-1" aria-hidden="true" tabindex="-1"></a>SHOW REST SERVICES;</span></code></pre></div>
<h2 data-number="6.4" id="show-rest-schemas"><span
class="header-section-number">6.4</span> SHOW REST
SCHEMAS</h2>
<p>The <code>SHOW REST SCHEMAS</code> statement lists
all available REST schemas of the given or current REST
service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestSchemasStatement:
SHOW REST SCHEMAS (
(ON | FROM) SERVICE? serviceRequestPath
)?
;</code></pre>
<p>showRestSchemasStatement ::= <img
src="sections/sql/../../images/sql/showRestSchemasStatement.svg"
title="showRestSchemasStatement"
alt="showRestSchemasStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST schemas of the
REST service using the request path
<code>/myService</code>.</p>
<div class="sourceCode" id="cb94"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb94-1"><a href="#cb94-1" aria-hidden="true" tabindex="-1"></a>SHOW REST SERVICES <span class="kw">FROM</span> <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="6.5" id="show-rest-views"><span
class="header-section-number">6.5</span> SHOW REST
VIEWS</h2>
<p>The <code>SHOW REST DATA MAPPING VIEWS</code>
statement lists all available REST data mapping views of
the given or current REST schema.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestViewsStatement:
SHOW REST DATA? MAPPING? VIEWS (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showRestViewsStatement ::= <img
src="sections/sql/../../images/sql/showRestViewsStatement.svg"
title="showRestViewsStatement"
alt="showRestViewsStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST data mapping
views of the given REST schema.</p>
<div class="sourceCode" id="cb96"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb96-1"><a href="#cb96-1" aria-hidden="true" tabindex="-1"></a>SHOW REST VIEWS <span class="kw">FROM</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="6.6" id="show-rest-procedures"><span
class="header-section-number">6.6</span> SHOW REST
PROCEDURES</h2>
<p>The <code>SHOW REST PROCEDURES</code> statement lists
all available REST procedures of the given or current
REST schema.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestProceduresStatement:
SHOW REST PROCEDURES (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showRestProceduresStatement ::= <img
src="sections/sql/../../images/sql/showRestProceduresStatement.svg"
title="showRestProceduresStatement"
alt="showRestProceduresStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<h2 data-number="6.7" id="show-rest-functions"><span
class="header-section-number">6.7</span> SHOW REST
FUNCTIONS</h2>
<p>The <code>SHOW REST FUNCTIONS</code> statement lists
all available REST functions of the given or current
REST schema.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestFunctionsStatement:
SHOW REST FUNCTIONS (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showRestFunctionsStatement ::= <img
src="sections/sql/../../images/sql/showRestFunctionsStatement.svg"
title="showRestFunctionsStatement"
alt="showRestFunctionsStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST procedures of
the given REST schema.</p>
<div class="sourceCode" id="cb99"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb99-1"><a href="#cb99-1" aria-hidden="true" tabindex="-1"></a>SHOW REST PROCEDURES <span class="kw">FROM</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="6.8" id="show-rest-content-sets"><span
class="header-section-number">6.8</span> SHOW REST
CONTENT SETS</h2>
<p>The <code>SHOW REST CONTENT SETS</code> statement
lists all available REST content sets of the given or
current REST service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestContentSetsStatement:
SHOW REST CONTENT SETS (
(ON | FROM) SERVICE? serviceRequestPath
)?
;</code></pre>
<p>showRestContentSetsStatement ::= <img
src="sections/sql/../../images/sql/showRestContentSetsStatement.svg"
title="showRestContentSetsStatement"
alt="showRestContentSetsStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST content sets of
the given REST service.</p>
<div class="sourceCode" id="cb101"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb101-1"><a href="#cb101-1" aria-hidden="true" tabindex="-1"></a>SHOW REST CONTENT SETS <span class="kw">FROM</span> SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="6.9" id="show-rest-content-files"><span
class="header-section-number">6.9</span> SHOW REST
CONTENT FILES</h2>
<p>The <code>SHOW REST CONTENT SETS</code> statement
lists all available REST content files of the given
content set.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestContentFilesStatement:
SHOW REST CONTENT FILES (
ON
| FROM
) (SERVICE? serviceRequestPath)? CONTENT SET contentSetRequestPath
;</code></pre>
<p>showRestContentFilesStatement ::= <img
src="sections/sql/../../images/sql/showRestContentFilesStatement.svg"
title="showRestContentFilesStatement"
alt="showRestContentFilesStatement" /></p>
<h2 data-number="6.10"
id="show-create-rest-content-set"><span
class="header-section-number">6.10</span> SHOW CREATE
REST CONTENT SET</h2>
<p>Shows the CREATE SQL statement corresponding to the
given content set.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestContentSetStatement:
SHOW CREATE REST CONTENT SET contentSetRequestPath (
(ON | FROM) SERVICE? serviceRequestPath
)?
;</code></pre>
<p>showCreateRestContentSetStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestContentSetStatement.svg"
title="showCreateRestContentSetStatement"
alt="showCreateRestContentSetStatement" /></p>
<h2 data-number="6.11"
id="show-create-rest-content-file"><span
class="header-section-number">6.11</span> SHOW CREATE
REST CONTENT FILE</h2>
<p>Shows the CREATE SQL statement corresponding to the
given content file.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestContentFileStatement:
SHOW CREATE REST CONTENT FILE contentFileRequestPath (
ON
| FROM
) (SERVICE? serviceRequestPath)? CONTENT SET contentSetRequestPath
;</code></pre>
<p>showCreateRestContentFileStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestContentFileStatement.svg"
title="showCreateRestContentFileStatement"
alt="showCreateRestContentFileStatement" /></p>
<h2 data-number="6.12" id="show-rest-auth-apps"><span
class="header-section-number">6.12</span> SHOW REST AUTH
APPS</h2>
<p>The <code>SHOW REST AUTH APPS</code> statement lists
all available REST auth apps of the given or current
REST service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestAuthAppsStatement:
SHOW REST AUTH APPS (
(ON | FROM) SERVICE? serviceRequestPath
)?
;</code></pre>
<p>showRestAuthAppsStatement ::= <img
src="sections/sql/../../images/sql/showRestAuthAppsStatement.svg"
title="showRestAuthAppsStatement"
alt="showRestAuthAppsStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example lists all REST auth apps of the
given REST service.</p>
<div class="sourceCode" id="cb106"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb106-1"><a href="#cb106-1" aria-hidden="true" tabindex="-1"></a>SHOW REST AUTH APPS <span class="kw">FROM</span> SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="6.13"
id="show-create-rest-service"><span
class="header-section-number">6.13</span> SHOW CREATE
REST SERVICE</h2>
<p>The <code>SHOW CREATE REST SERVICE</code> statement
shows the corresponding DDL statement for the given REST
service.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestServiceStatement:
SHOW CREATE REST SERVICE serviceRequestPath? (
INCLUDING SCHEMA ENDPOINTS
)?
;</code></pre>
<p>showCreateRestServiceStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestServiceStatement.svg"
title="showCreateRestServiceStatement"
alt="showCreateRestServiceStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
REST service with request path
<code>/myService</code>.</p>
<div class="sourceCode" id="cb108"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb108-1"><a href="#cb108-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST SERVICE <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="6.14"
id="show-create-rest-schema"><span
class="header-section-number">6.14</span> SHOW CREATE
REST SCHEMA</h2>
<p>The <code>SHOW CREATE REST SCHEMA</code> statement
shows the corresponding DDL statement for the given REST
schema.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestSchemaStatement:
SHOW CREATE REST SCHEMA schemaRequestPath? (
(ON | FROM) SERVICE? serviceRequestPath
)?
;</code></pre>
<p>showCreateRestSchemaStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestSchemaStatement.svg"
title="showCreateRestSchemaStatement"
alt="showCreateRestSchemaStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST schema.</p>
<div class="sourceCode" id="cb110"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb110-1"><a href="#cb110-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">SCHEMA</span> <span class="op">/</span>sakila <span class="kw">FROM</span> <span class="op">/</span>myService;</span></code></pre></div>
<h2 data-number="6.15" id="show-create-rest-view"><span
class="header-section-number">6.15</span> SHOW CREATE
REST VIEW</h2>
<p>The <code>SHOW CREATE REST DATA MAPPING VIEW</code>
statement shows the corresponding DDL statement for the
given REST data mapping view.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestViewStatement:
SHOW CREATE REST DATA? MAPPING? VIEW viewRequestPath (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showCreateRestViewStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestViewStatement.svg"
title="showCreateRestViewStatement"
alt="showCreateRestViewStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST data mapping view.</p>
<div class="sourceCode" id="cb112"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb112-1"><a href="#cb112-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">VIEW</span> <span class="op">/</span>city <span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="6.16"
id="show-create-rest-procedure"><span
class="header-section-number">6.16</span> SHOW CREATE
REST PROCEDURE</h2>
<p>The <code>SHOW CREATE REST PROCEDURE</code> statement
shows the corresponding DDL statement for the given REST
procedure.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestProcedureStatement:
SHOW CREATE REST PROCEDURE procedureRequestPath (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showCreateRestProcedureStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestProcedureStatement.svg"
title="showCreateRestProcedureStatement"
alt="showCreateRestProcedureStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<h2 data-number="6.17"
id="show-create-rest-function"><span
class="header-section-number">6.17</span> SHOW CREATE
REST FUNCTION</h2>
<p>The <code>SHOW CREATE REST FUNCTION</code> statement
shows the corresponding DDL statement for the given REST
function.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestFunctionStatement:
SHOW CREATE REST FUNCTION functionRequestPath (
(ON | FROM) serviceSchemaSelector
)?
;
serviceSchemaSelector:
(SERVICE serviceRequestPath)? SCHEMA schemaRequestPath
;</code></pre>
<p>showCreateRestFunctionStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestFunctionStatement.svg"
title="showCreateRestFunctionStatement"
alt="showCreateRestFunctionStatement" /></p>
<p>serviceSchemaSelector ::= <img
src="sections/sql/../../images/sql/serviceSchemaSelector.svg"
title="serviceSchemaSelector"
alt="serviceSchemaSelector" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST procedure.</p>
<div class="sourceCode" id="cb115"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb115-1"><a href="#cb115-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">PROCEDURE</span> <span class="op">/</span>inventory_in_stock <span class="kw">ON</span> SERVICE <span class="op">/</span>myService <span class="kw">SCHEMA</span> <span class="op">/</span>sakila;</span></code></pre></div>
<h2 data-number="6.18"
id="show-create-rest-auth-app"><span
class="header-section-number">6.18</span> SHOW CREATE
REST AUTH APP</h2>
<p>The <code>SHOW CREATE REST AUTH APP</code> statement
shows the corresponding DDL statement for the given REST
auth app.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestAuthAppStatement:
SHOW CREATE REST AUTH APP authAppName
;</code></pre>
<p>showCreateRestAuthAppStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestAuthAppStatement.svg"
title="showCreateRestAuthAppStatement"
alt="showCreateRestAuthAppStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST auth app.</p>
<div class="sourceCode" id="cb117"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb117-1"><a href="#cb117-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST AUTH APP <span class="ot">"MRS"</span> <span class="kw">FROM</span> SERVICE <span class="op">/</span>myTestService;</span></code></pre></div>
<h2 data-number="6.19" id="show-create-rest-role"><span
class="header-section-number">6.19</span> SHOW CREATE
REST ROLE</h2>
<p>The <code>SHOW CREATE REST ROLE</code> statement
shows the corresponding DDL statement for the given REST
role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestRoleStatement:
SHOW CREATE REST ROLE roleName roleService?
;
roleService:
ON (
ANY SERVICE
| SERVICE? serviceRequestPath
)
;</code></pre>
<p>showCreateRestRoleStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestRoleStatement.svg"
title="showCreateRestRoleStatement"
alt="showCreateRestRoleStatement" /></p>
<p>roleService ::= <img
src="sections/sql/../../images/sql/roleService.svg"
title="roleService" alt="roleService" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST auth app.</p>
<div class="sourceCode" id="cb119"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb119-1"><a href="#cb119-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="kw">ROLE</span> `myrole` <span class="kw">ON</span> SERVICE <span class="op">/</span>myTestService;</span></code></pre></div>
<h2 data-number="6.20" id="show-create-rest-user"><span
class="header-section-number">6.20</span> SHOW CREATE
REST USER</h2>
<p>The <code>SHOW CREATE REST USER</code> statement
shows the corresponding DDL statement for the given REST
user account.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showCreateRestUserStatement:
SHOW CREATE REST USER userName AT_SIGN authAppName
;</code></pre>
<p>showCreateRestUserStatement ::= <img
src="sections/sql/../../images/sql/showCreateRestUserStatement.svg"
title="showCreateRestUserStatement"
alt="showCreateRestUserStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example shows the DDL statement for the
given REST auth app.</p>
<div class="sourceCode" id="cb121"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb121-1"><a href="#cb121-1" aria-hidden="true" tabindex="-1"></a>SHOW <span class="kw">CREATE</span> REST <span class="fu">USER</span> myuser@`MRS` <span class="kw">ON</span> SERVICE <span class="op">/</span>myTestService;</span></code></pre></div>
<h2 data-number="6.21" id="show-rest-roles"><span
class="header-section-number">6.21</span> SHOW REST
ROLES</h2>
<p>Shows a list of roles, optionally filtered by service
or auth app and users that were granted the role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestRolesStatement:
SHOW REST ROLES (
(ON | FROM) (
ANY SERVICE
| SERVICE? serviceRequestPath
)
)? (FOR userName? AT_SIGN authAppName)?
;</code></pre>
<p>showRestRolesStatement ::= <img
src="sections/sql/../../images/sql/showRestRolesStatement.svg"
title="showRestRolesStatement"
alt="showRestRolesStatement" /></p>
<h2 data-number="6.22" id="show-rest-grants"><span
class="header-section-number">6.22</span> SHOW REST
GRANTS</h2>
<p>Show the list of REST privileges that were granted to
the given role.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>showRestGrantsStatement:
SHOW REST GRANTS FOR roleName (
(ON | FROM) (
ANY SERVICE
| SERVICE? serviceRequestPath
)
)?
;</code></pre>
<p>showRestGrantsStatement ::= <img
src="sections/sql/../../images/sql/showRestGrantsStatement.svg"
title="showRestGrantsStatement"
alt="showRestGrantsStatement" /></p>
<!-- Copyright (c) 2022, 2026, Oracle and/or its affiliates.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License, version 2.0,
as published by the Free Software Foundation.
This program is designed to work with certain software (including
but not limited to OpenSSL) that is licensed under separate terms, as
designated in a particular file or component or in included license
documentation. The authors of MySQL hereby grant you an additional
permission to link the program and your derivative works with the
separately licensed software that they have either included with
the program or referenced in the documentation.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
the GNU General Public License, version 2.0, for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -->
<h1 data-number="7" id="dump"><span
class="header-section-number">7</span> DUMP</h1>
<h2 data-number="7.1" id="dump-rest-project"><span
class="header-section-number">7.1</span> DUMP REST
PROJECT</h2>
<p>An existing REST service can be dumped as REST
project by using the <code>DUMP REST PROJECT</code>
statement.</p>
<p>The command will generate a directory containing the
following:</p>
<ul>
<li><code>mrs.package.json</code> containing the project
details</li>
<li><code>*.service.mrs.sql</code> containing the REST
SQL for each service</li>
<li>other SQL files containing schema dumps</li>
<li>directories containing schema dumps</li>
<li><code>appIcon.*</code> being the icon for this
project</li>
</ul>
<p>This directory may then be zipped into a file.</p>
<p><strong><em>SYNTAX</em></strong></p>
<pre class="antlr"><code>dumpRestProjectStatement:
DUMP REST PROJECT
restProjectName VERSION restProjectVersion
(dumpRestProjectService)+
(dumpRestProjectDatabaseSchema)*
(dumpRestProjectSettings)?
TO (ZIP)? directoryFilePath
;
dumpRestProjectService:
SERVICE serviceRequestPath
INCLUDING ((DATABASE (AND STATIC (AND DYNAMIC)?)?) | ALL) ENDPOINTS
;
dumpRestProjectDatabaseSchema:
DATABASE schemaName (FROM restProjectDatabaseSchemaFilePath)?
;
dumpRestProjectSettings: (
ICON FROM restProjectIconFilePath
| DESCRIPTION restProjectDescription
| PUBLISHER restProjectPublisher
)+
;</code></pre>
<p>dumpRestProjectStatement ::= <img
src="sections/sql/../../images/sql/dumpRestProjectStatement.svg"
title="dumpRestProjectStatement"
alt="dumpRestProjectStatement" /></p>
<p>dumpRestProjectService ::= <img
src="sections/sql/../../images/sql/dumpRestProjectService.svg"
title="dumpRestProjectService"
alt="dumpRestProjectService" /></p>
<p>dumpRestProjectDatabaseSchema ::= <img
src="sections/sql/../../images/sql/dumpRestProjectDatabaseSchema.svg"
title="dumpRestProjectDatabaseSchema"
alt="dumpRestProjectDatabaseSchema" /></p>
<p>dumpRestProjectSettings ::= <img
src="sections/sql/../../images/sql/dumpRestProjectSettings.svg"
title="dumpRestProjectSettings"
alt="dumpRestProjectSettings" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example dumps the REST service with the
request path <code>/myService</code> to a REST project,
including the database schema <code>sakila</code> it is
based on.</p>
<div class="sourceCode" id="cb125"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb125-1"><a href="#cb125-1" aria-hidden="true" tabindex="-1"></a><span class="fu">DUMP</span> REST PROJECT <span class="st">'myServiceProject'</span> VERSION <span class="st">'1.0.0'</span></span>
<span id="cb125-2"><a href="#cb125-2" aria-hidden="true" tabindex="-1"></a> SERVICE <span class="op">/</span>myService <span class="kw">INCLUDING</span> <span class="kw">ALL</span> ENDPOINTS</span>
<span id="cb125-3"><a href="#cb125-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">DATABASE</span> `sakila`</span>
<span id="cb125-4"><a href="#cb125-4" aria-hidden="true" tabindex="-1"></a> DESCRIPTION <span class="st">'My first REST project'</span></span>
<span id="cb125-5"><a href="#cb125-5" aria-hidden="true" tabindex="-1"></a> PUBLISHER <span class="st">'Oracle'</span></span>
<span id="cb125-6"><a href="#cb125-6" aria-hidden="true" tabindex="-1"></a> <span class="kw">TO</span> ZIP <span class="st">'~/myServiceProject.zip'</span>;</span></code></pre></div>
<p>Here’s a full example to dump a REST service
containing most variations of the command.</p>
<div class="sourceCode" id="cb126"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb126-1"><a href="#cb126-1" aria-hidden="true" tabindex="-1"></a><span class="fu">DUMP</span> REST PROJECT <span class="st">'myProject'</span> VERSION <span class="st">'1.0.0'</span></span>
<span id="cb126-2"><a href="#cb126-2" aria-hidden="true" tabindex="-1"></a> SERVICE <span class="op">/</span>myService <span class="kw">INCLUDING</span> <span class="kw">DATABASE</span> <span class="kw">AND</span> <span class="kw">STATIC</span> <span class="kw">AND</span> <span class="kw">DYNAMIC</span> ENDPOINTS</span>
<span id="cb126-3"><a href="#cb126-3" aria-hidden="true" tabindex="-1"></a> SERVICE <span class="op">/</span>myService2 <span class="kw">INCLUDING</span> <span class="kw">ALL</span> ENDPOINTS</span>
<span id="cb126-4"><a href="#cb126-4" aria-hidden="true" tabindex="-1"></a> <span class="kw">DATABASE</span> `chatApp` <span class="kw">FROM</span> <span class="st">'~/mrs_plugin/examples/mrs_chat/db_schema/chat_db_schema.sql'</span></span>
<span id="cb126-5"><a href="#cb126-5" aria-hidden="true" tabindex="-1"></a> <span class="kw">DATABASE</span> `chatRestService` <span class="kw">FROM</span> <span class="st">'~/mrs_plugin/examples/mrs_chat/db_schema/chat_rest_service.sql'</span></span>
<span id="cb126-6"><a href="#cb126-6" aria-hidden="true" tabindex="-1"></a> <span class="kw">DATABASE</span> `chatApp2` <span class="kw">FROM</span> <span class="st">'~/mrs_plugin/examples/mrs_chat/db_schema/'</span></span>
<span id="cb126-7"><a href="#cb126-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">DATABASE</span> `sakila`</span>
<span id="cb126-8"><a href="#cb126-8" aria-hidden="true" tabindex="-1"></a> DESCRIPTION <span class="st">'This is my first project'</span></span>
<span id="cb126-9"><a href="#cb126-9" aria-hidden="true" tabindex="-1"></a> PUBLISHER <span class="st">'Oracle'</span></span>
<span id="cb126-10"><a href="#cb126-10" aria-hidden="true" tabindex="-1"></a> ICON <span class="kw">FROM</span> <span class="st">'~/icon.svg'</span></span>
<span id="cb126-11"><a href="#cb126-11" aria-hidden="true" tabindex="-1"></a> <span class="kw">TO</span> ZIP <span class="st">'~/project.myService.zip'</span>;</span></code></pre></div>
<p>Lets go through the command an explain it in
detail.</p>
<p>The first part
<code>DUMP REST PROJECT 'myProject' VERSION '1.0.0'</code>
is the project name and version. The version is not
strict to any version format, allowing for
<code>'v1.0'</code> or <code>'1.0.0b'</code> to be valid
versions.</p>
<p>Following we have the services that should be
included in the project. You can check the</p>
<div class="sourceCode" id="cb127"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb127-1"><a href="#cb127-1" aria-hidden="true" tabindex="-1"></a>SERVICE <span class="op">/</span>myService <span class="kw">INCLUDING</span> <span class="kw">DATABASE</span> <span class="kw">AND</span> <span class="kw">STATIC</span> <span class="kw">AND</span> <span class="kw">DYNAMIC</span> ENDPOINTS</span></code></pre></div>
<p>The service request path is referring to the service
required on the project and you are able to include
multiple services in the project. Each of these
statements will create its own REST SQL file containing
all the REST SQL commands to recreate the service. From
that service, you choose what to include: -
<code>DATABASE</code> include REST objects like TABLE,
VIEW, PROCEDURE, FUNCTION and SCRIPT -
<code>STATIC</code> include content sets that are not of
SCRIPT type - <code>DYNAMIC</code> include content sets
that are of SCRIPT type - <code>ALL</code> short for
<code>DATABASE AND STATIC AND DYNAMIC</code></p>
<p>Each of these settings are a superset of the
former.</p>
<p>Then we have the <code>DATABASE</code> sub-statements
that either dump a schema</p>
<div class="sourceCode" id="cb128"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb128-1"><a href="#cb128-1" aria-hidden="true" tabindex="-1"></a><span class="kw">DATABASE</span> `sakila`</span></code></pre></div>
<p>or uses a SQL file containing the dump of a
schema</p>
<div class="sourceCode" id="cb129"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb129-1"><a href="#cb129-1" aria-hidden="true" tabindex="-1"></a><span class="kw">DATABASE</span> `chatApp` <span class="kw">FROM</span> <span class="st">'~/mrs_plugin/examples/mrs_chat/db_schema/chat_db_schema.sql'</span></span></code></pre></div>
<p>Then we can find the project settings. These settings
are optional but brings some detail to the project.</p>
<ul>
<li><code>DESCRIPTION</code> is a short description for
the project.</li>
<li><code>PUBLISHER</code> the owner or publisher of the
project.</li>
<li><code>ICON</code> will copy an icon file from the
specified path.</li>
</ul>
<p>All this information will be in the
<code>mrs.package.json</code> project file.</p>
<p>Finally there’s the
<code>TO ZIP '~/project.myService.zip'</code> which
defined the output of the command. The command can
generate a directory with all the contents or, if the
<code>ZIP</code> command is used, generate a zip file
with the directory contents.</p>
<h2 data-number="7.2" id="dump-rest-service"><span
class="header-section-number">7.2</span> DUMP REST
SERVICE</h2>
<p>An existing REST service can be dumped to disk by
using the <code>DUMP REST SERVICE</code> statement. This
dump does not include the database schema that it is
based on. To create a fully consistent dump that also
includes the database schema, please use the
<code>DUMP REST PROJECT</code> statement instead.</p>
<pre class="antlr"><code>dumpRestServiceStatement:
DUMP REST SERVICE serviceRequestPath AS (
SQL
)? SCRIPT INCLUDING (
(
DATABASE (
AND STATIC (
AND DYNAMIC
)?
)?
)
| ALL
) ENDPOINTS TO (ZIP)? directoryFilePath
;</code></pre>
<p>dumpRestServiceStatement ::= <img
src="sections/sql/../../images/sql/dumpRestServiceStatement.svg"
title="dumpRestServiceStatement"
alt="dumpRestServiceStatement" /></p>
<p><strong><em>Examples</em></strong></p>
<p>The following example dumps the REST service with the
request path <code>/myService</code>.</p>
<div class="sourceCode" id="cb131"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb131-1"><a href="#cb131-1" aria-hidden="true" tabindex="-1"></a><span class="fu">DUMP</span> REST SERVICE <span class="op">/</span>myService <span class="kw">AS</span> SQL SCRIPT <span class="kw">INCLUDING</span> <span class="kw">DATABASE</span> <span class="kw">AND</span> <span class="kw">STATIC</span> <span class="kw">AND</span> <span class="kw">DYNAMIC</span> ENDPOINTS <span class="kw">TO</span> <span class="st">'~/myService.sql'</span></span></code></pre></div>
<div class="sourceCode" id="cb132"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb132-1"><a href="#cb132-1" aria-hidden="true" tabindex="-1"></a><span class="fu">DUMP</span> REST SERVICE <span class="op">/</span>myService <span class="kw">AS</span> SQL SCRIPT <span class="kw">INCLUDING</span> <span class="kw">ALL</span> ENDPOINTS <span class="kw">TO</span> ZIP <span class="st">'~/myService.zip'</span>;</span></code></pre></div>
<p>The service request path is referring to the service
required on the project and you are able to include
multiple services in the project. This statements will
create a REST SQL file containing all the REST SQL
commands to recreate the service. From that service, you
choose what to include: - <code>DATABASE</code> include
REST objects like TABLE, VIEW, PROCEDURE, FUNCTION and
SCRIPT - <code>STATIC</code> include content sets that
are not of SCRIPT type - <code>DYNAMIC</code> include
content sets that are of SCRIPT type - <code>ALL</code>
short for
<code>DATABASE AND STATIC AND DYNAMIC</code></p>
<p>Each of these settings are a superset of the
former.</p>
<p>The destination file is defined with the
<code>TO</code> or <code>TO ZIP</code> expression when
the created file is a REST SQL script or a zipped REST
SQL file.</p>
<p>Copyright (c) 2023, 2025, Oracle and/or its
affiliates.</p>
</div>
</div>
</div>
</body>
</html>