Solved

script to create new user with existing user

Posted on 2008-09-30
4
695 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 71
PL SQL Search Across Columns 4 53
oracle DR - data guard failover. 18 47
making a message body variable from an oracle select statement 4 25
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

860 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