Problem with exporting an OAuth app from one tenant and import into another

[ThaminduDilshan]

Describe the issue:
Application management APIs provide two REST APIs to export an application into a xml document [1] and create an application by importing an exported XML file [2]. Currently for the OAuth apps, consumer key related details are stored in the "IDN_OAUTH_CONSUMER_APPS" table where it has a unique key constraint for the consumer_key column to make it unique across the server. Additionally some other OAuth related data are stored in different tables which references the "IDN_OAUTH_CONSUMER_APPS" table with the consumer_key. I.e. "IDN_OIDC_PROPERTY" table.

If we export an application from one tenant using the export API and import it into another tenant using the import API, ideally that import should fail as we cannot have two apps with the same OAuth client ID (consumer key) in the server. However the server allows to create the second application too and will create the related database entries. However due to the unique key constraint in the "IDN_OAUTH_CONSUMER_APPS" table, it won't add another entry for the second app, rather partially modifies the previous entry created from the first app. Due to this, the database entries related to the first app gets corrupted while having some malformed entries for the second app.

Apparently this doesn't break any functionality and can login to both the apps without any cross domain access, however the DB entries are corrupted. Moreover the migration client for the "client ID tenant unification" will fail to migrate any database that has this issue. That's because the mentioned issue lead to have two set of entries in the "IDN_OIDC_PROPERTY" table belonging to two tenants, which references to the same entry in the "IDN_OAUTH_CONSUMER_APPS" table. When we try to add a foreign key to the "IDN_OIDC_PROPERTY" table that references both the <client_id, tenant_id> in "IDN_OAUTH_CONSUMER_APPS" table, it will fail as there are no entries for both the apps.

[1] https://is.docs.wso2.com/en/latest/apis/application-rest-api/#/Applications/exportApplication
[2] https://is.docs.wso2.com/en/latest/apis/application-rest-api/#/Applications/importApplication
[3] #16370

Expected behavior:
The product should not allow to import the second application with the same client ID.

Environment information (Please complete the following information; remove any unnecessary fields) :

• Product Version: [e.g., IS 5.10.0, IS 5.9.0]
• OS: [e.g., Windows, Linux, Mac]
• Database: [e.g., MySQL, H2]
• Userstore: [e.g., LDAP, JDBC]

---

Optional Fields

Related issues:

Suggested labels:

[ThaminduDilshan]

With the improvement done in #16370, the same client ID can be exist in different tenants. Therefore this issue will not occur in future