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;