From: joel garry on 9 Mar 2007 17:26
On Mar 9, 1:29 pm, trp...(a)gmail.com wrote:
> Thanks for your response, I visited the URL provided and found the
> article on "How to copy all the privileges from one user to another".
> What would be the steps outside of a script for creating a user from
> scratch to be just like another user? I was able to pick out from the
> script that there were 3 table containing the privledge information
> (dba_sys_privs, dba_tab_privs, dba_col_privs). However for the sake of
> understanding I would like to see how this is done systematically from
> scratch at the beginning from creating the user through assigning
> permissions. So how would I step by step create a new user and then
> find out all that is necessary to make that user look just like
> another user. Thanks!
If you have access to metalink, there are example scripts that do what
you want. For example, see Note:90449.1 for the create user statement
(you can grab the one it generates and change the username and use a
proper password rather than identified by values). Search metalink
for "Database Scripts Library Index" but keep in mind, some of them
are old and may contain old wives tails.
Note there are also docs available at tahiti.oracle.com for the
complete syntax. There are probably scripts floating about on the
web, of various quality.
If you have a system with the strings and grep commands a gander at a
full export file (with rows=n, even) can be quite enlightening. Just
grep for a known username and imagine there are semicolons at the end
of the commands.
We can't just give you a standard set of commands, since grants,
synonyms and privileges are site-specific, hopefully controlled by a
decent DBA. In many cases, packaged applications require things to be
just plain weird.
@home.com is bogus.
From: Jaap W. van Dijk on 10 Mar 2007 06:21
On 9 Mar 2007 08:37:46 -0800, trpost(a)gmail.com wrote:
>I am looking for the SQL syntax to create an additional database user
>that is a copy of an existing user. So my new user will have a
>different username and password, but same permissions and access as
>another existing user. I am looking for the SQL syntax, not how to do
>it through a GUI tool.
The Enterprise Manager has the Create like ... function (reached by
right-clicking on a User)
From: rcyoung on 14 Mar 2007 09:48
On Mar 9, 12:37 pm, trp...(a)gmail.com wrote:
> I am looking for the SQL syntax to create an additional database user
> that is a copy of an existing user. So my new user will have a
> different username and password, but same permissions and access as
> another existing user. I am looking for the SQL syntax, not how to do
> it through a GUI tool.
You might try something like this
1) export with no data
2) import with the list option so that you put a copy of ALL the SQL
into a text file, but it is not actually executed
3) Edit the text file to extract and change the specifics features you
need for a new user, making it identical to the existing user
4) Run the script you just created
This way you "know" you have all privs,roles,etc taken care of.