|
|
So far we have covered some important topics when it comes to data storage and SQL Server. The last topic we will cover before taking you through our demo application in the next chapter is SQL Server security.
In Chapter 4, we discussed authentication and authorization. Authentication occurs when a user or application tries to log on to your SQL Server. Authorization determines what the user is allowed to do in the database server and its tables. In the following section, we will take a look at how authentication works in SQL Server.
There are two ways SQL Server can authenticate a user or group. Early in the design process, you should decide which method you are going to use, because each affects the way you set up your user accounts for your application. The two methods are
SQL Server authentication
Windows authentication
If you use SQL Server authentication, SQL Server matches the account and password the user supplies to a list that is stored in the sysxlogins system table. This table is found in the master database (see Figure 8-19). SQL Server authentication is quite easy to implement, and you use SQL Server Enterprise Manager to add, remove, or modify these logins (see Figure 8-20).
The logins created are local to the server on which the SQL Server resides, which is not a good solution if you have a multiserver environment. In that case, you cannot manage them very easily, and you would have to implement the accounts on several servers. This solution could work, however, if the number of users does not exceed 20 to 25. With more, you will soon discover how impractical this is, and how much administrative overhead this will cost. Imagine implementing a change to only one user on several servers.
What you should use instead in the preceding scenario is Windows authentication. When SQL Server authenticates using this method, it asks a domain controller to validate a user's credentials. A domain controller must be accessible for this to work, so you need to make sure one is available. When SQL Server authenticates a user or group against a domain controller, it receives an access token containing the user's SID and the SIDs of every group the user has membership in. SQL Server will then assign access to the database server based on these SIDs.
You can use Active Directory (AD) to store your users and groups for your applications, but in many cases you do not want to mix your internal corporate users with your application users. The SQL Server will perhaps be used only for a Web solution and not needed by anyone but those accessing the Web site. In that case, it could present a security risk to use the same AD as for your enterprise. To solve this, you could implement a separate AD infrastructure and set up a new server and domain, but this is not something you can do just like that. Luckily, Microsoft recently released a "light" version of Active Directory, called Active Directory Application Mode, or ADAM. ADAM is a stand-alone version of AD, intended for the use as a directory for Web-based applications and other types of applications. ADAM is deployed separately from the standard AD, and its directory data is not replicated throughout the enterprise core NOS directory. It also gives administrators the flexibility to deploy a directory without having to set up an entire Windows Server operating system environment on a domain controller. Nor do they have to activate Kerberos, DNS, or PKI.
ADAM is Microsoft's answer to Web-based directories from competitors like Sun's ONE Directory Server and Novell's eDirectory. (Given Microsoft's aggressive marketing in the past, ADAM will probably be seen on a server near you rather soon.)
Windows authentication gives you central management control regardless of whether you use AD or ADAM, which will give you lower TCO over time.
There are also ways of accessing SQL Server over the Internet using IIS, but this solution is not especially relevant when it comes to building an enterprise application, so we will not cover it here.
After you have decided which authentication method you should use, you must plan what permissions you will give to your users and groups. (We will in this discussion consider only Windows authentication.) You should strive to give permissions to your database server only to groups. If you do, you can more easily manage who has access to the server. Say you have a group of users belonging to the accounting department. Often these users will need the same permissions in the SQL Server. You do not want to add every user from this department to the database server and give them each the same permissions. What you do want to do is create a domain global group, add the accounting users to this group, and give only the domain global group database access. So instead of granting 30 people access and permissions on the server, you only do this for the domain global group they belong to, which cuts down on administrative overhead.
When you want to give permissions in a database on your SQL Server, you can use something called roles. There are some built-in roles you can use (see Figure 8-21), but you can also create your own. You give permissions only to roles, and then make the domain global groups members of those roles.
You create the roles you need, name them so you understand what they are, and then add the correct domain global group to the role. After that, you assign database permissions to it.
| Note |
A good practice is to name roles after the domain global groups, so as not to cause confusion. |
This procedure is the same as is often used to give users and groups access to data on a server. The global groups are members of local groups, and it is only the local groups that are given permissions to the data.
|
|
