SQL Server 2000 Security – Part 4 (Authorization)

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:

  • sysadmin – grants its members complete control over the
    SQL Server, its databases, and all of their objects. The group initially
    contains two logins – sa SQL login and local BUILTINAdministrators Windows
    login. You can assign additional logins to it (both SQL and Windows), you can
    also remove Windows local BUILTINAdministrator group from it (if you intend to
    separate Windows and SQL server administration), however sa login membership
    can not be altered (and the account can not be deleted, disabled, or renamed).

  • serveradmin – intended for users responsible for the configuration
    of SQL Server. This typically consists of modifying server-wide settings and
    options, such as, the amount of memory or processor time allocated to the SQL Server
    or query governor behavior (in essence, all operations which can be performed
    with sp_configure stored procedure). Members of this role can also modify table
    options (covered by sp_tableoption stored procedure).

  • setupadmin – gives its members the power to control
    configuration settings for linked servers and stored procedures to be executed
    at startup.

  • securityadmin – provides the ability to manage security
    related settings, such as changing authentication mode, creating logins or
    database users, and granting, denying, or revoking permissions to create
    databases (execute CREATE DATABASE statement).

  • processadmin – limited to terminating processes with the
    KILL command (from T-SQL) or via graphical interface in SQL Server Enterprise
    Manager.

  • dbcreator – permits its members to create, drop, and
    modify databases (execute CREATE DATABASE, DROP DATABASE, and ALTER DATABASE
    statements).

  • diskadmin – exists strictly for backwards compatibility
    purpose – allowing its members to manage disk devices created in the SQL Server
    6.5.

  • bulkadmin – grants permissions to execute the BULK INSERT
    command, used to import large quantities of data into SQL Server.

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:

  • db_accessadmin – intended for administrators responsible
    for granting and revoking access to the database (which also implies the ability
    to create or drop users).

  • db_backupoperator – provides the ability to backup a
    database (but not restore it).

  • db_datareader – allows reading all database tables and
    views (executing SELECT statement against them).

  • db_datawriter – allows modifying content of all database
    tables and views (executing INSERT, UPDATE and DELETE statement against them). Due
    to the fact that functionality provided by this role includes the ability to
    delete all data, you should very carefully control its membership.

  • db_dlladmin – grants its members the ability to execute
    any Data Definition Language (DDL) command (which result in creation of
    database objects, such as tables, triggers, stored procedures, etc.). Members
    of this role who issue the CREATE statements automatically become their owners,
    which, in turn, means they have full control over them.

  • db_denydatareader – serving function reverse to db_datareader
    – denies read access to all tables and views (through DENY SELECT permissions).
    Since impact of the membership in this role cannot be overridden by granting
    permissions to individual objects, this serves as a convenient mechanism to
    secure them against particular users or groups of users.

  • db_denydatawriter – serving function reverse to db_datawriter
    – denies write access to all tables and views (through DENY INSERT, DENY UPDATE
    and DENY DELETE permissions). Just as with the db_denydatareader role, impact
    of the membership in this role cannot be overridden by granting permissions to
    individual objects, so this can also be conveniently used to secure them
    against particular users or groups of users.

  • db_owner – the most powerful role on the database level
    (equivalent to the sysadmin on the SQL server level) with full administrative
    control over all database objects and operations. The role initially contains a
    single user dbo, but additional user accounts can be added to it.

  • db_securityadmin – its members have power to grant, revoke
    and deny permissions on every object in the database, in addition to managing
    membership of fixed and custom roles.

  • public – contains all database users and roles (resembling
    Everyone Windows group) and its membership can not be altered. This is
    important to remember, since it means that all users who are allowed to access
    a particular database are automatically granted all permissions assigned to
    public role. In general, you should avoid granting permissions to public role.

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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles