miercuri, 3 aprilie 2013

I want a user copy of a user

Well, now let's practice something little bit more tricky and not very well documented.
As DBA, you wonder, pretty often, how to create a user different as name and schema from some existent user, but the same as all of the other characteristics and parameters of a user. This is it, your new user will connect using the same password, will use the same tablespace and will share the very same grants and rights as his original ancestor.

The next command will do the trick:



select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) from dual
   UNION ALL
   select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) from dual
   UNION ALL
   select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual
   UNION ALL
  select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) from dual;

Of course, you have to specify your own desired username as a function parameter up there.

I warn you about something that can lead to an error: if your user hasn't got a granted resource, included in any of above categories (it doesn't have granted, let's say,  an object), an error of bellow form will be thrown:


ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7737
ORA-06512: at line 1
31608. 00000 -  "specified object of type %s not found"
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.

And let's see the correct answer (actually, in one of my databases, the user scott really wasn't granted with any right to any object, just has got some system grants and roles):


   CREATE USER "SCOTT" IDENTIFIED BY VALUES tigger
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP""
"
  GRANT UNLIMITED TABLESPACE TO "SCOTT""
"
   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT""


Of course, the result must be a little bit edited (some leading and trailing quotation marks should be deleted and semicolons added at any end of different command. So, the final code should be look like this:

CREATE USER "newuser" IDENTIFIED BY VALUES password
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
GRANT UNLIMITED TABLESPACE TO "newuser";
GRANT "CONNECT" TO "newuser";
GRANT "RESOURCE" TO "newuser";



Un comentariu: