Simulating Create or Replace for Users and Roles
I am really getting frustraited by all of the quirks in Oracle (my background is SQL Server) that are throwing me curve balls.. :) I am trying to create a procedure to enable me to create a user or role as part of a setup script. I don't want the script to stop if the user or role already exists. My first attempt based on [a StackOverflow.com post|http://stackoverflow.com/questions/1197236/create-or-replace-role] was:
CREATE OR REPLACE PROCEDURE Create_Role( role_name IN VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE'||role_name;
EXCEPTION
WHEN OTHERS THEN
-- ORA-01921: role name '%1' conflicts with another user or role name.
IF SQLCODE <> -01921 THEN
RAISE;
END IF;
END Create_Role;
/
GRANT EXECUTE ON Create_Role TO Public;
I ran into permissions issues around the dynamic execute statement for Create Role that I can't figure out why (since the calling user is the system user):
SQL Error: ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.CREATE_ROLE", line 8
01031. 00000 - "insufficient privileges"
I tried a difference approach as exceptions don't feel like they are suitable flow control in my experience:
CREATE OR REPLACE PROCEDURE Create_Role( Role_Name IN VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
DECLARE
Row_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO Row_Count FROM DBA_Roles WHERE UPPER(Rolename) = Role_Name;
IF (Row_Count == 0) THEN
EXECUTE IMMEDIATE 'CREATE ROLE '||role_name;
END IF;
END Create_Role;
/
GRANT EXECUTE ON Create_Role TO Public;
Now I am getting an "ORA-00942: table or view does not exist" because I do not have access to DBA_Roles directly (but through a role). I then read that procedures aren't able to calculate effective permissions from the roles when compiling ([www.adp-gmbh.ch/ora/err/ora_00942.html]). Honestly I'm not sure why it matters as the only thing that should be relevant is the permissions of the calling user at runtime.
Can someone help me figure out either the invalid permission
Edited by: user11095648 on Aug 26, 2009 11:54 AM
Edited by: user11095648 on Aug 26, 2009 11:54 AM