DBD

• Authentication


  The process of validating user credentials to prevent unauthorized users from using a system
• Authentication


  • Requires an evaluation of the question "Does this user have a legitimate right to access the system?"
  • Can be checked by requesting the user to provide something they are acquainted with (usually a password), something they own (such as a magnetic card or badge), or physical characteristics (such as a signature or fingerprints)
• Authentication


  Most commonly confirmed using a name and a password, evaluated by the system to determine whether you are the user
• Database Engine's security system


  Includes two different security subsystems: Windows security and SQL Server security
• Windows security


  Specifies security at the operating system level, the method by which users connect to Windows using their Windows user accounts
• SQL Server security


  Specifies the additional security necessary at the system level, how users who have already logged on to the operating system can subsequently connect to the database server

  
• SQL Server login


  Created within the system and associated with a password, some are identical to existing Windows user accounts

  
• Data encryption


  The process of scrambling information so that it is incomprehensible until it is decrypted by the intended recipient
• Database Engine's encryption layers

  • Symmetric Keys
  • Asymmetric Keys
  • Certificates

  
• Public key certificate


  A digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key
• Certificates


  • Contain the subject's public key value, subject's identifier information, issuer identifier information, and the digital signature of the issuer
• Creating a certificate without the ENCRYPTION BY option

  1. First create the database master key

  2. Each CREATE CERTIFICATE statement that does not include this option is protected by the database master key

  
• Barriers to accessing SQL Server data

  • The network operating system
  • SQL Server authentication
  • Database authentication
  • Object authentication

  
• Authorization


  The process that is applied after the identity of a user is verified through authentication, determines what resources the particular user can use
• Tracking changes


  The process of following and documenting the actions of unauthorized users on your system, useful to protect the system against users with elevated privileges
• Windows user account


  Allows a user to log in to the system
• Database user account


  Allows a user to access a particular database of the system
• Users must have a database user account for each database they want to use
• Creating Login Account


  1. CREATE LOGIN login_name

  2. WITH option_list1

  3. FROM {WINDOWS [ WITH option_list2 [,...] ]

  4. CERTIFICATE certname

  5. ASYMMETRIC KEY key_name}
• Principal


  An entity that can access objects, can be either an indivisible principal (single user) or a group principal (group of users)
• Principals are ownership of schemas, but the ownership of a schema can be transferred easily to another principal and without changing the schema name
• Separation of database users from schemas


  • One principal can own several schemas
  • Several indivisible principals can own a single schema via membership in roles or Windows groups
  • Dropping a database user does not require the renaming of objects contained by that user's schema
• Default schema


  The first schema that will be searched by the database server when it resolves the names of objects
• Transact-SQL schema-related statements


  1. CREATE SCHEMA

  2. ALTER SCHEMA

  3. DROP SCHEMA
• Creating a schema


  • USE sample
  • GO
  • CREATE SCHEMA my_schema AUTHORIZATION peter
  • GO
  • CREATE TABLE product
  • (product_no CHAR(10) NOT NULL UNIQUE,
  • product_name CHAR(20) NULL,
  • price MONEY NULL)
  • GO
  • CREATE VIEW product_info
  • AS
  • SELECT product_no, product_name
  • FROM product
  • GO
  • GRANT SELECT TO mary
  • DENY UPDATE TO mary
• Altering a schema


  ALTER SCHEMA schema_name TRANSFER object_name
• Altering a schema


  • USE AdventureWorks
  • GO
  • ALTER SCHEMA humanresources TRANSFER person.address
• Dropping a schema


  DROP SCHEMA
• You can successfully execute the DROP SCHEMA statement for a schema only if the schema does not contain any objects
• The ownership of an entity is specified using the CREATE SCHEMA statement
• Changing schema ownership


  • USE sample
  • GO
  • ALTER AUTHORIZATION ON SCHEMA ::my_schema TO mary
• Setting Up Database User Accounts

  1. CREATE USER user_name

  2. [FOR {LOGIN login |CERTIFICATE cert_name |ASYMMETRIC KEY key_name}]

  3. [ WITH DEFAULT_SCHEMA = schema_name ]
• Setting Up Database User Accounts

  • USE sample
  • GO
  • CREATE USER peter FOR LOGIN [NTB11901\pete]
  • CREATE USER mary FOR LOGIN mary WITH DEFAULT_SCHEMA = my_schema
• Modifying a database username

  ALTER USER statement
• Removing a user from the current database

  DROP USER statement
• Users that own securable (that is, database objects) cannot be dropped from the database
• Database role


  A group of database users that can access the same objects of the database
• Members of a database role

  • Windows groups and user accounts
  • SQL Server logins
  • Other Roles
• Security architecture in Database Engine

  • Includes several "system" roles that have special implicit permissions
  • Two types of predefined roles: Fixed server roles and Fixed database roles
• Types of roles


  • Fixed server roles
  • Fixed database roles
  • Application Roles
  • User-defined roles