oracle批量建同义词,Oracle批量赋权及批量建立同义词给其他用户

功能：两个用户，其中一个用户A 没有数据结构，实现通过用户A访问用户B的所有数据，只有读取数据的权限，看不到B用户数据结构，只能查询。

一、建立只读用户A：

-- Create the user

create user A

identified by ""

default tablespace LMIS

temporary tablespace LMIS

profile DEFAULT;

二、授予A相应的权限(在有DBA权限用户B下执行)

grant create session to A;

grant create synonym to A;

三、以拥有者B身份执行下面的语句(commit 窗口)

set heading OFF;

set feedback OFF;

SPOOL c:/temp/A.SQL;                             --放文件的路径

SELECT 'create synonym '||tname||' for B.'||tname||';' FROM TAB;

SPOOL OFF;

四、转变为被赋权用户A的身份，执行

@c:/temp/A.SQL

五、赋予用户A权限(在B用户COMMIT窗口下执行)

set heading OFF;

set feedback OFF;

SPOOL c:/grantA.SQL;

SELECT 'grant select,insert,update,delete,REFERENCES,INDEX on ' ||

TNAME || ' to A;'

FROM TAB WHERE TABTYPE <>'VIEW'

UNION ALL

SELECT 'grant select,insert,update,delete  on ' || OBJECT_NAME ||

' to A;'

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'VIEW'

UNION ALL

SELECT 'grant EXECUTE  on ' || OBJECT_NAME || ' to A;'

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'PROCEDURE'

UNION ALL

SELECT 'grant EXECUTE  on ' || OBJECT_NAME || ' to A;'

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'FUNCTION'

UNION ALL

SELECT 'grant select  on ' || OBJECT_NAME || ' to A;'

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'SEQUENCE';

SPOOL OFF;

六、执行脚本

@c:/grantA.SQL

另外可以使用TOAD 查询窗口 然后SAVE AS 存为文本 然后把文本第一行删掉。把其他语句放在PL/DELVEPOR COMMAND 窗口执行。

select 'create synonym '||TABLE_NAME||' for BA.'||TABLE_NAME||';' from user_tables where  TABLE_NAME LIKE 'T_MARKET%'

select 'create synonym '||TABLE_NAME||' for BA.'||TABLE_NAME||';' from user_tables where  TABLE_NAME LIKE 'T_USER%'

select 'create synonym '||TABLE_NAME||' for BA.'||TABLE_NAME||';' from user_tables where  TABLE_NAME LIKE 'T_VIP%'

select 'create synonym '||VIEW_NAME||' for BA.'||VIEW_NAME||';' from user_VIEWS

create synonym p_vip_sales_detail for ba.p_vip_sales_detail;

create synonym P_vip_Pagination for ba.P_vip_Pagination;

create synonym P_vip_Pagination

SELECT 'grant select,insert,update,delete,REFERENCES,INDEX on ' ||           TNAME || ' to baweb;'      FROM TAB WHERE TABTYPE <>'VIEW' AND TNAME LIKE 'T_MARKET%'  OR TNAME LIKE 'T_USER%'  OR TNAME LIKE 'T_VIP%'    UNION ALL    SELECT 'grant select,insert,update,delete  on ' || OBJECT_NAME ||           ' to baweb;'      FROM USER_OBJECTS     WHERE OBJECT_TYPE = 'VIEW'    UNION ALL    SELECT 'grant EXECUTE  on ' || OBJECT_NAME || ' to baweb;'      FROM USER_OBJECTS     WHERE OBJECT_TYPE = 'PROCEDURE' AND  OBJECT_NAME IN ('P_VIP_PAGINATION','P_VIP_SALES_DETAIL')