I'm fairly new to the PL/SQL game and I'm attempting to come up with a short script to create logins and grant read-only/read-write privs (making the necessary grants as necessary). However, I am encountering variable scoping issues with it. Can someone please help what I might be doing wrong?
Snipped is as follows:
DECLARE CNT INTEGER;
BEGIN
SELECT COUNT(*)
INTO CNT
FROM dba_users
WHERE username = :ParamUserName
IF (CNT > 0) THEN
IF (INSTR(:ParamSelectedRole, 'WRITE') = 0) THEN
REVOKE UNLIMITED TABLESPACE FROM :ParamUserName;
REVOKE READ_WRITE FROM :ParamUserName;
GRANT READ_ONLY TO :ParamUserName;
ELSE
GRANT UNLIMITED TABLESPACE TO :ParamUserName;
END IF;
ELSE
CREATE USER :ParamUserName DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP IDENTIFIED BY ":ParamUserPassword" PROFILE ELEV_USER;
ALTER USER :ParamUserName PASSWORD EXPIRE;
GRANT :ParamSelectedRole TO :ParamUserName;
GRANT CREATE SESSION TO :ParamUserName;
IF (INSTR(:ParamSelectedRole, 'WRITE') > 0 ) THEN
GRANT UNLIMITED TABLESPACE TO :ParamUserName;
END IF;
END IF;
END;
I'm doing the variable assignments using ODP.NET and parameterizing them using the OracleCommand.Parameters.Add()
All the DDL statements (GRANT, REVOKE, CREATE and ALTER) need to be in an EXECUTE IMMEDIATE such as
EXECUTE IMMEDIATE 'REVOKE UNLIMITED TABLESPACE FROM '||:ParamUserName;
and
EXECUTE IMMEDIATE 'ALTER USER '||:ParamUserName||' PASSWORD EXPIRE';
Actually, I'd simplify it a bit and put all the external variables in one place at the start.
DECLARE
V_CNT INTEGER;
V_USER VARCHAR2(30);
V_ROLE VARCHAR2(30);
BEGIN
--
V_USER := :ParamUserName;
V_ROLE := :ParamSelectedRole;
V_PWD := :ParamUserPassword;
--
SELECT COUNT(*)
INTO V_CNT
FROM dba_users
WHERE username = v_user
IF (CNT > 0) THEN
IF (INSTR(v_role, 'WRITE') = 0) THEN
EXECUTE IMMEDIATE 'REVOKE UNLIMITED TABLESPACE FROM '||v_user;
EXECUTE IMMEDIATE 'REVOKE READ_WRITE FROM '||v_user;
EXECUTE IMMEDIATE 'GRANT READ_ONLY TO '||v_user;
ELSE
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO '||v_user;
END IF;
ELSE
EXECUTE IMMEDIATE 'CREATE USER '||v_user||
'DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP '||
'IDENTIFIED BY '||v_pwd||' PROFILE ELEV_USER';
EXECUTE IMMEDIATE 'ALTER USER '||v_user||' PASSWORD EXPIRE;
EXECUTE IMMEDIATE 'GRANT '||v_role||' TO '||v_user;
EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||v_user;
IF (INSTR(v_role, 'WRITE') > 0 ) THEN
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO '||v_user;
END IF;
END IF;
END;