Home » SQL & PL/SQL » SQL & PL/SQL » How to execute dynamically the permission grant using AWS RDS custom procedure (merged) (Oracle rds 12.1.0.v1)
How to execute dynamically the permission grant using AWS RDS custom procedure (merged) [message #684769] Tue, 17 August 2021 16:10 Go to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Team,

I reqeust you to guide on this. This is relaed to RDS Oracle, I am trying to grant permissinos using AWS RDS custom procedure, i get this error when i call the EXECUTE IMMEDIATE comand. request you to guide on the same.

Thanks,

I created pl/sql block like this and its giving output.

SQL> declare
v_username VARCHAR2(30) := upper('USER001');
begin
FOR c in
2 3 4 5 ( select 'execute rdsadmin.rdsadmin_util.grant_sys_object('||'||table_name||'||','||'||v_username||'||','||ALL||','||'true'||')' as cmd from DBA_TAB_PRIVS WHERE TABLE_NAME = 'MAP_OBJECT')
loop
dbms_output.put_line('print.....'||c.cmd);
end loop;
end;
/
6 7 8 9 10 print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)

PL/SQL procedure successfully completed.


Further I run the output, it works well

SQL> execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true);
grant ALTER on SYS."MAP_OBJECT" to "USER001" with grant option
grant DEBUG on SYS."MAP_OBJECT" to "USER001" with grant option
grant DELETE on SYS."MAP_OBJECT" to "USER001" with grant option
grant FLASHBACK on SYS."MAP_OBJECT" to "USER001" with grant option
grant INSERT on SYS."MAP_OBJECT" to "USER001" with grant option
grant ON COMMIT REFRESH on SYS."MAP_OBJECT" to "USER001" with grant option
grant QUERY REWRITE on SYS."MAP_OBJECT" to "USER001" with grant option
grant READ on SYS."MAP_OBJECT" to "USER001" with grant option
grant SELECT on SYS."MAP_OBJECT" to "USER001" with grant option
grant UPDATE on SYS."MAP_OBJECT" to "USER001" with grant option

PL/SQL procedure successfully completed.


NOw, I would like to perform the execution using EXECUTE IMMEDIATE comamnd but it fails

SQL> declare
v_username VARCHAR2(30) := upper('USER001');
begin
FOR c in
( select 'execute rdsadmin.rdsadmin_util.grant_sys_object('||'||table_name||'||','||'||v_username||'||','||ALL||','||'true'||')' as cmd from DBA_TAB_PRIVS WHERE TABLE_NAME = 'MAP_OBJECT')
loop
EXECUTE IMMEDIATE c.cmd;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7


SQL>

Please guide me


How to execute dynamically the permission grant using AWS RDS custom procedure [message #684770 is a reply to message #684769] Tue, 17 August 2021 16:10 Go to previous messageGo to next message
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Team,

I reqeust you to guide on this. This is relaed to RDS Oracle, I am trying to grant permissinos using AWS RDS custom procedure, i get this error when i call the EXECUTE IMMEDIATE comand. request you to guide on the same.

Thanks,

I created pl/sql block like this and its giving output.

SQL> declare
v_username VARCHAR2(30) := upper('USER001');
begin
FOR c in
2 3 4 5 ( select 'execute rdsadmin.rdsadmin_util.grant_sys_object('||'||table_name||'||','||'||v_username||'||','||ALL||','||'true'||')' as cmd from DBA_TAB_PRIVS WHERE TABLE_NAME = 'MAP_OBJECT')
loop
dbms_output.put_line('print.....'||c.cmd);
end loop;
end;
/
6 7 8 9 10 print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)
print.....execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true)

PL/SQL procedure successfully completed.


Further I run the output, it works well

SQL> execute rdsadmin.rdsadmin_util.grant_sys_object('MAP_OBJECT','USER001','ALL',true);
grant ALTER on SYS."MAP_OBJECT" to "USER001" with grant option
grant DEBUG on SYS."MAP_OBJECT" to "USER001" with grant option
grant DELETE on SYS."MAP_OBJECT" to "USER001" with grant option
grant FLASHBACK on SYS."MAP_OBJECT" to "USER001" with grant option
grant INSERT on SYS."MAP_OBJECT" to "USER001" with grant option
grant ON COMMIT REFRESH on SYS."MAP_OBJECT" to "USER001" with grant option
grant QUERY REWRITE on SYS."MAP_OBJECT" to "USER001" with grant option
grant READ on SYS."MAP_OBJECT" to "USER001" with grant option
grant SELECT on SYS."MAP_OBJECT" to "USER001" with grant option
grant UPDATE on SYS."MAP_OBJECT" to "USER001" with grant option

PL/SQL procedure successfully completed.


NOw, I would like to perform the execution using EXECUTE IMMEDIATE comamnd but it fails

SQL> declare
v_username VARCHAR2(30) := upper('USER001');
begin
FOR c in
( select 'execute rdsadmin.rdsadmin_util.grant_sys_object('||'||table_name||'||','||'||v_username||'||','||ALL||','||'true'||')' as cmd from DBA_TAB_PRIVS WHERE TABLE_NAME = 'MAP_OBJECT')
loop
EXECUTE IMMEDIATE c.cmd;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7


SQL>

Please guide me


Re: How to execute dynamically the permission grant using AWS RDS custom procedure [message #684772 is a reply to message #684769] Wed, 18 August 2021 00:05 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

EXECUTE is a SQL*Plus command NOT a SQL or PL/SQL one.
Replace EXECUTE by BEGIN/END;
Example:
EXECUTE foo
is
BEGIN foo; END;
Please read How to use [code] tags and make your code easier to read.
Previous Topic: SYSAUX IMPACT ON PERFORMANCE
Next Topic: Creating time rows that doesn't cross midnight
Goto Forum:
  


Current Time: Thu Mar 28 15:21:06 CDT 2024