JHipster OAuth2 Oracle数据库未生成默认安全表问题问询

我在JHipster网关应用中选择OAuth2安全机制，希望在Oracle数据库中创建JHI_AUTHORITY、JHI_PERSISTENT_TOKEN、JHI_USER、JHI_USER_AUTHORITY这些核心安全表。使用JWT认证时，Liquibase变更日志会自动配置这些表，但切换到OAuth2认证后不会自动生成。尝试连接Okta或Keycloak，都无法让数据库创建这些默认安全表。

提供的配置文件 #

yo.src.json #

{
  "generator-jhipster": {
    "applicationType": "gateway",
    "authenticationType": "oauth2",
    "baseName": "r2Dbcwithgateway",
    "buildTool": "gradle",
    "cacheProvider": "hazelcast",
    "clientFramework": "angular",
    "clientTestFrameworks": [],
    "clientTheme": "none",
    "creationTimestamp": 1716961599093,
    "databaseType": "sql",
    "devDatabaseType": "oracle",
    "devServerPort": 4200,
    "enableGradleEnterprise": false,
    "enableHibernateCache": true,
    "enableSwaggerCodegen": false,
    "enableTranslation": false,
    "entities": [],
    "feignClient": null,
    "gradleEnterpriseHost": null,
    "jhipsterVersion": "8.4.0",
    "jwtSecretKey": "NWM5NjAyYTQ2NDNkZjkwNmJmM2ViODgwMDg4NDQwZDdiYTBiODMwODEzNjllYzRkNjQ5ODkzMTg3NjZiYmRjYjc5MjEzN2U2NDAxMTk5ZDQ0ZDAzMzFjZjEyZDY3NWFkNTdkNDcxMzY3OWNkMjE4ZmI3OWQyZDNiZjFmZmQ2YWU=",
    "messageBroker": false,
    "microfrontend": false,
    "microfrontends": [],
    "nativeLanguage": "en",
    "packageName": "com.mycompany.myapp",
    "prodDatabaseType": "oracle",
    "reactive": true,
    "searchEngine": false,
    "serverPort": 8080,
    "serverSideOptions": [],
    "serviceDiscoveryType": "consul",
    "syncUserWithIdp": null,
    "testFrameworks": [],
    "websocket": false,
    "withAdminUi": true
  }
}

Liquibase变更日志文件 #

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <!--
        JHipster core tables.
        The initial schema has the '00000000000001' id, so that it is over-written if we re-generate it.
    -->
    <changeSet id="00000000000001" author="jhipster">
    </changeSet>

    <changeSet author="jhipster" id="00000000000002" context="test">
        <createTable tableName="jhi_date_time_wrapper">
            <column autoIncrement="true" name="id" type="BIGINT">
                <constraints primaryKey="true" primaryKeyName="jhi_date_time_wrapperPK"/>
            </column>
            <column name="instant" type="timestamp"/>
            <column name="local_date_time" type="timestamp"/>
            <column name="offset_date_time" type="timestamp"/>
            <column name="zoned_date_time" type="timestamp"/>
            <column name="local_time" type="time"/>
            <column name="offset_time" type="time"/>
            <column name="local_date" type="date"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

解决步骤 #

1. 先搞懂JHipster的默认逻辑 #

JHipster选OAuth2模式时，默认认为用户数据由Okta/Keycloak这类外部身份提供商（IDP）管理，所以不会自动生成本地用户表。但如果要把IDP的用户同步到本地Oracle库，就得手动添加Liquibase变更集来建表。

2. 给Liquibase加核心安全表的变更代码 #

直接修改你的Liquibase变更日志，在00000000000001这个changeSet里添上创建表的语句，注意适配Oracle的数据类型：

<changeSet id="00000000000001" author="jhipster">
    <!-- JHI_AUTHORITY -->
    <createTable tableName="JHI_AUTHORITY">
        <column name="NAME" type="VARCHAR2(50 CHAR)">
            <constraints primaryKey="true" nullable="false"/>
        </column>
    </createTable>

    <!-- JHI_USER -->
    <createTable tableName="JHI_USER">
        <column name="ID" type="NUMBER(19,0)" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="LOGIN" type="VARCHAR2(50 CHAR)">
            <constraints nullable="false" unique="true"/>
        </column>
        <column name="PASSWORD_HASH" type="VARCHAR2(60 CHAR)"/>
        <column name="FIRST_NAME" type="VARCHAR2(50 CHAR)"/>
        <column name="LAST_NAME" type="VARCHAR2(50 CHAR)"/>
        <column name="EMAIL" type="VARCHAR2(100 CHAR)">
            <constraints unique="true"/>
        </column>
        <column name="IMAGE_URL" type="VARCHAR2(256 CHAR)"/>
        <column name="ACTIVATED" type="NUMBER(1,0)" defaultValueBoolean="true">
            <constraints nullable="false"/>
        </column>
        <column name="KEY" type="VARCHAR2(20 CHAR)"/>
        <column name="CREATED_BY" type="VARCHAR2(50 CHAR)">
            <constraints nullable="false"/>
        </column>
        <column name="CREATED_DATE" type="TIMESTAMP(6)">
            <constraints nullable="false"/>
        </column>
        <column name="LAST_MODIFIED_BY" type="VARCHAR2(50 CHAR)"/>
        <column name="LAST_MODIFIED_DATE" type="TIMESTAMP(6)"/>
    </createTable>

    <!-- JHI_USER_AUTHORITY -->
    <createTable tableName="JHI_USER_AUTHORITY">
        <column name="USER_ID" type="NUMBER(19,0)">
            <constraints nullable="false"/>
        </column>
        <column name="AUTHORITY_NAME" type="VARCHAR2(50 CHAR)">
            <constraints nullable="false"/>
        </column>
        <constraints>
            <primaryKey columnNames="USER_ID, AUTHORITY_NAME" constraintName="JHI_USER_AUTHORITY_PK"/>
            <foreignKey constraintName="FK_USER_AUTHORITY_USER" referencedTableName="JHI_USER" referencedColumnNames="ID">
                <column name="USER_ID"/>
            </foreignKey>
            <foreignKey constraintName="FK_USER_AUTHORITY_AUTHORITY" referencedTableName="JHI_AUTHORITY" referencedColumnNames="NAME">
                <column name="AUTHORITY_NAME"/>
            </foreignKey>
        </constraints>
    </createTable>

    <!-- JHI_PERSISTENT_TOKEN -->
    <createTable tableName="JHI_PERSISTENT_TOKEN">
        <column name="SERIES" type="VARCHAR2(255 CHAR)">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="USER_ID" type="NUMBER(19,0)">
            <constraints nullable="false"/>
        </column>
        <column name="TOKEN_VALUE" type="VARCHAR2(255 CHAR)">
            <constraints nullable="false"/>
        </column>
        <column name="TOKEN_DATE" type="DATE"/>
        <column name="IP_ADDRESS" type="VARCHAR2(39 CHAR)"/>
        <column name="USER_AGENT" type="VARCHAR2(255 CHAR)"/>
        <constraints>
            <foreignKey constraintName="FK_PERSISTENT_TOKEN_USER" referencedTableName="JHI_USER" referencedColumnNames="ID">
                <column name="USER_ID"/>
            </foreignKey>
        </constraints>
    </createTable>

    <!-- 插入默认权限数据 -->
    <insert tableName="JHI_AUTHORITY">
        <column name="NAME" value="ROLE_USER"/>
    </insert>
    <insert tableName="JHI_AUTHORITY">
        <column name="NAME" value="ROLE_ADMIN"/>
    </insert>
</changeSet>

3. 开启动态用户同步 #

把yo.src.json里的syncUserWithIdp改成true，这样JHipster会自动把IDP里的用户同步到本地库：

"syncUserWithIdp": true

改完后重新跑jhipster命令生成应用，确保同步逻辑生效。

4. 执行Liquibase变更 #

跑Gradle命令让Liquibase执行建表操作：

./gradlew liquibaseUpdate

启动应用后去Oracle里看看，这些表应该已经建好，默认的ROLE_USER和ROLE_ADMIN权限也插进去了。

5. 适配Reactive应用的配置 #

因为你的应用是Reactive（reactive": true），得确认Spring Security OAuth2的配置没问题，本地用户服务已经启用。检查SecurityConfiguration类，确保配置了UserDetailsService来读取本地数据库的用户信息。

内容的提问来源于stack exchange，提问作者Ishwar Iyer