• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

script to create new user with existing user

Hi,

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.

Kamran
0
kamisid
Asked:
kamisid
  • 2
1 Solution
 
MikeOM_DBACommented:

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.


0
 
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.

Like;

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.

Thanks.

Kamran
0
 
dbmullenCommented:
declare
   username_in dba_users.username%type;
begin
   if instr(upper(username_in), 'QRY' ) = 0
        then raise
   end if;
end;
0
 
kamisidAuthor Commented:
Thanks.
0
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now