Any way to grant roles via proc?
820011Apr 11 2011 — edited Apr 11 2011I have a procedure owned by a user that has DBA permissions. The procedure grants a role the ability to select in all tables in the specified schema. This works in the schema of the user. However, I get an insufficient permissions error when I try to grant a role in another schema select rights in all tables in the schema. If I run the grant statements outside of the procedure as the DBA user, the grant works just fine. Is it true that a stored procedure needs to be granted access to the role or object first and you cannot dynamically set role permissions via a stored procedure? I want to be able to automate the process of assigning role privileges to objects in various schemas and I'm wondering how people achieve this? Shell scripting?