0

I have written a procedure to grant permissions on all the tables of a particular schema to rest other schemas. 

  create or replace PROCEDURE GRANTS_PROC

    IS

    CURSOR GRANTS_CURSOR
    IS
        SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON "'
        ||T.OWNER
        ||'"."'
        ||TABLE_NAME
        ||'" TO '
        ||(SELECT rtrim(listagg(U.username||',')
         within group (order by U.username),',') USERNAME
         FROM ALL_USERS U
         WHERE U.USERNAME!=T.OWNER
         AND U.USERNAME IN
        ('AAA','BBB','CCC','DDD','EEE','FFF','GGG','HHH','III'))||';' FINAL_TXT
         FROM  ALL_TABLES T
         WHERE T.OWNER IN
        ('AAA','BBB','CCC','DDD','EEE','FFF','GGG','HHH','III')
         ORDER BY T.OWNER,UPPER(T.TABLE_NAME);
    BEGIN
        --DBMS_OUTPUT.PUT_LINE('CURSOR_GRANTS.FINAL_TXT');
        --QRY_TEXT:='ABC';
        FOR CURSOR_GRANTS IN GRANTS_CURSOR
        LOOP
        DBMS_OUTPUT.PUT_LINE(CURSOR_GRANTS.FINAL_TXT);

            EXECUTE IMMEDIATE CURSOR_GRANTS.FINAL_TXT;

        END LOOP;
    END;
/

The above procedure compiled successfully, but while execution it is not getting into FOR loop to run the EXECUTE IMMEDIATE block but PL/SQL procedure compiled successfully.

What could be done to fix my procedure and make it work?

sabarish jackson
  • 149
  • 3
  • 10
  • If the `FOR` loop is not running that would be because the strings in `T.OWNER IN (...` are not users with tables. That looks like dummy data, but make sure that the real data is in uppercase, like most usernames are. – Jon Heller Nov 19 '15 at 05:34
  • I just changed it to 'aaa','bbb', etc. I have given existing usernames, even then its not working and are in UPPERCASE too – sabarish jackson Nov 19 '15 at 05:40
  • Does the query alone return results? Also there are a few typos in the code, for example `TXT` should be `FINAL_TXT`. – Jon Heller Nov 19 '15 at 06:11
  • Query alone returns a result set perfectly. I need to execute those result set one by one automatically. That's my goal. And I have commented the line that has `TXT` . That's not needed. I just used it for testing purpose. – sabarish jackson Nov 19 '15 at 06:17
  • There's still a lot of unnecessary code in there. But it seems like it should work. Keep making it smaller until you find the part that breaks. Try using a declare/begin/end PL/SQL block instead of a procedure to simplify things. Does the DBMS_OUTPUT at least display everything? – Jon Heller Nov 19 '15 at 06:25
  • Those unnecessary codes are just for testing purpose. Though it works. I will comment those unnecessary codes. And yeah that `DBMS_OUTPUT` before `FOR` loop works but doesn't list any `GRANTS`(returns nothing) query whereas `DBMS_OUTPUT` inside `FOR` loop doesn't work. – sabarish jackson Nov 19 '15 at 06:29

1 Answers1

0

All_USERS and ALL_TABLES should be changed to DBA_USERS and DBA_TABLES, Hence my procedure works.

Because the all_tables view shows the tables that the owner of the stored procedure has privileges on, so, unless the stored procedure is being created by a privileged user, or a user that already has those grants (with grant option so they can actually give the privileges), there may be nothing in all_tables that qualifies.

sabarish jackson
  • 149
  • 3
  • 10