联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在使用expdp/impdp迁移的过程中,偶尔会遇到用户中关于sys对象的授权丢失导致不少pl/sql程序无效,通过测试重现sys授权丢失现象
创建用户并进行sys对象授权给该用户
C:\Users\XFF>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:04:22 2025 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user xff identified by oracle; User created. SQL> grant resource,connect to xff; Grant succeeded. SQL> grant select on sys.obj$ to xff; Grant succeeded. SQL> grant execute on sys.dbms_lock to xff; Grant succeeded. SQL> grant select on sys.v_$session to xff; Grant succeeded. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
使用exp导出数据
C:\Users\XFF>expdp "'/ as sysdba'" dumpfile=xff.dmp schemas=xff Export: Release 11.2.0.4.0 - Production on Sun Jan 19 12:05:35 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_04": "/******** AS SYSDBA" dumpfile=xff.dmp schemas=xff Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is: C:\APP\XFF\ADMIN\ORCL\DPDUMP\XFF.DMP Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Jan 19 12:05:37 2025 elapsed 0 00:00:02
使用impdp导入数据
C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff:nxff Import: Release 11.2.0.4.0 - Production on Sun Jan 19 12:06:22 2025 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" dumpfile=xff.dmp remap_schema=xff:nxff Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun Jan 19 12:06:23 2025 elapsed 0 00:00:00
验证用户的权限
C:\Users\XFF>sqlplus xff/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:09:21 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user;
USER is "XFF"
SQL> select count(1) from v$session;
COUNT(1)
----------
26
SQL> select count(1) from sys.obj$;
COUNT(1)
----------
90656
SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKNAME VARCHAR2 IN
LOCKHANDLE VARCHAR2 OUT
EXPIRATION_SECS NUMBER(38) IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
PROCEDURE SLEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SECONDS NUMBER IN
SQL> conn nxff/oracle
Connected.
SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist
SQL> select count(1) from sys.obj$;
select count(1) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(1) from v$session;
select count(1) from v$session
*
ERROR at line 1:
ORA-00942: table or view does not exist
确认通过impdp迁移过去的nxff用户没有之前xff用户里面sys授权的对象的访问权限.通过sqlfile查看expdp导出的dmp文件中ddl内容,确认确实没有sys部分的授权
出现这个问题的原因是由于expdp不会导出sys中对象,所以就丢失了这部分授权信息,可以通过获取语句获取权限,然后执行补全
SQL> select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||
2 ' to ' || grantee || ';' "GRANTS"
3 from dba_tab_privs
4 where owner = 'SYS' and privilege not in ('READ', 'WRITE')
5 and grantee in ('XFF')
6 order by 1;
GRANTS
--------------------------------------------------------------------------------
grant EXECUTE on "DBMS_LOCK" to XFF;
grant SELECT on "OBJ$" to XFF;
grant SELECT on "V_$SESSION" to XFF;
