Home » RDBMS Server » Server Utilities » Script to recreate user before import of data
Script to recreate user before import of data [message #233652] Fri, 27 April 2007 01:54 Go to next message
Langberg
Messages: 5
Registered: April 2007
Location: Denmark
Junior Member
Oracle 10G on Windows.
Anybody has a working script to extract DDL to be used to recreate user with rigth password, configuration, etc. - but without any objects.
I tried with "select dbms_metadata.get_ddl", but afterwards I keep getting an error saying that the password is wrong !
Re: Script to recreate user before import of data [message #233657 is a reply to message #233652] Fri, 27 April 2007 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
afterwards I keep getting an error

After what?

Regards
Michel
Re: Script to recreate user before import of data [message #233665 is a reply to message #233652] Fri, 27 April 2007 02:31 Go to previous messageGo to next message
Langberg
Messages: 5
Registered: April 2007
Location: Denmark
Junior Member
Sorry Embarassed
1. extract ddl with "select dbms_metadata.get_ddl ('USER','TESTUSER') from dual;"
2. drop user 'testuser'
3. create user 'testuser' with DDL from 1.
4. Logon as testuser/password
-> wrong userid or password
Re: Script to recreate user before import of data [message #233693 is a reply to message #233665] Fri, 27 April 2007 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should say that the password is not the one you're thinking it is.
Did you try to connect BEFORE recreating the user?

Regards
Michel
Re: Script to recreate user before import of data [message #233699 is a reply to message #233652] Fri, 27 April 2007 03:23 Go to previous messageGo to next message
Langberg
Messages: 5
Registered: April 2007
Location: Denmark
Junior Member
Oh yes - I "checked" the password.
Re: Script to recreate user before import of data [message #233700 is a reply to message #233699] Fri, 27 April 2007 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, nothing personal, but I only trust what I can see.
Copy and paste your SQL*Plus session.

Regards
Michel
Re: Script to recreate user before import of data [message #233709 is a reply to message #233652] Fri, 27 April 2007 03:50 Go to previous messageGo to next message
Langberg
Messages: 5
Registered: April 2007
Location: Denmark
Junior Member
Sorry folks - it seems to work anyway.
My mistake is that doing my testing - I used this method to "clone" a schema like this:
1. extract ddl with "select dbms_metadata.get_ddl ('USER','TESTUSERA') from dual;"
2. drop user 'testuserb'
3. create user 'testuserb' with adjusted DDL from 1.
4. Logon as testuserb/password
-> wrong userid or password
So the explanation must be that the "username" is used for encrypting the password - right ?
The method above works ok for dropping/creating a user.
Re: Script to recreate user before import of data [message #233715 is a reply to message #233709] Fri, 27 April 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So the explanation must be that the "username" is used for encrypting the password - right ?


Correct.

Regards
Michel
Re: Script to recreate user before import of data [message #233744 is a reply to message #233652] Fri, 27 April 2007 04:44 Go to previous messageGo to next message
Langberg
Messages: 5
Registered: April 2007
Location: Denmark
Junior Member
But anyway - does anybody has a proper script to extract all the necessary DDL to recreate the user without manually adjusting the generated script afterwards.
If I'm doing like following, it generates blank lines etc.
- set long 90000
- select dbms_metadata.get_ddl
('USER','username'),';' from dual;
- select dbms_metadata.get_granted_ddl
('ROLE_GRANT','username'),';' from dual;
- select dbms_metadata.get_granted_ddl
('SYSTEM_GRANT','username'),';' from dual;
Re: Script to recreate user before import of data [message #233745 is a reply to message #233744] Fri, 27 April 2007 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no way to avoir blank lines (but how does it hurt?).
You can ask him to add the ";" (I removed some blank lines to save space.):
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl ('USER',user) from dual;
DBMS_METADATA.GET_DDL('USER',USER)
-----------------------------------------------------------------

   CREATE USER "MICHEL" IDENTIFIED BY VALUES 'A16FD5BFC24EA1EC'
      DEFAULT TABLESPACE "TS_D01"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "PFL_ORADBA";

1 row selected.

SQL> select dbms_metadata.get_granted_ddl ('ROLE_GRANT',user) from dual;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USER)
-----------------------------------------------------------------

   GRANT "SELECT_CATALOG_ROLE" TO "MICHEL";

1 row selected.

SQL> select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',user) from dual; 
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USER)
-----------------------------------------------------------------

  GRANT SELECT ANY DICTIONARY TO "MICHEL";
  GRANT CREATE TYPE TO "MICHEL";
  GRANT CREATE TRIGGER TO "MICHEL";
  GRANT CREATE PROCEDURE TO "MICHEL";
  GRANT CREATE DATABASE LINK TO "MICHEL";
  GRANT CREATE SEQUENCE TO "MICHEL";
  GRANT CREATE VIEW TO "MICHEL";
  GRANT CREATE SYNONYM TO "MICHEL";
  GRANT SELECT ANY TABLE TO "MICHEL";
  GRANT CREATE TABLE TO "MICHEL";
  GRANT CREATE USER TO "MICHEL";
  GRANT UNLIMITED TABLESPACE TO "MICHEL";
  GRANT CREATE SESSION TO "MICHEL";

1 row selected.

Regards
Michel
Re: Script to recreate user before import of data [message #233751 is a reply to message #233744] Fri, 27 April 2007 06:00 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/120560/42800/?srch=cr_user_like#msg_120560
Previous Topic: How can i import database by using dump file?
Next Topic: DBA_JOBS not getting executed.
Goto Forum:
  


Current Time: Tue Jun 18 08:30:24 CDT 2024