Home » SQL & PL/SQL » Client Tools » Error to Associate Migration Repository (SQL Developer(Version 4.1..19), DB is XE Oracle 11g R2 , 64-bit Windows 7 Enterprise (Service Pack 1))
Error to Associate Migration Repository [message #647199] Fri, 22 January 2016 05:38 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

To migrate SQL Server DB to Oracle I have created user as below:

CREATE USER MWREP 
IDENTIFIED BY mwrep 
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;


After this I have click on option 'Associate Migration Repository'.

It shows the error as attached image file error1.png
/forum/fa/12977/0/

Would you please suggest me the work around for this error.

Thanks.
  • Attachment: Error1.png
    (Size: 199.06KB, Downloaded 277 times)
Re: Error to Associate Migration Repository [message #647204 is a reply to message #647199] Fri, 22 January 2016 07:51 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
Is this any help?
Re: Error to Associate Migration Repository [message #647207 is a reply to message #647199] Fri, 22 January 2016 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
GRANT ... RESOURCE


This is a bug, a REAL error!

Re: Error to Associate Migration Repository [message #647209 is a reply to message #647207] Fri, 22 January 2016 09:40 Go to previous messageGo to next message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
Well spotted, Michel.
Re: Error to Associate Migration Repository [message #647210 is a reply to message #647209] Fri, 22 January 2016 10:58 Go to previous messageGo to next message
thatjeffsmith
Messages: 71
Registered: July 2009
Location: Raleigh, NC
Member

Read the help


Repository


The Repository page of the wizard requires that you specify the database connection for the migration repository to be used.

The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. If you do not already have a migration repository and a database connection to the repository, create them as follows:

Create an Oracle user named MIGRATIONS with default tablespace USERS and temporary tablespace TEMP; and grant it at least the RESOURCE role and the CREATE SESSION, CREATE VIEW, and CREATE MATERIALIZED VIEW privileges. (For multischema migrations, you must grant the RESOURCE role with the ADMIN option; and you must also grant this user the CREATE ROLE, CREATE USER, and ALTER ANY TRIGGER privileges, all with the ADMIN option.)
Re: Error to Associate Migration Repository [message #647211 is a reply to message #647210] Fri, 22 January 2016 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not change the fact it is a big, huge security hole.
1/ With RESOURCE you can kill your SYSTEM tablespace, and I bet most of the privileges are not needed.
2/ With the other ones, give me them and in less than 10 seconds I am DBA and can do what I want.

Re: Error to Associate Migration Repository [message #647214 is a reply to message #647211] Fri, 22 January 2016 12:56 Go to previous messageGo to next message
thatjeffsmith
Messages: 71
Registered: July 2009
Location: Raleigh, NC
Member

With a migration, we assume you're 'God' or a DBA. You will be creating objects. You need quota on a tablespace. I guess we will agree to disagree.
Re: Error to Associate Migration Repository [message #647259 is a reply to message #647207] Sun, 24 January 2016 23:26 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member



If I revoke the 'RESOURCE', will it resolve this issue?

Re: Error to Associate Migration Repository [message #647262 is a reply to message #647259] Mon, 25 January 2016 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 21366
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think so. RESOURCE part of the discussion meant to say that - granting that role to a user - you probably granted MORE privileges than he really needs. Here's what it contains:
SQL> show user
USER is "SYS"
SQL> select privilege from role_sys_privs where role = 'RESOURCE';

PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

SQL>

I believe that people here meant to say that it would be OK if you granted only CREATE TABLE to that user and let it work with it. Then, in some stage, some minutes/days/weeks in the future, he might want to create a procedure but won't be able to do it because of lack of the appropriate privilege - so you'd grant CREATE PROCEDURE.

And so forth - grant only the necessary set of privileges, not predefined role(s) which might contain some privileges that you don't really want to grant.

Therefore, RESOURCE probably contains privileges you need to successfully associate migration repository, but it might also contain some privileges you don't need to associate migration repository.

Now, as the documentation says "grant it at least the RESOURCE role" and it was written by Oracle people, I'd stick to it and leave the RESOURCE role granted.

Think of the whole RESOURCE story as of the "best practices" and consider it next time when you create a new user - just don't automatically grant RESOURCE to it.
Re: Error to Associate Migration Repository [message #647263 is a reply to message #647262] Mon, 25 January 2016 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And above all, RESOURCE grants UNLIMITED TABLESPACE.
Better allocate quota on each tablespace you need to use and obviously NOT on SYSTEM (or SYSAUX).

Quote:
Now, as the documentation says "grant it at least the RESOURCE role" and it was written by Oracle people, I'd stick to it and leave the RESOURCE role granted.


Oracle is not better than other editors to follow the recommendations it provides, in many places it just ignores them.

Re: Error to Associate Migration Repository [message #648101 is a reply to message #647263] Tue, 16 February 2016 09:19 Go to previous message
gazzag
Messages: 909
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
Oracle is not better than other editors to follow the recommendations it provides, in many places it just ignores them.

Or as my mum likes to say: "Do as I tell you, not as I do!"
Previous Topic: Hide SQL > statements in the spool file
Next Topic: SQL Server to Oracle Migration
Goto Forum:
  


Current Time: Fri Feb 23 09:09:06 CST 2018

Total time taken to generate the page: 0.02730 seconds