Database Security and Roles

To adequately migrate your Oracle applications to Microsoft SQL Server 7.0, you must understand SQL Server’s implementation of database security and roles.

Login Accounts

A login account allows a user to access SQL Server data or administrative options. The login account allows users only to log in to SQL Server and view databases that allow guest access. (The guest account is not set up by default and must be created.)

SQL Server offers two types of login security: Windows NT Authentication Mode (also known as integrated) and SQL Server Authentication Mode (also known as standard). SQL Server 7.0 also supports a combination of standard and integrated security, known as mixed.

The Windows NT Authentication Mode uses the security mechanisms within Windows NT when validating login connections, and relies on a user’s Windows NT security credentials. Users do not need to enter login IDs or passwords for SQL Server–their login information is taken directly from the network connection. When this occurs, an entry is written to the syslogins table and is verified between Windows NT and SQL Server. This is known as a trusted connection and works like a trust relationship between two Windows NT servers. This functions like the IDENTIFIED EXTERNALLY option associated with Oracle user accounts.

The SQL Server Authentication Mode requires that a user enter a login ID and password when requesting access to SQL Server. This is known as a nontrusted connection. This functions like the IDENTIFIED BY PASSWORD option associated with Oracle user accounts. With the use of the standard security model, the login provides access to the SQL Server database engine only; it does not provide access to the user databases.

For more information about these security mechanisms, see SQL Server Books Online.

Groups, Roles, and Permissions

Microsoft SQL Server and Oracle use permissions to enforce database security. SQL Server statement-level permissions are used to restrict the ability to create new database objects (similar to the Oracle system-level permissions).

SQL Server also offers object-level permissions. As in Oracle, object-level ownership is assigned to the creator of the object and cannot be transferred. Object-level permissions must be granted to other database users before they can access the object. Members of the sysadmin fixed server role, db_owner fixed database role, or db_securityadmin fixed database role can also grant permissions on one user’s objects to other users.

SQL Server statement- and object-level permissions can be granted directly to database user accounts. However, it is often simpler to administer permissions to database roles. SQL Server roles are used for granting and revoking privileges to groups of database users (much like Oracle roles). Roles are database objects associated with a specific database. There are a few specific fixed server roles associated with each installation, which work across databases. An example of a fixed server role is sysadmin. Windows NT groups can also be added as SQL Server logins, as well as database users. Permissions can be granted to a Windows NT group or a Windows NT user.

A database can have any number of roles or Windows NT groups. The default role public is always found in every database and cannot be removed. The public role functions much like the PUBLIC account in Oracle. Each database user is always a member of the public role. A database user can be a member of any number of roles in addition to the public role. A Windows NT user or group can also be a member of any number of roles, and is also always in the public role.

Database Users and the guest Account

In Microsoft SQL Server, a user login account must be authorized to use a database and its objects. One of the following methods can be used by a login account to access a database:

Members of the db_owner or db_accessadmin roles, or the sysadmin fixed server role, create the database user account roles. An account can include several parameters: the SQL Server login ID, database username (optional), and up to one role name (optional). The database username does not have to be the same as the user’s login ID. If a database username is not provided, the user’s login ID and database username are identical. If a role name is not provided, the database user is only a member of the public role. After creating the database user, the user can be assigned to as many roles as necessary.

Members of the db_owner or db_accessadmin roles can also create a guest account. The guest account allows any valid SQL Server login account to access a database even without a database user account. By default, the guest account inherits any privileges that have been assigned to the public role; however, these privileges can be changed to be greater or less than that of the public role.

A Windows NT user account or group account can be granted access to a database, just as a SQL Server login can. When a Windows NT user who is a member in a group connects to the database, the user receives the permissions assigned to the Windows NT group. If a member of more than one Windows NT group that has been granted access to the database, the user receives the combined rights of all of the groups to which he belongs.

The sysadmin Role

Members of the Microsoft SQL Server sysadmin fixed server role have similar permissions to that of an Oracle DBA. In SQL Server 7.0, the sa SQL Server Authentication Mode login account is a member of this role by default, as are members of the local Administrators group if SQL Server is installed on a Windows NT computer. A member of the sysadmin role can add or remove Windows NT users and groups, as well as SQL Server logins. Members of this role typically have the following responsibilities:

*These items can be delegated to other security roles or users.

There are no restrictions on what a member of the sysadmin fixed server role can do in SQL Server 7.0. Therefore, a member of this role can access any database and all of the objects (including data) on a particular instance of SQL Server. Like an Oracle DBA, there are several commands and system procedures that only members of the sysadmin role can issue.

The db_owner Role

Although a Microsoft SQL Server database is similar to an Oracle tablespace in use, it is administered differently. Each SQL Server database is a self-contained administrative domain. Each database is assigned a database owner (dbo). This user is always a member of the db_owner fixed database role. Other users can also be members of the db_owner role. Any user who is a member of this role has the ability to manage the administrative tasks related to her database (unlike Oracle, in which one DBA manages the administrative tasks for all tablespaces). These tasks include:

Members of the db_owner role have permissions to do anything within their database. Most rights assigned to this role are separated into several fixed database roles, or can be granted to database users. It is not necessary to have sysadmin server-wide privileges to have db_owner privileges in a database.