Setting up Source User Privileges

SQLWays utility uses select statements to all_*, dba_* and sys.* tables and views in order to extract information about the source DDL for all the objects that need to be converted. So, the following privileges are required to support the export for Oracle objects:

• CREATE SESSION
• SELECT ANY TABLE
• GRANT SELECT ON sys.procedure$
• GRANT SELECT ON dba_users
• GRANT SELECT ON dba_part_key_columns
• GRANT SELECT ON dba_synonyms
• GRANT ALL [PRIVILEGES] ON OWNER.OBJECT_NAME for each object that needs to be migrated

NOTE: You can put a select statement into LOOP with EXECUTE IMMEDIATE, that will generate and execute grants for the required objects.

   BEGIN
      FOR cur IN (SELECT object_type, 'grant all on '||owner||'.'||object_name||' to <YOUR_USER_NAME>' as exec_stmt FROM all_objects
                  WHERE object_type NOT IN ('INDEX','LOB','TABLE PARTITION','CLUSTER','CONSUMER GROUP','CONTEXT','DESTINATION','DIRECTORY',
                                            'EDITION','EVALUATION CONTEXT','JAVA CLASS','INDEX PARTITION','JOB','JOB CLASS','JAVA DATA',
                                            'JAVA RESOURCE','XML SCHEMA','LIBRARY','LOB PARTITION','OPERATOR','WINDOW') 
                  AND owner IN ('<YOUR_LIST_OF_SCHEMA_NAMES>')
                  ) LOOP
         EXECUTE IMMEDIATE cur.exec_stmt;
      END LOOP;
   END;

OR

You can just initiate GRANT DBA TO user_name.

The minimum privileges required to collect information about the DDL of objects are the following:

• GRANT CREATE SESSION TO YOUR_ORACLE_USER;
• GRANT SELECT ANY DICTIONARY TO YOUR_ORACLE_USER;

Please note that in this case it is necessary to select “DBA” option on the Source Options page. To select “DBA” option click on the “Advanced…” button as shown in the screenshot below:

As for Microsoft SQL Server, SQLWays utility uses select statements to system tables and views to extract information about the source DDL for all the objects that need to be converted. So, we need to create a login with a default database where objects required for the migration are stored. Create a user for this login and assign the following privileges that are required to support the export for Microsoft SQL Server objects:

• create login sql_test with password = 'Pwd'
• alter login sql_test with default_database = test – Assign the default database 'test' for the login
• use test – We need to create user in the required database, so we need to use it
• create user test_user for login sql_test

Privileges that should be assigned to the user:

• grant select to test_user
• GRANT EXECUTE TO test_user
• GRANT VIEW DEFINITION TO test_user;

OR

You can just add your login to a sysadmin role EXEC master..sp_addsrvrolemember @loginame = N'sql_test', @rolename = N'sysadmin'.

To be able to extract DDL of objects in PostgreSQL, all you need to do is to create a new user and assign select privileges on sequences and tables to this user. It will be enough to extract the definitions of the objects required for the migration:

• create a user test_user with a password 'pwd';
• GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_user; –Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting).
• GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO test_user;

To be able to connect to a database and to extract DDL of objects in DB2, you need to create a user in the operational system where the DB2 server is installed. In this case you will be able to connect to the DB2 database using the credentials of this user and extract definitions of business logic objects in this database. To be able to extract the definitions of the tables and data stored in these tables you need to grant SELECT permission on a specific table which should be extracted:

• grant select on db2totd.address to user1;

Or you can grant access to all the data (ie, all tables in all schemas), then you need to grant dataaccess privilege to the user:

• db2 grant dataaccess on database to user user1;

To be able to connect to a database and to extract DDL of objects in MySQL, you need to create a user and assign select privileges to this user. As a template you can use the queries below:

• create user 'test_perm'@'%' identified by 'root';
• grant select on *.* to 'test_perm'@'%';

Or you can do it using only one query:

• grant select on *.* to 'test_perm'@'%' identified by 'root';

To be able to connect to a database and to extract DDL of objects in Informix, you need to create a user in the operational system where the Informix server is installed. After that you need to add this user to IX_USERS group in Administrative Tools in Computer Management. Also you need to grant a connect privilege to this user, so that it can connect to this database:

• grant connect to user1;

When we use Teradata as a source database in the SQLWays Wizard tool, we need to specify the user that will be able to read information from the system tables. You can utilize a user that already exists in your source database or create a new one and assign appropriate privileges to him. So to create a new user you can apply the statement below:

• CREATE USER test_user as PASSWORD = Pwd PERM = 100000000;

The user that you will utilize should have the select privilege on the DBC.TVM, DBC.Dbase, DBC.triggersTbl. So to assign the select privilege you need to execute the following statements:

• grant select on DBC.TVM to test_user ;
• grant select on DBC.Dbase to test_user ;
• grant select on DBC.triggersTbl to test_user ;

SQLWays utility uses select statements to sys* tables to extract information about the source DDL for all the objects that need to be converted. Also a user should have privelegies to work with all the objects that should be converted during your migration project. So, you can use a user that already exists in your source database or create the new one and assign appropriate privileges to him. To create a new user you need to create a new login and then create a user with the same name in the required database:

• SP_ADDLOGIN test_user, password
• SP_ADDUSER test_user

After that you need to grant appropriate privileges on the objects that should be converted in your current database. It can be done using the GRANT statement on the required object:

• GRANT SELECT ON test_schema.tab to test_user;
• GRANT EXECUTE ON test_schema.s_procedure to test_user;

NOTE: You can run a select statement that will generate GRANT ALL statement for the required objects. In WHERE clause you can control for which objects GRANT statement will be generated. In this example GRANT statements will be generated for all the tables, procedures, triggers and views in your database:

   select 'grant all on ' + name + ' to test_user' 
     from sysobjects 
     where type = 'U' or type = 'P' or type = 'TR' or type = 'V'

When we use Sybase SQL Anywhere as a source database in the SQLWays Wizard tool the user that we specify to connect to our source database should have appropriate rights to read information from all the system tables. You can use a user that already exists in your source database and grant him the required privileges or create the new one and assign appropriate privileges to him. So to create a new user you can use the statements below. For Sybase 11 version and higher you can use CREATE USER statement:

• create user test_user identified by password;

For the Sybase Anywhere 10 version and lower you need to use GRANT statement to create a user and grant the required privileges.

• GRANT CONNECT to test_user IDENTIFIED BY sql

The user that you will use should have select privilege on the SYS.sysuserperm, SYS.sysprocedure, SYS.sysviews, SYS.systable, SYS.sysprocparm, sysdomain, SYS.systrigger, sysgroup, sysusers, sysusertype, sys.syscolumn, sys.sysindex, sys.sysixcol, sys.sysfkcol, sys.sysforeignkey, sys.sysconstraint, sys.sysevent, sys.syseventtype, sys.sysschedule tables. So to assign the select privilege you need to execute GRANT statement for all these system tables. Here is an example of a grant statement:

• grant select on SYS.sysuserperm to test_user ;

OR

You can just initiate GRANT DBA TO test_user.