在測試環境做資料處理時，發現表的連線有問題，以下是簡單的模擬了一下。

--drop表後，role中賦予的許可權會自動回收
SQL> create user hrc identified by hrc;

User created.
SQL> grant connect,resource to hrc;

Grant succeeded.

SQL> conn hr/hr

SQL> create role hr_all;

Role created.

SQL> grant select,insert,update,delete on tt to hr_all;

Grant succeeded.

SQL> grant hr_all to hrc;

Grant succeeded.

SQL> /
select count(*) from hr.tt
*
ERROR at line 1:
ORA-00942: table or view does not exist --需要建同義詞之類的

SQL> select count(*) from hr.tt;

COUNT(*)
----------
4162

SQL> conn hr/hr

SQL> drop table tt;

Table dropped.

--使用hrc來查詢，會報錯
SQL> select count(*) from hr.tt;
select count(*) from hr.tt
*
ERROR at line 1:
ORA-00942: table or view does not exist

--hr 重建表tt
SQL> create table tt as select * from all_objects where rownum<200;

Table created.

--使用hrc來查詢，還是會報錯
SQL> select count(*) from hr.tt;
select count(*) from hr.tt
*
ERROR at line 1:
ORA-00942: table or view does not exist

drop後需要重新賦許可權。

關於drop操作對role的影響

相關文章