I am calling a function CREATE_ROLE (P_ROLE IN VARCHAR2) that executes the create statement. An abstracted extract of that code looks like this. Create a dynamic SQL and execute it.
DECLARE
P_CREATE_COMMAND VARCHAR2(400);
BEGIN
P_CREATE_COMMAND := 'CREATE ROLE ' || P_ROLE ;
EXECUTE IMMEDIATE P_CREATE_COMMAND;
END
The code command it runs looks like this.
CREATE ROLE NIRVANA
Inside the function, we log the USER context and then progress through steps to create the role.
We have temporarily granted APEX_PUBLIC_USER to our BI ADMINS role with the following permissions to get the function working. Once we get it to work, we will figure out exactly what limited permissions are needed and remove the rest.
It seems like this should work. The user context executing the CREATE ROLE command has CREATE ROLE privileges.
Any ideas?
Rob