假设存在schema scott,基于开发需要,需要新建一个账号test,需要查询scott用户的表、视图、序列等,可以调用scott的函数、过程。
新建账号:
create user test identified by 'test';
grant resource,connect,unlimited tablespace to test;
生成赋值语句:
SELECT OBJECT_TYPE,
CASE
WHEN OBJECT_TYPE IN('TABLE','VIEW','SEQUENCE')
THEN 'GRANT SELECT ON '
WHEN OBJECT_TYPE IN('PROCEDURE','FUNCTION')
THEN 'GRANT EXECUTE ON '
END
||OWNER||'.'||OBJECT_NAME||' TO TEST;' GRANTSQL
FROM DBA_OBJECTS
WHERE WNER='SCOTT'
AND OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION')
ORDER BY OBJECT_TYPE
再次执行赋值的语句即可。
come from:http://hi.baidu.com/nixsql/blog/item/abacb52f15495ae08b139942.html