How to write a procedure to execute set of queries automatically

3123956 · Nov 18, 2015 11:10PM

@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?

Purvesh K · 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 · Nov 19, 2015 7:33AM

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 · 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 · 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 · Nov 20, 2015 4:10AM

@John Spencer That works fine bro. Thanks.

3123956 · 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 · 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 · 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 · 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 · 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;

Forum Stats

Quick Links

Categories

How to write a procedure to execute set of queries automatically

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How to write a procedure to execute set of queries automatically

Answers