Chapter 7 - Developing: Databases - Migrating the Database Users

Article
12/05/2007

Introduction and Goals

Chapter 6, "Developing: Database — Migrating Schemas," concentrated strictly on the migration of the schema, its owner, and objects. The other class of database users is the one that accesses the data in the tables of these schemas. These users do not own any objects, such as tables or views, but may have some synonyms that may point to the tables and views in the application schema. The effort in migrating these users to Microsoft® SQL Server™ is in creating the users and reproducing the privileges they possess in the Oracle database. The privileges may be granted to the user directly or through roles. The migration of roles is also discussed in this chapter.

The steps in migrating the users are:

Create the user account.
Create roles and grant privileges.

Create User Accounts

A detailed discussion of database accounts, authentication, and password management was provided in the "Create the Schema Owner" section of Chapter 6. The creation of user accounts does not differ from the creation of schema accounts in any respect. The difference is in the privileges that they are granted. Figure 7.1 shows the relationship between database users, roles, and schema objects.

Figure 7.1 Dependencies in the migration of users from Oracle to SQL Server

The following query can be run in the source Oracle database to create a list of users that have privileges on any object in a specific schema. The query has been constrained to only a specific schema and its users. This aids in situations where only a subset of the schemas and the related users are migrated from an Oracle database.

SELECT grantee
FROM dba_tab_privs
WHERE owner = username
UNION
SELECT grantee
FROM dba_col_privs
WHERE owner = username

The grantee could be a user or a role. As with the case of the schema owner, the following query can be used to retrieve the characteristics of any login.

SELECT du.username, 
   DECODE(du.password,'EXTERNAL','EXTERNAL','DB') "AUTHENTICATION MODE", 
   du.default_tablespace, du.temporary_tablespace, 
   dp.resource_name, dp.limit
FROM dba_users du, dba_profiles dp
WHERE du.profile = dp.profile
AND dp.resource_type = 'PASSWORD'
AND du.username = username

The system stored procedure sp_grantlogin is used to create a SQL Server login for a domain-authenticated account, and sp_addlogin is used to create a SQL Server authenticated account. The procedure sp_grantdbaccess is used to create user

accounts in the individual databases for these logins. User accounts should be created in a database only if there are objects in the database the user needs to access.

Note While migrating Oracle accounts, there may be user accounts that are locked. The Oracle DBA of the original environment should be consulted before making decisions on whether to migrate these accounts.

Create Roles and Grant Privileges

A privilege is permission (as it is called in SQL Server) to perform work inside the database. Oracle and SQL Server provide two sets of privileges: system privileges and object privileges.

Table 7.1 provides terms relating to privileges in Oracle and SQL Server that can help avoid any confusion during a migration.

Table 7.1: Privilege Terms Used in Oracle and SQL Server

Oracle Terminology	SQL Server Terminology
Privilege	Permission
System privilege	Statement permission
Object privilege	Object permission
Predefined role permission (for example, DBA)	Implied permissions (for example, sysadmin)
Grantee	Security account

While Oracle has several object privileges, the ones commonly granted to users are SELECT, INSERT, DELETE, and UPDATE on tables and EXECUTE on stored programs. SQL Server has the same object privileges.

System and object privileges can be granted to users either directly or through roles. SQL Server also offers the WITH GRANT OPTION clause for object privileges.

Oracle and SQL Server differ a lot in the system privileges that are available. Oracle has very granular (more than 100) system privileges. SQL Server system privileges, called statement permissions, are restricted to the following list:

BACKUP DATABASE
BACKUP LOG
CREATE DATABASE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW

The rest of the Oracle system privileges are bundled into several large fixed roles. For example, the fixed database role db_datareader is equivalent to the SELECT ANY TABLE system privilege in Oracle.

Oracle and SQL Server offer predefined roles and user-defined roles. The predefined roles have powerful system and object privileges on the data dictionary and the instance. The "Roles" topic in SQL Server Books Online provides a list of fixed server roles and fixed database roles and their description. User-defined roles can be granted system as well as object privileges.

The privileges for administrative users should be revisited when migrating to a SQL Server database because each database is far more self-contained with respect to administration. Fixed database roles can be granted privileges on a single database instead of the system-wide powers afforded by the DBA role in Oracle. A complete discussion of the system privileges and their equivalents in SQL Server is beyond the scope of this guidance. For more information about the fixed server roles and fixed database roles available in SQL Server and their descriptions, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3xns.asp.

There are two important stored procedures for granting system privileges in SQL Server: sp_addsrvrolemember can be used for granting fixed system roles and sp_addrolemember can be used for granting fixed database roles.

In Oracle, roles are available at the instance or server level and can be granted privileges on more than one schema. SQL Server user-defined roles are local to a database and owned by a user. Hence, when migrating a role from Oracle to SQL Server, a role has to be created in each of the databases in which privileges have to be granted.

To grant privileges and roles to a user, follow these steps:

Create role(s) in each of the SQL Server database(s) that will be used to control the permission given to users. A role can be created in a database using the following commands:
```
Use database
```

GO sp_addrole [ @rolename = ] 'role_name' [ , [ @ownername = ] 'owner' ] GO

Where *role\_name* is the name of the role to be created and *owner* is the user who will own the role (which will normally be the schema owner or a security account for the application).

System level privileges can either be granted to the role created in step 1 or directly to the user. The following statement can be used for granting system privileges to roles and users:
```
GRANT {ALL | system_privilege_name}  
```

TO {user_name | role_name}

Where *system\_privilege\_name* is the system privilege to be granted, *user\_name* is the name of the user to whom the grant is made, and *role\_name* is the name of the role through which the privileges will be granted to the user.

As with system privileges, object privileges can be granted directly to the user or through roles. The following statement can be used to grant object privileges to users and roles:
```
GRANT {ALL | object_privilege_name}  
```

ON object_name
TO {user_name | role_name}
[WITH GRANT OPTION]

Where *object\_privilege\_name* is the object privilege to be granted, *object\_name* is the object on which the privilege is granted, *user\_name* is the name of the user to whom the grant is made, and *role\_name* is the name of the role through which the privileges will be granted to the user.

Object privileges can also be granted at the more granular column level instead of on the entire table. The following statement can be used to grant privileges on one or more columns of a table:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">GRANT {ALL | object_privilege_name}

ON table_name ( column_name [,...] ) TO {user_name | role_name}
[WITH GRANT OPTION]

Where *object\_privilege\_name* is the object privilege to be granted, *table\_name* is the table to which the column belongs, *column\_name* is the specific column on which the privilege is being granted, *user\_name* is the name of the user to whom the grant is made, and *role\_name* is the name of the role through which the privileges will be granted to the user.

If privileges are being granted through a role, the role can be granted to a user using the following command:
```
sp_addrolemember [ @rolename = ] 'role_name' ,  
```

[ @membername = ] 'user_name'

Where *role\_name* is the name of the role to be granted and *user\_name* is the target user.

Sample User Migration

When a user is migrated from Oracle to SQL Server, the same security architecture can be maintained by creating roles and granting privileges in a functionally equivalent manner. The difference in the implementation is depicted in Figure 7.1.

Privileges on objects in a database are given to a local user account at the database level and do not transgress database boundaries. This implies that, for a server or instance level login, a user account has to be created in each of the databases that it needs privileges in (either directly or through roles). The following steps are a practical example of how to migrate the security architecture of an application and its user from Oracle to SQL Server. This example is an extenuation of the migration of the HR schema in Oracle which was started in Chapter 6.

Create the user account in SQL Server for each of the Oracle users.

In Oracle, there is a single user account that is granted privileges on various schemas. In SQL Server, the single user account translates to a two-tier implementation.
1. Obtain a list of user accounts in the Oracle database that have to be migrated to SQL Server. The list can be obtained using the following statement:
```
SELECT grantee 
```

FROM dba_tab_privs WHERE owner = 'HR' UNION SELECT grantee FROM dba_col_privs WHERE owner = 'HR'

    where *owner* is the name of the schema whose security is being migrated.
    
    The following is the output for the HR schema.
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">GRANTEE

2.  Obtain the characteristics of user accounts in Oracle that were identified in substep a.
    
    The following statement can be executed to gather information on each of the users to be migrated:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT du.username,

     DECODE(du.password,'EXTERNAL','EXTERNAL','DB')
        "AUTHENTICATION MODE",
     du.default_tablespace, du.temporary_tablespace,
     dp.resource_name, dp.limit FROM dba_users du, dba_profiles dp WHERE du.profile = dp.profile AND dp.resource_type = 'PASSWORD' AND du.username = 'OE'

    where OE is the name of the user that is being migrated.
    
    The privileges granted to OE user are:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">USERNAME    AUTHENTICATION MODE    DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE RESOURCE_NAME LIMIT OE DB EXAMPLE TEMP FAILED_LOGIN_ATTEMPTS UNLIMITED OE DB EXAMPLE TEMP PASSWORD_LIFE_TIME UNLIMITED OE DB EXAMPLE TEMP PASSWORD_REUSE_TIME UNLIMITED OE DB EXAMPLE TEMP PASSWORD_REUSE_MAX UNLIMITED OE DB EXAMPLE TEMP PASSWORD_VERIFY_FUNCTION NULL OE DB EXAMPLE TEMP PASSWORD_LOCK_TIME UNLIMITED OE DB EXAMPLE TEMP PASSWORD_GRACE_TIME UNLIMITED

3.  Create SQL Server login accounts that provide access to the SQL Server instance.
    
    Because the Oracle account in this example is database-authenticated and none of the password features are in use, the SQL Server login can be created as a SQL Server-authenticated login.
    
    The following commands can be executed to create the SQL Server login for the user OE with the password OE in the HRDATA database into which the HR schema's objects are migrated:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Use MASTER

EXEC sp_addlogin @loginame='OE', @passwd='OE', @defdb='HRDATA'

4.  Create a user account in each of the databases in which the schema's objects have been migrated.
    
    The databases in which user accounts have to be created depend on the Oracle schemas for which the user has privileges and the SQL Server database to which they have been migrated. The following statement can be used to see the schemas on which the user OE has been granted privileges.
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT owner

FROM dba_tab_privs WHERE grantee = 'OE' UNION SELECT owner FROM dba_col_privs WHERE grantee = 'OE'

    The output of the statement is as follows:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">OWNER

HR SYS

    In Chapter 6, the HR schema was migrated to the HRDATA database in SQL Server. SYS is the data dictionary owner, which translates to master database in SQL Server. However, because the data dictionary is divided between the master database and the application (user-created) databases, for an application user, an account in the master database may not be necessary and should be created after proper scrutiny of the security requirements.
    
    The following commands can be used to create a user for the OE login in the HRDATA database:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Use HRDATA

EXEC sp_grantdbaccess @loginame='OE', @name_in_db='OE'

Create roles through which the privileges will be granted to users.

Only those roles that are granted to users and schemas that are being migrated will be created in SQL Server. Also, the role has to be created in each of the databases with the application schema as the owner.

The following statement can be used to retrieve the roles that have been granted to the user OE:
```
SELECT granted_role, admin_option
```

FROM dba_role_privs WHERE grantee = 'OE'

The following is the output for the OE user:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">GRANTED_ROLE    ADMIN_OPTION

CONNECT NO RESOURCE NO

The CONNECT and RESOURCE roles are both system roles. No application roles are in use for this schema. Hence all system and object privileges will be granted directly to the user. If you want to introduce roles in SQL Server, it should be performed at this point.

Grant privileges and/or roles to the user.

The roles that were identified in Step 2 are granted here.

The CONNECT role gives the user the capability to connect to the Oracle database. In SQL Server, the same privileges are inherently granted when the login and user accounts are created. The RESOURCE role gives the capability to create objects in the database. There is no role in SQL Server that provides the same level of privileges as RESOURCE. As a result, the following privileges are granted.
```
GRANT CREATE FUNCTION TO OE
```

GRANT CREATE PROCEDURE TO OE GRANT CREATE TABLE TO OE GRANT CREATE VIEW TO OE

The following statement is used to retrieve system and object privileges granted to the OE user:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT do.object_type Lvl, dtp.privilege, dtp.grantable, dtp.owner, dtp.table_name, NULL column_name

FROM dba_tab_privs dtp, dba_objects do WHERE dtp.owner = do.owner AND dtp.table_name = do.object_name AND grantee = 'OE' UNION SELECT 'Column', privilege, grantable, owner, table_name, column_name FROM dba_col_privs WHERE grantee = 'OE' UNION SELECT 'Sys Priv', privilege, admin_option, NULL, NULL, NULL FROM dba_sys_privs WHERE grantee = 'OE' ORDER BY privilege, grantable, owner, table name, column name

The following output is returned for the OE user:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml"> LVL        PRIVILEGE        GRANTABLE  OWNER  TABLE_NAME  COLUMN_NAME

Sys Priv CREATE SNAPSHOT NO Sys Priv QUERY REWRITE NO Sys Priv UNLIMITED TABLESPACE NO Table EXECUTE NO SYS DBMS_STATS Table REFERENCES NO HR COUNTRIES Table REFERENCES NO HR EMPLOYEES Table REFERENCES NO HR LOCATIONS Table SELECT NO HR COUNTRIES Table SELECT NO HR DEPARTMENTS Table SELECT NO HR EMPLOYEES Table SELECT NO HR JOBS Table SELECT NO HR JOB_HISTORY Table SELECT NO HR LOCATIONS

Based on the preceding output, grant statements can be devised as follows:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">GRANT SELECT ON countries to OE;

GRANT SELECT ON departments to OE; GRANT SELECT ON employees to OE; GRANT SELECT ON jobs to OE; GRANT SELECT ON job_history to OE; GRANT SELECT ON locations to OE;

SQL Server does not contain equivalents for CREATE SNAPSHOT, QUERY REWRITE, REFERENCES, and UNLIMITED TABLESPACE privileges.

The CREATE SNAPSHOT privilege is related to replication, and the QUERY REWRITE privilege is related to data warehousing, and both are beyond the scope of this guide. The REFERENCES privilege is for creating foreign key constraints across schemas which are not allowed in SQL Server. SQL Server does not impose storage quotas on schemas; an equivalent for REFERENCES is not needed in SQL Server.

Chapter 7 - Developing: Databases - Migrating the Database Users

On This Page

Introduction and Goals

Create User Accounts

Create Roles and Grant Privileges

Sample User Migration

Additional resources