同学录论文
credentials and sensitive data passed to and from SQL Server.
4. Authenticating callers at the database. SQL Server supports Windows authenti- cation (using NTLM or Kerberos) and SQL authentication (using SQL Server ’s
built-in authentication mechanism).
5. Authorizing callers at the database. Permissions are associated with individual database objects. Permissions can be associated with users, groups, or roles.
Windows Authentication
Windows authentication is more secure than SQL authentication for the following reasons:
(1) Credentials are managed for you and the credentials are not transmitted over the network.
(2)You avoid embedding user names and passwords in connection strings. (3)Logon security improves through password expiration periods, minimum
lengths, and account lockout after multiple invalid logon requests. This mitigatesthe threat from dictionary attacks.
Use Windows authentication in the following scenarios:
You have used the trusted subsystem model and you connect to SQL Server using a single fixed identity. If you are connecting from ASP.NET, this assumes that the Web application is not configured for impersonation.
In this scenario, use the ASP.NET process identity or a serviced component identity (obtained from the account used to run an Enterprise Services server application). You are intentionally delegating the original caller ’s security context by using delegation (and are prepared to sacrifice application scalability by foregoing database connection pooling).
Consider the following key points when you use Windows authentication to connect to SQL Server:
Use the principle of least privilege for the ASP.NET process account. Avoid giving the ASP.NET process account the ―Act as part of the operating system‖ privilege to enable LogonUser API calls.
2
同学录论文
Determine which code requires additional privileges, and place it within serviced components that run in out-of-process Enterprise Services applications. Using Windows Authentication
You have the following options when you use Windows authentication to connect to SQL Sever from an ASP.NET application (or Web service, or remote component hosted by ASP.NET):
(1) Use the ASP.NET process identity. (2) Use fixed identities within ASP.NET. (3) Use serviced components.
(4) Use the LogonUser API and impersonating a specific identity. (5) Use the original caller ’s identity. (6) Use the anonymous Internet User account. Recommendation
The recommendation is to configure the local ASP.NET process identity by changing the password to a known value on the Web server and create a mirrored account onthe database server by creating a local user with the same name and password. Further details for this and the other approaches are presented below. Using the ASP.NET Process Identity
If you connect to SQL Server directly from an ASP.NET application (or Web s ervice,or remote component hosted by ASP.NET), use the ASP.NET process identity. This is a common approach and the application defines the trust boundary, that is, the database trusts the ASP.NET account to access database objects. You have three options:
? (1) Use mirrored ASPNET local accounts. ? (2) Use mirrored, custom local accounts. ? (3) Use a custom domain account. Use Mirrored ASPNET Local Accounts
This is the simplest approach and is the one generally used when you own the target database (and can control the administration of local database-server accounts). With this
3
同学录论文
option, you use the ASPNET least-privileged, local accountto run ASP.NET and then create a duplicated account on the database server. SQL Authentication
If your application needs to use SQL authentication, you need to consider the following key points:
Use a least-privileged account to connect to SQL.
Credentials are passed over the wire so they must be secured.
The SQL connection string (which contains credentials) must be secured. Connection String Types
If you connect to a SQL Server database using credentials (user name and pass- word) then your connection string looks like this: SqlConnectionString =
\Database=YourDatabase;
uid=YourUserName;pwd=YourStrongPassword;\If you need to connect to a specific instance of SQL Server (a feature available only in SQL Server 2000 or later) installed on the same computer then your connection string looks like this:
SqlConnectionString = \Database=YourDatabase;uid=YourUserName; pwd=YourStrongPassword;\
If you want to connect to SQL Server using your network credentials, use the Integrated Security attribute (or Trusted Connection attribute) and omit the user- name and password:
SqlConnectionString = \Database=YourDatabase; Integrated Security=SSPI;\
- or -
4
同学录论文
SqlConnectionString =
\Database=YourDatabase; Trusted_Connection=Yes;\
If you are connecting to an Oracle database by using explicit credentials (user name and password) then your connection string looks like this: SqlConnectionString = \
Source=YourDatabaseAlias; User
ID=YourUserName;Password=YourPassword;\
Note that this does not address the issue of storing credentials in plain text in your Web.config files. All you’ve done so far is limit the scope of damage possible in the event of a compromise, by using a least-privileged account. To further raise the security bar, you should encrypt the credentials. Authorization
SQL Server provides a number of role-based approaches for authorization. These revolve around the following thee types of roles supported by SQL Server:
? User-defined Database Roles. These are used to group together users who have the same security privileges within the database. You add Windows user or group accounts to user database roles and establish permissions on individual database objects (stored procedures, tables, views, and so on) using the roles. ? Application Roles. These are similar to user database roles in that they are used when establishing object permissions. However, unlike user database roles, they do not contain users or groups. Instead, they must are activated by an applica- tion using a built-in stored procedure. Once active, the permissions granted to the role determine the data access capabilities of the application.
Application roles allow database administrators to grant selected applications access to specified database objects. This is in contrast to granting permissions to users. ? Fixed Database Roles. SQL Server also provides fixed server roles such as
5
同学录论文
db_datareader and db_datawriter. These built-in roles are present in all data- bases and can be used to quickly give a user read specific (and other commonly used) sets of permissions within the database.
Using Multiple Database Roles
If your application has multiple categories of users, and the users within each category require the same permissions within the database, your application re- quires multiple roles.
Each role requires a different set of permissions within the database. For example, members of an Internet User role may require read-only permissions to the majority of tables within a database, while members of an Administrator or Operator role may require read/write permissions. Options
To accommodate these scenarios, you have two main options for role-based authori- zation within SQL Server:
? User-defined SQL Server Database Roles. These are used to assign permissionsto
database objects for groups of users who have the same security permissions within the database.
When you use user-defined database roles, you check at the gate, map users to roles, (for example, in an ASP.NET Web application or in a middle-tier serviced component in an Enterprise Services server application) and use multiple identi- ties to connect to the database, each of which maps to a user-defined database role.
? SQL Application Roles. These are similar to user-defined database roles in that they are used when you assign permissions to database objects. However, unlike user-defined database roles, they do not contain members and are activated from individual applications by using a built-in stored procedure.When you use application roles, you check at the gate, map users to roles, con- nect to the database using a single, trusted, service identity, and activate the appropriate SQL application role. User-Defined Database Roles
6