schema expdp -> impdp 시 , 복구된 schema 에 부여된 권한은 ?

schema expdp -> impdp 시 , 복구된 schema 에 부여된 권한은 ?

 이를테면,

   1. scott101(테스트 유저 ) 생성

   2. scott 테이블에 대해서 scott101 유저에 권한 부여 

   3. expdp -> impdp 를 통해서 scott101 을 복구 하면,

      (2) 에서 부여한 scott 유저 테이블에 대한 scott101 유저의 권한이 복구 될까 ?

     정답은 안된다.

     가만히 생각 해보면, exp 나 expdp 시에 권한은 object 단위로 exp/expdp 되고 imp/impdp 된다. 

 

1) 사용자 생성 및 권한 부여      

-- 01 CREATE USER
CREATE USER SCOTT101 IDENTIFIED BY TIGER101 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ;
GRANT CREATE SESSION, ALTER SESSION TO SCOTT101 ;

-- 02 GRANT SELECT( 46 EA ) 
SELECT OWNER, TABLE_NAME, 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME ||' TO SCOTT101 ; ' AS G_SQL
FROM   DBA_TABLES
WHERE  OWNER ='SCOTT'
ORDER BY TABLE_NAME ; 

-- 03 CHECK GRANT SELECT 
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE ='SCOTT101';
-- 46 ROWS

 

2. expdp -> impdp(sqlfile) 을 통해서 expdp 내용 파악 하기 

-- 04 expdp schemas scott101
[oracle@oel7 ~]$ cat expdp_scott101.par
userid=scott
directory=DIR_EXT
schemas=scott101
dumpfile=scott101.dmp
logfile=scott101.log
[oracle@oel7 ~]$ expdp parfile=expdp_scott101.par

Export: Release 19.0.0.0.0 - Production on Fri Mar 3 18:25:45 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=expdp_scott101.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scott101.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Mar 3 18:26:12 2023 elapsed 0 00:00:18

[oracle@oel7 ~]$

-- 05 impdp sqlfile 
[oracle@oel7 ~]$ cat impdp_scott101.par
userid=scott
directory=DIR_EXT
dumpfile=scott101.dmp
logfile=scott101_impdp.log
sqlfile=scot101_impdp.sql
schemas=scott101
[oracle@oel7 ~]$ impdp parfile=impdp_scott101.par

Import: Release 19.0.0.0.0 - Production on Fri Mar 3 18:29:00 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SCOTT"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_SQL_FILE_SCHEMA_01":  scott/******** parfile=impdp_scott101.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SCOTT"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Fri Mar 3 18:29:06 2023 elapsed 0 00:00:02

[oracle@oel7 ~]$

-- 아래 내용 중 scott 테이블에 대한 권한 46는 보이지 않는다. 
[oracle@oel7 ~]$ cat scot101_impdp.sql
-- CONNECT SCOTT
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
 CREATE USER "SCOTT101" IDENTIFIED BY VALUES 'S:85F1D0F90B0CEE5E5718B0BD430A46D6F7FCCF39CAD838B9BF600263ACE0;T:7EBFA78E4985D1B266CC5212F0AEACFD8C4484D347F1A4BFD14C85AB0114C93392DF23130E67EDA36ABDFF58BDD4F7E759A4617BBFFCD2B6D9C75FB8F86F659905F9841D2A2127AD7DC8D421C6D5E525;DBC47A91A7C9C477'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT ALTER SESSION TO "SCOTT101";
GRANT CREATE SESSION TO "SCOTT101";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "SCOTT101" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PASSWORD_HISTORY
 DECLARE
     SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS;
     HIST_REC HIST_RECORD;
     i number := 0;
 BEGIN
      SYS.DBMS_PSWMG_IMPORT.PURGE_HISTORY('SCOTT101');
      i := i+1;
      HIST_REC(i).USERNAME := 'SCOTT101';
      HIST_REC(i).PASSWORD := 'T:7EBFA78E4985D1B266CC5212F0AEACFD8C4484D347F1A4BFD14C85AB0114C93392DF23130E67EDA36ABDFF58BDD4F7E759A4617BBFFCD2B6D9C75FB8F86F659905F9841D2A2127AD7DC8D421C6D5E525';
      HIST_REC(i).PASSWD_DATE := '2023/03/03 18:21:57';

      SYS.DBMS_PSWMG_IMPORT.IMPORT_HISTORY(HIST_REC,i);
 END;
/
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT101

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORACLE19', inst_scn=>'12914034');
COMMIT;
END;
/
-- fixup virtual columns...
-- done fixup virtual columns
[oracle@oel7 ~]$
--