Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to write a procedure to execute set of queries automatically

@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?
Answers
-
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
-
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.
-
'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.
-
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
-
@John Spencer That works fine bro. Thanks.
-
@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"
-
@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.
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 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.
-
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;