11g , role 삭제 후, 복구 - flashback Query 를 통해서 복구 가능
- 대상 테이블 DBA_TAB_PRIVS, DBA_SYS_PRIVS, DBA_ROLE_PRIVS ( 3 EA )
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | [TG11204]tg:/home/oracle> ss SQL*Plus: Release 11.2.0.4.0 Production on Fri May 4 18:00:11 2018 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 Enter value for gname: 11G 11G> show user USER is "SYS" 11G> create role test_role ; Role created. 11G> !date Fri May 4 18:00:34 KST 2018 11G> select owner, table_name from dba_tables where owner ='SCOTT'; OWNER TABLE_NAME ------------------------------ ------------------------------ SCOTT DEPT SCOTT EMP SCOTT SALGRADE SCOTT BONUS -- 테스트 ROLE 생성 11G> grant select on scott.emp to test_role ; Grant succeeded. 11G> grant dba to test_role ; Grant succeeded. 11G> grant connect to test_role ; Grant succeeded. -- 테스트 ROLE 삭제 전 시간 확인 11G> !date Fri May 4 18:04:57 KST 2018 -- 테스트 ROLE 삭제 11G> drop role test_role ; Role dropped. 11G> col owner for a20 11G> col table_name for a20 11G> select OWNER, TABLE_NAME, GRANTEE, PRIVILEGE 2 from dba_Tab_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; OWNER TABLE_NAME GRANTEE PRIVILEGE -------------------- -------------------- ------------------------------ ---------------------------------------- SCOTT EMP TEST_ROLE SELECT 11G> select * 2 from dba_sys_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; no rows selected 11G> select * 2 from dba_role_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- TEST_ROLE DBA NO YES TEST_ROLE CONNECT NO YES -- 복구 문장 작성 하기 11G> COL ROLLBACK_GRANT1 FOR A100 11G> select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' AS ROLLBACK_GRANT1 2 from dba_Tab_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; ROLLBACK_GRANT1 ---------------------------------------------------------------------------------------------------- GRANT SELECT ON SCOTT.EMP TO TEST_ROLE; 11G> COL ROLLBACK_GRANT2 FOR A100 11G> select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';' AS ROLLBACK_GRANT2 2 from dba_role_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; ROLLBACK_GRANT2 ---------------------------------------------------------------------------------------------------- GRANT DBA TO TEST_ROLE; GRANT CONNECT TO TEST_ROLE; 11G> COL ROLLBACK_GRANT3 FOR A100 11G> select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';' AS ROLLBACK_GRANT3 2 from dba_sys_privs as of timestamp to_timestamp('2018.05.04 18:04:57','YYYY.MM.DD HH24:MI:SS') 3 where grantee ='TEST_ROLE' ; no rows selected 11G> | cs |