Create user as other user

sysassysdba

I remember that in Oracle Enterprise Manager 8i,9i ( and 10g java console) you could create user as other user, but

i think that you can´t do it in sql.

(https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#SQLRF01503)

the idea is something like this:

Create user mike like scott identified by p4ssw0rd;

"Like scott" involves:

    grant to mike the same system privileges that scott has.

  

    grant to mike the same roles that scott has.

  

    grant to mike the sames tablespace quotas that scott has.

  

    set the same default/temporary tablespace  as scott.

  

    set the same profile to mike like scott has.

  

   

I don´t like to grant the same object privileges...but this is an personal opinion.  

today you can do something similiar using dbms_metadata...but this new feature would be more easy to use.

top.gun

I can see the attraction of such an idea.

However when it comes to security, do you really want to make it easy to give someone the wrong role/privileges simply because you just copied someone elses metadata?

Especially giving someone the same password as someone else......

I just have reservations about making it super easy to copy someones password and access.

sysassysdba

Top,  in my "example"  the command does not copy the password, you have to assign a new password. I remember that OEM 9i did not copy the password.

of course this feature could be a security lack if it is used by unexperienced administrators... but probably exist graphical tools that do it now .

If anybody have create/alter user role, you must know what to do.

I am exposing an idea that, of course , it can be improved with any  wellcome contribution. Your remark is very reasonable

Regards.

top.gun

Top,  in my "example"  the command does not copy the password, you have to assign a new password. I remember that OEM 9i did not copy the password.

of course this feature could be a security lack if it is used by unexperienced administrators... but probably exist graphical tools that do it now .

If anybody have create/alter user role, you must know what to do.

I am exposing an idea that, of course , it can be improved with any  wellcome contribution. Your remark is very reasonable

Regards.

No problem - I'll vote up anyway

Sven W.

I like it!

Especially to set the tablespace is often forgotten. So many schemas end up creating tables in USERS...

Nimish Garg

I like it!

Especially to set the tablespace is often forgotten. So many schemas end up creating tables in USERS...

i like this, will save some time of dba for creating schema

ritan2000

OEM Database Express 12c has Security->User->Create Like option. I agree it could be very helpfull to have the command CREATE USER ... LIKE...

with all the Clauses that you explain and as optional [COPY OBJECT PRIVILEGES]  too.

Alex Lamar-Oracle

voted up

Sven W.

voted up

Please stop spamming the ideas space. Only add something if you have a substantial contribution/comment to the idea.
It is enough to do the vote, no need to additionally add a message that you did vote. This is considered to be against the forum netiquette.

Maaz Khan

My thought -

Create user mike like scott identified by p4ssw0rd; will be beneficial for dbas with include/exclude/modify options wherein you can have control to tweak tablespace name, some profile options or exclude/include grants.

This will give more flexibility and control to dbas.

Regards,

Maaz