How do I grant the execute privilege on multiple objects in Oracle? The following command (assuming that I am USER1) selects the objects I want to give access to:
select object_name from all_objects
where owner = 'USER1'
and REGEXP_LIKE(object_name, '^TEST')
..but trying to use it in a grant statement doesn't work:
grant execute on
(select object_name from all_objects
where owner = 'USER1' and
REGEXP_LIKE(object_name, '^TEST'))
to USER2;
-- the error is:
SQL Error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
Another answer has suggested that a dynamic query can be used in a pl/sql script, but I'm looking for a plain SQL command, like is seemingly possible with SQLServer.
Is there a way without writing a procedure to do it?
