gen_grant_sel.sql oracle 数据库如何获取用户的DDL 所有权限
set echo off feedback off verify off pagesize 0 linesize 120
define v_grantee=&1
define v_grant_sel_command_file = .\log\grant_sel_&v_grantee..sql
define v_grant_sel_log_file = .\log\grant_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.
########sample .如果存在多个schema, 如果需要检查交叉权限，比如A 用户的对b用户的表权限，检查表的权限
SELECT 'grant '||granted_role||' to '||grantee||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END
FROM dba_role_privs WHERE grantee in('userCDE')
union all
SELECT 'grant '||privilege||' to '||grantee||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END
FROM dba_sys_privs WHERE grantee in('userCDE')
union all
SELECT 'grant '||privilege ||decode(privilege,'READ',' on directory ','WRITE',' on directory ',' on ')
||OWNER||'.'||table_name
||' to '||grantee||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END
FROM dba_tab_privs WHERE grantee in ('userCDE');
select * from role_sys_privs where role in ('userCDE');
select * from role_tab_privs where role in ('userCDE');
#######数据库如何获取用户的DDL 所有权限 sample1
数据库如何获取用户的DDL 所有权限
使用如下脚本判断；
set longchunksize 20000 pagesize 0 feedback off verify off trimspool on
column Extracted_DDL format a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
undefine User_in_Uppercase;
set linesize 1000
set long 2000000000
select (case
when ((select count(*)
from dba_users
where username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '&User_in_Uppercase') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&User_in_Uppercase') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V$PWFILE_USERS
where username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'&User_in_Uppercase'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&User_in_Uppercase') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual
/
##脚本完成
需要注意的是是哦那个使用如下SQL ,dbms_metadata.get_granted_ddl('OBJECT_GRANT', 这条语句会碰到bug. 报错代码为ora-31608 。所以只能用如上脚本判断查询
3.3 get_ddl_privs_pac.sql
#get all privs of specified user
clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : ' spool &&outfile..gen
SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180
SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') from dual;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname') from dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&uname') from dual;
