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
declare
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);
begin
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;
--HERE CODE STARTS TO PARSE THE STRING
dbms_output.put_line(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;
--HERE CODE ENDS TO PARSE THE STRING
v_num := v_num + v_inc;
end loop;
end;
select username from dba_users
where username like 'JP%' order by 1;
USERNAME
------------------------------
JP1
JP2
JP3
JP4
JP5
JP6
JP7
JP9
JP10
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.
Back to DBAsupport.com
