Authentication for SQL Server 2000

1
Authentication for SQL Server 2000
2
Security Layers for SQL Server Data

• Access Control Lists in Windows operating systems
• A list of users or groups who are given
  permissions on various resource.
• Database Permission Levels
• There are two categories, permissions on specific
  statements and permissions on objects.
• Physical Database Server Security
• Configuring adequate permissions and using server
  roles. Access to SQL server database should be
  confined to the DBA and other authorized
  individuals.
• There are more security layers.

3
Login authentication mode

• Windows authentication
• SQL server depends on the validation of user by
  the Windows operating system.
• Offer windows security, such as being able to
  lock out accounts, and encryption and expiration
  of passwords.
• SQL Server authentication
• User can login using a SQL login ID

4
Permissions

• Permissions authorize database users to perform
  various operations such as CREATE DATABASE and
  SELECT.
• Permissions are divided into two categories
  statement and object.

5
Granting Statement Permissions

• Use the GRANT statement to grant permissions.
• GRANT statement permission1, 2 TO user1, 2
• Statement permissions include
• CREATE DATABASE
• CREATE DEFAULT
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE RULE
• CREATE TABLE
• CREATE VIEW
• BACKUP DATABASE
• BACKUP LOG

6
Granting Statement Permissions Contd.

• Grant all permissions to user1.
• e.g. GRANT ALL TO user1
• Grant two permissions to user1 and user2
• e.g. GRANT CREATE TABLE, CREATE VIEW TO
• user1, user2

7
Granting Object Permissions Contd.

• Object permissions apply to a specific database
  object such as a table.
• Permissions include SELECT, UPDATE, INSERT,
  DELETE, and other object manipulations.

8
Granting Object Permissions

• General form of the GRANT statement
• GRANT ALL permission_list ON table/view
  column_list procedure TO account_list
• WITH GRANT OPTION AS group_name
  role_name
• The object is specified with the ON clause.
• The TO clause specifies who receives the
  permission.
• The WITH GRANT OPTION enables the grantee to
  grant the permission to others.

9
Granting Object Permissions Contd.

• Grant SELECT on a table.
• GRANT SELECT ON table_name TO user
• Grant SELECT and DELETE on a table.
• GRANT SELECT, DELETE ON table_name TO user
• Grant UPDATE on specific table columns.
• GRANT UPDATE ON table_name( att1, att2, )
• TO user
• Grant ALL permissions to everyone (PUBLIC).
• GRANT ALL ON table_name TO PUBLIC

10
Granting Object Permissions Enterprise Manager
11
Denying Permissions

• Denying permissions removes existing permissions
  from a database user account or role and prevents
  granting the permission to the database user
  account by accident through any other defined
  role.
• / Examples /
• DENY CREATE TABLE TO user
• DENY SELECT, UPDATE ON table_name
• TO user

12
Revoking Permissions

• You can revoke both statement and object
  permissions. Permissions that are revoked can
  later be allocated again.
• / Examples /
• REVOKE SELECT ON table_name FROM PUBLIC
• REVOKE UPDATE ON table_name FROM user
• You can also revoke GRANT options and cause
  system users who have been granted permissions by
  a specific database user to also lose their
  permissions through the CASCADE clause.
• REVOKE GRANT OPTION FOR DELETE ON
• table_name FROM user CASCADE

13
SQL Server Roles

• The groups of users can be categorized and
  permissions can be assigned or denied to all the
  members of the group.
• Fixed Server roles have permissions on all
  databases in a server. All members of this role
  gain all permissions applied to it.
• Fixed database role only exists in the database
  level. Every member added to this role gains all
  permissions applied to it.

14
Fixed Server Roles

• sysadmin has full access to all database objects
  and is able to perform any task.
• serveradmin can shutdown server and set all
  server-related options.
• setupadmin can install replication and have full
  access to extended procedures.
• securityadmin has access to set login and CREATE
  DATABASE permissions.
• processadmin can manage all SQL Server-related
  processes
• dbcreator has authority to create and alter all
  the databases in the server.
• diskadmin has full access to all disk files.
• bulkadmin has full authority to perform bulk copy
  operations such as executing the BULK INSERT
  statement.

15
Fixed Database Roles

• db_owner has full access to the database.
• db_accessadmin can manage user IDs
• db_datareader can give the read (SELECT)
  permissions on any database object.
• db_datawriter can give the write (INSERT, UPDATE,
  and DELETE) permissions on any database object.
• db_ddladmin can issue ALL DDL (Database
  Definition Language) which is used to define and
  declare all database objects.
• db_securityadmin can manage all permissions,
  ownerships, and roles.
• db_backupoperator can check for database
  consistency and make backup by executing the
  DBCC, CHECKPOINT, and ACKUP statements.
• db_denydatareader can deny the read (SELECT)
  permissions on any database object. The opposite
  of db_datareader.
• db_denydatawriter. Can deny the write (INSERT,
  UPDATE, and DELETE) permissions on any database
  object.

16
Granting Statement Permissions-continued.

• Grant permissions to a role.
• e.g. GRANT CREATE PROCEDURE, CREATE FUNCTION
  TO role_name_here

17
Allocating Fixed Server Roles Enterprise
Manager

• Expand the security folder and select the Server
  Roles option.
• Right-click the role as shown.

18
Allocating Fixed Server Roles Enterprise
Manager Contd.

• Click the Add button to display the Add Members
  window as shown and add the members desired and
  click OK.

19
Executing System Proceduresp_addlogin,
sp_grantdbaccess

• EXEC procedure_name param ,
• e.g. EXEC sp_addlogin hellologin,
  worldpass, mydb

20
Example of adding a user to a server role

• EXEC sp_addsrvrolemember martha,securityadmin
• ?add user martha to securityadmin fixed server
  role.
• EXEC sp_addrolemember db_datareader, Henry
• ?allow user Henry to see any data from all user
  tables in the database.

21
Demonstration of adding a new user and granting
permissions to the user
22
Adding a new user
23
Adding a new userEXEC SP_ADDLOGIN
user_name, password, database ,
language
24
Adding a new userEXEC SP_ADDLOGIN demoUser,
pass
25
Adding a new userEXEC SP_ADDLOGIN demoUser,
pass, X
26
Adding a new user
27
Adding a new user
28
Granting permissions
Right-click on a user
29
Granting permissionsEXEC SP_GRANTDBACCESS
user_nameEXEC SP_GRANTDBACCESS DEMOUSER
30
Granting on a specific object permissionsGRANT
UPDATE, DELETE ON DAKINE TO DEMOUSER
31
Useful stored procedure References

• sp_addlogin creates a new Microsoft SQL Server
  login that allows a user to connect to an
  instance of SQL Server using SQL Server
  Authentication. Ex 1 EXEC sp_addlogin
  'user_name'. Ex 2.. EXEC sp_addlogin
  'user_name', 'password', 'database_name'.
• sp_grantdbaccess adds a security account in the
  current database for a Microsoft SQL Server
  login or Microsoft Windows NT user or group, and
  enables it to be granted permissions to perform
  activities in the database. Ex. EXEC
  sp_grantdbaccess 'user_name'
• sp_revokedbaccess removes a security account from
  the current database. Ex. EXEC sp_revokedbaccess
  'user_name'
• sp_dropuser Removes a Microsoft SQL Server user
  or Microsoft Windows NT user from the current
  database. sp_dropuser is provided for backward
  compatibility. Use sp_revokedbaccess to remove a
  user. sp_dropuser executes sp_revokedbaccess to
  remove the user from the current database.
• sp_droplogin Removes a Microsoft SQL Server
  login, preventing access to SQL Server using that
  login name.
• sp_password adds or changes a password for a
  Microsoft SQL Server login. The first example
  changes the password for the login Victoria to
  ok. Ex 1. EXEC sp_password NULL, 'ok',
  'Victoria'. The second example changes the
  password for the login Victoria from ok to
  coffee. Ex 2. EXEC sp_password 'ok', 'coffee',
  'Victoria'
• sp_defaultdb Changes the default database for a
  login. EXEC sp_defaultdb ' Victoria ', 'pubs'
• sp_helpuser Reports information about Microsoft
  SQL Server users, Microsoft Windows NT users,
  and database roles in the current database.

32
Creating an SQL Server Databasefull syntax

• SEE CREATE DATABASE