photo

Vincent Millar

shared this question
6 years ago

Statistics

1
Comments
242
Views

Relates to

Share

1
votes

Permissions on Oracle

Hello DB Best,

I'm migrating a schema in Oracle to SqlServer.

The SSMA requirements are to grant some very strong privileges to the oracle schema that I want to migrate and it is a concern for me.

create any procedure

execute any procedure

select any table

select any sequence

create any trigger

Why this is needed? I assume SSMA just reads and copies from Oracle to Sqlserver and are there any ddl changes made on the Oracle side that I need to be aware of, since this is our production database I want to very careful.

Vincent

Add Comment

Comments (1)

photo
2

Hi Vincent,

SSMA does not make any changes to the source Oracle database. SSMA Tester is the only exception to this rule, but the conversion testing is not a mandatory step of a database migration so you can ignore this component.

The Oracle permissions required by SSMA are not intended for creation of Oracle procedures or triggers. In fact, these permissions are necessary to read metadata of Oracle procedures, functions or triggers that reside in other user’s schemas. If your goal is to migrate your own schema only, you might not need these privilege settings. Same if the tables are the only objects to be migrated.

But if you want to migrate SQL or PL/SQL statements (e.g. in views), there could be a problem with standard functions (like NVL, TO_DATE or SUBSTR) or packages (like DBMS_OUTPUT). SSMA reads metadata for these functions from SYS and that is evidently not your own schema. If the SSMA user does not have create/execute any procedure privilege, the standard functions will be treated by SSMA as unknown objects, and they will not be converted.

Best wishes,

= = Yuri Rusakov

Leave Comment

photo

Attach files...

The file must be a jpg, gif, png, bmp, ico, pdf, doc, rtf, txt, zip or rar no more than 2M