|
Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server
authentication. Windows authentication allows the SQL server authentication to be integrated directly with security
on a Windows 2000/NT system. Authentication will depend on the security mode that SQL Server 2000
runs under, Windows Authentication mode, or Mixed Mode.
If you are running SQL Server 2000, you will need to configure the SQL server to run in mixed mode to allow
the non-Windows 2000/NT clients to connect where it will interact with computers running operating systems
other than Windows 2000 or Windows NT. Mixed mode allows clients who are not operating Windows
2000/NT to connect to a SQL 2000 server using SQL Server authentication.
If you are running in a fully Windows 2000/NT environment then you should configure the SQL server to run
under the Windows Authentication security mode. In Windows Authentication mode the SQL server relies on
Windows 2000/NT for security. While running under the Windows Authentication Mode, a server can utilize
security features such as password aging, complexity requirements, and encryption. The SQL server grants access
based on the Windows authentication of the user.
Auditing The basis of auditing in SQL Server 2000 is the server side SQL Trace and the client side viewing utilities. Auditing
is configured using the SQL Profiler. With the SQL Profiler you can create a custom auditing scheme
that allows you to audit nearly every event that is applied to server data and security.
SQL Server 2000 allows login auditing similar to auditing available on Windows 2000/NT. Administrators can
select the level of auditing that they require via the Enterprise Manager. Auditing keeps track of whenever a
user is successful or unsuccessful in their attempt to log on.
C2 auditing is a special type of auditing required by some government agencies that requires that every event
occurring be logged. SQL 2000 Server is capable of C2 auditing, but the overhead for maintaining this level of
auditing is very high.
Creating Logins Logins are used to prevent intentional or unintentional tampering from unauthorized users. Each login has a set
of sub-accounts that are linked to it that provide aliases for it. Logins can be created in three ways; in SQL
Server 2000, through the Enterprise Manager, or by using T-SQL commands.
Users A user is defined for a particular database and linked to a login via a sub-account. Users can be created through
the Enterprise Manager, the Login Wizard or through the use of T-SQL. Once a user is created, permissions
should be assigned to that reflect the job for which it was created. Permissions are assigned to a specific user
login. There are two types of permissions that can be assigned to a user. Server permissions are granted to allow
certain users to perform database administration tasks. Database permissions are used to control access to
database objects.
Permissions and Ownership of Database Objects Every object in a SQL database has a user who is designated as an owner or dbo. When an object is first created,
the only person who has the ability to perform any operations on the object is the dbo. The dbo will grant
other users permission to perform certain operations on the object.
For a user to be able to manipulate, perform operations on, or even see a database object the user must have
permissions to do so. Permissions on tables and views are:
- INSERT
- UPDATE
- DELETE
- SELECT
- REFERENCES
Alternatively, the owner of a database object may assign ALL permissions to grant a user all of the above. A
user must have the appropriate permissions before they can execute the corresponding statement. Owners of a
stored procedure can grant EXECUTE permission to a user to enable the user to run the stored procedure.
Database Roles The task of managing permissions to many database users can be simplified through the use of database roles.
Using database roles allows like permissions to be assigned to many users without having them being individually
assigned. Roles can be created and managed using the Enterprise Manager or through the use of T-SQL
Statements. There are several predefined roles that are established when SQL Server 2000 is installed:
- sysadmin: performs any server activity
- dbcreator: creates and alter databases
- diskadmin: manages disk files
- setupadmin: manages linked servers and startup procedures
- bulkadmin: performs bulk inserts
- serveradmin: sets any server option and shut down the database
- securityadmin: manages logins and create database permissions
- processadmin: manages SQL processes
Other roles can be defined and altered by the administrator after installation. Fixed roles cannot be modified.
Managing Security through Other Means Security can be managed through other means through limiting access to objects that could affect sensitive data.
Like permissions, access to functions and stored procedures can be explicitly restricted or explicitly granted. In
addition there are several system stored procedures that allow an administrator to manage security more easily:
- sp_grantlogin Allows a Windows 2000/NT user to connect with Windows authentication
- sp_addapprole Adds an application security role
- sp_changeobjectowner Changes the owner of an object in a database
- sp_changedbowner Changes the owner of a database
- sp_defaultlanguage Changes the default language of a login
- sp_addlogin Adds a SQL Server login that uses SQL Server Authentication
- sp_helplogins Supplies information about logins and users associated to the database
- sp_addrolemember Adds a security account as a member of an existing database role
- sp_grantdbaccess Adds a security account to a database and allows an administrator to grant permissions
Maintaining security and protecting information from triggers that may negatively alter sensitive information
can be achieved by establishing constraints. Properly established constraints can ensure that data integrity and
security is maintained across a database. | 
Disclaimer
