Enhancing Database Security and Functionality: Question and Answer

Q.1. How does a TCP Client Establish Connection with a SQL Server?

Ans:- When a TCP client aims to connect to a SQL Server, it must determine the appropriate port to use. Before SQL Server 2005, a thread was continuously waiting on User Datagram Protocol (UDP) port 1434 to provide information about all running SQL Server instances and their corresponding port numbers. A client could establish a UDP connection to port 1434 and specify the desired port to connect to, based on a specific instance of SQL Server. This approach was effective until hackers exploited it, launching Denial of Service (DoS) attacks on SQL Servers by incessantly sending packets to port 1434 for enumeration. The "SQL Slammer" worm virus caused significant issues due to this vulnerability. Since SQL Server 2005, this functionality has been segregated into a separate service called the SQL Server Browser service, allowing enabling or disabling without affecting the SQL Server service itself.

As a result, the Browser service is now susceptible to DoS attacks, and a precautionary measure is to block port 1434 on the firewall. If concerned about intranet attacks within the firewall, one should consider disabling the Browser service and explicitly specifying port numbers in the connection strings.

Image: SQL Server Authentication Model

At this stage, the service verifies the user's login credentials and attempts to validate them. A successful login is authorized against the endpoint associated with the type of connection made to the SQL Server. In this case, the login's CONNECT permissions to the TCP endpoint are checked. If successful, the authentication process proceeds; otherwise, the connection fails at this point.

New logins are automatically granted CONNECT permissions to the TCP endpoint by default. SQL Server switches to a database context (default database specified for the login or in the connection string) once the login passes the endpoint check. It then tries to authenticate the login as a database user. The connection succeeds if the login can be authenticated; otherwise, it fails. Once a database context is established, and login is authenticated, the user can interact with the database on the server. Further information can be found in Online SQL certification courses.

Q.2. Explaining The Password Policies of SQL Server 2012?

Ans:- In Windows, administrators can set login expirations and enforce password policies, such as requiring passwords to meet specific criteria (length, special characters, etc.). SQL logins in SQL Server have not previously adhered to these global policy settings. However, since SQL Server 2005, both Windows-authenticated and SQL logins have been subject to the Windows domain's group policy settings. It's essential to note that password policy enforcement is available only in SQL Server versions 2003 and later.

Image: Password Policy node of Local Security Settings

This tool allows you to modify several parameters. For instance, you can set a minimum password length to eight characters. If you attempt to create a login that doesn't meet the Windows password policy:

CREATE LOGIN SomeUser WITH PASSWORD = '2short',
CHECK POLICY-ON,
CHECK EXPIRATION-ON

Attempting to create a login that does not satisfy the Windows password policy.This statement is incorrect as the password is only seven characters long, whereas the Windows password policy requires a minimum of eight characters.

The system view sys.sql logins provides information about logins, including policy and expiration settings. For more specific information about a particular login, such as the number of bad passwords attempted, you can use the LOGINPROPERTY built-in function.

SELECT 'IsLocked' AS Property, UNION ALL SELECT 'IsExpired', UNION ALL SELECT 'IsMustChange',
UNION ALL SELECT 'BadPasswordCount', UNION ALL SELECT 'PasswordLastSetTime", UNION ALL SELECT 'BadPasswordTime', UNION ALL SELECT 'LockoutTime',
LOGINPROPERTY('sa', 'IsLocked') AS Value LOGINPROPERTY('sa', 'IsExpired')
LOGINPROPERTY('sa', 'IsMustChange') LOGINPROPERTY('sa', 'BadPasswordCount') LOGINPROPERTY('sa', 'PasswordLastSetTime") LOGINPROPERTY('sa', 'BadPasswordTime') LOGINPROPERTY('sa', 'LockoutTime')

Querying the LOGINPROPERTY function.

The query results display various properties of the same login.

To gain a deeper understanding of this topic, you can explore Online SQL certification courses.

Q.3. What is User-Schema Separation and Its Benefits?

Ans:- SQL Server 2005 introduced a novel schema concept distinct from previous versions. In SQL Server 2000 and earlier, database users and schemas were synonymous. Each database user was the owner of a schema with the same name. This one-to-one mapping made reassigning ownership cumbersome.

However, starting with SQL Server 2005, users and schemas became separate entities. Schemas became independent containers capable of housing zero or more objects. Users could own multiple schemas, and a default schema was assigned to them. If no default schema was specified, the user defaulted to the database's "dbo" schema. This default schema facilitated name resolution for security variables referenced without their fully qualified name. In SQL Server 2000, the schema owned by the calling database user was checked first, followed by the "dbo" schema.

This separation offers several benefits:

• Shared Ownership: Through membership in roles or Windows groups, multiple users can own a single schema, enhancing functionality and allowing roles/groups to own objects.
• Simplified User Removal: Dropping database users has been greatly simplified. Removing a user does not require renaming objects within the user's schema, saving time and avoiding application revisions.
• Default Schemas: Several users can share a single default schema, promoting uniform name resolution.
• Custom Schema: Shared default schemas allow developers to store shared objects in a schema created for a specific application rather than the default "dbo" schema.
• Precise Permissions: Permissions on schemas and schema-contained securables can be managed more precisely than in previous releases.
• Schema-Catalog Views: Catalog views (e.g., sys.objects) should be used instead of old system views (e.g., sysobjects) to ensure accurate query results.

Q.4. How to Manage Permissions for a Table in a Schema?

Ans:- To create a new schema, the CREATE SCHEMA statement is utilized. It can also be employed to create tables and views within the new schema and to grant, deny, or revoke access to those objects.

USE master
GO
- Create three server logins
CREATE LOGIN Rob WITH PASSWORD 'jackpot_0' CREATE LOGIN Tammie WITH PASSWORD= 'jackpot_0' CREATE LOGIN Vince WITH PASSWORD= 'jackpot_0'
-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB') DROP DATABASE MyDB
GO
CREATE DATABASE MYDB
GO
USE MyDB
GO
-- Create three database users mapped to the logins
CREATE USER Rob FOR LOGIN Rob
CREATE USER Tammie FOR LOGIN Tammie
CREATE USER Vince FOR LOGIN Vince
GO
Create a schema owned by Rob
CREATE SCHEMA Sales AUTHORIZATION Rob
GO
-- Create a table in the schema owned by Rob
CREATE TABLE Sales. Leads (id int, name varchar(50), phone varchar(20))
-- Allow Tammie but not Vince to SELECT from the table in the schema owned by Rob GRANT SELECT ON Sales. Leads TO Tammie
DENY SELECT ON Sales. Leads TO Vince

Granting and denying permissions to a schema table

This example assigns the Sales schema to Rob. Within the Sales schema, a Leads table is created, granting Tammie select access to the table while denying Vince the same access. Before SQL Server 2005, Tammie would have needed to use Rob as the schema name, but now, with SQL Server 2005, she can use the Sales schema name directly:

SELECT * FROM Production.SalesReps.Sales.Leads

In the event that Rob no longer holds the Sales schema, the sysadmin user can transfer ownership to Tammie with a simple statement:

ALTER AUTHORIZATION ON SCHEMA::Sales TO Tammie

If you find these code segments intriguing, consider exploring Online SQL certification programs to delve deeper into this subject.

Q.5. Benefits of Schema Separation for Database Users?

Ans:- The separation of database users and schemas brings forth numerous advantages for both developers and administrators. These advantages include:

• Enhanced Functionality: Multiple users can now own a single schema through membership in roles or Windows groups. This introduces new capabilities and enables roles and groups to take ownership of objects.
• Streamlined User Removal: The process of dropping database users has been significantly simplified.
• No Need for Object Renaming: When removing a database user, there's no longer the necessity to rename objects within that user's schema. As a result, there's no obligation to revise or test applications that directly reference schema-contained securables after the user's removal.
• Shared Default Schemas: Several users can share a common default schema, which fosters consistent name resolution.
• Collaborative Schema Usage: Shared default schemas empower developers to store shared objects within a schema tailored specifically for a particular application, rather than relying solely on the default "dbo" schema.
• Precise Permissions: Managing permissions on schemas and schema-contained securables can now be executed with greater precision compared to previous software releases.
• Fully Qualified Object Names: Fully qualified object names now consist of four components: server.database.schema.object.

Q.6. Understanding Execution Context with an Example?

Ans:- The task of granting and managing permissions for non-sysadmin users has always been a captivating challenge, especially when users own stored procedures that interact with tables and objects beyond their ownership. Which involves three logins: Login1, Login2, and Login3. These logins correspond to database users User1, User2, and User3, each possessing their respective schemas: Schema1, Schema2, and Schema3.

USE master
GO
-- Create 3 logins
CREATE LOGIN Login1 WITH PASSWORD = 'P@$$word1' CREATE LOGIN Login2 WITH PASSWORD = 'P@$$word2' CREATE LOGIN Login3 WITH PASSWORD
GO
'P@$$word3'

- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = "MyD8") DROP DATABASE MyDB
CO
CREATE DATABASE MYDB 00
USE MYOB
GO
-- Create 3 users mapped to the 3 logins CREATE USER User3 FOR LOGIN Login) CREATE USER User2 FOR LOGIN Login2 CREATE USER User1 FOR LOGIN Loginl
-- Create a corresponding schema for each of the 3 users CREATE SCHEMA Schema3 AUTHORIZATION User3
00
CREATE SCHEMA Schema2 AUTHORIZATION User2 GO
CREATE SCHEMA Schemal AUTHORIZATION User1
00
-- Let User3 create tables and let User2 create stored procedures
GRANT CREATE TABLE TO User3
GRANT CREATE PROCEDURE TO User2
00
-- Impersonate Login3 (User3) EXECUTE AS LOGIN = 'Login'
-- Create and populate a table in Schema3
CREATE TABLE Schema3.Region (RegionName nvarchar(50))
INSERT INTO Schema3.Region VALUES("East Coast'), ('West Coast'), ('Midwest')
-- Allow User2 to SELECT from the Schema3 table
GRANT SELECT ON Schema3.Region TO User2
00
REVERT
00
--Impersonate Login (User2)
EXECUTE AS LOGIN = 'Login'
00
-- Create a stored procedure in Schema2 that selects from the table in Schema3 CREATE PROCEDURE Schema2.GetRegions AS
SELECT * FROM Schema3.Region
--Allow User1 to EXECUTE the Schema2 stored procedure
GRANT EXECUTE ON Schema2.GetRegions TO User1
00

REVERT
GO
-- User1 cannot access the Schema3 table, even via a Schema2 stored
--proc that they have permission to execute
EXECUTE AS LOGIN = 'Login'
GO
SELECT * FROM Schema3.Region -- fails
EXEC Schema2.GetRegions
GO REVERT
GO
-- fails
-- Modify the Schema2 stored proc so that it always run in the context -- of the owner, not the caller
EXECUTE AS LOGIN = 'Login2'
GO
ALTER PROCEDURE Schema2.GetRegions WITH EXECUTE AS OWNER AS
SELECT FROM Schema3. Region
GO
REVERT
GO
-- User1 still cannot access the Schema3 table directly, but now they can -- indirectly via the Schema2 stored procedure
EXECUTE AS LOGIN = 'Login'
GO
SELECT * FROM Schema3. Region EXEC Schema2.GetRegions
GO
REVERT
fails -- works!
GO

In this scenario, User3 owns the Schema3.Region table, while User2 is the proprietor of the Schema2.GetRegions stored procedure, responsible for retrieving data from said table. This arrangement functions seamlessly due to User2's SELECT permission granted on User3's table.

However, a challenge arises when User1 must execute User2's stored procedure. While User1 possesses EXECUTE permission on the stored procedure, the execution will fail due to the absence of SELECT permission on the underlying table accessed by the procedure. Prior to SQL Server 2005, a simple resolution to this problem was elusive. When this requirement is multiplied across an enterprise, the complexities of permissions management become apparent.

Thankfully, starting from SQL Server 2005, addressing this issue has become straightforward. Switching the execution context (a specialized form of impersonation) between various logins and database users can now be effortlessly achieved. For instance, in our example, User2 can adjust the execution context of the Schema2.GetRegions stored procedure by using one of the various WITH EXECUTE AS clauses available in an ALTER PROCEDURE statement.

These WITH EXECUTE AS options include:

• WITH EXECUTE AS THE CALLER: Executes the procedure using the caller's credentials, requiring access to all underlying objects referenced by the procedure (the default behavior in prior versions of SQL Server).
• USE EXECUTE AS SELF: Executes the procedure using the credentials of the user who last modified it.
• WITH EXECUTION AS: Runs the procedure with the credentials of the designated user. To facilitate this, the user creating or modifying the procedure must possess IMPERSONATE permission granted to the specified user.
• WITH EXECUTE AS OWNER: Executes the procedure using the credentials of the login that owns it. If the object's owner changes post-creation, the execution context is automatically mapped to the new owner.

Previously mentioned, User1 encounters difficulties accessing the Schema3 table owned by User3 without altering the execution context. Any attempt will prompt SQL Server to return an error message stating: "The SELECT permission was denied on the object 'Region', database 'MyDB', schema 'Schema3'."

Prior to SQL Server 2005, granting User1 access to User3's table was a prerequisite for running User2's stored procedure. However, starting with SQL Server 2005, User2 can conveniently modify the execution context of the stored procedure, eliminating the necessity for User1 to be granted direct access to User3's table. By changing the execution context in Listing 5-4 to EXECUTE AS OWNER, User2 ensures the stored procedure is executed with User2's credentials, given that User2 is the stored procedure's owner.

As User1 runs User2's stored procedure, it operates under User2's credentials, granting access to User3's table. This access remains confined to the context of the stored procedure. As a result, User1 can execute User2's stored procedure without needing direct access to the underlying table owned by User3.

Conclusion

We've uncovered the remarkable benefits brought by schema separation and execution context in SQL Server. The separation of database users and schemas has revolutionized the way developers and administrators manage their systems. Through shared ownership, streamlined user removal, and precise permissions management, this approach brings unprecedented flexibility and security.Furthermore, we've seen how execution context addresses the complex challenge of granting permissions to non-sysadmin users, especially those owning stored procedures that access external objects. With SQL Server 2005's powerful features, such as the WITH EXECUTE AS clauses, users can seamlessly adjust the execution context, ensuring necessary permissions without compromising security.

By understanding these concepts and harnessing their capabilities, you'll be better equipped to optimize your SQL Server environment. Whether you're a developer seeking more robust functionality or an administrator aiming to simplify user management, schema separation and execution context are essential tools in your toolkit.Stay updated on the latest advancements in SQL Server and database management by exploring online SQL certification programs. Continuous learning will empower you to master these techniques and keep your databases secure, efficient, and future-ready.