Forum Stats

  • 3,824,927 Users
  • 2,260,440 Discussions
  • 7,896,351 Comments

Discussions

Create user as other user

sysassysdba
sysassysdba Member Posts: 459 Silver Badge
edited Jan 11, 2016 6:17PM in Database Ideas - Ideas

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.

sysassysdbaJagadekaraBPeaslandDBActriebtop.gunGeert GruwezborneselWilliam RobertsonRichard Harrison .caadecarvalhoPravin TakpireManish ChaturvediPkLothar FlatzSven W.Emad Al-MousaPritiranjan KhilarKayKulohmannApexBineNimish Gargritan2000pattonjgmartin.bergerMatheus BoesingJames G KTHEinternetAnderson GonçaloAndreas HuberAlex Lamar-OracleRodolfo Martinez-OracleL. FernigriniMaaz KhanAdityanath Dewoolkarjormart-Oracleuser11970842sdstuber
39 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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
  • sysassysdba
    sysassysdba Member Posts: 459 Silver Badge

    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.

    Emad Al-Mousa
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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.
    Sven W. Member Posts: 10,535 Gold Crown

    I like it!

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

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    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
    ritan2000 Member Posts: 20 Bronze Badge

    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.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    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.

    William Robertson
  • Maaz Khan
    Maaz Khan Member Posts: 226 Gold Badge

    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