DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Function to create metadata

  1. #11
    Join Date
    Feb 2003
    Posts
    85
    Originally posted by efrijters
    Pando? What dialect are you talking?
    LOL

    You never used awk? You dont know what you are missing boy....

  2. #12
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I remember a trick of a Oracle teacher: he copied the encrypted password into the clipboard, changed the password and later on he updated the same field with the original password.

    I think I got the solution now...

    Thanks!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  3. #13
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by guru_heaven
    LOL

    You never used awk? You dont know what you are missing boy....
    AWK? Never heard of it... (bit of an AWKward name... awk...)

    Okay, I'll let google search for it. Probably I'll have to study again.... Thanks a LOT!
    Last edited by efrijters; 09-11-2003 at 11:41 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #14
    Join Date
    Jul 2002
    Posts
    335
    Originally posted by efrijters
    Pando? What dialect are you talking?

    Furthermore: I tried something, but now I see that many users are only created so they can access the application. The users have no objects, but are granted select/insert/etc rights to access the application-schema. Great!

    Now I think to use to create a script from selecting DBA_USERS to create all those users at the other db.

    Question :

    How do I transport the password?

    The following statement fails:
    SELECT 'CREATE USER '||user_name||' IDENTIFIED BY '||password||';'
    FROM DBA_USERS;

    Is there a trick to create a users password by using the encrypted password in DBA_USERs?
    I'm feeling generous:

    spool r1_create_users.sql

    select 'create user '||username||
    decode(password,'EXTERNAL',' identified externally',
    ' identified by values '||''''||password||'''')||
    ' default tablespace '||default_tablespace||
    ' temporary tablespace '||temporary_tablespace||';'
    from sys.dba_users
    where username NOT IN ('ADAMS', 'BLAKE', 'CLARK', 'JONES', 'SCOTT',
    'ANONYMOUS','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
    'CTXSYS', 'DBSNMP', 'LBACSYS', 'MDSYS', 'MTSSYS',
    'ODM', 'ODM_MTR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS',
    'OSE$HTTP$ADMIN', 'OUTLN', 'OAS_PUBLIC',
    'HR', 'OE', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM',
    'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SH',
    'PERFSTAT', 'REPADMIN', 'RMAN',
    'SYS', 'SYSTEM', 'TRACESVR', 'WEBSYS',
    'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')
    order by username
    /

    spool off

    The list of users you can edit as reuired, but its handy as it stops you getting the standard set of users etc.

    just run the spool file against your new database and chocks away. Your users will still have their old passwords. You can use this script as a basis to do a similar thing with roles, and then grants.

    Barry

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by efrijters
    Pando? What dialect are you talking?


    err it's a simple awk script which does what you want convert vertical text to horizontal right?

  6. #16
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by bazza
    I'm feeling generous:

    spool r1_create_users.sql

    select 'create user '||username||
    decode(password,'EXTERNAL',' identified externally',
    ' identified by values '||''''||password||'''')||
    ' default tablespace '||default_tablespace||
    ' temporary tablespace '||temporary_tablespace||';'
    from sys.dba_users
    where username NOT IN ('ADAMS', 'BLAKE', 'CLARK', 'JONES', 'SCOTT',
    'ANONYMOUS','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
    'CTXSYS', 'DBSNMP', 'LBACSYS', 'MDSYS', 'MTSSYS',
    'ODM', 'ODM_MTR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS',
    'OSE$HTTP$ADMIN', 'OUTLN', 'OAS_PUBLIC',
    'HR', 'OE', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM',
    'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SH',
    'PERFSTAT', 'REPADMIN', 'RMAN',
    'SYS', 'SYSTEM', 'TRACESVR', 'WEBSYS',
    'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')
    order by username
    /

    spool off

    The list of users you can edit as reuired, but its handy as it stops you getting the standard set of users etc.

    just run the spool file against your new database and chocks away. Your users will still have their old passwords. You can use this script as a basis to do a similar thing with roles, and then grants.

    Barry
    Thanks Barry, you hit the bullseye!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width