Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

How to write a procedure to execute set of queries automatically

3123956
3123956 Member Posts: 9
edited Nov 23, 2015 11:46PM in SQL & PL/SQL

@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 ( QRY_TEXT OUT VARCHAR )
 IS QUERIES_TXT VARCHAR2(3000); 
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);
QRY_TEXT:='LOOP';
QRY_TEXT:=CURSOR_GRANTS.FINAL_TXT;
EXECUTE IMMEDIATE CURSOR_GRANTS.FINAL_TXT;
EXECUTE IMMEDIATE CURSOR_GRANTS.TXT;
QRY_TEXT:=CURSOR_GRANTS.FINAL_TXT;
--INTO QUERIES_TXT;
--DBMS_OUTPUT.PUT_LINE(QUERIES_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?

KPR3123956
«1

Answers

  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Nov 19, 2015 3:07AM

    Perhaps, it is better to debug where the logic you implemented fails.

    If you are just trying to grant privileges to other schemas on your database to query your tables, following should work: However, beware of what you are trying to do. This code will grant permissions to all users mentioned in filter of DBA_USERS, which may not be intended. For this reason, its best to identify the users who need appropriate permissions and fire the sql statements with help of DBA so that you do not compromise the data.

    create table test_table (col1 number);
    insert into test_table values (1);
    
    commit;
    
    declare
      cursor cur is
      select 'grant select on test_table to ' || username stmt
      from dba_users
      where username like 'some_user%';
     
    begin   for idx in cur loop
        execute immediate idx.stmt;
      end loop; end;
    anonymous block completed
    
    <<Login to SOME_USER_1>>
    
    select *
      from tab_owner.test_table;
    
    COL1
    ----------
    1
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,930 Red Diamond
    edited Nov 19, 2015 7:40AM

    Hi,

    3123956 wrote:
    ...
    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')
    ...
    
    

    Remember, anything inside quotes is case-sensitive.  Do you really have schemas with lower-case letters?  Look at the output of

    SELECT DISTINCT   owner
    FROM      all_tables
    ORDER BY  owner;
    
    

    If this query shows the owner as AAA (upper case), then you need to say things like

    WHERE T.OWNER IN
    ('AAA',  -- not 'aaa'
    ...
    
    

    Outside of quotes, you can use aaa and AAA interchangeably; you can also use Aaa, aAA, AaA or other combinations, so

    SELECT  ename
    FROM    scott.emp;
    

    works, even though the table name is EMP (and the column is actually ENAME, and the schema is SCOTT.

  • 3123956
    3123956 Member Posts: 9
    edited Nov 19, 2015 7:58AM

    'aaa','bbb','ccc','ddd','eee','fff','ggg','hhh','iii'. I changed it likewise to make it readable for you people. Actually in my script I have the schemas that are present in the DB and all are in UPPERCASE.

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 19, 2015 10:02AM

    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.  What does a simple query on all_tables get if done by the procedure owner?

    Having said that, I think that you approach is needlessly complicated.  I would create a role, give the privileges to the role and grant the role to the other user, unless the other users are likely to use each others objects in stored procedures.  If you really do need to do direct grants, then I would do something like:

    begin
      for urec in (select username
                    from dba_users
                    where username in ('AAA','BBB','CCC','DDD','EEE','FFF',
                                      'GGG','HHH','III')) loop
          for trec in (select owner, table_name
                      from dba_tables
                      where owner in ('AAA','BBB','CCC','DDD','EEE','FFF',
                                      'GGG','HHH','III') and
                            owner <> urec.username) loop
            execute immediate 'grant select, insert, update, delete on '||
                            rec.owner||'.'||rec.table_name||
                            ' to '||urec.username;
          end loop;
      end loop;
    end;
    
    

    John

    3123956
  • 3123956
    3123956 Member Posts: 9
    edited Nov 20, 2015 4:10AM

    @John Spencer That works fine bro. Thanks.

  • 3123956
    3123956 Member Posts: 9
    edited Nov 20, 2015 4:14AM

    @John Spencer Yours is also working. And what's wrong with mine is "ALL_USERS should be changed to DBA_USERS" and "ALL_TABLES to DBA_TABLES"

  • 3123956
    3123956 Member Posts: 9
    edited Nov 20, 2015 5:16AM

    @John Spencer hey I just added DBMS_OUTPUT.PUT_LINE(

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

                            rec.owner||'.'||rec.table_name||

                            ' to '||urec.username)

  • 3123956
    3123956 Member Posts: 9
    edited Nov 20, 2015 5:19AM

    And it worked. But execute immediate block gives the following error.

    John Spencer hey I just added

                                               "DBMS_OUTPUT.PUT_LINE(

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

                            rec.owner||'.'||rec.table_name||

                            ' to '||urec.username)"


    And it worked. But "execute immediate" block gives the following error.


    'ORA-00911: invalid character

    ORA-06512: at "SABARISH.GRANTS_PROC", line 24

    ORA-06512: at line 2

    Process exited.'

  • 3123956
    3123956 Member Posts: 9
    edited Nov 20, 2015 5:28AM
    3123956 wrote:
    
    And it worked. But execute immediate block gives the following error.
    
    

    John Spencer hey I just added

                                               "DBMS_OUTPUT.PUT_LINE(

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

                            rec.owner||'.'||rec.table_name||

                            ' to '||urec.username)"


    And it worked. But "execute immediate" block gives the following error.


    'ORA-00911: invalid character
    ORA-06512: at "SABARISH.GRANTS_PROC", line 24
    ORA-06512: at line 2
    Process exited.'
    

    Sorry, it returns table (or) View doesn't exist. Not the above mentioned error.

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Nov 20, 2015 6:18AM

    Why nest one loop inside another when you can do it in one query:

    begin

      for urec in (select u.username, t.owner, t.table_name

                   from dba_users u, dba_tables t

                   where u.username in ('AAA','BBB','CCC','DDD','EEE','FFF',

                                        'GGG','HHH','III')

                   and t.owner in ('AAA','BBB','CCC','DDD','EEE','FFF',

                                      'GGG','HHH','III')

                   and t.owner <> u.username) loop

            execute immediate 'grant select, insert, update, delete on '||

                            urec.owner||'.'||urec.table_name||

                            ' to '||urec.username;

      end loop;

    end;

This discussion has been closed.