Setting up Source Database User Privileges

SQLWays tool uses select statements to all_*, dba_*, 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 export of 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_sysnonyms
  • GRANT ALL [PRIVILEGES] ON OWNER.OBJECT_NAME for each object that should 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.

As for Microsoft SQL Server, SQLWays tool 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 migration are stored. Then create a user for this login and assign the following privileges that are required to support export of 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 objects' DDLs from PostgreSQL, all you need to do is to create a new user and to assign select privileges on sequences and tables to him. 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 objects' DDLs from DB2, you need to create a user in the operational system where DB2 server is installed. In this case you will be able to connect to the DB2 database using the credentials of this user and to extract the 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 (i.e., all tables in all schemas), then you will need to grant dataaccess privilege to the user:

  • db2 grant dataaccess on database to user user1;

To be able to connect to the database and to extract objects' DDLs from MySQL, you need to create a user and to 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 the database and to extract objects' DDL from Informix, you need to create a user in the operational system where Informix server is installed. After that you need to add this user to IX_USERS group in Administrative Tools of Computer Management. Also you need to grant a connect privilege to this user, so that it could connect to this database:

  • grant connect to user1;

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

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

The user that you will utilize should have select privilege on the DBC.TVM, DBC.Dbase, DBC.triggersTbl. 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 DDLs for all the objects that need to be converted. The user should have privileges to work with all the objects that should be converted. You can either specify the user that already exists in your source database or create a new one and assign appropriate privileges to him. To create a new user you need to create a new login and then to 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. 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 the objects for which 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 indicating Sybase SQL Anywhere as a source database in 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. It can be either the user that already exists in your source database (in this case you just need to grant him the required privileges) or a new one that should be created and assigned with appropriate privileges. To create a new user you can use the statements below. For Sybase version 11 and higher you can use CREATE USER statement:

  • create user test_user identified by password;

For Sybase Anywhere version 10 and lower you need to use GRANT statement to create a user and to 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. To assign the select privilege you need to execute GRANT statement for all these system tables. Here is example of a grant statement:

  • grant select on SYS.sysuserperm to test_user ;

OR

You can just initiate GRANT DBA TO test_user.



More about Ispirer Systems