Simplified User Maintenance

June 26, 2007

by JP Vijaykumar

On a regular basis, I create multiple userids in Oracle databases. Every userid requires the execution of three sql statements:

01 create user ... identified by ... account unlock password expire;

02 grant create session to ...;

03 grant role_name to ...;

Every time I create a group of new users, I need to execute this set of statements; I would like to loop through the list of names and create the userids and grant necessary permissions.

How can I loop through the list of names (say 10 usernames)?

These are the options:

01 Load the list of names into a table, using the sql loader utility and fetch the data into a cursor loop to process the records.

02 Create an external table and select the usernames into a cursor and loop through.

03 Edit the list of names manually in a notepad and run the edited file from sqlplus.

04 Write script in unix, perl or java to read the usernames from the flat file and connect to Oracle database to process.

05 Write a pl/sql procedure to read the list of usernames from a flat file, using the utl_file option and create the userids.

06 Parse the string of names and create the userids.

The first five options, I leave to the readers’ choice and imagination; I will present my work on option 6, using a pl/sql string parser.

For more information on string parser, pls refer to my script on pl/sql string parser. I modified the script, so that single codes are preserved.

To demonstrate the script, first I created the users using the pl/sql string parser and selected the usernames from DBA_USERS view.

Next I dropped the users using the pl/sql string parser with the necessary modifications and showed the results from DBA_USERS view.

set serverout on size 1000000
v_word varchar2(4000):='jp1 jp2 jp3 jp4 jp5 jp6 jp7 jp8 jp9 jp10';
    v_num number:=0;
    v_len number:=0;
    v_inc number:=0;
    v_str varchar2(100);
    v_sql varchar2(1000);
   select length(v_word) into v_len from dual;
   while ( v_num <= v_len ) loop
   /*v_sql:='select instr(substr('''||v_word||''', ( '||v_num||' + 1 ),'||v_len||'),
   '||''' '''||') from dual'; */
   v_sql:='select instr(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),
   '||v_len||'),'||chr(39)||' '||chr(39)||') from dual';
   execute immediate v_sql into v_inc;
   if ( v_inc = 0 ) then
   v_inc := v_len - v_num + 1;
   end if;
 v_sql:='select upper(substr('||chr(39)||v_word||chr(39)||', ( '||v_num||' + 1 ),
 ( '||v_inc||' - 1 ))) from dual';
   execute immediate v_sql into v_str;
   --v_sql:='drop user '||v_str;
   v_sql:='create user '||v_str||' identified by abc123 account unlock password expire';
   execute immediate v_sql;
   v_sql:='grant create session to '||v_str;
   execute immediate v_sql;
   v_sql:='grant scott_select to '||v_str;
   execute immediate v_sql; 
      v_num := v_num + v_inc;
      end loop;

select username from dba_users 
          where username like 'JP%' order by 1;

I commented out the userids’ creation dynamic sql statements and included drop userids dynamic sql in the script to drop all of the 10 users created. After executing the script, I selected the userids from dba_users:

select username from dba_users 
where username like 'JP%' order  by 1;
no rows selected

While dropping existing users from a database, I can select the usersname from the data dictionary view on specified conditions and loop through to drop the users from the database.

This is not the case with the creation of new users. The new users’ ids are not present in the data dictionary view. As such, I cannot select the data into a cursor and loop through it. Now I can parse the values from a string and loop through to complete the task.