Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: Oracle equivalent for creating user

    Hi,
    I am trying to automate something in Oracle that has been automated in mssql and I was wondering if somebody can give me the equivalent to the below in Oracle. I am familiar with the syntax of creating users etc.. in Oracle. Thanks.

    In MSSQL I can do something like this in a sql file...

    if exists (select 1 from syslogins where name = 'scott')
    exec sp_droplogin @loginame='scott';
    exec sp_addlogin @loginame='scott', @passwd='tiger', @defdb='prod';
    else
    exec sp_addlogin @loginame='scott', @passwd='tiger', @defdb='prod';

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'd suggest something like this even if I didn't test it as I'm not currently connected. Idea could be fine anyway. I'm not sure about dba_users but you can use any view or table where all users are hold.
    Code:
    declare
    	v_cnt integer;
    	v_username VARCHAR2(30) := 'SCOTT';
    	v_password VARCHAR2(30) := 'tiger';
    	V_DEFAULT_TSP VARCHAR2(30) := 'USERS';
    	V_TEMP_TSP VARCHAR2(30) := 'TEMP';
            V_DEFAULT_ROLE VARCHAR2(30) := 'connect, resource';
    begin
    	select count(*) into v_cnt from FROM dba_users
    	where user_name = v_username;
    
    	if v_cnt = 0 then
      		execute immediate 'create user ' || v_username ||                    
    		' identified by ' || v_password ||                    
    		' default tablespace ' || V_DEFAULT_TSP ||                     
    		' temporary tablespace ' || V_TEMP_TSP;
    
                        execute immediate  'grant '|| V_DEFAULT_ROLE ||' to '||v_username;
    	else
    		execute immediate 'drop user' || v_username || 'cascade';
    
      		execute immediate 'create user ' || v_username ||                    
    		' identified by ' || v_password ||                    
    		' default tablespace ' || V_DEFAULT_TSP ||                     
    		' temporary tablespace ' || V_TEMP_TSP;
    
                        execute immediate  'grant '|| V_DEFAULT_ROLE ||' to '||v_username;
    
    	endif;
    end;
    Last edited by madafaka; 11-11-05 at 13:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •