oracle - flyway init oracle权限不足

Question

我正在从 Maven 构建执行 Flyway。我有使用 Flyway 的经验，但几年没有使用 Oracle。当它尝试运行 init 目标时，我收到此错误消息:

    [INFO] --- flyway-maven-plugin:3.0:clean (prepare-schema-using-flyway) @ my-project ---
    [INFO] Database: jdbc:oracle:thin:@my-oracle-host:1521:dev (Oracle 11.2)
    [INFO] Cleaned schema "MY_SCHEMA" (execution time 00:03.483s)
    [INFO]
    [INFO] --- flyway-maven-plugin:3.0:init (prepare-schema-using-flyway) @ my-project ---
    [INFO] Database: jdbc:oracle:thin:@my-oracle-host:1521:dev (Oracle 11.2)
    [INFO] Creating Metadata table: "MY_SCHEMA"."schema_version"
    [INFO] ------------------------------------------------------------------------
    [INFO] BUILD FAILURE
    [INFO] ------------------------------------------------------------------------
    [INFO] Total time: 16.991s
    [INFO] Finished at: Thu Aug 21 09:44:09 CDT 2014
    [INFO] Final Memory: 20M/160M
    [INFO] ------------------------------------------------------------------------
    [ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.0:init (prepare-schema-using-flyway) on project my-project: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at line 17: CREATE TABLE "MY_SCHEMA"."schema_version" (
    [ERROR] "version_rank" INT NOT NULL,
    [ERROR] "installed_rank" INT NOT NULL,
    [ERROR] "version" VARCHAR2(50) NOT NULL,
    [ERROR] "description" VARCHAR2(200) NOT NULL,
    [ERROR] "type" VARCHAR2(20) NOT NULL,
    [ERROR] "script" VARCHAR2(1000) NOT NULL,
    [ERROR] "checksum" INT,
    [ERROR] "installed_by" VARCHAR2(100) NOT NULL,
    [ERROR] "installed_on" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    [ERROR] "execution_time" INT NOT NULL,
    [ERROR] "success" NUMBER(1) NOT NULL
    [ERROR] ): ORA-01031: insufficient privileges
    [ERROR] -> [Help 1]
    [ERROR]
    [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
    [ERROR] Re-run Maven using the -X switch to enable full debug logging.

当我尝试通过 JDBC 连接(我使用 IntelliJ 的 DBHelper)执行相同的 SQL 时，我收到了相同的错误消息。当我删除所有双引号时，它成功执行。

我无法想象 Flyway 3.0 与 Oracle 不兼容，但我怀疑可能有一个服务器设置指定了在创建过程中围绕数据库对象的字符。例如，"my_table"."my_id"或 [my_table].[my_id]

或者，有没有办法关闭双引号作为 Flyway 配置的一部分？

Answer 1

老实说，我确实相信“引用”与问题无关，即使它真的很烦人并且总是会导致在没有正确使用函数的情况下查询 Oracle 字典 View 的问题 lower .这里真正的关键是错误信息:ORA-01031: insufficient privileges .
它通常意味着:

通过JDBC连接的用户与 MY_SCHEMA 不一样.如果是这种情况，用户必须拥有权限 CREATE ANY TABLE或具有此类特权的角色(如 DBA )。

通过JDBC连接的用户是 MY_SCHEMA .如果是这种情况，您的用户必须拥有权限 CREATE TABLE ，或包含此类系统权限的角色(如 RESOURCE)

根据 flyway的GitHub项目，通过确保用户已被授予系统权限 CREATE VIEW 报告并解决了一个非常相似的问题。

Ensure that the character set of the Oracle database supports Unicode.In addition, for the initial creation of the database, the Oracle usermust have the CREATE VIEW system privilege. Without this privilege,the following error might be generated during installation: Schemacreation failed: ORA-01031: insufficient privileges.

我在您的日志消息中还看到您正在运行创建之前清理架构。如果此类操作意味着删除 MY_SCHEMA用户，您应该注意此类创建包含必要的系统权限。
还有 flyway如果权限可能是一个问题，建议在外部处理架构创建。

It's better for users to manually deal with schema creation if Flywayisn't able to do it correctly due to permissions. I suppose it's atrade off between convivence and security - applying permissionsbecause you might one day need them feels wrong. In this case, you canapply the GRANTs you need.

如果你有足够的权限访问数据库，我的第一个建议是检查权限是否有问题，是使用具有 DBA 角色的 Oracle 用户执行操作，因此你将能够验证它是否有效。
对于您的消息，我猜您处于开发环境中，因此应该不成问题。