Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

Setting up Target User Privileges

When making import into the target database you need to be sure that the target database and all the required schemas exist on the target server. Also the user, that you utilize to connect to the target database and to load all the objects and data, should have appropriate privileges to be able to do that.

Below is the list of privileges that are required to make import into the target database.

Oracle

In Oracle database a new user can be created following the below statement:

 create user iq2ora identified by ora
 DEFAULT TABLESPACE USERS QUOTA 100M ON USERS
 TEMPORARY TABLESPACE TEMP QUOTA 10M ON TEMP
 

Then you will need to assign connect privileges and create/alter/delete privileges to that user, so that it will be able to connect and create/alter/delete all types of the objects. To assign these privileges you can use the statements below:

 GRANT CREATE SESSION TO iq2ora
 /
 GRANT CREATE TABLE TO iq2ora
 /
 GRANT CREATE PROCEDURE TO iq2ora
 /
 GRANT CREATE TRIGGER TO iq2ora
 /
 GRANT CREATE VIEW TO iq2ora
 /
 GRANT CREATE SEQUENCE TO iq2ora
 /
 GRANT ALTER ANY TABLE TO iq2ora
 /
 GRANT ALTER ANY PROCEDURE TO iq2ora
 /
 GRANT ALTER ANY TRIGGER TO iq2ora
 /
 GRANT ALTER PROFILE TO iq2ora
 /
 GRANT DELETE ANY TABLE TO iq2ora  
 /
 GRANT DROP ANY TABLE TO iq2ora
 /
 GRANT DROP ANY PROCEDURE TO iq2ora
 /
 GRANT DROP ANY TRIGGER TO iq2ora
 /
 GRANT DROP ANY VIEW TO iq2ora
 /
 GRANT DROP PROFILE TO iq2ora
 /

Or you can just grant DBA privilege to your Oracle user:

  GRANT DBA TO user_name. 

MSSQL

To make an import into the MSSQL Database you can utilize the existing user or create a new user and assign appropriate privileges to it. If you decide to create a new user in MSSQL you can follow the statements below:

  create login tester with password = 'Ispirer_1999'
  alter login tester with default_database = imp      -- Assign the default database 'test' for the login
  use imp                                             -- We need to create a user in the required database, so we need to use it
  create user test_user for login tester

After creating a new user you will need to assign CREATE privileges to it to be able to create all types of the objects in the target database. It can be done following the queries below:

GRANT ALTER ON SCHEMA::dbo TO test_user 
go
grant create table to test_user    
go
grant create view to test_user
go
grant create synonym to test_user
go
grant create procedure to test_user

Or instead of all the GRANT CREATE statements you can just add your user to a “db_ddladmin” role:

  EXEC sp_addrolemember 'db_ddladmin', N'MyUserName'

Then you will need to add privileges to be able to load the data into the target MSSQL database.

 grant select to test_user 
 go
 grant insert to test_user 
 go
 grant update to test_user
 go
 grant delete to test_user
 go

You can just add your login to a sysadmin role right after the user creation:

 EXEC master..sp_addsrvrolemember @loginame = N'tester ', @rolename = N'sysadmin' 
 

More information about the roles and privileges that are already added in the role, you can see in the screenshot below:

PostgreSQL

To make import into PostgreSQL Database you can utilize the existing user or create a new one. To create a new user you can execute the statement below:

 CREATE USER tester with PASSWORD 'postgres';

Once the user is created you can run the import process. In PostgreSQL there is no need to add additional privileges to be able to create objects or load the data.

MySQL

To make import into PostgreSQL Database you can utilize the existing user or create a new one. To create a new user you can execute the statement below:

 create user tester identified by 'Ispirer_1999'
 

As soon as the user is created, you will need to assign appropriate privileges to that user, to be able to load the definitions and data into the target MySQL database. Here are the sql statements that should be executed to assign required privileges:

 grant create on itest.* to tester;
 grant insert on itest.* to tester;
 grant delete on itest.* to tester;
 grant drop on itest.* to tester;
 grant alter on itest.* to tester;
 grant create view on itest.* to tester;
 grant create routine on itest.* to tester;
 grant alter routine on itest.* to tester;
 
 FLUSH PRIVILEGES;
 

Or you can just assign all privileges to the created user:

 GRANT ALL privileges on itest.* to tester;
 FLUSH PRIVILEGES;

Then you can run the import process.


If you have any questions, please feel free to contact our support team: support@ispirer.com


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

sqlways/users-guide/check-list/setting-up-target-user-privileges.txt · Last modified: June 21, 2018, 12:57:10 PM by vera.bonda
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement