Using SSMA for oracle with user having READ ONLY permission

Question

Hi,

https://support.microsoft.com/en-in/help/2020711/you-may-get-permission-related-error-messages-when-using-ssma-for-orac

With the reference to above link, we do understand the SSMA needs some additional permissions. Its mentioned as

To obtain metadata for objects in other schemas and then convert objects in those schemas, your account must have the following permissions on the source database.

CREATE ANY PROCEDURE
EXECUTE ANY PROCEDURE
SELECT ANY TABLE
SELECT ANY SEQUENCE
CREATE ANY TYPE
CREATE ANY TRIGGER

We are using the SSMA for migration.

I am having read only oracle user and I want to access schemas/database objects created by other users via SSMA.

Can anyone please help me to UNDERSTAND why SSMA needs CREATE & EXECUTE permissions?

Is there any other alternative available to use SSMA with READ ONLY access oracle user?

Thanks,
Madhavi

Answer 1

Hi @Madhavi Hardas ,

Here is the list of permissions necessary for SSMA to function with the explanation as to why each permission is needed: https://learn.microsoft.com/en-us/sql/ssma/oracle/connecting-to-oracle-database-oracletosql?view=sql-server-ver15

Unfortunately it is not up to us to decide which permission controls access to what data in Oracle. CREATE ANY PROCEDURE, for instance, is needed to access the PL/SQL text associated with the procedure. There is simply no READ ANY PROCEDURE permission.

In order to better understand what particular system data dictionary/view is hidden behind what permission in Oracle, you can switch "Collector" logs to "Debug" under Tools -> Global Settings -> Logging in SSMA. Once you attempt to connect to Oracle with the "read-only access" user, you will be able to see in the log file which queries SSMA attempted to execute and whether they have failed due to permissions.

Regards,
Alex.