| PLSQL grant script not working correctly [message #653319] |
Fri, 01 July 2016 12:35  |
 |
ICB555
Messages: 2
Registered: July 2016
|
Junior Member |
|
|
I'm getting table or view doesn't exist on line 20 of this script (The grant script) and I can't figure out what's going on with it. Fairly new at this.
--part 1, disables constraints
DECLARE
TYPE cur_typ IS REF CURSOR;
c cur_typ;
select_cmd varchar2(2000);
grant_cmd varchar2(2000);
i_table_name varchar2(1000);
i_user_name varchar2(1000);
BEGIN
dbms_output.enable();
select_cmd := ' select du.username, dt.table_name
from dba_tables dt, dba_users du
where du.common=''NO''
';
open c for select_cmd;
dbms_output.put_line(select_cmd);
loop
fetch c into i_table_name, i_user_name;
exit when c%NOTFOUND;
grant_cmd := 'grant select on ' || i_user_name || '.' || i_table_name || ' to alm_ro';
execute immediate grant_cmd;
--dbms_output.put_line(grant_cmd);
end loop;
close c;
end;
/
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PLSQL grant script not working correctly [message #653363 is a reply to message #653323] |
Mon, 04 July 2016 02:56  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You don't need a dynamic select for this, can simply be:
BEGIN
dbms_output.enable();
FOR rec IN (SELECT 'grant select on ' || du.username || '.' || dt.table_name || ' to alm_ro' as grant_cmd
FROM dba_tables dt
JOIN dba_users du ON du.username=dt.owner
WHERE du.common = 'NO') LOOP
dbms_output.put_line(rec.grant_cmd);
execute immediate grant_cmd;
END LOOP;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
