SQL Server Authentication: Managing Users, Roles, Permissions, Auditing, Best Practices, Slides of Network security

Download SQL Server Authentication: Managing Users, Roles, Permissions, Auditing, Best Practices and more Slides Network security in PDF only on Docsity!

Authentication

Authentication

• Logins
• Server authentication
• Managing users
• Roles
• Permissions
• Auditing access
• Best practices

Logins

• Access is enforced using an Access Control List
• This type of authentication establishes a trusted connection
• Communication from client to SQL Server is not encrypted by default
• If a valid SSL Certificate is installed on the SQL Server system, then the communications are encrypted

Server Authentication

• SQL Server installation allows choice of Windows or SQL Server authentication
• Windows authentication is stronger than SQL Server authentication
• Untrusted connection requests rejected
• No SQL Server ‘standard’ logins allowed
• Mixed Mode authentication in SQL Server installation allows both Windows and SQL Server logins

Server Authentication

• Authentication is not just for a user, it can be for local and global groups as well
• Once authenticated, Windows yields control to SQL Server
• SQL Server authentication works with systems besides Windows: - TCP/IP protocol connection - AppleTalk protocol connection - Banyan Vines protocol connection

Server Authentication

• Windows 2000 uses Kerberos security

through Active Directory

• Kerberos protocol authenticates both the

client and the server

• Kerberos verifies data integrity (especially

useful for remote logins)

• SQL Server authentication is known as

‘Standard login’

Managing Users

• In SQL Server Login Properties – New Login dialog box, there is a box for ‘Deny Access’
• This feature allows the DBA or NetAdmin to deny SQL Server access to selected individuals who otherwise have group access with Windows
• Transact SQL command for new standard logins is sp_addlogin
• Example:

exec sp_addlogin ‘dave’ ‘getgo’ ‘pubs’

Managing Users

• Default database for any T-SQL login

command is “Master”

• T-SQL command allows for specifying

encryption for password

• exec sp_droplogin ‘dave’ removes the

user from SQL Server

• T-SQL command for Windows login:

exec sp_grantlogin ‘dave’ ‘getgo’ ‘pubs’

Roles

• Fixed server roles
  • Sys admin
  • Security admin
  • Server admin
  • Database admin
• User defined roles
  • Gives flexibility to add/remove permissions to users
  • Roles can be nested
  • Formerly called ‘groups’

Roles

• User defined roles
  • sp_addrole ‘role’ ‘owner’
  • sp_droprole ‘role’
  • sp_addrolemember ‘role’ ‘member-ID’
• Database roles
  • Fixed roles
  • Public roles
  • Application roles
    • Applies to applications and not users

Permissions

• Common permissions are GRANT,

REVOKE, DENY

• DENY explicitly prevents access and

overrides any other permissions

• REVOKE removes a GRANT as well as a

DENY that is in effect

Permissions

• Example:
  • GRANT CREATE VIEW TO CHARLIE
  • REVOKE CREATE VIEW FROM CHARLIE
  • DENY CREATE VIEW TO CHARLIE
• Example:
  • GRANT ALL PRIVILEGES ON STUDENT TO CHARLIE
  • GRANT ALL PRIVILEGES ON STUDENT TO CHARLIE WITH GRANT OPTION AS MANAGER

Auditing

• SQL Server 2000 permits security audits with varying levels of detail
• Permission chains are enforced
• Ownership chain enables the owner to know how the data or other objects such as view are used
• Understand the information contained in sysxlogins, sysusers, syspermissions, and sysprotects

Best Practices

• Set password for the ‘sa’ account
• Revoke all permissions for the PUBLIC

role

• Remove GUEST user from all databases
• Use Windows authentication instead of

SQL Server authentication