securables

SQL Server 2005 security basics

ksbytes, , , , Leave a comment

Who and what

  • Principles (new in SQL Server 2005) – users, groups, processes.
    • Windows Principal – user or group (local/domain login)
    • SQL Server Principal (SQL Server login)
    • Database Principal:
      • Database User (maps either to SQL Server or Windows principle)
      • Database Role (predefined set of permissions; role is assigned to user so he automatically gets these permissions)
      • Application Role (allows application to run with its own, user-like permissions, “access to specific data to only those users who connect through a particular application”, “Unlike database roles, application roles contain no members and are inactive by default, are enabled by using sp_setapprole, which requires a password.”
  • Securables – any resources or objects on SQL Server. Objects may contain other objects which creates an hierarchy of securables or scopes: Schema scope (table, view, …) -> database scope (user, schema, …) -> Server scope (login, database, …). This allows to simplify management of permissions for objects
  • Permissions – actions granted, revoke or denied to principals on securables

Schema

Named collection of DB objects which form a namespace (create objects and schemas, associate objects with schemas)
User vs. Schema: objects in 2005 have the following fully qualified naming convention: server.database.schema.object. Advantages of using schema:

  • Previously user was in the place of the schema, which either created problem since different users own different parts of schema and the change is not easy, or restricted the user to always be dbo.
  • Ability to group related objects. Previously objects had to be prefixed to identify different components of the more complex application. With schema objects in the same database can belong to different schemas.
  • Simplified permissions management: can give permissions on Schematic level, rather than on object level.

Fully qualified naming conventions are only used in certain cases (e.g. for linked servers). Server name can be omitted if connected to one server; db name can be omitted, if specified default db, and schema name can be omitted if specified default schema.

Creating a login

Right lick on Logins, choose New: create and manage Windows and SQL Server principals. Has the following tabs:

  • General Select user type and specify user/group name. Certain options (e.g. Enforce password policy/expiration, user must change password on next logon) are only available for SQL Server authentication. Also allows to specify default database. Enforce password policy (uses local policy of the machine on which it’s installed) is only available if installed on Windows 2003 or above. Enforce password expiration is only enabled if Enforce password policy is set. Also has advanced features of SQL Server authentication: Mapped to certificates/asymetric key.
  • Server roles Allows to give user permissions on server scope. Server roles can be created in Server Roles node, but fixed server roles are:
    public View databases
    bulkadmin Bulk insert operations
    dbcreator Create, alter, drop, backup, restore databases
    diskadmin Manage mdf, ldf files
    processadmin Stop/kill SQL processes
    securityadmin Create logins, grant/revoke/deny permissions, both server-level and database-level permissions.
    serveradmin Can change the properties of the server
    setupadmin Create/alter/drop linked servers
    sysadmin Everything in the system
  • User Mapping Create or map users on database level. Assign database role permissions.
  • Securables: when login is created on server level, this tab allows to configure explicit permissions for securables in server scope. Options: 
    GRANT
WITH GRANT (gives user or group the ability to grant this permission to someone else)
DENY
  • Status: grant/deny connection to DB engine, enable/disable login, lock/unlock SQL authentication user.

Note that Credentials allows SQL Server authenticated user to have presence outside of SQL Server, by associating SQL user with Windows user.

Assigning database roles and permissions

Schemas allow creating schemas and configuring permissions per schema. Each schema has ONE owner (e.g. dbo). Tables are created and associated with schemas (in table properties). Database users are created and mapped with SQL Server logins (database user might have the same or different name than server user). SQL Database user can have default schema (similar to how server user can have default database), but Windows users cannot have default schema.

When creating a database user:

  • General tab: specify user name, choose existing login name to associate with, choose default schema. Also allows to set which schemas user owns (but each schema has only one owner, so it replaces the owner). And allows to choose database roles.
  • Fixed database roles are:
    db_accessadmin Grant access to this database
    db_backupoperator Backup database
    db_datareader SELECT from database
    db_datawriter INSERT, UPDATE, DELETE from database
    db_ddladmin Data definition language administrator, can create any object: table, view, stored procedure, etc.
    db_denydatareader opposite of db_datareader
    db_denydatawriter Opposite of db_datawriter
    db_owner All permissions on the database
  • Securables tab: similar concept as for server login, but in this case securables are for the database. Thus there’ are more types of them.
  • Extended Properties tab: can create value/description pairs. For example for documentation purposes.

Add Securables to Database User

Can be done for:

  • Specific objects…
  • All objects of the types …
  • All objects belonging to schema…

For each object, permissions to select/insert/etc are provided. More granular permissions for columns can be set as well.

SQL Server 2005 security basics