oracle - liquibase 用户的 Oracle 角色

Question

我正在努力在连接到 Oracle DB 的 Spring Boot 应用程序中添加 liquibase。我对在我的应用程序中使用模式 ID 感到不舒服。所以我想创建另一个用户 ID，用于从我的应用程序连接到数据库。由于我使用的是 liquibase，因此这个新用户 ID 需要在该模式中的所有表上创建、删除、选择、插入、更新、删除。

在 Mysql 中，我可以运行以下命令：

GRANT CREATE，DROP，SELECT，INSERT，UPDATE，DELETE ON SCHEMA_NAME.* TO 'liquidbase_local_usr'@'localhost';

Oracle中有类似的查询吗？

标签： oracleliquibase

Answer 1

我使用基于 Liquibase 的 Datical 产品做到了这一点。

首先，我创建了一个名为 DATICAL_PACKAGER_ROLE 的角色并将其分配给用户 $DaticalUser：

CREATE ROLE DATICAL_PACKAGER_ROLE NOT IDENTIFIED;
 
-- When using the tracefileLocation=REMOTE, you will need to setup the following permissions.
-- Note: These permissions are only required on schemas running the Convert SQL Scripts command.  This is only needed on the packaging / reference database.
 
GRANT EXECUTE ANY PROCEDURE TO DATICAL_PACKAGER_ROLE;
GRANT ALTER SESSION TO DATICAL_PACKAGER_ROLE;
GRANT CREATE ANY DIRECTORY TO DATICAL_PACKAGER_ROLE;
GRANT DROP ANY DIRECTORY TO DATICAL_PACKAGER_ROLE;
 
-- The following permissions are needed for backing up and restoring the database.  This is only needed on the packaging / reference database.
GRANT EXP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;
GRANT IMP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;
 
 
-- The following must be run as sysdba
-- SQL > conn / as sysdba
GRANT EXECUTE on SYS.UTL_FILE TO DATICAL_PACKAGER_ROLE;
 
-- Grant the new role to the Datical User
GRANT DATICAL_PACKAGER_ROLE to $DaticalUser;

然后我将角色授予我想要的架构：

GRANT DATICAL_PACKAGER_ROLE to <yourschema>;