Export (0) Print
Expand All

Chapter 7 - Developing: Databases - Migrating the Database Users

Published: April 27, 2005
On This Page

Introduction and Goals Introduction and Goals
Create User Accounts Create User Accounts
Create Roles and Grant Privileges Create Roles and Grant Privileges
Sample User Migration Sample User Migration

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:

  1. Create the user account.

  2. 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

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 http://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:

  1. 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).

  2. 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.

  3. 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:

    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.

  4. 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.

  1. 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.

      GRANTEE 
      OE
    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:

      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:

      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:

      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.

      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:

      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:

      Use HRDATA
      EXEC sp_grantdbaccess
      @loginame='OE',
      @name_in_db='OE'
  2. 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:

    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.

  3. 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:

    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:

     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:

    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.

Download

Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows

Update Notifications

Sign up to learn about updates and new releases

Feedback

Send us your comments or suggestions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft