为一用户加受控权限用户过程

--被引用用户所有视图生成对象授权
select 'grant select on jtsjk.'||t.table_name||' to qys1;' from dba_tables t where t.owner='JTSJK';
--被引用用户所有视图生成对象授权
select 'grant select on jtsjk.'||t.view_name||' to qys1;' from dba_VIEWS t where t.owner='JTSJK';
--被引用用户所有过程生成对象授权
select 'grant select on jtsjk.'||t.PROCEDURE_name||' to qys1;' from dba_PROCEDURES t where t.owner='JTSJK';
--被引用用户所有触发器生成对象授权
select 'grant select on jtsjk.'||t.TRIGGER_name||' to qys1;' from dba_TRIGGERS t where t.owner='JTSJK';
--当前用户下所有对象
select * from user_objects where object_type  in('TABLE','VIEW','PROCEDURE','TRIGGER','FUNCTION','PACKAGE');
--在被引用用户下取要改的同意词
select ' create public synonym '||OBJECT_NAME||' for jtsjk.'||OBJECT_NAME||';'  
from user_objects where object_type  in('TABLE','VIEW');
--授权同意词
grant create any synonym to qys1;
grant create public synonym to qys1;
--SYS下视图明细
select *from dba_VIEWS t where t.view_name like 'DBA_T%'

--别一种写法取所有函数
select  'grant select on jtsjk.'||object_name||' to qys1;' from user_objects where object_type  in('FUNCTION');

--当前用户所有对象

select  distinct object_type from user_objects

1 SEQUENCE
2 PROCEDURE
3 LOB
4 PACKAGE
5 PACKAGE BODY
6 TRIGGER
7 TABLE
8 INDEX
9 VIEW
10 FUNCTION

如果用户的等级不够建立public同义词，可以在system下授权：

 

grant create any synonym to scott;

 

grant create public synonym to scott;

 

取消授权：

 

revoke create any synoym from scott;

 

revoke create public synoym from scott;

 

取消同义词：

 

drop public synonym abc;

 

授权取消同义词：

 

grant dorp public synonym to scott;

 

取消授权：

 

revoke drop public synonym to scott;