关于drop操作对role的影响

在测试环境做数据处理时，发现表的连接有问题，以下是简单的模拟了一下。

--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后需要重新赋权限。