This month we are going to look at the subject of security. The article
is not an in depth tutorial, and is in no way comprehensive. Instead, what I
have tried to do is to simply highlight some of the areas where many people (myself
included) commonly leave gaps in their security.
SQL Server Passwords
When
using SQL Server security, make sure there are no blank passwords – a simple
query will reveal which users have not set a password:
select name from master..syslogins where password is null
Watch out for NT logins, which of course have no password!
After
the SQL Slammer virus experience, nobody should be left with a blank password
for their sa login
Remove
the guest login – it allows unauthorised access by default.
Choose
a strong password for the sa login, and encourage your users to do the same.
User
IDs and Passwords in user tables are bad news, the data is in too public a
place. Cryptography is Maths-PHD and million-pound-supercomputer territory and
"home made" encryption is usually weak. If one of your developers
reckons they can write their own uncrackable encryption scheme, they should be
working for the spooks.
Integrated security
Choose
strong passwords for all local and network logins. If an intruder can guess a
machine’s local administrator login, they will own your server.
Do
your NT administrators really need admin rights inside your SQL Server? Check
that no administrator groups are accidentally granted rights via Integrated
Security.
Lock
down the extended stored procedures – you do not want users running command
shells with administrator rights on your servers.
Database rights
Nobody
needs access to the master database to log in, and most people do not need
access to MSDB and the scheduling engine. Lock them both down.
Grant
only the minimum rights to a user in order to get things done. Understanding
ownership hierarchies helps here: If a stored procedure accesses a table, users
do not need rights on the table in order to run the SP successfully, provided
that the ownership of both SP and table is the same. The same rule applies to
views and their underlying tables, and to nested stored procedures.
Always
grant user rights at role level, and make sure all your users are grouped
according to functionality.
Never
allow your developers to produce clients that log in as ‘sa’, and never let
them develop as ‘sa’, even in an environment where security is not such an
issue. All too often apps are developed using sa privileges, and you find out
just before they go-live that these privileges have magically become necessary
for the system to work. Take my word on this: "we will fix it in the next
release" is right up there with "the cheque is in the post."
How
many times have you heard the story "No, you cannot change the password,
it’s hard coded"? Passwords are meant to be typed in by authorised
users, not hard coded into programmes, stored in .ini files or held in the
registry. Use Integrated Security if users feel the need to connect without
typing a password.
Encourage
your client developers to build security into a database and client at design
time, not bolt it on as an afterthought. Developers always want to concentrate
on functionality, which is understandable, because that is their job, so it is
up to you to get them thinking about security at an early stage.
Consider
using application roles to enhance security.
Patching
A
touchy subject if ever there was one. It’s easy to say that you should always
apply the latest SQL Service Packs, SQL Hotfixes, Windows Updates and what-have-you,
but there is a problem here: You cannot just install any old software into your
mission critical environment as soon as it is released. What if it breaks
something? How do you roll it back? If you apply a patch without testing it,
and it breaks your environment, you can bet Microsoft will not be sending you
compensation money any time soon.
Sometimes,
good practice helps you eliminate the need for applying patches in a hurry: If
you did not have a blank SA password in the first place, you would not need the
Slammer hotfix.
Network Security
Taken
a backup of your database and left the file lying round on the network for a
while? Who can see that network drive? Anyone who can, can simply pick up your
file and restore it onto another instance of SQL Server and voila – there goes
your data!
Are
your development environments as safely guarded as your live ones? Or, is
sensitive data ported back to your test and development environments where half
the IT department can see them.
If
you are opening a test database to other people, is there sensitive data in
there that you might want to encrypt first? Personnel databases that list people’s
salaries are a concern in every company.
Off
to a leaving drink? They might have been the nicest, most reliable people in
the company, but you really should disable or remove their account before you
get that first beer down your neck.
Where
are your backup tapes? Is your off-site backup provider as security-conscious
as you would like them to be?
It
is not just SQL Server and Windows security you need to worry about. Got IIS? Linked
servers? Check that those machines are properly secured so they cannot act as a
back door into your system. Unattended PCs left logged in are a major security
hole. Educate your users, enable passwords on screen savers or implement
inactivity timeouts in your applications.
The dark room
Security
isn’t all about software – make sure that your server is in a locked cabinet,
in a locked room, with proper access controls. Organised gangs make a living
breaking into server rooms and stealing the kit. If somebody just wants your
data, all they need to carry away is your nightly backup tape, or your
hot-pluggable disks.
