重庆思庄Oracle、Redhat认证学习论坛

标题: 用户权限收集 [打印本页]

作者: admin 时间: 2024-10-12 15:13
标题: 用户权限收集

SQL> drop table t_tmp_user_lhr;

SQL> drop sequence s_t_tmp_user_lhr;

SQL> create table t_tmp_user_lhr(id number,username varchar2(50),

exec_sql varchar2(4000),

create_type varchar2(20));

SQL> create sequence s_t_tmp_user_lhr;

SQL> begin

for cur in (select d.username,

d.default_tablespace,

d.account_status,

'create user ' || d.username || ' identified by ' ||

d.username || ' default tablespace ' ||

d.default_tablespace || ' TEMPORARY TABLESPACE ' ||

d.temporary_tablespace || ';' create_user,

replace(to_char(DBMS_METADATA.GET_DDL('USER',d.username)),

chr(10), '') create_user1

from dba_users d

where d.username in ('业务用户')) loop

insert into t_tmp_user_lhr

(id,username,exec_sql,create_type)

values

(s_t_tmp_user_lhr.nextval,cur.username,cur.create_user,'USER');

insert into t_tmp_user_lhr

(id,username,exec_sql,create_type)

select s_t_tmp_user_lhr.nextval,

cur.username,

case

when d.admin_option = 'YES' then

'GRANT ' || d.privilege || ' TO ' || d.grantee ||

' WITH GRANT OPTION ;'

else

'GRANT ' || d.privilege || ' TO ' || d.grantee|| ';'

end priv,'DBA_SYS_PRIVS'

from dba_sys_privs d

where d.grantee=cur.username;

insert into t_tmp_user_lhr

(id,username,exec_sql,create_type)

select s_t_tmp_user_lhr.nextval,

cur.username,

case

when d.admin_option='YES' THEN

'GRANT ' || d.granted_role || ' TO ' || d.GRANTEE ||

' WITH GRANT OPTION;'

else

'GRANT ' || d.granted_role || ' TO ' || d.GRANTEE || ';'

end priv,'DBA_ROLE_PRIVS'

from DBA_ROLE_PRIVS d

where d.grantee=cur.username;

insert into t_tmp_user_lhr

(id,username,exec_sql,create_type)

select s_t_tmp_user_lhr.nextval,

cur.username,

case

when d.grantable='YES' THEN

'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

d.table_name || ' TO ' ||d.grantee ||

' WITH GRANT OPTION ;'

else

'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||

d.table_name || ' TO ' ||d.grantee || ';'

end priv,'DBA_TAB_PRIVS'

from DBA_TAB_PRIVS d

where d.grantee=cur.username;

end loop;

commit;

end;

SQL> select * from t_tmp_user_lhr;

SQL> select id,username,create_type,exec_sql from t_tmp_user_lhr where create_type not in ('USER');

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)