SQL Server 2000 Security - Part 4 (Authorization)
May 28, 2004
In our series, so far, we have been discussing topics related to authentication, which establishes identity of a login attempting to access SQL Server. Starting with this article, we shift our focus to authorization, which takes place once the submitted credentials (in the form of a login name and password or Windows access token, depending on authentication mechanism) are validated. The authorization process determines the level of privileges granted to SQL Server, its databases and all of their manageable properties and objects. This determination is based on checking associations between a login name and server roles for SQL Server level privileges and a login name and database users and roles for database level privileges (there are also application roles, which we will cover separately). The first of these two processes occurs as soon as a successful login takes place; the second is triggered for every database accessed afterwards. Let's analyze in more detail server roles (we will look into database users, database roles, and application roles next).
Server roles are used to control delegation of server-wide management features and settings. SQL Server designers decided to limit room for potential misconfiguration mistakes and disallowed the creation of custom roles. Instead, they looked into common tasks that are performed routinely as part of server administration and defined corresponding fixed (non-modifiable) server roles:
You can assign a login to any of the fixed server roles listed above using either SQL Enterprise Manager (by expanding Security folder, double-clicking on the appropriate login listed under Logins node, selecting Server Roles tab on the Login Properties dialog box, and placing a checkbox next to the server role to which that login should be assigned) or from Query Analyzer window (by running sp_addsrvrolemember stored procedure).
Access rights to each database are determined based on a number of factors. The first one is association between a login used during initial authentication and the name of a database user. When a database is created, the only account that is defined in it by default is dbo (of course this can be changed by including additional users in the model database), which maps to the members of sysadmin fixed server role and grants unlimited access to the database and all of its objects. For non-sysadmin logins, access can be granted by either mapping them to the dbo user (only if such login requires full management rights to the database), by creating a separate users account (by selecting New Database User option from the context sensitive menu of the Users container under database node in Enterprise Manager) and creating appropriate mapping (associating login name and user name in the Database User Properties dialog box), or by creating a guest account. The last option should be avoided, since it grants access to the database to anyone with login rights to the server (for logins without explicit mapping to a database user, database level rights are equivalent to those associated with the guest account).
This process of determining which database user should be used is performed by comparing the login stored in the sysxlogin master database table and a user name contained in the sysusers table, residing in each database. In case of a match, login is granted access to the database with rights assigned to the matching user account. Otherwise, access is denied, unless the guest account exists in the target database, in which case, its access rights are used.
The next factor determining access rights to a database takes into consideration database roles. Here, SQL Server designers granted more flexibility than with server roles, by allowing two different role types - standard and application. You also have an option of defining your own custom roles, in addition to fixed (predefined and non-modifiable) ones, which include:
In order to define your own custom database roles, you can use either Enterprise Manager (by selecting the New Database Role option from the context sensitive menu of the Roles subnode listed under respective database node) or Query Analyzer (by executing sp_addrole stored procedure). Once the roles are defined, you add the users to them either from the Database Role Properties dialog box or with sp_addrolemember stored procedure (this applies to both fixed and custom standard database roles).
In our next article, we will continue our discussion of database roles and ways to manage permissions to database objects and operations.