Solved

script to create new user with existing user

Posted on 2008-09-30
4
682 Views
Last Modified: 2013-12-07
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
Comment
Question by:kamisid
  • 2
4 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22608620

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
 

Author Comment

by:kamisid
ID: 22609245
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
 
LVL 10

Accepted Solution

by:
dbmullen earned 250 total points
ID: 22620803
declare
   username_in dba_users.username%type;
begin
   if instr(upper(username_in), 'QRY' ) = 0
        then raise
   end if;
end;
0
 

Author Closing Comment

by:kamisid
ID: 31501629
Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now