This discussion is archived
1 Reply Latest reply: Sep 9, 2010 5:47 AM by 795786 RSS

PL/SQL to create a user, role Dilemma

795786 Newbie
Currently Being Moderated
I must create a user and role in my schema before deploying the application.

The way I was going about this was inserting a new row into the applications table like so :

INSERT INTO ORA_ASPNET_APPLICATIONS (ApplicationName, LoweredApplicationName, ApplicationId, Description) VALUES ('Security', 'security', default , 'Security App');

Designating 'default' for the SYS_GUID/Raw Column.

The next step is that I must create a new role and a new user for this role.

In Sql Server, I was simply designating the Guid for the respective columns in each row, but with Oracle, I belive a more elegant solution is needed.

Two options - select the SysGuid out of the applications table row, and use that value for the other applicationid columns in Users and Roles ?

OR

Execute the Create User and Create Role Function

I am new to Oracle, and Am not sure of the standard for executing either of them - any one have any examples / suggestions ?

Thanks
Tom
  • 1. Re: PL/SQL to create a user, role Dilemma
    795786 Newbie
    Currently Being Moderated
    Like all other posts here, I figured out a way...

    For others facing the dilemma :

    --ORA_ASPNET_APPLICATIONS

    INSERT INTO ORA_ASPNET_APPLICATIONS (ApplicationName, LoweredApplicationName, ApplicationId, Description) VALUES ('Security', 'security', '0E073F83C65E4F15A67BAE61566129F1' , Security');
    /


    DECLARE
    sql_stmt_user VARCHAR2(2000);
    aid RAW(32);
    anonUser CONSTANT NUMBER :=0;
    adminDate DATE:=CURRENT_DATE;
    adminUser NVARCHAR2(256 CHAR) := 'Administrator';
    adminLoweredUser NVARCHAR2(256 CHAR) := 'administrator';
    mobileAlias NVARCHAR2(16 CHAR):='';



    BEGIN
    SELECT TJ.ora_aspnet_applications.ApplicationId INTO aid FROM TJ.ora_aspnet_applications WHERE LOWEREDAPPLICATIONNAME ='security';
    sql_stmt_user := 'INSERT INTO ORA_ASPNET_USERS(
    APPLICATIONID,
    ISANONYMOUS,
    LASTACTIVITYDATE,
    USERNAME,
    LOWEREDUSERNAME,
    MOBILEALIAS,
    USERID)
    VALUES (:1, :2,:3,:4,:5,:6,default)';
    EXECUTE IMMEDIATE sql_stmt_user USING aid, anonUser, adminDate, adminUser, adminLoweredUser, mobileAlias;
    END;
    /
    --CREATE MEMBERSHIP ENTRY
    DECLARE
    sql_stmt_mem VARCHAR2(2000);
    aid RAW(32);
    usid RAW(32);
    pass NVARCHAR2(128 CHAR):='Administrator1!';
    salt NVARCHAR2(128 CHAR):='2oNjBRBAUpnXDIrzpDL14A==';
    now DATE := CURRENT_DATE;
    mdate DATE := to_date('-4712-1-1 0:0:0', 'syyyy-mm-dd hh24: mi: ss');
    BEGIN

    SELECT TJ.ora_aspnet_applications.ApplicationId INTO aid FROM TJ.ora_aspnet_applications WHERE LOWEREDAPPLICATIONNAME ='security';
    SELECT TJ.ora_aspnet_users.userid INTO usid FROM TJ.ora_aspnet_users WHERE LOWEREDUSERNAME ='administrator';
    sql_stmt_mem := 'INSERT INTO tj.ORA_ASPNET_MEMBERSHIP (ApplicationId,
    UserId,
    Password,
    PasswordFormat,
    PasswordSalt,
    MobilePIN,
    Email,
    LoweredEmail,
    PasswordQuestion,
    PasswordAnswer,
    IsApproved,
    IsLockedOut,
    CreateDate,
    LastLoginDate,
    LastPasswordChangedDate,
    LastLockoutDate,
    FAILEDPWDATTEMPTCOUNT,
    FAILEDPWDATTEMPTWINSTART,
    FAILEDPWDANSWERATTEMPTCOUNT,
    FAILEDPWDANSWERATTEMPTWINSTART)
    VALUES (:1, :2, :3, 0, :4, '''', ''tom.johnsen@globalsubmit.com'', ''tom.j@company.com'', ''question'', ''answer'', 1, 0, :5, :6, :7, :8, 0, :9, 0, :10)';
    EXECUTE IMMEDIATE sql_stmt_mem using aid, usid, pass, salt, now, now, now, mdate, MDATE, MDATE;
    END;
    /

    --CREATE ADMIN ROLE
    DECLARE
    sql_stmt_role VARCHAR2(2000);
    aid RAW(32);
    BEGIN

    SELECT TJ.ora_aspnet_applications.ApplicationId INTO aid FROM TJ.ora_aspnet_applications WHERE LOWEREDAPPLICATIONNAME ='security';
    sql_stmt_role := 'INSERT INTO tj.ORA_ASPNET_ROLES (ApplicationId,
    ROLEID,
    ROLENAME,
    LOWEREDROLENAME,
    DESCRIPTION)
    VALUES(:1, default, ''Administrator'', ''administrator'', "Built-In Security Administrator'')';
    EXECUTE IMMEDIATE sql_stmt_role using aid;
    END;
    /
    --ADD USER TO ROLE
    DECLARE
    sql_stmt_uir VARCHAR2(2000);
    rlid RAW(32);
    usid RAW(32);
    BEGIN

    SELECT TJ.ora_aspnet_users.userid INTO usid FROM TJ.ORA_ASPNET_USERS WHERE LOWEREDUSERNAME = 'administrator';
    SELECT TJ.ora_aspnet_roles.roleid INTO rlid FROM TJ.ORA_ASPNET_ROLES WHERE LOWEREDROLENAME = 'administrator';
    sql_stmt_uir := 'INSERT INTO tj.ORA_ASPNET_USERSINROLES (USERID,
    ROLEID)
    VALUES(:1, :2)';
    EXECUTE IMMEDIATE sql_stmt_uir using usid, rlid;
    END
    ;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points