SQL Server Security and User Administration

1
SQL Server Security and User Administration
Training Division New Delhi
2
SQL Server Security and User Administration

• Basic concepts of SQL Server security
• Authentication modes
• Logins
• Database components
• Every user that connects to SQL Server must have
  a valid login id, and that login id must be
  registered with SQL Server.

3
(No Transcript)
4
(No Transcript)
5
Principals, Securables and Permissions

• Principals are logical entities that need access
  the resources of SQL Server
• Principals can be given permission to access an
  object, such as tables or views in the database
  views. Principals can exist at 3 levels
• Windows (Local user accounts, Domain user
  account, windows group
• SQL Server (SQL Server Login, SQL Server Role)
• Database (Database user, Database role,
  Application Role)

6
Principals, Securables and Permissions

• Securables are objects that are accessed by the
  principals. For example, a database or database
  object or service. Similar to principals,
  securables also exists at different levels
• Windows Level (consist of files and registry keys
  that the SQL Server uses)
• SQL Server Level (with nested hierarchies called
  scope)
• Server Scope (logins, databases and end points)
• Database Scope (databases users, roles,
  application roles, assemblies, full text
  catalogs, schemas and DDL events (which are
  created at the database level)
• Schema Scope (tables, views, functions,
  procedures, and types)

7
Principals, Securables and Permissions

• You can allow or disallow principals to access
  the securables by setting permissions.
  Permissions govern the level of access that
  principals have on securables. Depending on the
  role different users can perform different
  actions on the database.

8
Basic concepts of SQL server security
SQL server security starts with login, the server
and its databases
Databases
9
Access to SQL Server

• Clients (users/applications) connect to SQL
  Server by having a login that SQL Server can
  authenticate. That login may be a SQL Server
  login or a Windows login.

10
There are two kinds of logins SQL Server and
Windows Server
Logins
SQL Server
Users
Role
Windows
Groups
Users
11
Logins

• SQL Server logins consist of a login name and
  password that are stored in SQL Server.
• Windows Server logins are Windows Server user
  names or groups that are registered by the system
  administrator within SQL Server.
• With Windows 95 and 98, all you can use are SQL
  Server logins.

12
Logins can have two levels of access, the server
level and the database level
Server Roles
Database Users and Roles
Database Objects
Permissions
Schema
Application Roles
Application Roles
13
Authentication Modes

• SQL Server has two security authentication
  modes
• SQL Server and Windows server authentication
  "mixed" security
• Windows authentication only "integrated" security

14
Guidelines for Authentication Modes

• With Windows authentication SQL Server relies on
  Windows Server to provide the Logon security.
  Network security attributes are validated thru an
  encryption process. Because SQL Server Login and
  windows logon security processes are integrated,
  no further authentication methods are required to
  access when you are authenticated by the OS.
• It is a better security method because of
  additional security features
• secure validation and encryption of password
• Auditing
• password expiration
• minimum password length
• automated account lockout after a number of
  unsuccessfull logon attempts.

15
Guidelines for Authentication Modes

• With Mixed Mode authentication Users can access
  SQL Server by using either of modes.
• Web Applications require SQL Server
  authentication because users of these
  applications are most likely not with in the same
  domain as the server and thus can not rely on
  Windows security
• By default SQL Server authentication uses
  encrypted communication process, implemented by
  self signed certificate to encrypt
  username/password packet.
• Capability of integrating the password policy
  from windows and applying it to SQL Server Logins
  for example password expiration and automated
  account lockout after a number of unsuccessfull
  logon attempts.

16
Authentication and Permission

• Once a login has access to a particular SQL
  Server, the user may perform server-level tasks
  or database-level tasks.

17
Authentication and Permission

• In SQL Server 2000
• all the data in a database was stored in and
  accessed by database objects.
• every object in a database is owned by some
  entity, called the database object owner.
• object owners are identified in the database as
  database users, though not all database users
  must own objects. Database users, in turn, can be
  associated with database roles.
• In SQL Server 2005
• all the objects are organised under schema, they
  are not owned by users but by a schema

18
Schema

• A schema is a logical collection of objects that
  form a unique namespace. In SQL Server 2005
  schema and users are decoupled.
• By assigning the ownership of a schema to a role,
  multiple users can own a schema. If a user is
  deleted, the ownership of an objects is not
  required to be changed.
• Dropping users is simplified.
• Multiple users can share the same schema,
  providing uniform name resolution of objects
  among users.
• Fully qualified name will be
• server.database.schema.object

19
Authentication and Permission

• In addition to database users and roles, there
  is also a concept application roles, which
  contain sets of privileges that an application
  can switch to after logging in.
• To gain access to database objects, every login
  must be mapped either to a database user or to an
  application role.

20
Note

• The upshot is that to gain access to a database,
  every login must have some explicit or implicit
  mapping to a database user application role in
  that database.

21
Implementing security

• When you log into a fresh SQL Server
  installation, SQL Server security can seem
  invisible.
• When you use the sa login, or you use a trusted
  connection and your Windows login belongs to that
  servers administrators group, you can immediately
  access all the databases and their objects
  (tables, views, stored procedures, and so forth).

22
Implementing security

• BECAUSE
• privileged logins automatically map to the dbo of
  every database.

23
Note

• Any login with system administrator rights can
  immediately see all the database objects of every
  database in the server.
• Therefore, implementing SQL Server security
  consists of using logins other than following
• sa
• windows server login with local Administrator
  rights.
• Warning in above cases you are bypassing SQL
  Server security.

24
SQL Server Logins

• SQL Server comes preinstalled with one SQL Server
  login, sa, short for system administrator.
• The sa login automatically has administrative
  rights over the entire SQL Server and its
  databases.

25
SA login

• Every SQL Server login requires a password.
• In the case of sa, it is recommended not to set
  it to blank (or no password at all, default for
  SQL Server 7).
• Dont leave it blank, change it for security
  reasons.

26
Server Roles

• In prior releases of SQL Server, you would have
  to log into SQL Server as sa in order to do any
  kind of maintenance activity.
• From SQL Server 7 onwards, the sa functionality
  has been subdivided into a set of server roles.
• These server roles stand between the logins and
  the server.

27
Server roles stand between logins and the server
28
Fixed Server Roles
29
Fixed Server Roles

• The sysadmin server role is equivalent to the sa
  login in fact, the sa login is automatically
  given the sysadmin server role and cannot be
  removed from it.
• The addition of server roles makes it possible to
  delegate server administrative tasks to other
  logins and keep the number of people who must use
  the sa login or the sysadmin role to a minimum.
• The number and nature of the server roles is
  fixed. You cannot add or modify their properties.

30
Warning

• The Administrators group of the server computer
  automatically has a login into the server and
  belongs to the sysadmin server role.
• So if a windows server user belongs to the
  administrators group of the local machine, that
  user can immediately log into the SQL Server and
  has sysadmin (and therefore sa) privileges.
• You can deny access to the BUILTIN\Administrators
  group to deny such users access to the server.

31
Every database contains database objects (in
schemas), user and roles

32
Database Access

• SQL Server data is contained in schemas in
  databases. Just connecting to SQL Server will not
  by itself allow access to the server's data.
• Consequently, there must be some means of mapping
  authenticated logins to data in databases.
• The way SQL Server accomplishes this is by three
  components inside a database
• database objects are inside schema, database
  users, and database roles.
• Each login is mapped to one user per database.

33
Database Objects

• The database objects are the tables, indexes,
  triggers, defaults, constraints, roles, views,
  and stored procedures in the database.

34
Database Users

• Database users are the names of the owners and
  users of the database objects.

35
Database Roles

• Database roles are groupings of users.
• Both database users and roles can be granted and
  denied permissions to the database objects.

36
Database Access

• Every database object is owned by exactly one
  database user in earlier versions of SQL Server
  (2000).
• In SQL Server 2005, objects reside in schema.
• The only way you can access a database object in
  a schema is either by being the objects schemas
  owner or having access to the objects at schema
  level or the object level, granted by the
  object's schemas owner.
• Every database has at least one database user the
  dbo.
• The dbo user controls the dbo schema and owns all
  the system tables in a database, as well as all
  objects created by the sa login or sysadmin role.

37
Note

• When you log in as sa or have a login that gives
  you the sysadmin role, you can automatically see
  all the objects in a database that are owned by
  dbo.
• These include all the system databases such as
  master, msdb, model, and tempdb.
• Then all objects you create under such logins
  become owned by dbo.

38
Mapping a login to a database

• A SQL Server login gains access to a database
  object by having a login that maps to a database
  user.
• The sa login, NT/2000 user logins that are part
  of the local Administrators group, and any logins
  with sysadmin server roles are all automatically
  mapped to the dbo users in every database.
• Every other login must either be explicitly
  mapped to a database user to gain access to a
  database or implicitly mapped to the guest user
  or public role.

39
Note

• The key concept here is that without the mapping
  of a login to a database user, no login can
  access a database's data.
• You can map a login to only one database user per
  database, but you can map that login to more than
  one database at a time.

40
Database User dbo

• Every database has at least one database user,
  namely the database owner, or dbo.
• The dbo owns all the system tables in the
  database.
• Every sa login or any NT/2000 login with the
  sysadmin server role automatically becomes the
  dbo in the database.

41
Database User guest

• An optional database user is assigned the keyword
  guest.
• If you add a database user with that name to the
  database, then all logins that are not already
  mapped to some other user in the database become
  implicitly mapped to the guest database user.
• You can use the guest database user to establish
  a minimal public sort of access to a database.

42
Database users contd.

• Every database user can be given a set of
  permission to the database objects.
• The dbo starts out automatically with all
  permissions, but you must explicitly set all
  permissions for other database users.
• However, you can take a shortcut by making your
  users members of one or more database roles.

43
Database Roles

• Database roles are ways of grouping users
  together
• so that they inherit the permissions of the
  roles,
• so that you do not have to explicitly grant
  permissions for each database user.

44
Database roles

• Fixed standard Server Subdivisions of dbo rights
  supplied by SQL Server
• Custom standard Roles created by the database
  administrator
• Application Roles for applications that
  bypass database users

45
Fixed database roles
46
Fixed database roles

• The database user, dbo, is automatically a
  member of the db_owner role.
• You cannot add new fixed database role.

47
Custom standard roles

• With custom standard database roles, you define
  your own database roles and then grant
  permissions to each role.
• All permissions granted to these roles are
  inherited by their members, unless explicitly
  denied.

48
Application roles

• Application roles are a special kind of role that
  can be used to switch the permissions that a
  login would normally have, based on a password
  sent to the system.
• In this way, someone running an application could
  get permissions to database objects that they
  would never get using their own login.

49
Application roles

• If the users log in without the application, you
  might deny them access to the database objects.
• However, when they run the application, the
  application can assume the application role that
  does give access to the database objects.

50
Application roles

• Application can use the application role to gain
  new security without logging off and logging into
  SQL Server.

51
Permissions

• After a database has database users and roles,
  you can grant permissions to database objects
  through them.

52
You can set permissions to database objects for
both database users and database roles
Server Roles
Logins
SQL Server
Standard roles

Database
Windows NT/2000
Objects
Database users
NT/2000 Groups
NT/2000 Users
Application roles
53
Permissions

• Database objects include tables, views, and
  stored procedures etc.
• Normally the creator of the object is its owner.
• The owner of an object can grant and deny
  permissions to the object.

54
Note

• The full name of every database object includes
  the database name, the owner name (a database
  user), and the object name.
• Two different users can each create a table or
  object with the same name in a single database

55
Assigning permissions

• Any login with the sysadmin or securityadmin
  server roles (therefore including the sa login)
  can assign permissions to database users and
  roles,
• Permissions are assigned with the GRANT, REOVKE ,
  and DENY commands , and the content of the
  permission is chosen in the body of the command.

56
GRANT, REVOKE DENY

• These can be applied to a database user or a
  database role on two levels
• the database level
• the object level

57
GRANT

• Applies to the database user or a role.
• If the role has the GRANT permission, and the
  permission is not otherwise denied, every
  database user will inherit the GRANT permission
  from the role.

58
REVOKE

• When you REVOKE a permission, it removes the
  prior GRANT permission, if there was one,
• but does not explicitly prevent a database user
  from inheriting a GRANT from a database role.

59
DENY

• The DENY action takes precedence over the GRANT
  action.
• When you deny a permission to a database user or
  role, the permission is explicitly removed.
• Further the deny status overrides all other GRANT
  permissions in the various roles to which a
  database user may belong.

60
Database permissions

• You can grant all of the following CREATE or
  BACKUP statement or a subset of them, to a user
  or role.
• CREATE BACKUP
• DATABASE DATABASE
• DEFAULT LOG
• PROCEDURE
• RULE
• TABLE
• VIEW

61
Object-level permissions

• Table or view Stored procedure
• SELECT EXECUTE
• INSERT
• UPDATE
• DELETE

62
Security functions

• A full set of security functions exist to find
  out the login name, user name, and role
  memberships for each login.
• You can use these functions to add a further
  level of security.

63
Security functions

• Some useful security functions
• select current_user
• select suser_sname()
• select from sys.fn_builtin_permissions(default)
• SELECT SCHEMA_NAME()
• etc.

64
SQL Server Certificates Encryption

• SQL Server 2005 provides encryption as a new
  feature to protect data against the attacks of
  hackers. Hackers may be able to get hold of the
  database or tables, but they wouldn't understand
  the data or be able to use it. It is very
  important to encrypt crucial security related
  data when stored in the database, as well while
  transmitting across a network between the client
  and the server.

65
SQL Server Certificates Encryption

• There are three levels of encryption hierarchy.
  These levels provide different mechanisms for
  securing data across networks and local servers.
  Different level of hierarchies allows multiple
  instances of services (e.g. SQL Server Services)
  to run on one physical server.
• Windows Level - Highest Level - Uses Windows DP
  API for encryption
• SQL Server Level - Moderate Level - Uses Services
  Master Key for encryption
• Database Level - Lower Level - Uses Database
  Master Key for encryption

66
SQL Server Certificates Encryption

• There are two different kind of keys used in
  encryption.
• Symmetric Key - Symmetric cryptography system in
  which the sender and receiver of a message share
  a single, common key that is used to encrypt and
  decrypt the message. This is relatively easy to
  implement and the sender and receiver either can
  encrypt or decrypt the messages.
• Asymmetric Key - Asymmetric cryptography, also
  known as Public-key cryptography, is a system in
  which the sender and the receiver of a message
  have a pair of cryptographic keys - a public key
  and a private key - to encrypt and decrypt the
  message. This is relatively complex system and
  the sender can use its key to encrypt the
  message, but he can't decrypt it. The receiver
  can use its key to decrypt the message but he
  can't encrypt it. Due to its complexity, this is
  a resource intensive process.
• Certificates are used as well for encrypting
  data. A public key certificate is 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. A Certification Authority (CA)
  issues and signs certifications.

67
SQL Server Certificates Encryption

• There are two different kinds of encryption
  available in SQL Server
• Database Level - This will secure all the data in
  database. However, every time data is written or
  read from database, the whole database has to be
  decrypted. This is a very resource intensive
  process and not a practical solution.
• Column (or Row) Level - This level of encryption
  is the preferred method of encryption. Only
  columns containing important data should be
  encrypted this will result in less CPU load than
  the whole database level encryption. If a column
  is used as primary key, or used in comparison
  clauses (WHERE clauses, JOIN conditions) the
  database will have to decrypt the whole column to
  do operations involving those columns.