用户所有表迁移到另外单独用户

1.背景 此前开发申请建立DB用户，dba都是只建立一个给开发使用，权限过大，现在要收缩权限，进行分离，新要求如下： Schema Owner:系统对象所有者(收归dba) --DDL、DQL、DML权限 AP User：系统程式使用 ---DQL、DML权限 Select User：系统维运人员用户--select权限 Mid User:其他系统调用---select权限

2.当前状况： dcp用户需要进行拆分，所有表迁移到dcpdbo下面，禁止dcp用户对表进行异动

3.对象状态检查 查看所有object状态----不只是dcp用户，与DCP相关用户都需要查看，因为可能连带受影响,因为迁移导致失效的，需要手动编译，迁移前就失效的，迁移后不一定能编译成功，能编译的就顺带了。

--总数
select count(*) from dba_objects a where a.owner='DCP';
-- valid invalid 数量
select a.status,count(*) from dba_objects a where a.owner='DCP' group by a.status;
--invalid 类型和数量
select a.owner,a.object_type,count(object_type) from dba_objects a where a.owner='DCP' and a.status='INVALID' group by a.owner,a.object_type  order by a.owner,a.object_type;
--所有invalid详情
select * from dba_objects a where a.owner='DCP' and a.status='INVALID' order by a.owner,a.object_type;

---对user所有object进行编译
select 'alter '||a.object_type ||' '||a.owner||'.'||a.object_name||' compile;' from dba_objects a where a.owner='DCP' and a.status='INVALID' ;

--锁定用户
alter user DCP account lock;

---DCP权限同步赋予DCPdbo
select * from dba_sys_privs a where a.grantee='DCP';
CREATE VIEW

select * from dba_role_privs a where a.grantee='DCP';
RESOURCE_NEW
CONNECT

4.创建新的dcpdbo用户，表空间

create tablespace ndx_dcpdbo datafile '/u02/oradata/SCMGSTDB/ndx_dcpdbo01.dbf' size 20M autoextend on next 16M maxsize 30G;
create tablespace tbs_dcpdbo datafile '/u02/oradata/SCMGSTDB/tbs_dcpdbo01.dbf' size 30G;
alter tablespace tbs_dcpdbo add datafile '/u02/oradata/SCMGSTDB/tbs_DCPdbo02.dbf' size 1G autoextend on next 128M maxsize 30G;


create user DCPdbo identified by "dcpdbo" default tablespace tbs_dcpdbo;
alter user DCPdbo quota unlimited on  tbs_dcpdbo  ;
alter user DCPdbo quota unlimited on  ndx_DCPdbo  ;

grant CREATE VIEW to dcpdbo;
grant RESOURCE_NEW to dcpdbo;
grant CONNECT to dcpdbo;

5.授权收集

--其他用户授权给dcp用户的权限 
select  'grant '||a.privilege||' on '||a.OWNER||'.'||a.TABLE_NAME||' to '||a.GRANTEE||' ;'  from dba_tab_privs a where a.GRANTEE='DCP';

--DCP授权给其他用户的权限，虽然表导入的时候会自动授权，还是需要手动检查一下
select 'grant '||a.privilege||' on '||a.OWNER||'.'||a.TABLE_NAME||' to '||a.GRANTEE||' ;' from dba_tab_privs a where a.owner='DCP';

6.导出导入 --导出DCP 所有表

expdp \'/ as sysdba\' directory=DUMP dumpfile=scmgstdb_dump_DCP_tables_2023_11_06_%U.dmp logfile=scmgstdb_dump_DCP_tables_2023_11_06_exp.log schemas=DCP parallel=4 compression=data_only

impdp \'/ as sysdba\' directory=DUMP dumpfile=scmgstdb_dump_DCP_tables_2023_11_06_%U.dmp logfile=scmgstdb_dump_DCP_tables_2023_11_06_imp.log schemas=DCP parallel=4 remap_schema=DCP:DCPDBO remap_tablespace=TBS_DCP:TBS_DCPDBO,NDX_DCP:NDX_DCPDBO include=table

7.把第五步收集的权限手动执行一下 其他用户授权给pcp权限 DCP 表授权给其他用户的部分 dcpdbo 表增删改查授权给DCP，debug是为了查看trigger 删除原用户pcp下面的表 创建同义词给dcp(表要先删除，否则同义词创建失败)

select 'grant select,delete,insert,update,debug on DCPDBO.'||a.table_name||' to DCP;' from dba_tables a where a.owner='DCPDBO';

select 'drop table DCP.'||a.table_name||' ;' from dba_tables a where a.owner='DCP';

select 'CREATE OR REPLACE SYNONYM DCP.'||a.table_name||' for DCPDBO.'||a.table_name||' ;' from dba_tables a where a.owner='DCP';

8.问题： 触发器是建立在表上面的，表别删除后，触发器还在吗？ --触发器没有了，但是导出导入，触发器会同步导入,如果需要查看trigger内容太，需要给dubug table权限

--补充，部分表使用到sequence作为自增列，需要把DCP sequence授权给DCPdbo，否则后续使用会有问题,

查看默认值是否有使用sequence的表
SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.DEFAULT_LENGTH,a.DATA_DEFAULT FROM dba_TAB_COLUMNS a where a.OWNER='DCPDBO' and a.DATA_DEFAULT is not null;

9.invalid重新编译

对比迁移前object 状态，重新去编译
--总数
select count(*) from dba_objects a where a.owner='DCP';
-- valid invalid 数量
select a.status,count(*) from dba_objects a where a.owner='DCP' group by a.status;
--invalid 类型和数量
select a.owner,a.object_type,count(object_type) from dba_objects a where a.owner='DCP' and a.status='INVALID' group by a.owner,a.object_type  order by a.owner,a.object_type;
--所有invalid详情
select * from dba_objects a where a.owner='DCP' and a.status='INVALID' order by a.owner,a.object_type;

---对user所有object进行编译
select 'alter '||a.object_type ||' '||a.owner||'.'||a.object_name||' compile;' from dba_objects a where a.owner='DCP' and a.status='INVALID' ;

10.dcp还有truncate表的需求，目前没有直接权限可以实现，通过存储过程来完成，开发需要该代码 在dcpdbo下面建立truncate_table_list表，指定可删除列表，此表不赋予dcp修改权限，只做查看，做权限管控

create or replace procedure pro_truncate_dcpdbo_table(v_table_name in VARCHAR2) is

  v_table VARCHAR2(4000);

begin
  SELECT table_name
    into v_table
    from truncate_table_list
   WHERE delete_flag = '0'
     and table_name = trim(v_table_name);
  if v_table IS NOT NULL then
    execute immediate 'truncate table dcpdbo.' || trim(v_table_name);
  end if;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20001,trim(V_TABLE_NAME)||'无TRUNCATE权限');
end;
/

grant execute on pro_truncate_dcpdbo_table to dcp;

create table DCPDBO.TRUNCATE_TABLE_LIST
(
  table_name  VARCHAR2(1000),
  delete_flag VARCHAR2(10) default '0',
  create_date DATE default SYSDATE,
  remark      VARCHAR2(1000)
);

10.用户解锁

alter user dcp account unlock;