Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

Setting up Source User Privileges

Oracle

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 in support of 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_sysnonyms
  • 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.

Microsoft SQL Server

As to 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 in support of 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
  • 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'.

PostgreSQL

In PostgreSQL to be able to extract DDL of objects, 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;

DB2 LUW

In DB2 to be able to connect to a database and to be able to extract DDL of objects you need to create a user on 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 the user user1;

MySQL

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

  • create a 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';

Informix

In Informix to be able to connect to a database and to be able to extract DDL of objects you need to create a user on 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;


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

sqlways/users-guide/check-list/setting-up-source-user-privileges.txt · Last modified: October 28, 2015, 04:15:00 AM by alexandr.kirpichny
 
© 1999-2017, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement