如何把一个表空间下的所有表赋权给指定用户

如题，一开始想到的是像sqlserver那样使用类似sp_executesql的方法执行select返回的结果集。

select 'grant select on '||owner||'.'||table_name||' to LEE;' from  dba_tables where tablespace_name='EXAMPLE'

但发现  execute immediate 貌似执行不了select返回的结果集。大家指点一下，我的这个需求可以怎么样实现呢！ 

13 个解决方案

#1

begin
    for x in (select table_name from user_tables) loop
        execute immediate 'grant select on ' || x.table_name || ' to U_NAME' ;
    end loop;
end;

#2

DECLARE
v_sql varchar2(2000);
begin
    select 'grant select on '||owner||'.'||table_name||' to LEE;' into v_sql 
    from  dba_tables where tablespace_name='EXAMPLE';
    execute immediate v_sql;
end;

#3

DECLARE
    CURSOR CUR_MYCUR IS
        SELECT * FROM USER_TABLES;
    V_NAME    USER_TABLES.TABLENAME%TYPE;
    V_SQLSTMT VARCHAR2(300);
    V_OWNER   VARCHAR2(50) := 'ORACLE';
BEGIN
    V_SQLSTMT := 'GRANT SELECT ON :OWNER.:TABLE_NAME TO LEE';
    FOR CUR_ITERATOR IN CUR_MYCUR LOOP
        EXECUTE IMMEDIATE V_SQLSTMT
            USING V_OWNER, CUR_ITERATOR.TABLE_NAME;
    END LOOP;

END;

#4

跟上面几楼学习了

#5

引用 楼主 pps873792861 的回复:

 

动态SQL

#6

1、首先，普通用户是没有给别的用户赋权限的权限，只有超级用户admin或者管理员帐号才可以给别的用户赋予权限。
2、赋予权限SQL语句（给普通用户user赋所有权限）：
grant all to user;
如果只要赋予部分权限，则：
grant create session, select any table, dba to user;

#7

引用 1 楼 wmxcn2000 的回复:

begin
    for x in (select table_name from user_tables) loop
        execute immediate 'grant select on ' || x.table_name || ' to U_NAME' ;
    end loop;
end;

我是想把一个表空间下的所有表查询权限都赋值给某个用户，所以有两个参数：
select owner,table_name from  dba_tables where tablespace_name='EXAMPLE';，
for x in 只是传了一个参数，如果我要传两个参数要怎么传呢？

#8

引用 2 楼 js14982 的回复:

DECLARE
v_sql varchar2(2000);
begin
    select 'grant select on '||owner||'.'||table_name||' to LEE;' into v_sql 
    from  dba_tables where tablespace_name='EXAMPLE';
    execute immediate v_sql;
end;

执行报错，实际返回的行数超出请求的行数

#9

引用 7 楼 pps873792861 的回复:

我是想把一个表空间下的所有表查询权限都赋值给某个用户，所以有两个参数：
select owner,table_name from  dba_tables where tablespace_name='EXAMPLE';，
for x in 只是传了一个参数，如果我要传两个参数要怎么传呢？

拼接一下就可以了

begin
    for x in (select owner,table_name from  dba_tables where tablespace_name='EXAMPLE') loop
        execute immediate 'grant select on ' || x.owner || '.' ||  x.table_name || ' to U_NAME' ;
    end loop;
end;

#10

引用 8 楼 pps873792861 的回复:

Quote: 引用 2 楼 js14982 的回复:

DECLARE
v_sql varchar2(2000);
begin
    select 'grant select on '||owner||'.'||table_name||' to LEE;' into v_sql 
    from  dba_tables where tablespace_name='EXAMPLE';
    execute immediate v_sql;
end;

执行报错，实际返回的行数超出请求的行数

 因为给参数赋值，into v_sql 。只能是查询一行出来，不能多行，否则就报错。

#11

引用 9 楼 wmxcn2000 的回复:

Quote: 引用 7 楼 pps873792861 的回复:

我是想把一个表空间下的所有表查询权限都赋值给某个用户，所以有两个参数：
select owner,table_name from  dba_tables where tablespace_name='EXAMPLE';，
for x in 只是传了一个参数，如果我要传两个参数要怎么传呢？

拼接一下就可以了

begin
    for x in (select owner,table_name from  dba_tables where tablespace_name='EXAMPLE') loop
        execute immediate 'grant select on ' || x.owner || '.' ||  x.table_name || ' to U_NAME' ;
    end loop;
end;

 谢谢，你的方法简明正确。但是这样子做，报错了：无法引用索引表的溢出表，后面的就不执行了。后来，我把筛选条件改成：
select owner,table_name from  dba_tables where tablespace_name='EXAMPLE' and table_name not like '%74%'
。就没有再报错了。所以，我想再请教一下，索引表的溢出表，是什么意思，百度了一下都是介绍索引表的。然后，如果要让代码报错了，还是能继续执行下去，要怎么改呢?

#12

begin
    for x in (select owner,table_name from  dba_tables where tablespace_name='EXAMPLE') loop
        begin
             execute immediate 'grant select on ' || x.owner || '.' ||  x.table_name || ' to U_NAME' ;
        exception 
            when others then null;
        end;
    end loop;
end;

#13

mark 一下