Security in Sybase

A little addressed issue in Sybase, and Databases in general.  Lots of
attention is given to securing your Servers (Unix and otherwise), but 
often times the data stored in Databases is far more important to secure.
Here's some Security issues to address inre: your Sybase server.

Note; this is mostly written for Sybase servers created on Unix boxes.
A product exists called dbScanner, which performs vulnerability assessment
for Oracle and Microsoft SQL Server databases, but not Informix, Sybase
or other RDBMS's.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Operating System Security Issues

---
Weak Sybase account passwords.  

A very common way to gain access to Sybase databases and data.  The problem 
with passwords comes on several levels:
- Generic application logins: Commonly front end applications use one generic 
login to gain access to a server (and control user logins, security, and 
permissions through the front end).  These passwords often are often the
same as the username, or easily broken.  If you must use this method of login
to the Sybase server, as least make sure the "hostname" parameter is populated,
so some sort of audit trail exists to help troubleshoot problems (an sp_who 
will just show dozens of the same process name logged in otherwise).  Also
populate the "program_name" parameter for Open Client connections, to create 
a rudimentary audit trail tracking the location of connections.
- Generic Sybase unix account login: similarly to above, Sybase gets installed
typically with a generic user account (sybase) that often gets an easily broken
password.  Counter this by ensuring the unix logins are decently secure.
- sa password: when Sybase gets installed, the sa password is initially NULL.
This should be the first thing you change.

Notes:
- There are no known password cracking tools for Sybase (ala Crack) but
it probably would not be difficult to code, if an enterprising person
was found.  Such a tool does exist with capabilities for Oracle (dbScanner).
The tool would fail though if the "maximum failed logins" audit option was set.

- There are no built-in modules to lock accounts after X failed
login attempts.  However it would not be difficult to write a home
grown script that works with the auditing feature; install auditing, set
sp_audit login, "all", "all", "fail" which logs all failed login attempts.
Then write code in a third party program to count successive failures
and issue a sp_locklogin.

- There are no npasswd-like utilities to ensure a password
is created intelligently (npasswd prevents easy-to-crack passwords
from being created; passwords like "password," "security," "14username," etc).

---
Weak Unix server Security

Its relatively straightforward to gain SA to a Sybase server if you have 
root access.  
- once you get root, su - sybase
- edit the $SYBASE/install RUN_server file, add "-p" at the end of the
dataserver line
- restart the server.  

This causes the system to reset the sa password and print it
out to the errorlog as the server comes back up.  Log in as sa with new password.

The problem is, most Sybase DBAs have little control over their overall system.
A Linux server will have an un-hacked lifespan measured in days if brought up 
without the Security patches.  A Sun box really needs the security patches 
installed immedately, because of published known security holes.  Attempt to
make your Unix servers as secure as possible.

---
Sybase passwords used on command line

isql is the main Unix program used by dbas and developers to log into 
Sybase from a Unix prompt.  isql needs 3 or 4 parameters passed to it
to be useful in anything other than a one-server environment, and typically
is called like this:
% $SYBASE/bin/isql -Uusername -Sservername 

isql also takes a "-Ppassword" option that is often used by lazier users.
This is an obvious security hole, in that any other user can run a simple
ps and obtain the password.  There are two solutions to this problem:
- don't use the -P option on command line; you will be prompted for the
password at the next line.
- use sqsh or some other isql wrapper solution, which can read from a .rc 
file and can automatically log users into databases w/o the need for 
multiple command line parameters.  This means your .rc files contain the
password and must be secured.

---
Use of Sybase passwords embedded in scripts or flat files

Many Database operations jobs are automated through the use of scripts.
There are hourly, nightly, and weekly tasks that might be scripted out by
DBAs, and a method must be used to read in the sa password to a script.
Typically the script will either contain the password in clear text within
the code (as an environment variable perhaps) or will read the password
from a flat file contained in a central location (a better way to manage
the password).  In both cases the security requirements are the same; make 
sure the permissions on this file and the directory it sits in are set to 
owner only.  If the password is stored within the code, the code must be
secured.

There are many shell coding methods to avoid the security hole of having
the password appear in a ps command, and they are detailed in the Sybase 
FAQ, ASE Question 4.1, located at http://www.isug.com/Sybase_FAQ. 

Open Client-based programs (Perl scripts, C programs, etc) are
a bit easier to manage, in that the Open Client construct does not fork
a separate process to call the external program isql.  However, the
password must still be stored somewhere, either within the program code
or in a flat file.

None of these solutions are especially secure.  The best solution i've
heard of was posted to Sybase-L 1/22/01 by Bert Minten 
(Bert.Minten@TDH.STATE.TX.US) and involves writing a wrapper
script that calls isql and passes in a password read from an encrypted
file (which is created by a second script and is stored in the user
home directory).  I've also been at locations that use a password
proxy of some sort, but have not been able to study such a solution at
length for security holes.

---
Make sure you have a Good backup plan and a disaster recovery plan 
(consider Replication?)

An important part of Database security is making sure you can recover if 
something goes wrong.  Just as many systems have a poor or no backup plan in
place, Sybase does not automatically start backing its user databases up.  A 
DBA must implement such a system, and these databases much be uptodate in the 
case of a security problem.

Disaster Recovery is a bit more tricky; planning for the destruction of
the entire Sybase server, or the entire Unix machine, or the entire building
where the Unix machine sits.  If these three situations came to pass, would
you be in trouble?  Replication Server is an excellent (if expensive) way
to keep a warm-standby contingency server ready to go in the case of a problem.
Keep backup copies on an alternate media besides a file system file on the
same server as the Sybase server.  Keep these tapes in a different location.

---
A couple of other notes

- Use raw disk devices, not file system files.  This is a slight additional
level of safety for your data files, in that a hacker cannot "see"
the flat files consisting of data devices.  However, once a hacker has
the root password, they can simply run dd to convert the raw partition to
a readable file (eg: dd if= of=)
and run strings on it.  This is a difficult way to sift through potential 
personal information, but available nonetheless if your data is not stored 
encrypted.

If you do use flat files, make sure that the Sybase unix user owns the
files and directories, and the file permissions are appropriate (700 or
600, meaning no world or group access to these files).

---
- Never install Sybase as root.  Always create a special account to be
the owner of the Sybase server processes and files.  You will need the root
owner briefly while creating your Sybase server (specifically, to modify
/etc/system to add Shared Memory, to format the disks being allocated to
Sybase, to change the ownership of devices in /dev/rdsk, to create 
start/stop scripts in /etc/init.d, and to create the Sybase user, home
directory, and directory structure).  If the root user runs the Sybase 
passwords, then any user can issue a command similar
to the following: "dump database abc to "/etc/passwd" and wreak havoc
on your server.

---
- Consider installing Jaguar; a Sybase middle tier between a client and
server which encrypts and protects all data transmission.  ID and password
transmissions are encrypted by default in Open Client 11.x versions and
higher, but communications after login are not.  And, ASE 12.5
includes SSL connections and row-level security within the database.  

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Database and Data Security Issues

There is an entire manual in the Sybase Sybooks documentation set called
"Security Administration Guide."  These topics are discussed in more detail.
However, the gist of this manual is described below.
http://manuals.sybase.com/onlinebooks/group-as/asg1150e/secag

---
Use individual user accounts, not generic accounts

Covered in the Operating system comments.  Just a security notes here: 
logins are created with a default database but no specific access to any
database or any object (unless rights are given to a public group).  This
is good from a security standpoint.  DBAs must manually decide which databases
to add the login to, and as a login is added as a user to a database, a 
group is selected for permissions.

sp_locklogin can lock login account access without having to necessarily
drop the login or change its password (this may be necessary if the login
happens to own objects elsewhere in the database server).

sa is a special Sybase login; capable of doing anything on the server.
It is akin to a "root" user on a Unix box.  Access must be very strictly
controlled; a destructive hacker w/ an sa password can easily destroy
your database system with just a few commands.  The Sybase security manual
recommends using the sa account only to do the initial Sybase installation
and the highest levels of operations (device and database creations) and 
then to disable the account using sp_locklogin.  However in practice, this
is never done, and a small Admin community shares the sa password for a
particular server.

---
Use custom groups to manage object permissions, not public

Sybase provides the "public" user group as a default group for users.  Access
to objects should never just be arbitrarily granted to "public."  Instead,
these rights should be granted at a group level, and users are specifically 
added to a group within a database.  The recommendation is to restrict access
to tables by creating different groups (a read-only group versus a read-write
group).  This method of securing data makes it easier to manage object 
permissions for your users anyway; instead of having to grant individual
rights to each user in a database, one command can be issued.  Rights currently
on a database can be seen using the sp_helprotect command, but a GUI DBA tool
makes rights administration a bit easier.

One nice Sybase security feature (albeit completely arrived at as an added
benefit) is that objects created have zero rights granted to any user
or group (except the public group; see above).  This eliminates a potentially
huge data security problem from the start.  

When granting privileges to users for specific rights, never grant them
using the "with grant option."  This basically passes on admin privliges
to the user, who can in turn grant those rights to any user he sees fit.

---
Use Views to control select access, and Stored Procedures to control data
modification operations (inserts, updates, deletes).

Taking the group controls one step further, a well secured application
can deny any individual OR group rights to a database user, and force all
access (read only or modification) to custom written code (Views and Stored
Procedures).  

Views are a common way to get a cheap level of data viewing security; grant
select to a user on a view into a table, which is written to only show the
columns of the table that the users are allowed to see.

Stored Procedures can be used to control all data modification access, with
users only needing to be granted execute privledges on the stored procedure.
The limitation is that text and image data cannot be manipulated through
stored procedures.

Triggers can be created to prevent unwanted destruction of data.  You can
create update and delete triggers which do nothing but rollback the transaction
attempt.  This is a quick way to prevent data from being deleted.

Caveat/Known Hole: An excellent post by Tim Green (tim.green@mercer.com) on
8/12/1997 discusses an interesting workaround to this point.  Basically,
if you have two databases owned by the same account, any user in one
of the databases aliased to dbo will have access to the other database's
objects by creating cross-database views.  Sybase believes its a feature;
but I believe its a slight hole.

---
Use built in Sybase roles to control System access

Starting with Sybase 10, several system level roles were established so that
certain operations could be performed by users who did not need full system
level access (the sa password).  

- dbo; the dbo role within a database allows full reign of power to the
user, but only on that database.  Outside the database, the user is normal
and has no specific rights.
- sa_role: full power
- sso_role: can add logins, change passwords
- oper_role: can perform dump and load commands

You may also configure your own roles.

The existence of roles provides a dangerous security backdoor on your
system.  Imagine if a user obtained the sa password, however briefly,
and granted himself sa_role.  At some later date, without needing the
sa password, he would have sa rights.  sp_displaylogin is a good audit
check to see what roles are configured for what login (sa or sso required).
Its recommended to create separate logins for the SSO and the Operator,
then grant these individual roles to those logins.


---
Use sp_hidetext for stored procedure code

Sybase offers the sp_hidetext file to prevent users from being able to 
see the SQL code that a stored procedure executes.  This was originically
offered as a feature available to commercial 3rd party developers, who
did not want their copyrighted code to be stolen by users (if a user
issues "sp_helptext sp_name" the user can see the entire code listing
of "sp_name."

---
Eliminate non-logged transactions as a common means of operation

Certain operations in Sybase are "non-logged," meaning the action and
results are not logged by the Transaction log mechanism provided by Sybase,
and thus are not reproducable nor recoverable.  These are: truncate
table, fast bcp, use of bcp library routines, writetext to text fields, 
select into, and parallel sorts.  These commands are only available on
databases with the sp_dboption "select into/bulkcopy/pllsort option"
set to true.

---
Install some form of Auditing

Sybase has a robust Auditing feature, which i've described in detail at
www.bossconsulting.com/sybase_dba (click Auditing topic).  Sybase's system
can be configured to track just about any aspect of the server's activities,
but the setup is complicated and the maintenance can be difficult.

There are no known intrusion detection tools for Sybase databases.  By
nature, the information inside a database is very difficult to analyze
automatically for tampering; it would be akin to amalyzing the contents
of personal files on your hard drive, looking for read attempts. 
But, you can set some quick audit options to detect failed logins.  Set
the "log audit logon failure" option to track login attempts.  You can
also set "maximum failed logins" and lock an account after too many
failures.

Absent of running Auditing, I suggest a "poor man's" audit trail.  Add 4
fields to every user table; create_date, create_user, update_date, update_user.
(some locations just have the update_date and update_user).  Populate these
fields through insert or update triggers, and you will have a simple audit
trail to assist in troubleshooting data problems.

---
Encrypt all personal data stored

Login passwords are already encrypted one-way by Sybase (back in Sybase 4.9.2
passwords were stored in cleartext! in the master..syslogins table).
Unfortunately this encryption scheme is not available for other data in your
database.  Application passwords, credit cards, social security
numbers, home addresses, etc must all be encrypted with a 3rd party method:

Methods:
- home grown security encryption; a crypt() function based module in a
perl or C script?  One could also issue 
- If you're using Sybase to store an application password, encrypt the
password in the database, and when a user submits a password, encrypt 
answer and compare the hashes.
- There's supposedly an undocumented feature of dbcc called "hidetext," but 
I cannot find any information about it.
- A nice writeup of techniques and thoughts was posted to Sybase-L by
Frank Soloman 6/9/2000 (sysfrank@pop.uky.edu), including some simplistic
all SQL options