Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    France
    Posts
    754

    Unanswered: Grant select on all the tables of another user

    Is it possible to grant SELECT on all the tables of another user in a single command without using GRANT SELECT ANY TABLE ?

    If there is no such single command, I'll do it table after table within a PL/SQL procedure owned by the other user (looping through the other user's tables in ALL_TABLES and granting SELECT with EXECUTE IMMEDIATE).

    Thanks & regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The usual is to spool output to some file and then execute SQL to write the SQL for you.

    spool doit.sql
    select 'grant select on '||owner||'.'||table_name||' to other_user;' from all_tables where owner = 'some_user';
    spool off
    @doit.sql

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thanks Bill, that's a good way too.

    I finally did it in PL/SQL like this (maybe I'll put it in a procedure with neater code later) :

    Code:
    declare
        result sys_refcursor;
        strTableName Varchar2(100);
        strQuery varchar2(4000);
    begin
    
    open result for
        select table_name from user_tables where 
        table_name not like 'BIN$%'
        and table_name not like 'SYS_%';
    
    loop
        fetch result into strTableName;
        exit when result%notfound;
            
        DBMS_OUTPUT.PUT('Granting select on '||strTableName||' to gb...');
            
        strQuery := 'Grant select on '||strTableName||' to gb';
        
        execute immediate strQuery;
        
        DBMS_OUTPUT.PUT_LINE('SUCCES');
    end loop;
    
    close result;
    
    end;
    /
    Thanks & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Oct 2014
    Posts
    7
    It’s really great posts.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Aren't you forgetting something?
    What about the views?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •