script to create new user with existing user


Would someone has a script which should do the following;

Complete the script to create a new user based on another user.  These are the requirements for the script:
1-One pl/sql script should do everything
Parameters are:  new username, new user password, current user to copy
2-The script should only be used to create "qry" accounts .
3-The script should create the user, set the profile, grant system privs, grant roles privs (all roles should be on by default), grant table privs, grant column privs
4-Lastly, it should display the user account information, all system privs, all roles privs, table and column privs (priv, table_name, column_name only) for both the existing and new user for comparison.

I urgently need for step 2 . Would someone help me ?

Great thanks in advance.

Who is Participating?
dbmullenConnect With a Mentor Commented:
   username_in dba_users.username%type;
   if instr(upper(username_in), 'QRY' ) = 0
        then raise
   end if;

This looks like a nice homework assignment, what have you coded unto now?

For "2-The script should only be used to create "qry" accounts", just copy the "SELECT" grants, not the update/insert/delete ones.

kamisidAuthor Commented:
Thanks for your reply.

Actually this script should only accept input (new username) having only qry inside th name . Like  accessqry_prod. If I would get input accesstest_prod it should send error message and exit. If input is having qry then it should go ahead.


Select username from dba_users where username like '%QRY%';

If the input for new user is "test1" , then there should be error.
If the input for new user is  "testqry_test" then it should accept it as this name has qry in the name.


kamisidAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.