Db2 Rest End Point
and Database Security
27 July 2022
Dean Compher
While the Db2 REST endpoint can be a great convenience to developers, I believe that it can also enhance security around your Db2 databases. The biggest reason that I say this is that when using the REST endpoint, none of your applications need log into the database or even have a user id on the database system. Further, the applications don’t even need know any information about the location of the database including the host/IP, port or even the database name. The same goes for the application developers. With the Db2 REST endpoint, you can define the only queries that are allowed to be run against the database and prohibit any others from running. In this article I will show how this can be done.
This is not an official guide to database security. It is my own thoughts on how the REST endpoint could be used to possibly enhance the security of your database. You should review these suggestions and take any that your organization deems helpful. Also, none of the code snippets have been rigorously tested and are only here for illustration purposes.
You can see the basics of the DB2 REST endpoint in my previous article including diagrams of two possible deployment options. You can find information about the Db2 REST Endpoint in the Knowledge Center. Further, you can get detailed information about the provided REST endpoint services including code samples for each in JSON, Python, CURL and JavaScript from the REST endpoint container after you deploy it. This container documentation also shows the error codes and their meanings. You can view this documentation by putting this URL in your browser: https://<host_or_IP>:50050/docs and then choosing the desired option in the menu on the left. If you are running your container as http instead of https, then use that in your URL instead. In the rest of this document, I will refer to this documentation as “Container docs”.
The endpoint allows you to create your own REST services that each execute an SQL query on the database. Applications call the services on the endpoint. In turn the endpoint connects to the database and executes the SQL defined in the service, providing results back to the application. One of the services that is deployed with the endpoint by default is the “execsql” service that allows the caller to execute any free form SQL they desire. If you are implementing the REST endpoint to enhance security for a production database then the ability to execute any SQL may be undesirable. Therefore, you can deploy the REST endpoint container using this parameter to turn that service off:
-e DB2REST_EXECSQL_DISABLED=true
If you have already created the container without this parameter then you should remove it and recreate the container. See Disabling direct SQL execution in the documentation.
One interesting thing to point out is that when you create a service, nothing is stored in the endpoint container. Services you create are implemented as stored procedures in the database being queried. Further, all authorities such as who can create, list and execute the services are invoked at the database level. Generally, you will call pre-defined services in the endpoint to GRANT and REVOKE privileges to the services you create, and those built-in services then tell the database to grant or revoke the privileges to the stored procedures that the endpoint creates for you.
Endpoint Configuration Steps
· Creating and Authorizing Services
· Give Access to the New Service
After the container is created the next step is to define which database or databases you wish to query through the endpoint. The steps for doing this are describe in Activating and initializing REST capability and there is also an example of this in my previous Db2 REST article. This process requires a fairly high level of access to the database as described in the Setting up metadata section of the Required privileges for working with REST endpoints in Db2 documentation. This process not only tells the endpoint about the database, but also creates some tables and stored procedures in the database under the DB2REST schema, unless you chose your own SCHEMA name for the endpoint database objects. This only needs to be done once for each database. It might be convenient to have your friendly Database Administrator run these commands for you using her credentials rather than giving someone else such high levels of access for a one-time process.
Before creating services to execute individual queries against the database, there are a few items to take care of first. It is a good idea to put the service objects into their own schema in the database. If you have different application projects using the same database, then you may want to create a schema for each project to group services by project. In the following examples I will use schema called REST_SERVICES. A user with DBADM privileges must do that, so this is another thing you can ask your DBA to do.
CREATE SCHEMA REST_SERVICES;
The Required privileges for working with REST endpoints in Db2 page shows the privileges to do various tasks related to service administration and use. You can choose to give all these privileges to certain user ids, or you may choose to separate duties for security purposes. To do the later, I show an example creating roles, and enforcing those roles by giving only particular authorities in the database to those roles. Here is one way to divide these the roles. You may decide to do this differently.
|
Role |
Description |
|
service_admin |
Creates, drops and updates the REST services and grants access to those services to users of the service which will mostly be applications. Allows token creation for users in this role. User ids with this role should also be allowed to list and describe services. |
|
service_user |
Executes the services that will query the database. User ids with this role will also be allowed to list services, plus create tokens. |
The endpoint uses Db2 Database user ids to control access and authorities. It uses the database to keep track of which users can use which of the REST services that you created. So, before you start doing anything with the REST endpoint, you will want to create the needed users whether directly in your database or LDAP system. You don’t actually need to give these user ids to the administrators or users. You can have a user id, probably a service account that is used by an application that generates tokens, that are given to those people or applications. I have created the following user IDs in my database server in the corresponding roles for the rest of my examples.
· service_admin1 in the service_admin database role
· service_user1 in the service_user database role
A database administrator or security administrator of the database will then need to grant the minimum authorities to allow these users to perform their tasks. I did that as shown here to grant the needed permissions to roles and then add the users to their specific role.
Figure 1. Roles and Authorities Granted
CREATE ROLE service_admin;
CREATE ROLE service_user;
-- SERVICE_ADMIN - Min privileges to create, execute and describe services
-- - Also allows GRANT and REVOKE permissions to service users
-- - Allows token creation for this user
-- - Allows all tables in the GOSALES schema to be selected.
-- - Assumes REST_SERVICES schema already created
GRANT CONNECT ON DATABASE TO ROLE SERVICE_ADMIN;
GRANT SELECTIN ON SCHEMA SYSCAT TO ROLE SERVICE_ADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON DB2REST.RESTSERVICE TO ROLE SERVICE_ADMIN;
GRANT ALL ON SCHEMA REST_SERVICES TO ROLE SERVICE_ADMIN WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_CMD TO ROLE SERVICE_ADMIN;
GRANT EXECUTE ON FUNCTION SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID TO ROLE SERVICE_ADMIN;
-- Grant authorities on tables used in any service created by this user id
GRANT SELECTIN ON SCHEMA gosales TO ROLE SERVICE_ADMIN;
-- SERVICE_USER - Min privileges to execute services in the REST_SERVICES schema
-- - and describe any service
-- - Allows token creation for this user
GRANT CONNECT ON DATABASE TO ROLE SERVICE_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_CMD TO ROLE SERVICE_USER;
GRANT EXECUTE ON FUNCTION SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID TO ROLE SERVICE_USER;
GRANT ROLE service_admin TO USER service_admin1;
GRANT ROLE service_user TO USER service_user1;
Note that the SERVICE_ADMIN role is given access to some tables in the database, but the SERVICE_USER role is not. To create the service an administrator role needs to have access to the tables. In Figure 1, the administrator has authority to select any table in the GOSALES schema. It would not be able to create services that do anything else like update any tables. You would need to determine which tables or other objects the services need access, and then give the appropriate permissions to the role.
Once the service is created any user id allowed to execute it does not need any access to the tables used by the service. This is one way that exclusively using the Db2 REST endpoint can make your database more secure. Even if someone were to get the SERVICE_USER1 user id and password, and somehow got connected to the database, they would not have any access to any tables. They would only be able to execute the stored procedures to which they have been give access.
For this article I created three Python Jupyter Notebooks to show in detail what I am discussing in the following sections. These notebooks and the grants script can be found in the Db2 REST Security Article directory on my github repo. The notebooks are:
· Db2 RESTful Endpoint Get Token – Creates a token used by the other notebooks to authenticate to the REST Endpoint. Depending on the user id used, the token will have different levels of access. This is the only place database user ids are needed.
· Db2 RESTful Endpoint Create Service – Creates a service called getorderinfo that selects from a join of two database tables, authorizes the user role to execute the service, and has other useful examples including deleting, listing and describing services.
· Db2 RESTful Endpoint Execute – Example of executing the service created with examples of listing and describing the service.
To call the REST services that are created, users will use an access token instead of a user id and password. Creation of that token does require a database user id and password. So instead of letting all users create tokens, you may want to create an application or a small set of users who can issue tokens and give them to other applications or users. Many modern architectures use services and API calls using tokens instead of user id and password, so this will likely not be anything new to your developers. Generally good security practices will call for the tokens to expire so new tokens will need to be issued at some interval. Therefore, you may want to create a self-service application that lets applications to securely get the tokens they need. You can see more information about creating a token on the Db2 documentation Authenticating REST commands page. You can also view the Container docs as described above and click “AUTHENTICATION”.
When a token is created it must be done with a particular database user id and password. The users of that token will then have the privileges of that user. The authorities that the user id had at the time it generated the token are the authorities it will always have, even if you grant or revoke authorities to the underlying user or roles/groups that it is in. If you change a privilege you need to generate a new token for the user and start using that new token to get the new authorities.
The following examples show python code snippets setting up variables to be used to get a token – one for creating a token for administering services and one for creating a token for a user of services.
Example 1. Configure for service administrator token
API_Auth = "/v1/auth"
body = {
"dbParms": {
"dbHost": "192.168.0.3",
"dbName": "SAMPLE",
"dbPort": 50000,
"isSSLConnection": False,
"username": "service_admin1",
"password": "passw0rd"
},
"expiryTime": "10m"
}
Example 2. Configure for service user token
API_Auth = "/v1/auth"
body = {
"dbParms": {
"dbHost": "192.168.0.3",
"dbName": "SAMPLE",
"dbPort": 50000,
"isSSLConnection": False,
"username": "service_user1",
"password": "passw0rd"
},
"expiryTime": "10m"
}
Notice that in both of these examples the token expires in 10 minutes. You may want to make this longer or shorter depending on the user and how long they really need the token. You can see all my Python code for fetching the token in my Db2 RESTful Endpoint Get Token notebook on GitHub. This notebook ends with displaying the token generated. This poorly simulates an application that generates tokens for other applications, but illustrates the point of separating that task.
If you wish to use tokes to authenticate to your database without using the REST endpoint, then then you can actually use Db2 Token Authentication as described in the Knowledge Center.
Once a token has been generated by the service_admin1 user id, that token can be given to the person, persons or applications that create the services. Generally speaking, you will probably test your queries and services in a development environment, then giving the code for creating the service to your change control process for adding the services to production. That way only the change control process needs the database credentials or token for administering services. In addition to the basic privileges needed for creating services, the user id (in this case service_admin1) needs authorities on tables and other objects referenced in the queries in the services being created. As you can see in the GRANT statements in Figure 1 above, service_admin1 has the ability to select any tables in the GOSALES schema. In this case if this user tried to create a service to insert a row into a table, then the service would fail because it does not have INSERT authority on any tables. The user ids that can create services should be tightly controlled for production databases.
After generating the token, I copied and pasted the token into the notebook where I create the service. You can view the Db2 RESTful Endpoint Create Service notebook to see the details of creating the service. You will notice that by using the token the server creator was able to create the service without needing the database user id. In a production environment you would probably use a more sophisticated means of getting the token to the user/application creating the services. In Example 4 you can see the code snippet defining the service.
Example 4. Create a new Service called “getorderinfo”
API_makerest = "/v1/services"
body = {"isQuery": True,
"parameters": [
{
"datatype": "VARCHAR(150)",
"name": "@RETAILER"
},
{
"datatype": "DECIMAL(19,2)",
"name": "@PRICE"
}
],
"schema": "REST_SERVICES",
"serviceDescription": "Select order and order details information",
"serviceName": "getorderinfo",
"sqlStatement": "SELECT H.ORDER_NUMBER, RETAILER_NAME, \
ORDER_DATE, PRODUCT_NUMBER, QUANTITY \
FROM GOSALES.ORDER_HEADER H, GOSALES.ORDER_DETAILS D \
WHERE H.ORDER_NUMBER = D.ORDER_NUMBER \
AND RETAILER_NAME LIKE @RETAILER \
AND UNIT_SALE_PRICE > @PRICE\
LIMIT 2",
"version": "1.0"
}
Please notice the following about example 4:
· You can pass the service two parameters: RETAILER and PRICE allowing the calling application to change the results of the query based on those values.
· “schema” – defines the schema into which the stored procedure that this service creates will be placed.
· “serviceName” – This is where you name your service in this case “getorderinfo”.
· The back slash (\) is used for line continuation of the SQL statement. You need to remove any space characters following it or you will get errors. This really tripped me up until I figured that out.
At this point, the applications that would execute this service do not have authority to execute it. So, the next step in the process is to grant access to the service. Execute authority can be given to individual users, database roles or OS/LDAP groups. You can see the command documentation in the Container docs and then clicking SERVICES and then clicking “Grants permission to execute a REST service” menu item. You can also see instructions for Revoke just below it. Example 5 shows how to grant permission to execute our new service to a role. That gives everyone in that role the ability to execute the procedure.
Example 5. Grant Execute Permission to SERVICE_USER Role
API_grant = "/v1/services/grant/getorderinfo/1.0"
body = {
"roles": {
"withGrantOption": False,
"names": ["SERVICE_USER"]
}
}
Notice in Example 5 that the API_grant variable used in the later PUT operation contains the service name (getorderinfo) and version (1.0) defined in the body of the service created in Example 4. You can see the full set of role authorization commands I used in the Db2 RESTful Endpoint Create Service notebook. As you create additional services, you can grant execution of them to this role and all users of the role will be able to run them. Also, as you add additional users who need to run this set of services, you just grant the role to them.
While developing a service you may create one that isn’t quite right, and you want to recreate it. If you try to create it again you will get an error because the service already exists. So, you will need to delete it before recreating it. The Db2 RESTful Endpoint Create Service notebook contains the full code for deleting the service. Example 6 shows the key code snippet for deleting the getorderinfo service.
Example 6. Delete a Service
API_deleteService = "/v1/services"
Service = "/getorderinfo"
Version = "/1.0"
You can view more information on service deletion in the Container docs and then clicking SERVICES and then clicking “Deletes the service”. There is also an API to update a service and it is also described under SERVICES. The notebook also has some other commands the administrator may find useful like retrieving service details and listing services.
Applications that execute the new service do not need the SERVICE_USER1 password or even know the database user id exists. Instead, those applications should be given a token as described in the Issuing Tokens section above. In this case the user id for creating the token would be configured as shown in Example 2 that generates the token based on permissions given to the SERVICE_USER1 by way of the SERVICE_USER database role. As before, I copied the token from the notebook that generates the token and pasted it into my Db2 RESTful Endpoint Execute notebook. You will probably have a more sophisticated way of passing tokens to applications that need them. This notebook is a simple example of how any Python application can now use the new service.
Example 7. Execute the getorderinfo Service
API_runrest = "/v1/services/getorderinfo/1.0"
retailer = "%Sports%"
minprice = 10.2
body = {
"parameters": {
"@RETAILER": retailer,
"@PRICE": minprice
},
"sync": True
}
Note that the configuration of the call references the service to be called (getorderinfo) and the version (1.0) as defined in Example 4. In this simple example values are simply assigned to the input variables, but of course, you can have any logic in your application to generate these.
Developers are likely to need information about the service in order to use it, such as the input variables, their data types and what output to expect. They can get this information as shown in the “Retrieve Service Details” section of the notebook. This inquiry about the service requires the user to have SELECT access on the tables or other objects referenced in the service being described. This is probably fine for your development databases but is not likely something you would want to permit in a production database.
My examples are coded in Python, but please note that the API guide (Container docs) you can access after deploying the Db2 REST endpoint shows code for using the APIs in Python, JSON, cURL and JavaScript. This document also defines the response/error codes.
In addition to the security aspects of using a REST Endpoint, there are also other benefits. One is that most of your developers don’t need to know SQL. Only those who create the Db2 Services need to know it. Also, you don’t need any Db2 drivers in your applications or containers. Further, you never need to troubleshoot database connectivity from your applications because they don’t connect to it. Only the Db2 REST endpoint needs to connect to the database.
***
If you have ideas about how the REST Endpoint can improve security or make life easier please share them to my Facebook Page.