Debugging and monitoring all database operations with log files (MySQL or MariaDB)

 Recently I got involved with a new software project of a customer based in Melbourne, where they have a web application used to support all the operations in the business. The web application uses a MariaDB database in the backend to store all the data. There was a bug reported on the web application in production environment where it throws an exception when performing an operation related to inserting some data into the database. There were many tables affected and I was not having much time to be familiar with the database design. With enabling the query logs on MariaDB, I was able to quickly identify the exact queries/commands getting executed against the database and that helped me to identify where the bug was. Here I am explaining all the steps involved.

First we need to modify the server configuration to enable the logs. For MariaDB, we need to modify the /etc/mysql/mariadb.conf.d/50-server.cnf file. You can locate the corresponding file for Windows or Mac installations as this is the file for Linux based installations. 

 Then you need to add the following configuration under [mysqld] instance in the configuration file.

general_log_file = /var/log/mysql/mysql.log general_log = 1 log_output = FILE

Note that you can change the log file as you wish. Then we need to restart the server to get the changes affected.

sudo systemctl restart mariadb

Above commands is for mariadb on Ubuntu. You can find the corresponding one for MySQL or installations on other platforms like Mac or Windows.

If the log file is already having previous logs, you may take a backup of it and add an entry to the file for easy identification of the starting point. Note that this overwrites the file and you will lose previous logs. Otherwise you can just append it using >> instead of >.

echo "--- TEST BEGIN ---" > /var/log/mysql/mysql.log

Then perform the operation which generates the content of the log file with all the queries and commands getting executed against the database. Finally, you can append a line to mark the end of testing.

echo "--- TEST END ---" >> /var/log/mysql/mysql.log

This way you can easily extract only the logs related to the task you performed.

Authentication and Permission Requirements in WSO2 OAuth 2.0 Token Introspection API

WSO2 Identity Server provides an API for OAuth 2.0 Token Introspection (see spec [1] for more information) for authorized parties to query the metadata of the access tokens it has issued. For knowing more information on the API requests, you can refer the official documentation in [2]. The main focus of this blog post is to discuss the authentication and required permissions for invoking this API by external parties.

Required Permission for Invoking the API

By default, the OAuth Token Introspection API of WSO2 Identity Server is protected where it requires authentication and also users should be authorized (needs a particular permission) to invoke the API. This is due to following reasons as per the specification [1].

   To prevent token scanning attacks, the endpoint MUST also require
   some form of authorization to access this endpoint, such as client
   authentication as described in OAuth 2.0 [RFC6749] or a separate
   OAuth 2.0 access token such as the bearer token

   If left unprotected and un-throttled, the introspection endpoint
   could present a means for an attacker to poll a series of possible
   token values, fishing for a valid token.  To prevent this, the
   authorization server MUST require authentication of protected
   resources that need to access the introspection endpoint and SHOULD
   require protected resources to be specifically authorized to call the
   introspection endpoint.

So, this required level of permission is defined in <IS_HOME>/repository/conf/identity/identity.xml file. It has following by default.

<ResourceAccessControl> .....      <Resource context="(.*)/oauth2/introspect(.*)" http-method="all" secured="true">          <Permissions>/permission/admin/manage/identity/applicationmgt/view</Permissions>      </Resource>

So, for invoking this API, the user/client should have the above permission. What you can do is, create a role in Identity Server granting this particular permission (shown in image below) and assign the role to the users who needs to invoke this API.

By modifying the particular <Permissions> element value in the identity.xml file for this, you can lower or raise the required level of permission.

Removing Authentication from Token Introspection API

In case you need to remove the authentication enforcement of this endpoint, what you can do is, modify the identity.xml file and delete/comment the following section.

<Resource context="(.*)/oauth2/introspect(.*)" http-method="all" secured="true">          <Permissions>/permission/admin/manage/identity/applicationmgt/view</Permissions>      </Resource>

This will remove the authentication enforcement where any user can invoke the Token Introspection API without providing user credentials or OAuth bearer token.

Sample request is below.

curl -v -k -H 'Content-Type: application/x-www-form-urlencoded' -X POST --data 'token=XXXXXXXXXXX' https://localhost:9443/oauth2/introspect

Invoking the API with Basic Authentication

If you are testing this with curl, you can include the user’s credential in -u parameter as shown below and invoke the API.

curl -v -k -u <username>:<password> -H 'Content-Type: application/x-www-form-urlencoded' -X POST --data 'token=XXXXXXXX' https://localhost:9443/oauth2/introspect

Also, you can send the user credentials in the Authorization header as well. You need to prepare a string like <username>:<password> and then base64 encode the string and put in the Authorization header value after Basic.

curl -v -k -H 'Authorization: Basic XXXXX' -H 'Content-Type: application/x-www-form-urlencoded' -X POST --data 'token=xxxxxxxxxxxxxxxxx' https://localhost:9443/oauth2/introspect

Note: In OAuth APIs, for token requests, you use ClientID:ClientSecret base64 encoded string in the Authorization header for basic authentication. However for Token Introspection API, that is not supported. For basic authentication, you must use user credentials.

Invoking the API with OAuth Bearer Token for Authentication

For invoking the Token Introspection API, instead of using user credentials (basic authentication), you can use OAuth access tokens. For that, first you need to obtain an OAuth access token from Identity Server and include it in the Authorization header value after Bearer. The token sent in the body is the access token to be validated.

curl -v -k -H 'Authorization: Bearer XXXXX' -H 'Content-Type: application/x-www-form-urlencoded' -X POST --data 'token=xxxxxxxxxxxxxxxxxxx' https://localhost:9443/oauth2/introspect

IMPORTANT: When you are using OAuth access tokens for invoking the API, if the token is obtained through authorization_code, implicit or password grant types, then the user account associated with the access token should have the required permission (defined in identity.xml file). If the access token is obtained through client_credentials grant type, then the user who registered the OAuth application should have the particular required permission.

Error Responses

• 403: Forbidden

If we use OAuth bearer token for authenticating to the API, if the token’s associated user does not have required permission, then server will give 403: Forbidden response. Also we’ll get the token’s associated user’s username in WWW-Authenticate response header. The response is same even if we use basic authentication when the user does not have the required permission.

• 401: Unauthorized

When basic authentication is used to authenticate to the Token Introspection API, if the user’s credentials are incorrect, the server will respond with 401 Unauthorized HTTP status code. In the WWW-Authenticate response header, it will return whatever the username we sent in the request (regardless it’s a valid username).

References

[1] https://tools.ietf.org/html/rfc7662
[2] https://docs.wso2.com/display/IS540/Invoke+the+OAuth+Introspection+Endpoint


Tharindu Edirisinghe
Platform Security Team
WSO2

[SOLVED] "Illegal option: -ext" error in keytool command when generating keypair with setting subject alternative names

We can use the keytool command for generating key pairs and keystores which would be needed configuring SSL for websites, encryption scenarios, etc.

Usually when generating the keypair, we would define the CN (Common Name) field of the digital certificate to match the domain name of the website, so we don’t run into any hostname verification issues later.

However, there are scenarios where we might need to use the same digital certificate for multiple websites (different domains). In such case, we can define all the domains in the Subject Alternative Name [1] extension of the certificate.

An example is if you check https://ikman.lk website’s digital certificate, you will see that the CN is not matching the above domain name. The CN is a subdomain of cloudflare because ikman.lk is using cloudflare as a proxy.

Therefore, to match the hostname, the domain is added as DNS names under the subject alternative name extension of the certificate.

Now, when we generate a keypair using keytool command, we can define the subject alternative names using the -ext parameter. For more information on keytool commands, refer [2].

Here is a sample keytool command where I generate a keypair (wrapped in a JKS file) giving a DNS Name “xyz.com” as a subject alternative name.

keytool -genkeypair -keystore keystore.jks -dname "CN=test.com, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown" -keypass keypwd -storepass storepass -keyalg RSA -alias testcert -ext SAN=dns:xyz.com

However, when I run the command, I would get “Illegal option:  -ext” error.

If I run the “keytool -help” command, it would list the supported parameters by keytool command. However, under -genkeypair flow, it does not list the -ext as a valid parameter.

The reason is, in a linux system, the keytool command would reside in /usr/bin/keytool which is executed if the command starts with “keytool”.  This executable does not support the -ext parameter which causes the issue.

Apart from that, keytool executable is located with the JDK/JRE installation as well.

So, you have to use the keytool executable in either JDK or JRE for executing the keytool command for setting the subject alternative name.

If we browse the generated keystore from a tool like Keytool Explorer we can see that the subject alternative name is correctly set.

We can use the keytool -list command to list the generated entry and that also will confirm that the subject alternative name is correctly set in the certificate.

References

[1] https://en.wikipedia.org/wiki/Subject_Alternative_Name

[2] https://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html

Tharindu Edirisinghe

Platform Security Team

WSO2

(LinkedIn | GitHub | Twitter | Instagram)

Using WSO2 Identity Server as a SAML Identity Provider for Facebook Workplace

WSO2 Identity Server can act as a SAML Identity Provider for any SAML based relying party application. In this article, I’ll be providing all necessary steps for configuring Facebook Workplace to use WSO2 Identity Server for SAML based single sign on. After setting this up, the users in a Facebook Workplace can successfully login to their workplace through WSO2 Identity Server over SAML 2.0 protocol.

Creating Facebook Workplace

If you still do not have a Facebook Workplace, you can visit https://www.facebook.com/workplace and click on ‘Join Now or Log in’ button.

The, you can provide your business email address and create the Facebook Workplace for your organization.

Then you will receive an email to the business email you gave in above step with a confirmation code. You need to enter that correctly to proceed further.

Then you need to fill your personal details and business organization details to continue.

The, you can add your co-workers to the workplace by providing their email addresses. I’m going to skip this step at this moment.

Now we have successfully setup the Facebook Workplace. The account that we are using now is the system administrator account.

You will receive your own sub-domain like https://COMPANY_SUBDOMAIN.facebook.com/work/admin/ as the Facebook Workplace.

Configuring SAML Authentication in Facebook Workplace

Next step is to configure the SAML authentication for Facebook Workplace. Here, FB Workplace acts as a SAML relying party.

Click on the ‘Dashboard’ from the top menu and then click on ‘Authentication’ link from the left menu panel. Then, click on ‘Get Started’.

The following image has the sample configuration that I am using.

For “Coworker Authentication” field, select “SSO” option from the dropdown.

For “SAML URL”, you need to enter the SAML SSO endpoint of WSO2 Identity Server. It’s https://<server>:<port>/samlsso. Here, if you run the Identity Server locally, still you cannot use ‘localhost’ as Facebook does not allow that. Therefore I am using my localhost IP Address 127.0.0.1 here where my URL is https://127.0.0.1:9443/samlsso .

Then for “SAML Issuer URI”, you need to enter the Identity Provider Entity ID value. By default, Identity Server’s Resident IDP’s IDP Entity ID value is ‘localhost’. So I’m going to use the same value for SAML Issuer URI. You have freedom to change this accordng to your requirement.

Next field is “SAML Certificate”. Here, we need to give the public certificate of WSO2 Identity Server.

We can obtain this by going into the <IS_HOME>/repository/resources/security directory and executing the following keytool command against the keystore file which is used for storing the certificates.

keytool -exportcert -alias <alias of certificate> -keypass <private key password> -keystore <keystore file> -storepass <keystore password> -rfc -file <name of output file> Example command for exporting default certificate of WSO2 Identity Server keytool -exportcert -alias wso2carbon -keypass wso2carbon -keystore wso2carbon.jks -storepass wso2carbon -rfc -file ispublic_crt.pem

Then in the file it outputs, it will have the public certificate. You need to copy all the text in it and paste in “SAML Certificate” field in FB Workplace.

Then in the “SAML Configuration” section, it lists the following details.

Audience URL https://www.facebook.com/company/XXXXXXXXXXXXXXXX Recipient URL https://work-XXXXX.facebook.com/work/saml.php ACS (Assertion Consumer Service) URL https://work-XXXXX.facebook.com/work/saml.php

Above URLs are useful when we create the service provider configuration in WSO2 Identity Server. We cannot “Test SSO” or “Save” the configuration in Facebook Workplace until we configure WSO2 Identity Server properly to authenticate the requests coming from FB Workplace.

Configuring WSO2 Identity Server

Next step is to add Facebook Workplace as a Service Provider (relying party) in Identity Server. For that, in the Management Console of Identity Server, go to “Service Providers” in the “Main” menu and click on “Add”. As the name of the Service Provider, you can give any name. Then register the service provider.

Here, Facebook Workplace which is the relying party application, talks to Identity Server via SAML protocol. So, next step is to add the SAML related configuration in the service provider configuration. For that, Expand the “Inbound Authentication Configuration” of the service provider configuration and then under “SAML 2 Web SSO Configuration”, click on “Configure” button.

Then we need to add SAML related configuration as shown in the image below.

For the “Issuer” field, we need to put the “Audience URL” displayed in FB Workplace’s SAML Configuration section. It would be something like https://www.facebook.com/company/XXXXXX.

For the “Assertion Consumer URLs”, we need to add the “ACS URL” shown in FB Workplace’s SAML Configuration section. It wold be something like https://work-xxx.facebook.com/work/saml.php.

I am enabling “Response Signing” and then Identity Server will sign the SAML response given to FB Workplace by the private key of Identity Server. Then FB Workplace can verify that from the public certificate of Identity Server which we already have configured.

By enabling the “Attribute Profile”, I can include user’s profile attributes in SAML response.

I am enabling audience restriction and recipient validation giving the “Audience URL” and “Recipient URL” of Facebook Workplace’s SAML configuration.

After adding the configuration, save it and then click on the “Update” button of the Service Provider configuration as shown below.

In addition to the above configuration, we need to set the”Claim Configuration” as well in the service provider configuration in Identity Server. There, in the “Subject Claim URI”, we need to add the Email Address claim, which is http://wso2.org/claims/emailaddress. Then, when Identity Server gives the SAML response to FB Workplace, in the SAML assertion, the subject will have the user’s email address value. Facebook Workplace requires the Identity Provider to send the authenticated user’s email address as the subject of the SAML Assertion.

Creating a User in Identity Server for Testing the Authentication Flow

For testing the authentication flow, let’s create a new user in Identity Server. You can add a new user from the Management Console.

Give the username and password for the new user.

Then, edit the newly created user’s profile.

In the profile, filling the email address attribute is important. When Identity Server generates the SAML Response, in the SAML Assertion’s subject, this email address will be added when we login from this user account. Also, the same email address must be already added as a user in Facebook Workplace.

Testing SAML Configuration in Facebook Workplace

Now we have configured Identity Server as a SAML Identity Provider for Facebook Workplace.

For testing if the configuration in Identity Server and Facebook Workplace are correct, click on the “Test SSO” button in FB Workplace’s authentication configuration.

Then it will open a new browser window and show the SSO login page of WSO2 Identity Server. Here we can enter the newly created user’s credentials.

Then, Identity Server will send the SAML response to FB Workplace and you will see that user is successfully authenticated to FB Workplace as well.

Now you can save the Facebook Workplace SAML authentication configuration as the flow is working.

After that, you can try the end to end flow by visiting the FB Workplace URL. (eg: https://COMPANY_SUBDOMAIN.facebook.com). Now you can see that it will show the optoin “Log in with SSO” because we have now enabled SSO for FB Workplace using WSO2 Identity Server. Once you click this button, it will show the Identity Server login page where you can login to Identity Server from the user account created there and then finally you will be automatically logged into FB Workplace too.

Troubleshooting

If any make any mistakes when configuring this scenario, when testing the SSO login, you will most probably see the following error message in FB Workplace. There is no logs or some console in FB Workplace yet for identifying the exact issue for getting this to work.

So, you can use a browser plugin like SSO Tracer to trace the SAML request going from FB Workplace to Identity Server.

<samlp:AuthnRequest xmlns:samlp="urn:oasis:names:tc:SAML:2.0:protocol"                    xmlns:saml="urn:oasis:names:tc:SAML:2.0:assertion"                    ID="ONELOGIN_f11ce7039b3ce9fcb6e3a25d2803c09a5a399271"                    Version="2.0"                    IssueInstant="2017-11-08T11:51:14Z"                    Destination="https://127.0.0.1:9443/samlsso"                    ProtocolBinding="urn:oasis:names:tc:SAML:2.0:bindings:HTTP-POST"                    AssertionConsumerServiceURL="https://work-xxxxx.facebook.com/work/saml.php"                    >     <saml:Issuer>https://www.facebook.com/company/XXXXXXXX</saml:Issuer>     <samlp:NameIDPolicy Format="urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress"                        AllowCreate="true"                        /> </samlp:AuthnRequest>

There’s nothing special in the SAML Request. Only thing is we can see the SAML Issuer value here. In Identity Server side, inside the Service Provider configuration’s SAML configuration, the SAML Issuer must have the same value as this one.

Then you can get the SAML Response from the tracer which is sent by Identity Server to FB Workplace. Here’s an example. The important fields are highlighted.

<saml2p:Response Destination="https://xxxxxx.facebook.com/work/saml.php"                 ID="_23428a8650d533cb06b041e8d97ef5ac"                 InResponseTo="ONELOGIN_f11ce7039b3ce9fcb6e3a25d2803c09a5a399271"                 IssueInstant="2017-11-08T11:51:15.524Z"                 Version="2.0"                 xmlns:saml2p="urn:oasis:names:tc:SAML:2.0:protocol"                 >     <saml2:Issuer Format="urn:oasis:names:tc:SAML:2.0:nameid-format:entity"                  xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion"                  >localhost</saml2:Issuer>        <saml2:Subject>            <saml2:NameID Format="urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress">[email protected]</saml2:NameID>            <saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer">                <saml2:SubjectConfirmationData InResponseTo="ONELOGIN_f11ce7039b3ce9fcb6e3a25d2803c09a5a399271"                                               NotOnOrAfter="2017-11-08T11:56:15.524Z"                                               Recipient="https://work-xxx.facebook.com/work/saml.php"                                               />            </saml2:SubjectConfirmation>            <saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer">                <saml2:SubjectConfirmationData InResponseTo="ONELOGIN_f11ce7039b3ce9fcb6e3a25d2803c09a5a399271"                                               NotOnOrAfter="2017-11-08T11:56:15.524Z"                                               Recipient="https://work-xxx.facebook.com/work/saml.php"                                               />            </saml2:SubjectConfirmation>        </saml2:Subject>            <saml2:AudienceRestriction>                <saml2:Audience>https://www.facebook.com/company/XXX</saml2:Audience>                <saml2:Audience>https://www.facebook.com/company/XXX</saml2:Audience>            </saml2:AudienceRestriction>        </saml2:AuthnStatement>     </saml2:Assertion> </saml2p:Response>

In the Destination parameter, it should have the ACS URL of FB Workplace.

As the Issuer value, it has the Identity Server’s Resident IDP’s Entity ID Value. This should be matching to the value you have put in “SAML Issuer URI” field of SAML configuration in FB Workplace.

The Subject’s NameID format should be urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress and the value should be the email address of the authenticated user. This email address should already be added to FB Workplace.

The audience and recipient values should be the same URLs as in FB Workplace’s SAML Configuration.

Extra steps to be done in tenant flow

In your Identity Server deployment, you might have separate tenants for different organizations apart from the super tenant. In such case, the required configuration changes a little bit.

When comes to Identity Server’s service provider configuration, there is no change. Only thing is you need to create the service provider in the particular tenant. Also the user account/s in Identity Server should be created inside that tenant. Then, in the user’s profile, email address should be defined where the email address is already added to the Facebook Workplace.

The SAML Response given by Identity Server in the tenant flow will be signed by the private key of the particular tenant. Therefore, we need to obtain the public certificate of that tenant’s keystore and add it to Facebook Workplace as the SAML Certificate. For that, login to the management console of Identity Server as the tenant admin. Then, go to Main -> Manage -> Keystores -> List from the left menu. There you can see the tenant’s keystore. From that, click on the Public Key link to download the public certificate of the tenant.

After downloading the public certificate, you need to convert it to PEM format. For that, you can use the following command where you have to specify the downloaded file and a name for the exported PEM file.

openssl x509 -inform der -in <downloaded file> -out <outputfile.pem>

After above step, you can open the output PEM file using a text editor and then copy the public certificate value and paste in SAML Certificate field in FB Workplace.

In addition to that, the “SAML URL” field in FB Workplace should have the SAML SSO endpoint of Identity Server as following where we add the tenantDomain as a query parameter. The value of this parameter is the particular tenant’s domain name.

https://<IS_HOST>:<IS_PORT>/samlsso?tenantDomain=XXXXX

Now you can try out this flow and when the SSO login page is prompted by Identity Server, you need to enter the user credentials of a user account in the particular tenant. In terms of the SAML response, only difference is the response is signed with the private key of the particular tenant. In the subject of the SAML assertion, it will include the email address of the user, which is similar even in the super tenant flow.

Tharindu Edirisinghe

Platform Security Team

WSO2