DB Best blog

There are three main mechanisms within DB2 that allow implementing a database security plan: authentication, authorization, and privileges. These mechanisms differ from those used in SQL Server.

AUTHENTICATION

DB2 Authentication defines who is allowed accessing to the instance and/or database, and where and how a user’s password will be verified. Different types of DB2 authentication can be used in client-server applications to define where checking of a login and a password will be performed: on a server, on a client or on an intermediate DB2 Connect gateway. Authentication in SQL Server is always performed on the server side and it is implemented in a different way than in DB2. SQL Server allows both Windows Authentication and SQL Server Authentication also known as Mixed Authentication. That is why an authentication should be adjusted in accordance to the system architecture during the conversion.

To adjust a trusted connection, DB2 uses the instance configuration parameters TRUST_ALLCLNTS and TRUST_CLNTAUTH. When connecting to SQL Server by means of a Windows login, SQL Server relies upon the operation system to make authentication checking and checks only if the Windows user has the corresponding login defined in this instance of SQL Server or if this login belongs to a Windows group with the login corresponding to this SQL Server instance. Thus, it is enough to use Windows Authentication mode to implement a trusted connection in SQL Server.

AUTHORIZATION

Authorities in DB2 are implemented with predetermined groups of privileges for authorization at the instance level and at the level of a definite database. Authorities in SQL Server differ from those of DB2 as instead of predetermined groups of privileges, SQL Server has fixed server and database roles. In DB2, privileges of the instance level, SYSADM, SYSCTRL and SYSMAINT, can be granted only to users and user groups of operational system. That’s why it is necessary to create a login based on operational system login or users group in SQL Server to migrate DB2 authorities groups. The corresponding server or database role must be granted for this SQL Server login. It is important to remember that Windows local machine administrators and domain administrators are automatically mapped into SQL Server administrator roles.

To create a login in SQL Server based on Windows user or users group you should run a script like this:

create login [<servername>|<loginname>] from windows;

or

create login [<servername>|<groupname>] from windows;

DB2 SYSADM authority corresponds to the fixed server role SYSADMIN in SQL Server. DB2 SYSMAINT authority corresponds to the server role SERVERADMIN in SQL Server. There is no similar fixed role in SQL Server for SYSCTRL authority, that’s why you should grant all required permissions for users of this authority group explicitly, or grant server role SERVERADMIN with explicit granted permissions CREATE ANY DATABASE and ALTER ANY DATABASE. In the last case users will have all required permissions except the right to delete a database.

The following example illustrates creating a SQL Server login corresponding to DB2 SYSCTRL authority:

create login [<servername>|<groupname>] from windows;
go
exec sys.sp_addsrvrolemember @loginame = n’<servername>|<groupname>’,
     @rolename = n’serveradmin’;
grant create any database,
 alter any database
to [<servername>|<groupname>];
go

DBADM and LOAD DB2 privileges are applied only for a particular database, and they can be granted to an existing user group of operational system, DB2 user group and a separate user of operational system or database.

In SQL Server, privileges of DBADM authority correspond to the fixed database role db_owner. This role is granted to a database user. To complete the conformity of DBADM authority to db_owner role, users must be explicitly denied permission to backup database. To migrate DB2 LOAD authority, it is enough to grant database-level INSERT permissions to SQL Server users, which should have these privileges.

The following is the example of creating user in a database based on SQL Server login and granting a database role:

create user [<user_name>] for login [<servername>|<groupname>];
exec sys.sp_addrolemember db_owner, <user_name>;
go

PRIVILEGES

Permissions in SQL Server are similar to privileges in DB2. DB2 database-level privileges can be replaced with SQL Server database-level permissions. DB2 CONNECT and CREATETAB privileges correspond to SQL Server CONNECT and CREATE TABLE permissions accordingly. LOAD privileges can be replaced with database-level INSERT privileges. It is important to understand the difference between user’s permissions to load data into a table in DB2, and user’s permission to insert data into a table in SQL Server. In DB2, granting LOAD privileges does not automatically give permission to insert rows into a table and the privilege INSERT ON TABLE <table name> must be granted too. SQL Server INSERT permission allows performing both simple insert operations and bulk insert data into a table.

DB2 Example:

grant load on database  to user <user_name>;

SQL Server Example:

grant insert to <user_name>;

As an alternative for DB2 BINDADD permissions, such as CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED, the permissions CREATE PROCEDURE and CREATE FUNCTION can be used in SQL Server:

grant create procedure, create function to <user_name>;

Object-level DB2 privileges can be replaced with the similar SQL Server permissions or their combination.

DB2 Example:

grant createtab on database  to user <user_name>;
grant execute on function <function_name> to user <user_name>;
grant control on <view_name> to user <user_name>;

SQL Server Equivalent:

grant create table to <user_name>;
go
grant execute on <function_name> to <user_name>;
go
grant control on <view_name> to <user_name>;
go

Written by Yuri Kovtun

Tagged as DB2, Migration, security, SQL Server + Categorized as Newsletters, Newsletter #6, February 3, 2009