通过shell得到数据库中权限的脚本（r2笔记77天）
通过shell得到数据库中权限的脚本（r2笔记77天）
有些时候想直接查看某个用户下对应的权限信息。自己每次从数据字典中查找有些太麻烦了。如果涉及的对象类型多一些，很容易遗漏。
一种方式就是通过exp直接导出对象的信息来，可以直接解析dump内容来得到object的一些信息，也可以直接访问数据字典表来得到。
以下是在Metalink中提供的脚本，我在原本的脚本基础上稍微改动了一下。
不过可以看到这个脚本还是有一些的缺点，首先会创建一个临时的表。把各种过滤信息都放入临时的表中，然后继续筛查，而且对于表中的有些对象类型（比如回收站中的对象）也罗列了出来，这个不是大家期望看到的。其它的部分功能都很全面。
sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
SET ECHO off
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM DBA privs
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Anonymous
REM Copyright 1995, Oracle Corporation
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Running this script will in turn create a script of
REM all the object grants to users and roles. This created
REM script is called tfscsopv.lst.
REM
REM Since a DBA cannot grant objects other than his own,
REM this script will contain various connect clauses before
REM each set of grant statements. You must add the passwords
REM for each user before executing the script. Object grants
REM are very dependant on the user who issues the grant,
REM therefore, it is important that the correct user issue the
REM grant.
REM
REM In addition, DO NOT change the order of the grant statement.
REM They are spooled in sequence order, so that dependant grants
REM are executed in the correct order. For example, lets say
REM that Scott grants Jack select on emp with grant option, and
REM in turn Jack grants select on Scott.emp to Steve. It is
REM essential that Scott's grant be issued before Jack's.
REM Otherwise, Jack's grant will fail.
REM
REM NOTE: This script DOES NOT include grants made by 'SYS'.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
set feedback off
set termout off
set pagesize 500
set heading off
set recsep off
set linesize 200
set termout on
select 'Creating object grant script by user...' from dual;
set termout off
create table g_temp (seq NUMBER, grantor_owner varchar2(20),
text VARCHAR2(800));
DECLARE
cursor grant_cursor is
SELECT ur$.name, uo$.name, o$.name, ue$.name,
m$.name, t$.sequence#,
decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';')
FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$
WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND
t$.col# IS NULL AND t$.grantor# = ur$.user# AND
t$.grantee# = ue$.user# and
o$.owner#=uo$.user# and
-- o$.name=upper('$2') and
ur$.name=upper('$1') and
t$.grantor# != 0
order by sequence#;
lv_grantor sys.user$.name%TYPE;
lv_owner sys.user$.name%TYPE;
lv_table_name sys.obj$.name%TYPE;
lv_grantee sys.user$.name%TYPE;
lv_privilege sys.table_privilege_map.name%TYPE;
lv_sequence sys.objauth$.sequence#%TYPE;
lv_option VARCHAR2(30);
lv_string VARCHAR2(800);
lv_first BOOLEAN;
procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is
begin
insert into g_temp (seq, grantor_owner,text)
values (lv_sequence, lv_grantor, lv_string);
end;
BEGIN
OPEN grant_cursor;
LOOP
FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee,
lv_privilege,lv_sequence,lv_option;
EXIT WHEN grant_cursor%NOTFOUND;
lv_string := 'GRANT ' || lv_privilege || ' ON ' || lv_owner ||
'.' ||
lv_table_name || ' TO ' || lv_grantee ||
lv_option;
write_out(lv_sequence, lv_grantor,lv_string);
END LOOP;
CLOSE grant_cursor;
END;
/
spool gen_sqls/$1_roles.sql
break on guser skip 1
col text format a60 word_wrap
col text format a100
select text
from g_temp
order by seq, grantor_owner
/
spool off
drop table g_temp;
EOF
exit
脚本的运行效果如下，输入schema名称即可。
[ora11g@rac1 dbm_lite]$ ksh genroles.sh n1
Creating object grant script by user...
GRANT READ ON SYS.EXPDP_LOCATION TO PRDCONN;
GRANT WRITE ON SYS.EXPDP_LOCATION TO PRDCONN;
GRANT READ ON SYS.EXT_DATAPUMP TO MIG;
GRANT WRITE ON SYS.EXT_DATAPUMP TO MIG;
GRANT SELECT ON N1.BIG_INSERT TO APP_CONN WITH GRANT OPTION;
GRANT SELECT ON N1.TT TO APP_CONN WITH GRANT OPTION;
GRANT SELECT ON N1.T TO APP_CONN WITH GRANT OPTION;
GRANT SELECT ON N1.BIN$/KBps0AbJ07gRQAAAAAAAQ==$0 TO APP_CONN WITH GRANT OPTION;
稍后会在这个基础的版本做一个大改造。让脚本的功能更加灵活和全面。