Database Security in SQL Server 6.5 - Aliasing Example

Let’s consider an example to clarify the way this process works. Assume that JoeS has the SUID of 11 and JaneD has the SUID of 12. In the Accounting database, there is a row in the sysusers table assigning JoeS the UID of 4, but there is no corresponding row containing JaneD’s SUID. The table’s entire contents are shown here:

However, there is a row in sysalternates mapping JaneD’s SUID of 12 to JoeS’s SUID of 11. sysalternates’s contents are as follows:

When JaneD attempts to use the Accounting database, SQL Server queries the sysusers table. Finding no match for SUID 12, it checks the sysalternates table to see if there is a row with 12 in the suid column. Finding that row, SQL Server assigns JaneD the UID 4 and the database username JoeS. At that point, she impersonates JoeS’s identity and receives all of his permissions.

Note that checking the sysalternates table comes after checking the sysusers table and before checking to see if the guest user account is enabled.

If JaneD’s SUID were also in sysusers, she would have gained access using her own credentials, not JoeS’s. The net effect is that a user can have their SUID in either sysusers or sysalternates. If it somehow happens to end up in both tables, the identity in the sysuser’s table takes precedence over the alias in sysalternates.

The main reason for using aliases is to allow multiple users to impersonate the database owner account, dbo, which has some permissions in the database that cannot be duplicated by assigning individual permissions to another database user. SQL Server 6.5 permits only one login account to be designated as dbo; therefore, the only way for other users to perform some actions in a database is to be aliased to dbo.

The typical scenario is that multiple database developers need full access to the database during the development phase. The problem is that there can be only one database owner. sysusers has a row for dbo that maps the SUID of the person who created the database to UID 1. That UID has been hard-coded in the database engine with the special permissions. You can change the SUID mapped to dbo, but you cannot change its UID. If more than one person needs dbo permissions, the only way to make it work is by adding rows to sysalternates that map other SUIDs to the SUID assigned to dbo.

sp_addalias is the system stored procedure that performs the task of inserting rows in sysalternates. Its syntax is as follows:

sp_addalias [ @loginame = ] 'login'
    , [ @name_in_db = ] 'alias_user'

One real benefit of aliases is that aliasing one SUID to another does not have any effect outside the database. Recall that the sa account always has the SUID of 1 and that SQL Server is written so that it completely bypasses all security checks for sa. The standard practice is to have system administrators create databases; therefore, sa will typically be the database owner, which means the row for dbo in sysusers will contain a SUID of 1. Using aliases, you can grant someone the status of dbo without giving that user all the power of the sa account. Without aliases, the only recourse would be to allow anyone who needed full access to a database to log into SQL Server with the sa account. Although it is a very common practice for developers to use the sa account, it is nonetheless a very bad practice.

It is important to note that SQL Server checks database access whenever a user attempts to access database objects. The typical example is when a user issues the Transact-SQL (T-SQL) USE <database> command. However, if you refer to a table using its full name—that is, <database.owner.tablename>—for a table outside the currently selected database, then SQL Server will perform all three checks in the specified database to see if the user has been granted access.

For example, if a user issues the following command:

USE Northwind
SELECT * FROM pubs.dbo.authors

Having set the current database to Northwind, SQL Server then checks to see if there is a row in sysusers in the Pubs database that contains the user’s SUID before it begins processing the SELECT query. If there is no matching row in sysusers, SQL Server will check the sysalternates table in Pubs for the user’s SUID. If that check fails, it will check to see if the guest database account has been enabled in Pubs. In this case, because the default setup enables the guest user account in the Pubs database, everyone has permission to access Pubs. If all three checks fail, the user will be denied permission to run the query.

If you look back at the definition of sysusers, you will see it also has a name column. This allows an administrator to give a name to a user, which can be different from the one specified in Master..syslogins. Its usefulness is fairly limited, and Enterprise Manager defaults to creating a user account with the same name as the login account.

The one situation, however, in which it can be useful to have a username different from a login name, is when the user logs in with a Windows NT/2000 authenticated account. The username Joe Smith is easier to read than the login name domain_JoeS for things such as reports and activity logs. For example, on a recent web project, I put the user’s full name in the name column instead of creating a table that pretty much duplicated sysusers. It eliminated the need for a multitable join query to map the login name to the name the user supplied on the web site. It is the only time I have ever used the feature, but it did help performance a little bit.

After the user issues the T-SQL USE <database> command, he will have four names within SQL Server. There are four system functions to find these names:

• Two within the server:

•    The SUID returned by SUSER_ID()

•    The system username returned by SUSER_NAME()

•  Two within the database:

•     The database UID returned by USER_ID()

•     The database username returned by USER_NAME()

NOTE  Note that using an alternative username should not be confused with aliasing. Aliasing maps a user’s SUID to another database user’s SUID, whereas the database username is just another way of referring to a user. SQL Server still uses the UID for identification internally; therefore, the username has no effect beyond giving you the option of using a different name from the login name.

The following is the syntax for the two system stored procedures, sp_adduser and sp_dropuser, used in SQL Server 6.5 to add and drop users, respectively:

sp_adduser [ @loginame = ] 'login'
            [ , [ @name_in_db = ] 'user' ]
            [ , [ @grpname = ] 'group' ]
sp_dropuser [ @name_in_db = ] 'user'

For the sp_adduser stored procedure

• The @loginame parameter specifies the login name from the syslogins table. Note that although SQL Server 6.5 uses the SUID internally, the stored procedure uses the name itself.

• The @name_in_db parameter is the name the user will have within the database.

Common practice is to make the database username match the login name, but it is not a requirement. It does get confusing, however, if the two names do not match.

The @grpname parameter specifies the user’s default database group, which I cover in the next section.

Next: Database Groups >>
 

More MS SQL Server Articles
More By Apress Publishing

Comments On: Database Security in SQL Server 6.5

>>> Be the FIRST to comment on this article!