已索引

问题描述

脱敏平台创建了一个用户 datamask 专门用来跑脱敏任务，该用户拥有 DBA 角色。
脱敏系统的思路是从生产备库获取 DDL 语句后，在目标库建表等，然后执行 insert 语句。
但使用 datamask 或者 SYS 为业务用户 appuser 创建物化视图时报权限不足的错误。
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
ORA-01031: insufficient privileges.

将 create materialized 权限分配给脱敏用户或者业务用户均可，但无论采用哪种方式，都需要给业务用户显式分配 create table 权限。

方法一：将 create materialized 分配给脱敏用户

创建业务用户：
SQL> create user appuser identified by appuser;
SQL> grant connect,resource,create view to appuser;
SQL> connect appuser/appuser
SQL> create table demot as select * from user_objects;

创建脱敏系统用户：
SQL> connect / as sysdba
SQL> create user datamask identified by datamask;
SQL> grant dba to datamask;

尝试创建物化视图（报错）：
SQL> connect datamask/datamask
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
create materialized view appuser.demot_v as select * from appuser.demot

                                                              *

ERROR at line 1:
ORA-01031: insufficient privileges

分配 create materialized 权限：
SQL> connect / as sysdba
SQL> grant create materialized view to datamask;

尝试创建物化视图（报错）：
SQL> connect datamask/datamask
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
create materialized view appuser.demot_v as select * from appuser.demot

                                                              *

ERROR at line 1:
ORA-01031: insufficient privileges

给业务用户分配 create table 权限：
SQL> connect / as sysdba
SQL> grant create table to appuser;

尝试创建物化视图（成功）：
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
Materialized view created.

方法二：将 create materialized 分配给业务用户

创建业务用户：
SQL> create user appuser identified by appuser;
SQL> grant connect,resource,create view to appuser;
SQL> connect appuser/appuser
SQL> create table demot as select * from user_objects;

创建脱敏系统用户：
SQL> connect / as sysdba
SQL> create user datamask identified by datamask;
SQL> grant dba to datamask;

尝试创建物化视图（报错）：
SQL> connect datamask/datamask
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
create materialized view appuser.demot_v as select * from appuser.demot

                                                              *

ERROR at line 1:
ORA-01031: insufficient privileges

分配 create materialized 权限：
SQL> connect / as sysdba
SQL> grant create materialized view to appuser;

尝试创建物化视图（报错）：
SQL> connect datamask/datamask
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
create materialized view appuser.demot_v as select * from appuser.demot

                                                              *

ERROR at line 1:
ORA-01031: insufficient privileges

给业务用户分配 create table 权限：
SQL> connect / as sysdba
SQL> grant create table to appuser;

尝试创建物化视图（成功）：
SQL> connect datamask/datamask
SQL> create materialized view appuser.demot_v as select * from appuser.demot;
Materialized view created.