---
title: "Oracle批量赋权及批量建立同义词给其他用户"
author: "winzenghua"
date: "2009-07-16T18:05:00Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2009-07-16T18:05:00Z"
id: 987864
url: "https://java4gis.iteye.com/blog/987864"
---

功能：两个用户，其中一个用户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 &lt;&gt;'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 &lt;&gt;'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')