set echo off feedback off verify off pagesize 0 linesize 120
define v_grantee=&1
define v_grant_sel_command_file = .loggrant_sel_&v_grantee..sql
define v_grant_sel_log_file = .loggrant_sel_&v_grantee..log
spool &v_grant_sel_command_file.
prompt spool &v_grant_sel_log_file.
prompt set echo on feedback on
prompt show user
----将原有的权限赋予用户,no exsists ( select null于（select *）差不多) 就是where如果子查询没有返回行，则----则满足 NOT EXISTS 中的 ----WHERE 子句，目的应该是检查是否表的权限是否有丢失。
----按照每个表的权限进行遍历
--- &v_grantee 是request , 执行者是 data owner.
select
'grant select on ' || t.table_name || ' to &v_grantee with grant option;'
from user_tables t
where not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = t.table_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by t.table_name
/
--
select
'grant select on ' || v.view_name || ' to &v_grantee with grant option;'
from user_views v
where not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = v.view_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by v.view_name
/
select
'grant execute on ' || o.object_name || ' to &v_grantee;'
from user_objects o
where object_type in ('PACKAGE')
and not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = o.object_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by o.object_name
/
prompt set echo off feedback off
prompt spool off
spool off
@&v_grant_sel_command_file.
补充测试说明:
data user: for ddl usr
user: for app dml/select （同义词）
patch user: for app supprot user （同义词）
query : for app supprot user （同义词）
##
step 1:
检查 data user的表是否给了权限usr user.
变量为： dbUSR
select * from user_tab_privs p where p.owner = user and p.grantee = upper('&v_grantee')) and user != upper('&v_grantee')
step 2.1: 测试取消data user 的表的update/select权限
revoke update on testfrom dbUSR; revoke select on testfrom dbUSR;
step 3.1: 测试脚本01_schema_rollout.sql能否将的表的update/select权限 重新授权
测试01_schema_rollout.sql 结果：无法将2个权限 重新授权
step 2.2: 测试取消data user 的表的delete/insert权限
revoke insert on testfrom dbUSR;
revoke delete on testfrom dbUSR;
step 3.2: 测试脚本能否将的表的delete/insert/update/select权限 重新授权
测试01_schema_rollout.sql 结果：可以
step 2.3 删除一个表。然后使用备份表（.sql）文件恢复，是可行的。（.sql 文件包含授权grant命令 ）
step 3.3 同义词 状态是invalide,可以忽略。
原因如下： 先建一个可用的同义词，然后将该同义词对应的表删除，dba_objects对应的状态就是INVALID了 然后当你再去select这个同义词的时候，status又会变成VALID.