db2Dean a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !
|
Utah Events None currently Scheduled
Nevada Events None currently Scheduled
|
See my Github Repo:
|
|
|
Online Events
Other Events
|
Welcome to db2Dean’s web site. I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with Cloud Pak for Data and DB2 on Linux, UNIX and Windows (LUW) technical questions and issues. As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products. If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions. My e-mail address is dean@db2Dean.com
I am located in Utah and primarily serve CPD and DB2/LUW customers in the Western US, but I can forward requests to my peers in other technology and geographic areas as well. My team also covers Big Data and Informix and products. There are questions that I get on a regular basis, and I will write articles relating to them here. I hope that you find them useful. I also welcome suggestions for future content. Click here for more information about me.
|
|
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 Initialize Database Access
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. Database
Prep
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.
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. Issuing
Tokens
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. Creating and Authorizing Services
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. Give
Access to the New Service
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. Delete a Service
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. Use
the Service
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. Other
Benefits
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.
|
Db2Dean’s Other Interests
|