【Oracle】簡易的にユーザーを作成

DBA権限持ってるユーザーから実行しないとできないと思うけど、
とりあえずユーザーを作成する動的SQLを載せておこうと思う。
権限設定とかいろいろ面倒なので、テスト環境で実行するときなどは考えなくて済むのではないかと思う。

CREATE OR REPLACE PROCEDURE USER_CREATE (
    USER_NAME    IN VARCHAR2,
    USER_SPACE   IN VARCHAR2 DEFAULT 'USERS',
    TEMP_SPACE   IN VARCHAR2 DEFAULT 'TEMP',
    OUT_STS      OUT VARCHAR2
) AS

/*改行*/
    WCRLF   VARCHAR2(4) := CHR(13)
                            || CHR(10);
BEGIN
    OUT_STS   := 'CREATE USER '
               || USER_NAME
               || ' '
               || WCRLF
               || 'IDENTIFIED BY '
               || USER_NAME
               || ' '
               || WCRLF
               || 'DEFAULT TABLESPACE '
               || USER_SPACE
               || ' '
               || WCRLF
               || 'TEMPORARY TABLESPACE '
               || TEMP_SPACE
               || ' '
               || WCRLF
               || 'PROFILE DEFAULT';

/*ユーザー*/
    EXECUTE IMMEDIATE 'CREATE USER '
                      || USER_NAME
                      || ' '
                      || WCRLF
                      || 'IDENTIFIED BY '
                      || USER_NAME
                      || ' '
                      || WCRLF
                      || 'DEFAULT TABLESPACE '
                      || USER_SPACE
                      || ' '
                      || WCRLF
                      || 'TEMPORARY TABLESPACE '
                      || TEMP_SPACE
                      || ' '
                      || WCRLF
                      || 'PROFILE DEFAULT';

/*権限*/
    EXECUTE IMMEDIATE 'GRANT DBA TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
    EXECUTE IMMEDIATE 'GRANT RESOURCE TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TABLE TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
    EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
    EXECUTE IMMEDIATE 'GRANT CREATE USER TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO '
                      || USER_NAME
                      || ' WITH ADMIN OPTION';
END USER_CREATE;

余談だけど、Oracle 12cではデフォルトでパスワードの有効期限が180日になっているらしい。
それを調べる方法を載せておこう

SELECT * FROM DBA_PROFILES
WHERE RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

必要であれば無期限、有効期限を設定するSQLも

--パスワードの有効期限を無期限にする
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--パスワードの有効期限を500日にする 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 500;