Help : DBMS_SQL and CREATE USER Problems

Hi

I've Oracle v7.2 (if it helps) and am trying to write a procedure to create a new user, and setup their grants and synonyms, because using a
script is a bit klunky. I appreciate
that the privileges of the person that created the procedure apply when the procedure is run, even if this is another user, but am experiencing difficulty in both finding exaples across the net showing this sort of operation, and in solving the problem myself using the oracle CD ROMs and Oracle Press books I have.

I imagine that this is a fairly typical operation that needs to be performed, as I currently have done it using sed and a standard script where I throw in the new user name and password, and then run the script
using sqlplus.

Thanks In Advance

Mike.

|Mike Sweetman        +-----+ Tel     : +44 (0)1344 413155    |
|Science Systems Ltd  |     | Fax     : +44 (0)1344 413186    |

Save energy ==> :-))

Thus far:-

CREATE OR REPLACE PROCEDURE sp_create_user (as_currentusername IN varchar2, as_username IN varchar2, as_userpw IN varchar2, as_usert ype IN varchar2 ) as
li_cid integer;
BEGIN
--

• Drop The User
• Does the user already exist - how do you find out ?
  --
  

   li_cid := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(li_cid, 'grant drop user to ' || as_currentusername, dbms_sql.v7);

   DBMS_SQL.CLOSE_CURSOR(li_cid);

   li_cid := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(li_cid, 'drop user ' || as_username || ' cascade', dbms_sql.v7);

   DBMS_SQL.CLOSE_CURSOR(li_cid);

--

• Create The User
  --
  

   li_cid := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(li_cid, 'grant create user to ' || as_currentusername,
dbms_sql.v7);

   DBMS_SQL.CLOSE_CURSOR(li_cid);

   li_cid := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(li_cid, 'create user ' || as_username || ' identified by
' || as_userpw, dbms_sql.v7);

   DBMS_SQL.CLOSE_CURSOR(li_cid);

--

• Give User Connect
• Connect is a role - and roles don't apply - what would be equivalent ?
• Failure occurs during the following section (should it be uncommented)
  --
  
• li_cid := DBMS_SQL.OPEN_CURSOR;
• DBMS_SQL.PARSE(li_cid, 'grant create session to ' || as_currentusername, dbms_sql.v7);
• DBMS_SQL.CLOSE_CURSOR(li_cid);
  --
  
• li_cid := DBMS_SQL.OPEN_CURSOR;
• DBMS_SQL.PARSE(li_cid, 'grant connect to ' || as_username, dbms_sql.v7);
• DBMS_SQL.CLOSE_CURSOR(li_cid);

--

• Create the grants on tables
  --
  
• etc

end;

-------------------==== Posted via Deja News ====-----------------------

      http://www.dejanews.com/ Search, Read, Post to Usenet Received on Fri Oct 03 1997 - 00:00:00 CDT