1 Reply Latest reply: Sep 9, 2010 7:47 AM by 795786 RSS

    PL/SQL to create a user, role Dilemma

    795786
      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
          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
          ;
          /