SPECIFYING EXECUTION CONTEXT FOR PROCEDURAL CODE pre-SQL

SPECIFYING EXECUTION CONTEXT FOR PROCEDURAL CODE pre-SQL Server 2005 execution context this will execute as the direct caller CREATE PROCEDURE count_rows(@name NVARCHAR(50) WITH EXECUTE AS CALLER AS EXECUTE( SELECT COUNT(*) FROM + @name) GO this will execute as the stored procedure creator CREATE PROCEDURE count_rows_as_me(@name NVARCHAR(50)) WITH EXECUTE AS SELF AS EXECUTE( SELECT COUNT(*) FROM + @name) GO this will execute as a specific user CREATE PROCEDURE count_rows_as_fred(@name NVARCHAR(50)) WITH EXECUTE AS FRED AS EXECUTE( SELECT COUNT(*) FROM + @name) GO Note that the third option is just a convenience for a DBA running a CREATEscript. It saves the DBA from having to do a SETUSERFRED(change the current user to FRED) before executing the CREATEstatement. The second option shows how ownership chaining affects stored procedures that make use of dynamic SQL. Prior to SQL Server 2005, permission was always checked against the identity of the caller of a stored procedure when it referenced a database object using dynamic SQL. That is still the default behavior in SQL Server 2005. EXECUTE AS SELF can be used in the definition of the stored procedure so that even though permission will be checked when dynamic SQL is used, the behavior will be the same as static SQL. Figure 6-3 shows using EXECUTE AS SELF to make dynamic SQL behave the same as static SQL. Special care must be taken to guard against SQL injection (that is, piggybacking of dangerous code after normal parameters) when EXECUTE AS SELFis used. Although counting the rows in a table is pretty mundane code, the fact is that any dynamically constructed code in a stored procedure can be potentially dangerous. Given the count_rows_as_mestored procedure in the previous example, if the procedure was cataloged by the DBO role, the following code will execute as DBO, regardless of the user who calls it. DECLARE @s VARCHAR(50) SET @s = authors;drop table customers count the rows and drop the table! count_rows_as_me @s

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

SECURITY Specifying Execution Context for Procedural Code In

SECURITY Specifying Execution Context for Procedural Code In previous versions of SQL Server, cataloged procedural code always ran within the security context of the caller, as explained earlier in the section on ownership chaining. This is a good strategy for the most common case for example, when you want to allow users access to tables through stored procedures without giving them access to the base tables. However, it is not always what you want. Take, for example, a stored procedure that executes dynamic SQL composed by concatenating strings. This does a type of indirect parameterization of the table name. This is necessary because you may want to build a query with a table name as a parameter. this won t work CREATE PROCEDURE count_rows(@name NVARCHAR(50)) AS SELECT COUNT(*) FROM @name GO this will the dynamic SQL executes in the caller s context CREATE PROCEDURE count_rows(@name NVARCHAR(50)) AS EXECUTE( SELECT COUNT(*) FROM + @name) GO SQL Server 2005 now allows you to specify that procedural code execute in a different execution context. There are three reasons you might want to do this. You want dynamic SQL to execute in the context of the creator of the stored procedure, as static T-SQL would. Since data access code in CLR procedures (through the SqlServer data provider discussed in Chapter 4) is effectively dynamic SQL, you might want this code to execute in the context of the creator of the stored procedure as well. You want to evaluate ownership chains in the context of the creator of the stored procedure rather than the caller of the procedure. You choose the execution context on a per-procedure basis when you create the procedure, using the EXECUTE AS parameter. Execution context can also be set on user-defined functions, except for inline table-valued user-defined functions. Examples are shown in the following code.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

SEPARATION OF USERS AND SCHEMAS Synonyms SQL Server

SEPARATION OF USERS AND SCHEMAS Synonyms SQL Server 2005 introduces support for a database object known as a synonym. A synonym is just an alternate name for an existing database object that keeps a database user (more likely, a database programmer) from having to use a multipart name for an object. Synonyms can be defined on a two-part, three-part, or four-part SQL Server object name. A synonym can be defined by the following database objects: Table View Stored procedure User-defined function Extended stored procedure Replication filter procedure Although synonyms can be created on a multipart object name, they are scoped to the database that they are created in. Here are some examples of creating and using synonyms. USE pubs GO CREATE SYNONYM customers_east FOR eastserver.northwind.dbo.customers GO CREATE SYNONYM employees FOR payroll.employees GO these work SELECT * FROM customers_east SELECT * FROM employees GO USE northwind GO so does this SELECT * FROM pubs..customers_east

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

SECURITY default named schema CREATE USER janet

SECURITY default named schema CREATE USER janet FOR LOGIN janet WITH DEFAULT_SCHEMA = prschema GO CREATE ROLE payroll — if it does not exist GO sp_addrolemember payroll , janet CREATE SCHEMA prschema AUTHORIZATION payroll GO GRANT CREATE TABLE TO janet GO Now, user janet can create tables, and they will be contained within the prschemaschema. If Janet is reassigned, the user janetcan be dropped from the database without affecting any of the tables she has created. Having named schemas affects the way database object names are resolved. If Janet issues the SQL statement SELECT*FROM benefits, SQL Server will attempt to resolve the table name benefitsin this order: 1. prschema.benefits(using the default schema) 2. dbo.benefits 3. sys.benefits One further special case needs to be mentioned. It is possible that a database user will have a default schema that she does not own (such as dbo), but will have the ability to create database objects in a different schema. In that case, the database object in the CREATEDDL statement must explicitly use the two-part name. For example, if user janetwas defined without a default schema keyword, her default schema would be dbo, since she is not a member of the dborole. this statement would fail CREATE TABLE benefits2003 (empid INT) other columns elided this statement would succeed CREATE TABLE prschema.benefits2003 (empid INT) Schemas have their own sets of permissions. You can grant or deny permissions like SELECT, EXECUTE, or VIEW DEFINITION on a schema-wide basis. The following SQL statement prohibits the group public from seeing any database objects in the bobschema using the system views. DENY VIEW DEFINITION ON schema::bob TO public

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SEPARATION OF USERS AND SCHEMAS The owner of

SEPARATION OF USERS AND SCHEMAS The owner of a schema (a single user or multiple users) can create database objects within that schema and also grant schema-level privileges to others. The schema owner does have to be granted permission to create the database objects, but the grant permission exists on a database level, not on a schema level. Here s an example of a user that has an associated schema and is also the owner of that schema. USE demo1 GO CREATE LOGIN alogin1 WITH password = password1 , DEFAULT_DATABASE = demo1 GO default named schema CREATE USER auser1 FOR LOGIN alogin1 WITH DEFAULT_SCHEMA = aschema1 GO CREATE SCHEMA aschema1 AUTHORIZATION auser1 GO GRANT CREATE TABLE TO auser1 GO SETUSER auser1 GO this works and creates aschema1.table1 CREATE TABLE table1 (theid INTEGER) go In this case, if we did not set a default schema for the auser1 user, his default schema would be dbo. Because auser1 is not a member of the dbo database role, the CREATE TABLEstatement would fail. What this means to the database administrator is that because schemas (and the objects they contain) can be owned by a role, an application role, or a Windows group, when a user is dropped from the database, the database objects she has have created do not have to be reassigned or dropped and re-created. Here s an example using a SQL Server role for a payroll system. We ll assume that a role called payrollhas already been created. USE payrolldb GO CREATE LOGIN janet WITH PASSWORD = temppwd , DEFAULT_DATABASE = payrolldb GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SECURITY a CREATE SCHEMADDL statement, but you did

SECURITY a CREATE SCHEMADDL statement, but you did not have the option of naming your schema, only its owner. SQL Server 2000 create schema, no schema name CREATE SCHEMA AUTHORIZATION fred GRANT SELECT ON v1 TO public CREATE VIEW v1 AS SELECT au_id, au_lname FROM authors GO SQL Server 2005 create schema with name CREATE SCHEMA fredstuff AUTHORIZATION fred This pre SQL Server 2005 CREATE SCHEMA statement actually was a convenient way to create objects that belonged to a specific user (like fred, in this case) and grant permissions to them in a single DDL statement batch. The problem of having database objects tied to a particular user was that in order to drop the user, the database administrator had to reassign or drop and re-create all of that user s database objects. SQL Server 2005 introduces the concept of named schemas as separate from users. When you use the new CREATE USER DDL to statement to create a user, you can assign a default schema for that user. If a default schema is not assigned, the DBO (database owner) schema is the default. user s default schema is uschema CREATE USER u1 FOR USER u1WITH, DEFAULT_SCHEMA = uschema go user s default schema is dbo CREATE USER u2 FOR LOGIN u2 go A schema can be owned not only by a specific user (created with a SQL Server login or Windows login), but also by a Windows group, a database role, or an application role defined in that database. The new CREATE APPLICATIONROLEDDL statement permits assignment of a default schema, but because many users can be assigned to a role (an ordinary role, not an application role), CREATE ROLE does not assign a default schema for the role. Note that the legacy procedures sp_adduser and sp_addapprole have been changed to first create a schema with the same name of the user or application role and then call the appropriate CREATE statement, specifying that schema as the default schema. Use of the new CREATE statements is preferred; the behavior of the stored procedures is kept only for backward compatibility.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

SEPARATION OF USERS AND SCHEMAS Credentials SQL Server

SEPARATION OF USERS AND SCHEMAS Credentials SQL Server 2005 introduces .NET procedural code that makes it easier to access resources outside SQL Server. This access is controlled by security levels on assemblies, as will be discussed later in the chapter. When accessing resources outside the database, SQL Server logins have no specific privileges only Windows principals defined to SQL Server are known to the underlying operating system. In previous versions of SQL Server, there were two choices for using external resources and SQL Server logins: Use the service account (the account of the service that runs sqlserver. exe) or use the guest account. The guest account is almost always disabled on Windows operating systems when hosting SQL Server. In SQL Server 2005, you can assign Windows credentials to SQL Server logins by first cataloging them with SQL Server. The same credentials can be assigned to multiple SQL Server logins. It looks something like this. CREATE CREDENTIAL sqlusers WITH IDENTITY = machinesqlusers , SECRET = *Y6fy) go CREATE LOGIN mary WITH PASSWORD = mary GO ALTER LOGIN mary WITH CREDENTIAL = sqlusers GO Note that the Windows principal (machinesqlusers, in this case) must already be defined to the Windows security system. Separation of Users and Schemas SQL-99 defines the concept of a database schema as a named group of data that is owned by a particular authorization ID. Schemas are scoped to the database (called catalog in SQL:1999), and one database can contain one or more schemas. Schema objects, such tables, views, and stored procedures, live in a schema, and the two-part name of a database object is actually schemaname.objectname. Prior to SQL Server 2005, the concept of a schema was tied to a particular user. Any objects created by a user were owned by that user, and SQL Server really defined the two-part name of a database object as ownername.objectname rather than schemaname.objectname. There was

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

SECURITY Lockout Counter After (the amount of time

SECURITY Lockout Counter After (the amount of time after which the invalid login attempts revert to zero, if you haven t exceeded them) are not applicable until you set Account Lockout Threshold to something other than zero. There are two password options for SQL Server logins: CHECK_ EXPIRATION and CHECK_POLICY. CHECK_EXPIRATION encompasses minimum and maximum password age, and CHECK_POLICY encompasses all the other policies. When you run afoul of either policy, the SQL Server login must be unlocked by the DBA, as shown shortly in an example. An administrator can add a new login through SQL Server Management Studio or by using the Transact-SQL statement CREATE LOGIN. The legacy stored procedure sp_addlogin will be supported for backward compatibility but will not expose the new features. As shown in the following example, you can create a new SQL Server login that requires the password to be changed on the user s first login attempt by using the MUST_CHANGE keyword. Attempting to access the SQL Server instance without changing the password will result in an error. CREATE LOGIN fred WITH PASSWORD = hy!at54Cq MUST_CHANGE, DEFAULT_DATABASE = pubs, CHECK_EXPIRATION = ON, CHECK_POLICY = ON go If a user has been locked out, the database administrator can unlock the login by using the following code. ALTER LOGIN fred WITH PASSWORD = hy!at54Cq UNLOCK go In those rare cases where the database administrator wants to turn off the password expiration enforcement or security policy enforcement, ALTER LOGIN can accomplish this. Neither of these statements will work when the MUST_CHANGEflag is set and the user has not yet changed his password. ALTER LOGIN fred WITH CHECK_EXPIRATION = OFF go ALTER LOGIN fred WITH CHECK_POLICY = OFF go

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

SQL SERVER PASSWORD POLICIES AND CREDENTIALS With the

SQL SERVER PASSWORD POLICIES AND CREDENTIALS With the new SQL Server 2005 security features, SQL Server logins will have all the same security policy features available. Both SQL Server users and application roles will use the policy. With Windows Server 2003 or later, the policy will be implemented via an OS-level call, Net ValidatePasswordPolicy, so that the administrator can use the same policy for both Windows integrated and SQL Server logins. To give companies that convert to SQL Server 2005 time to analyze how the policy will affect existing applications, the policy can be turned off on a per- login basis. Obviously, this is not recommended. As Windows provides users with the ability to change their password at login time (or while logged on to Windows), so SQL Server users will have the ability to change their password during login. Both the client APIs, like OLE DB and ADO.NET, and the client tools, like SQL Server Management Studio, will support this. Password policy is set by using the Active Directory Users and Computers tool if you re using Active Directory, or by using the Local Security Settings administrator tool if you re administering a nondomain computer. Table 6-1 shows the settings that are exposed using Local Security Settings. Note that Account Lockout Duration (the amount of time accounts are locked out when you reach the Account Lockout Threshold) and Reset Table 6-1: Security Policies for Windows and SQL Server 2005 Logins Policy Category Policy Name Default (Local Server) Password Policy Enforce Password History 0 passwords remembered Maximum Password Age 42 days Minimum Password Age 0 days Minimum Password Length 0 characters Password Must Meet Complexity Requirements Disabled Store Passwords Using Reversible Encryption Disabled Account Lockout Policy Account Lockout Duration Not applicable Account Lockout Threshold 0 invalid login attempts Reset Lockout Counter After Not applicable

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SECURITY procedure for update FRED owns

SECURITY procedure for update FRED owns the PROCEDURE ALTER PROCEDURE update_salary( @EMP_ID INT, @NEW_SALARY MONEY) AS FRED owns the employee table table is accessed through dynamic SQL execute ( UPDATE employee SET salary = @NEW_SALARY WHERE emp_id = @EMP_ID ) go In this version, access is always checked when dynamic SQL is invoked, regardless of the owner of the object that the dynamic SQL statement accesses. Because we re using dynamic SQL, when BOB executes the stored procedure, BOB s access to the employee table is checked. Because BOBdoes not have access to the table, the stored procedure fails. SQL Server 2005 refines the concept of ownership chaining to deal with the concept of schemas and introduces the notion of execution context other than current user. Setting execution context can solve the dynamic SQL problem just described but must be managed carefully. We ll discuss it later in this chapter. SQL Server Password Policies and Credentials In addition to new security features related to .NET managed code, other security features are intended to tighten authentication through SQL Server logins when SQL Server runs under Windows Server 2003. As we mentioned at the beginning of this chapter, users can use Windows authentication or SQL Server authentication to log in to SQL Server. Windows authentication is secure.Auser s password is never sent across the network, and the system administrator can enforce password policy. The password policy can require that users change their password at the first login to the NT domain or machine. The policy can require users to use strong passwords for example, at least eight characters including at least one number, letter, and special character. The policy can also require users to change their password every so often. The policy can specify that a login will be locked out after a certain number of bad password attempts. When a database administrator switches all SQL Server logins to Windows authentication, SQL Server inherits this level of enforceable security. Until SQL Server 2005, SQL Server logins had none of these necessary security characteristics. And weak passwords are acknowledged to be the weakest link in most security systems.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services