Procedure to create user, grants and synonyms for a given username
SamBFeb 1 2007 — edited Feb 2 2007Just wondering how you would code a procedure that first creates a user then grants a user certain priveledges and then creates private synonyms for that user.
ie.
PROCEDURE cre_usr(p_username IN VARCHAR2, p_password IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE USER ' || p_username || ' PROFILE "DEFAULT"
IDENTIFIED BY ' || p_password || ' DEFAULT TABLESPACE "USERS"
ACCOUNT UNLOCK';
EXECUTE IMMEDIATE 'grant create session to '|| p_username;
EXECUTE IMMEDIATE 'GRANT "SOME_ROLE1" TO '|| p_username;
EXECUTE IMMEDIATE 'GRANT "SOME_ROLE2" TO '||p_username;
EXECUTE IMMEDIATE 'GRANT "SOME_ROLE3" TO '||p_username;
EXECUTE IMMEDIATE 'GRANT "SOME_ROLE4" TO '||p_username;
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || p_username;
EXECUTE IMMEDIATE 'create synonym ADD_CHECK_DIGIT for my_schema.ADD_CHECK_DIGIT';
END;
Will this script work? I will test it myself but since I want to use this code on production data I want to make sure that it is the correct way to do it since what may work in my test environment could break in production.
Message was edited by:
SamB