September, 2008

Do You know Where Your Previous Mobile Phone Is?

Cell phone companies are tempting us more and more with phones that act as PDAs (Personal Data Accessory??), send and receive email, surf the Web, have bigger capacity to store documents, are music players, cameras and oh, by the way: a phone. And in the coming years some have proposed utilizing your phone to pay bills and buy stocks.

It’s wonderful and terrible all at the same time. There is no standard procedure for wiping a phone’s information. Phone manufacturers have proprietary hardware, and have been extremely reluctant to release information to software developers who could provide us with a way to wipe the phone and its’ memory. As a result, we have millions of phones available with sensitive data, on an open market. Thank you manufacturers, for protecting the consumer? As usual, no one really thought about security, not to mention privacy.

Three years ago, Graham Clements - A managing director for a subsidiary of Japanese packaging multinational Ishida - decided to get rid of his BlackBerry and turned it in to his IT department for recycling. At the start of this month that BlackBerry was one of the top items on the agenda at the first board meeting that Clements had called since his return from vacation - because the data on it had come back to haunt him.

Instead of being recycled, the BlackBerry, like millions of other mobile devices every year, had been passed on to a company to be sold. On Clements’s device were business plans, details of customer relationships, information on the structure of the company, details of his bank accounts and details about his children. Ouch.

Fortunately, that BlackBerry was among several that were recovered from mobile phone recycling companies as part of a study into data loss on mobile devices. It’s a significant issue that many companies have not addressed.In a 2006 survey by the Business Performance Management Forum (BPMF), nearly half the respondents reported that at least 25 percent of all mobile devices in their organizations carry mission-critical information and applications.

Imagine having a computer that you could never wipe clean of any of your confidential business activities. Instead of recycling, we can only destroy the items. Mobile device security software commonly available can secure the device, but cannot wipe it. If anyone knows of a good wipe program, please drop me an email.

Some folks leave their SIM cards in the phone they return to corporate headquarters, along with their messages and documents. Taken any pictures on that phone you wish you hadn’t? That office Christmas party where your senior manager got drunk and acted up? They’re probably still there.

I’ve just thought of a new Rule of Thumb: There’s no such thing as DELETE on a cell phone/PDA/camera. We must act accordingly until assurance can be confirmed about wiping these devices. If it cannot be wiped, it must be destroyed, which is not exactly “green” in any corporate environment.

My old one (a Palm) is in my desk drawer, kept for parts because my spouse is still using a Palm. Where’s yours? What was on it?

FREE Tool - Changing Local Administratior Passwords On Your Domain

I just love VBS.

And I love the folks that share their tools, AND give us a nice interface AND allow us to push a report to a .csv file. So a BIG thank-you should go out to Jeffrey Hicks, who has his own site, anjd a helpful Blog.

He has a nifty free tool for changing the local Administrator password, PWDMan. This tool also reports on the password age. I’ve been looking for a tool like this to give to the admins I “visit” so that I don’t have to hear any more excuses about how hard it is to change that password periodically.

Jeffrey also has a whole page of VBS scripts and yes, Power Shell scripts, something I’m going to learn when I grow up.

I look for tools I can offer to help admins do their jobs, detect problems and provide easy ways to assess their own systems. I teach how to fish, and try to get buy-in so that the watchfulness happens all year long, not just during an audit.

Host vs. Network IDS

I’ve noticed a definite tendency for organizations to move to monitoring network traffic with their Intrusion Detection Systems. It’s a lot easier than trying to update a host IDS service/agent and keeps the increased CPU at the monitor, where it belongs. Also, host agents are limited by what the operating system is willing to log.

Windows, for instance, will give you hundreds of logging messages that actually have no useful information for an IT Admin or Auditor to review. The setup of their Event Log auditing mechanism is still klugy, outdated and difficult to interpret. (Micro$oft, are you listening?). I can’t say I get to wow about UNIX either, and FORGET anything like logging with Novell.

So, why bother? I still think that a host IDS has a place, because there are things it can watch for that you will only see on the server. For instance, if someone is doing brute-force against the administrator account. If someone has made Active Directory changes who should not be in there.

How would you tell if someone added themselves to the Server Operator group? (where I’d go to look around and maybe get my hands on a SAM database). If you’ve got an Event Log monitoring function, you could pick it up that way, but wouldn’t it be nice if the IDS would pick it up? If you just installed a host IDS on certain critical servers? There’s lots of options if you step out of the all-or-nothing approach.

Speaking of which, monitoring development and test servers really does have to be included. As much as we’d like to forget that they’re there, that’s the first place hackers look. As a penetration tester, I can attest to that, as well. Patch ‘em, monitor ‘em, they’re on YOUR network.

Auditing MS SQL - Roles, and Why They Matter

SQL “Server” runs on top of MS Windows, and it has groups inside of it that are not seen on the Windows server or even the Windows Domain. That’s why we have to check and make sure that inappropriate users don’t have complete access to everything inside the database. Not everyone should be looking at those payroll files!

So, to confuse us a little more, instead of calling them groups (like Users, Administrators, Power Users, etc) SQL calls them “roles.” There are roles based on SQL as a whole (the SQL “Server”) and roles based on individual databases. Kind of like Domain Admins vs Local Admins.

SQL Server roles are equal to Domain Admins inside SQL Server. They give rights to many different core functions of SQL, and the sysadmins role has rights to everything inside SQL. By default, sa is here, but you should look very carefully at any other user in this group. DBAs like to give themselves a “backdoor” into the server this way, but that should be removed. Best practices recommend always having the DBA use their Domain ID for insertion into this role. This way you can log and monitor their access, and when they leave the company and their ID is disabled, they won’t be able to access the SQL databases. It also let’s them know that you are watching, and people tend to behave better that way.

I have found that software developers LOVE to put their names in this group, and not their Domain IDs, either. If it’s a production database, they have no business having anything other than SELECT rights anywhere. Kick ‘em out!

Here’s the SQL Server roles default permissions:

Fixed Server Roles
Sysadmin – can perform any activity (and Builtin\Administrators are part of this group by default)
Serveradmin – can set SQL-server config options and shut down SQL Server.
Securityadmin – manage logins and CREATE DATABASE permissions, read error logs and change passwords (within SQL, not Windows)
Setupadmin - manages linked servers and startup procedures
Processadmin – can manage processes running in SQL server
Dbcreator- can create alter and drop databases
Diskadmin - can manage disk files
Bulkadmin - can execute BULK INSERT statements

How do you find out who is in these roles? Use the results of a stored procedure: sp_helpsrvrolemember with the results taken from the Master database.

For individual databases, there are database roles. People in these roles can be all powerful, but only within that individual database, not all of SQL. How do you know how many databases are inside one SQL Server installation? Again, a stored procedure: sp_helpdb

You’d be surprised how DBAs like to create their own little mini databases (on production boxes!) just to “do things.” They may be great “things,” but those databases need to go somewhere else.

In order to see the members of all the database roles, run another stored procedure: sp_helprolemember run from each production database in SQL Server. It’s a little tedious, but you will get the information you need.

I commonly monitor the membership in the db_owner role (equivalent to a local server Administrator). By default, the dbo, or db_owner is the only one in this role. If the DBAs are already sysadmins, they don’t need to be in this role, unless you want to be very granular in your controls (NOT a bad idea). Developers shouldn’t be in this role, or just as bad, the application ID of an application accessing data.

This is where a lot of software development falls off the security bandwagon. If the application ID is db_owner, it means that the ID has access to everything in that database. If I can acquire the application ID’s password, (and some of them don’t have one) I can get into all the data. I wouldn’t use the application, just connect directly via ODBC or even Excel.

It’s easier to write applications using the application ID as db_owner, but unless you are using middleware to vet everyone’s login via the Windows Domain, you take the risk of losing all that confidential data out the back door.

If you can acquire this information on a quarterly basis, you will go a long way towards having an easy audit and a better night’s sleep!

FREE Tools for Auditing MS SQL Server

There’s a lot of really nice application tools to audit SQL databases out there. They have lots of bells and whistles and write out a really nice report with professional formatting. If you’ve got one of those, LUCKY YOU. But most of us Admins and Auditors have to scrounge for what we can find with the budget we’ve got (read $0).

So I always like to start out with SQLPing A nice tool from SQLSecurity.com that scans the network both actively and passively, testing SQL’s default listening port 1433 and it reports on what it finds (nothing fancy, just text). This includes those desktop versions of SQL Server: MSDE, that are often configured with SA-no password. This tool will tell you what version SQL is running, and will even do a test for SA-no password. Since SA cannot be locked out, you won’t damage the server with one attempt.

NOTE: Check with your network administrator before running this test. It’s also a great test of your intrusion detection system, because, if the IDS is configured properly, it should catch it and alert for it. Make sure management knows you are using this, if that’s what you’re going to use it for. No IDS? No worries.

I also recommend SQLSecurity. com for a lot of great information and scripts for DBAs. They know all about SQL Injection (Unlike a CIO I recently interviewed three years ago) and they have lots of MS SQL information well worth your visit.

The other free tools I use are found inside SQL Server. Yes, inside the SQL database, and they are called “Stored Procedures.” This is a fancy name for pre-written sequenced query language batch files. The folks at Microsoft have done us all a great favor by writing hundreds of them. Inside the Master database are the stored procedures you want to run, or have a DBA run for you and output to .csv format files. (Each database also has stored procedures, but the Master database SPs are the ones you want.) There is a table full of them, and here are the ones I use:

sp_helpdb Names and file locations on the Windows server of all SQL databases. (And you should find out who or what has access to those files)

sp_helpuser Review usernames, groups the user belongs to, and their default login database.

sp_helplogins a. Identify and review any external users and groups; b. Look for mappings of login name to UserOrAlias or as DB Owner. c. Check AUser and ARemote columns to identify who has remote access to what database.

sp_helpsrvrolemember Users and groups assigned to each server role (More on this later)

sp_helprotect Permissions in the database. Examine this list carefully for what rights are granted and denied to whom.

Get the results of these queries, results from the last post, and SQLPing. You’ll have some very interesting items to review. and remember, Google is your friend.

Inside the Database Server - MS SQL

The first question to answer is: “Is the SQL system patched?” You or a DBA can confirm this inside Enterprise Manager (the software client that runs on SQL or from a remote installation of it) by right-clicking the primary database icon and selecting Properties. You can also run a query inside Query Analyzer “SELECT @@ VERSION” and make sure the latest service packs from Microsoft are installed. No excuses! Make sure it’s updated. MS SQL doesn’t have a lot of patches and updates (unlike the operating system) but they are important.

MS SQL Server is one of the easiest systems for an auditor to review. I have a script I use (when they let me ON the server) or hand off to the DBA/Network Administrators and watch them run it. The most difficult part is sorting all the data into something readable. I always ask for the output in .csv format, which I can then port to Excel.

SQL Server always has a core of databases installed by default. A “tempdb” database used by SQL during its operations, a “sample” database, Northwind (which you can delete, by the way), a “model” database, and the critical one (to auditors and IT Security) the Master database. I do a thorough audit of this database before I move to any other databases installed by applications.

The Master database holds the core functions and controls of MS SQL. SQL has its own users and groups, that are created outside of the Windows server and any Windows Domain the server might belong to. You won’t see them in the local server administration; only the “user” SQL installs to run the SQL services. You can add Windows users and groups to SQL users and groups, and this causes a lot of confusion amongst auditors and non-DBAs.

For auditing purposes, we can keep it simple. But I do want to add one important note first: None of the queries and stored procedures I’m going to discuss with you do ANYTHING in SQL other than READ. All of the procedures use the SELECT command, which is equivalent to READ in SQL.

This is really important in auditing, as the first rule is always: Do No Harm to the System. Always! And it means I don’t accept excuses from DBAs who are afraid of “adding more stress” to the server. Reading a table takes a nanosecond.

Tables in the Master database hold information about the management of SQL as a whole. There is a table in the Master database called syslogins. In SQL 2005 it’s called sysxlogins. This contains information about all users that have a login ID to SQL, regardless of what database they access. (And you want to make sure that very few can log into the Master database).

This is where you will find out about the “sa” ID, which is the equivalent to “administrator” inside MS SQL. You won’t see this ID anywhere in the Windows operating system, but it is critical to examine inside SQL. It can be disabled in SQL 2005, but NOT SQL 2000.

So, here’s what we use: SELECT sid,name,password,dbname FROM syslogins If you can, grab it off your SQL system (even a development box) to get a sense of the structure.

I look for several things in the results of the query, but the two most important are: Who has access and do all the users have a password?

Yes, a password. I am STILL running across production systems that use the sa ID with no password. You might as well write HACK ME across your forehead, poke yourself with a pointed stick and resign. Developers still do this in their coding because it’s easy and fast. You get what you pay for.

Under the Password column in the results, you should see gobbledygook or the word NULL. If there are users and groups from a Windows Domain, they will be identified by the domain name or server name and then the ID, thus: Server\Name (for a local account) or Domain\Name. These IDs always have NULL in the password column, because SQL expects them to be authenticated by Windows.

I look for a user ID with NULL in the password column. And then I test to confirm the ID is blank by using ODBC to connect to the SQL server. I am regularly horrified, I am sorry to say.

Examine the list of users for IDs that have been created inside SQL. DBAs and developers are notorious for creating “backdoor” IDs that are not part of the Windows domain and only exist inside SQL. The problem is, when they leave, those IDs don’t get deleted or disabled when you disable or delete their Domain ID. They just sit there, waiting to be used and abused. Again, I’m regularly horrified, I’m sorry to say.

Finally, the query results will show you each user ID’s default database. Make sure IDs only have access to the database they need.

Next: MS SQL internal groups

Data Loss Due to Insider Access = Much Higher Risk

Similar to the Countrywide data theft, a recent news report from Korea highlights the theft of 11 million email addresses and national identification numbers.

Two out of the four people arrested were employees of a Call Center (does this make you nervous? Me, too) and acquired the information and pushed it into Excel files for easy sorting. (Nice of them.) Two compatriots helped burn CDs and DVDs with the information, to make selling it easier. Quite the nice home-grown production.

As much as we have to be concerned about external breaches, it appears that data theft from an internal source carries a much higher risk of fraud.

A study from ID Analytics suggests that consumers victimized by insider data theft — theft by an employee — are 12 times more likely to be ultimately hit by fraud than victims of an accidental data loss, like a lost laptop computer. According to the latest Computer Security Institute report, insider threats are up 17 percent this year.

“For the most part a company doesn’t lose its data, they lose your data,” Bruce Schneier tells the Business Technology Blog. (emphasis mine)

When an insider wants information he/she can sell, where does she go? To the corporate databases. HR records, Payroll, and Medical Administration Plans.

The only way to catch a thief is to watch for him.

Next: More on Auditing Databases

Auditing Databases - Part II

Thanks to marketing, we have a confusing jumble of terms when thinking about databases. For instance, Microsoft’s database product, SQL Server, isn’t really a “server.” It has to run on a Microsoft Windows Server 200x operating system. It’s a database system, that can hold many databases inside of it.

Another example would be Oracle databases.Oracle can run on any version of *NIX, and Windows, too (but ooohhh so slowly, since the two don’t get along). But since Oracle sells its database product by the CPU, one database is almost always set up to have multiple “schemas,” and those are actually databases inside the database.

Sybase is very similar to MS SQL (after all, M$ bought it from them!), and DB2 can run on an IBM-based system, like the iSeries, or AIX, or even Windows (good luck there!).

So each database product has a slightly different structure, which means we have to have an understanding of each structure in order to audit who, what and when.

All the products install “pieces” of themselves on top of a server structure, whether it’s Windows, *NIX, or AS 400. They all install a database user ID, usually with administrative rights. They all install files and services that open listening ports so that users or applications can connect to those ports. MS SQL uses 1443 and Oracle uses multiple ports, but principally 1521. It’s important to know those ports, and what connects to them.

Using those ports, someone with an ID and password can connect to your database directly, using any database-aware application to pull data out. Microsoft has an ODBC software client that connects using this port. Many DBA’s and software developers connect directly to databases this way. You will want to monitor any connections to your databases using this access.

A DBA should be able to identify who has direct access and set up a custom procedure to write a report on access via ODBC, or any direct Oracle access. Given that this connection can be unencrypted (by default), any user ID and password could be captured. Another reason to monitor!