?
Solved

script to create new user with existing user

Posted on 2008-09-30
4
Medium Priority
?
713 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 750 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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