Re: 911!! DDL in Forms 4.5 question

From: Patrick Flahan <flahan_at_earthlink.net>
Date: Tue, 4 May 1999 09:21:11 -0400
Message-ID: <7gmsb3$4bc$1_at_birch.prod.itd.earthlink.net>

You could also use a stored procedure in the database that uses DBMS_SQL to perform the create user and grant role actions. The advantage of doing it this way is that you are not dependent on Oracle Forms. So, if in the future you decide to move this action to a web based application (that isn't forms) you already have the code written and don't have to pull it out of forms and then re-write it.

I think I did something similar to what you are doing and I will include a copy of the solution that I went with below.

Hope this helps,
Patrick Flahan
flahan_at_earthlink.net

CREATE OR REPLACE PROCEDURE CreateUser(pcUserName IN VARCHAR2

,pcPassWord IN VARCHAR2
,pbConnect IN BOOLEAN
,pbResource IN BOOLEAN
,pcDefaultTableSpace IN VARCHAR2
,pcTempTableSpace IN VARCHAR2

                    )

/*

---

---

• PROGRAM: CreateUser
• FILE NAME: CreateUser.SQL
• PROGRAMMER: PF
• CREATE DATE: 5 Apr 99

  ---

  ---

CHANGE HISTORY

---

Date         Name                 Change
------------ -------------------- ------------------------------------------
----
05-apr-1999  Patrick Flahan       Initial creation

----------------------------------------------------------------------------

---

NOTES:
  This stored procedure is used to create users, assign the roles passed in and
  the appropriate tablespaces. Access to this procedure should be tightly   controlled.

*/
 IS
  cSqlStmt VARCHAR2(1000);

  --Procedure that will allow DDL actions to be performed in PL/SQL
  --This is a potentially dangerous procedure that should also be
  --controlled very tightly, as it could allow dropping, deleting,
  --truncating and other potentially harmfull actions possibel.

  PROCEDURE PerformAction(pcStatement IN VARCHAR2)    IS
    curAction INTEGER := DBMS_SQL.OPEN_CURSOR;     nReturn INTEGER;
  BEGIN
    DBMS_SQL.PARSE(curAction,pcStatement,DBMS_SQL.NATIVE);     nReturn := DBMS_SQL.EXECUTE(curAction);     DBMS_SQL.CLOSE_CURSOR(curAction);
  END PerformAction;

BEGIN
  cSqlStmt := 'CREATE USER '||pcUserName||' IDENTIFIED BY '||pcPassWord

              ||' DEFAULT TABLESPACE '||pcDefaultTableSpace
              ||' TEMPORARY TABLESPACE '||pcTempTableSpace;

  PerformAction(cSqlStmt);

  IF pbConnect = TRUE
  THEN
    cSqlStmt := 'GRANT CONNECT TO '||pcUserName;     PerformAction(cSqlStmt);
  END IF;   IF pbResource = TRUE
  THEN
    cSqlStmt := 'GRANT RESOURCE TO '||pcUserName;     PerformAction(cSqlStmt);
  END IF;   --Could add other default roles here

--EXCEPTION
-- WHEN OTHERS THEN END CreateUser;

<techwritery2k_at_my-dejanews.com> wrote in message
news:7glc36$tef$1_at_nnrp1.dejanews.com...
> creating a user_security module in forms and need to know how to

> create an oracle account/pwd and assign predefined roles in a > when_button_pressed trigger???I am running Forms [32 Bit] Version 4.5.7.1.6

>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

Received on Tue May 04 1999 - 15:21:11 CEST