Computer SecurityCS 426Lecture 21Database SecuritySQL server elementsElisa BertinoPurdue UniversityIN, [email protected] scenarioApplication server hostDatabase server hostLAN (or Intranet)ApplicationEnd users DBAsOrganizationDB1) Identify2) Authenticate3) AuthorizeDBMSTo begin with ….1. Database server Host (or simply Host): the physical machine on which the DBMS Engine (a program) is running2. The Database server Host is controlled by the OS (e.g. Windows, Linux)3. Note that multiple instances of the DBMS Engine may be running on the same HostDatabase server HostLAN (or Intranet)End users DBAsOrganizationDBMSEngine• Single instance of a DBMS• End Users access the DBMS throughtheir PC (e.g. command line in Windows)Simple scenario Database: a set of data managed by a DBMS A DBMS Engine can manage multiple DatabasesDatabase server HostDBMSEnginePayrollDBProductDBTo begin with ….• Principals– Entities that can request SQL Server resources– Can be arranged in a hierarchy– Each principal has a security identifier (SID)• Securables– Resources to which access is regulated– Can be arranged in a hierarchy• Permissions– Every securable has associated permissions that can be granted to a principalTerminology•Windows Level– Windows login– Windows group• SQL Server Level– SQL Server Login– SQL Server Roles (fixed)• Database Level– Database User– Database Role– Application RolePrincipalsSQL ServerSecuring an SQL Server involves three areas:  the platform (host) and the network,  principals and securables, and  applications that access the database•Ref:• http://technet.microsoft.com/en-us/library/bb283235(SQL.90).aspxSQL Server 2008 protection objects Securables• Securables are the resources to which the SQL Server Database Engine authorization system regulates access.• Some securables can be contained within others, creating nested hierarchies called "scopes" that can themselves be secured. • The securable scopes are server, database, and schema.Privileges•ALTERConfers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from theschema.• ALTER ANY <Server Securable>, where Server Securable can be any server securable. Confers the ability to create, alter, or drop individual instances of the Server Securable. For example, ALTER ANY LOGIN confers the ability to create, alter, or drop any login in the instance.• ALTER ANY <Database Securable>, where Database Securablecan be any securable at the database level. Confers the ability to CREATE, ALTER, or DROP individual instances of the Database Securable. For example, ALTER ANY SCHEMA confers the ability to create, alter, or drop any schema in the database.• CREATE <Server Securable>Confers to the grantee the ability to create the Server Securable• CREATE <Database Securable>Confers to the grantee the ability to create the Database Securable• CREATE <Schema-contained Securable>Confers the ability to create the schema-contained securable. However, ALTER permission on the schema is required to create the securable in a particular schema.• CONTROL– ownership like capabilities on the grantee– grantee can also grant permission to other principals– implies all permissions on all securables under the scope of the securable on which it is grantedPrivileges• Hierarchical permission model• For example: Alter permission on schema_1implies Alter permission on all securables (such as schema_1.table_1) defined under schema_1.•Alteron table_1 is the implied permissions; Alteron schema_1 is the covering permissionCovering/implied permissionsCreating users in SQL Server 2008 CREATE USER user_name[ { { FOR | FROM } { LOGIN login_name | CERTIFICATE cert_name | ASYMMETRIC KEY asym_key_name }| WITHOUT LOGIN ] [ WITH DEFAULT_SCHEMA = schema_name ]Arguments • user_name Specifies the name by which the user is identified inside this database. user_name is a sysname. It can be up to 128 characters long.• LOGIN login_name Specifies the SQL Server login for which the database user is being created. login_name must be a valid login in the server. When this SQL Server login enters the database it will acquire the name and ID of the database user that is being created.• CERTIFICATE cert_name Specifies the certificate for which the database user is being created.• ASYMMETRIC KEY asym_key_name Specifies the asymmetric key for which the database user is being created. • WITH DEFAULT_SCHEMA = schema_name Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user. • WITHOUT LOGIN Specifies that the user should not be mapped to anexisting loginCreating users in SQL Server 2008 Example: create a user mapped to SQL server login The following example first creates a server login named Bob with a password, and then creates a corresponding database user for the SimpleDB database:CREATE LOGIN BobWITH PASSWORD = '340$Uuxwp7Mcxo7Khy';USE SimpleDB;CREATE USER Bob FOR LOGIN BobGOCreating users in SQL Server 20081 CREATE LOGIN Bob2 WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';3 USE SimpleDB;4 CREATE USER Bob FOR LOGIN BobGOSQL serverEngineSimpleDBDB1,23,4SQL server login BobSimpleDB user BobCreating users in SQL Server 2008 Example: create a user mapped to SQL server loginUSE SimpleDB; CREATE CERTIFICATE CarnationProduction 50 WITH SUBJECT = 'Carnation Production Facility Supervisors', EXPIRY_DATE = '11/11/2011'; GO CREATE USER TomFord FOR CERTIFICATE CarnationProduction50; GO Note: The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. The subject can be up to 128 characters long.Creating users in SQL Server 2008 • GRANT –– Grants a permission on a securable– WITH GRANT OPTION: gives the ability to grant the permission to other principals• DENY –– Denies a previously granted permission (we will explore this in detail)• REVOKE –– Removes a previously granted or denied permission. – CASCADING: removes the revoked permission from other principals to which it has been granted by this principal.Permission AssignmentCommands Role Purposepublic every SQL Server login belongs to the public