As an Oracle DBA, you are very likely to get requests somewhat like this:
Create an account for Sam Hill. The privileges should look exactly like Rob Nixon.
If you are not into automating everything, your first instinct would be to fire-up an IDE like Toad or SQL Developer and script out the source user, then find and replace the user name to run it in and create the new user.
This can and should be easily automated. I created the script below that would let you clone all the details of an user and create a new user. When you run it you will be promoted for the reference/model user and the target user names. Please make sure that you have not “SET DEFINE OFF” since these input details will be prompted for by the “&” character substitution mechanism.
The script primarily uses DBMS_METADATA to get the DDL and then substitutes the model user ID with the new user ID. The code is available on GitHub
Note: After fighting WordPress for an hour, I could not fix the formatting that it kept messing up by jumbling the lines. Then, I discovered that you could create a gist and reference the link to embed github code in blogs which is what I ended-up doing!
Save the above to a .sql file and when you run it in your favorite script runner (like SQL*Plus), you should get prompted for the following:
- Model_UserName – The reference user to use. E.g., SAMHILL
- Cloned_UserName – The new user name to create. E.g., ROBNIXON
- Skip_If_Model_User_Missing – Y or N to raise an error if model user does not exist
- Remove_Cloned_User_If_Exists – Y or N. Should cloned user be removed if it already exists?
- Retain_Same_Password – Y or N. May only be supported in the older versions
Variations:
It should not take much effort to get the output written to DBMS_OUTPUT or to a file. You can even capture the DBMS_OUTPUT to a table as outlined in my previous post and act on the DDL later as part of a bigger process. Using the technique outlined in the referenced post, you can capture the DBMS_OUTPUT and redirect it to a file.
In my shop, this script is the key script that is part of a larger program that clones, records the activity for audit and then emails the newly user (cc DBA’s) with instructions on how to reset the password and access the new account (all done with PowerShell).
This script will also come in handy for testing the privileges of an user without mangling the actual account!
