Sybase has a number of features that distinguish it from other database systems, and in particular, distinguish it from Microsoft SQL Server.
Sybase ASE supports Java extensively, incorporating its own VM and full interoperability with Transact-SQL. Sybase implements part 1 of the SQLJ standard, and extends the standard, for instance by permitting direct references to Java methods and classes (the standard stipulates the use of aliases). As an example, the following transact SQL will raise an exception if the host 192.168.1.1 is not listening on TCP port 22:
declare @s java.net.Socket select @s = new java.net.Socket( "192.168.1.1", 22 ) select @s>>"close"()
As you can see, it is possible to declare transact-sql variables of Java types, instantiate objects using parameterized constructors, and call functions.
Here's a quick run-through of the preceding example:
declare @s java.net.Socket
This declares a Transact-SQL variable in the normal way, but using the “Socket” type from the Java “net” standard package.
This instantiates @s with a newly created socket using the (java.lang.String, java.lang.Integer) constructor. It's fortunate that Sybase implements this syntax because many objects in Java require creation via a parameterized constructor to be useful. In this case, we're creating the object and attempting to connect to the IP address “192.168.1.1” on TCP port 22. If we cannot connect to the host in question, we'll see a Transact-SQL error message that wraps a Java exception, like this:
Server Message: Number 10707, Severity 16 Server 'SybTest', Line 2: Unhandled Java Exception: java.net.SocketException: Connect failed: Connection refused. at java.net.PlainSocketImpl.socketConnect(PlainSocketImpl.java) at
And so on.
Assuming we can connect, we then call the “close” member of the Socket class, to tidy up:
select @s>>"close"()
There are two interesting points here: first, the member access operator >>that we use to access members of the object and second, the fact that we've had to enclose the member function name in double quotes. Since there are a lot of name collisions between Java and Transact-SQL, there has to be some way of using functions like close and connect without confusing the SQL parser. In general, putting the identifier in double quotes does the trick. The quotes are only necessary if the member is a Transact-SQL reserved word, so for example
set @is = @s>>getInputStream()
will set the variable @is to the result of the getInputStream member function of the @s object.
Sybase supports XML via the built-in functions xmlextract, xmltest, xmlparse, and xmlrepresentation. You can obviously interact with XML data using the standard Java libraries, as well as a collection of Java-based functions provided by Sybase (forxmlj, forxmldtdj, forxmlschemaj, and so on).
If you want a simple, straightforward way of exporting the result of a select statement as XML, you can simply add “for xml” on the send of a select statement:
It results in output such as this:
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <name>master</name> <dbid>1</dbid> <suid>1</suid> <status>0</status> <version>1</version> <logptr>2744</logptr> <crdate>2004-10-04 10:00:55</crdate> <dumptrdate>2004-10-18 10:02:16</dumptrdate> <status2>-32768</status2> <audflags>0</audflags> <deftabaud>0</deftabaud> <defvwaud>0</defvwaud> <defpraud>0</defpraud> <status3>0</status3> <status4>0</status4> </row>
All of this XML support eases the integration of Sybase into an existing XML-driven architecture, but has little security relevance in itself; it's simply a different way of representing the same data.
As previously mentioned, Sybase supports a variety of operating systems, including Linux, HPUX, Mac OS, Sun OS (Solaris), and Windows. Sybase places a fair degree of emphasis on performance and performance tuning, especially on high-end hardware.
Sybase supports the use of raw disk partitions for database devices, and allows configuration of performance-relevant parameters such as delay-write caching.
Sybase supports a variety of authentication protocols, including Kerberos, DCE, NT LanMan, and native Sybase authentication.
Sybase, like most other enterprise-level database systems, is found in a variety of deployment scenarios. Each deployment scenario has its own set of challenges for the administrator that's securing it. In some cases it's necessary for “everyone” to be able to directly connect to the database — for example, a client/server expenses system; in others only a single host is permitted to connect — for example, a back-end database to an e-Commerce web site. This section goes through the various common deployment scenarios and discusses some of the security issues around them.
It's not uncommon to find older Client-Server applications buried deep inside corporate networks. These applications typically address business needs such as expenses, helpdesk systems, software bug tracking, timesheets, and in some cases, project management.
Typically in this kind of system, each client machine connects to the database server via ODBC or some other similar generic API (OLE-DB, JDBC, and so on), and interacts with the database via a standalone, compiled application using some low-privileged database account.
Likely security problems are
Frequently this “group working” type of application is installed on a shared “team” server. The problem with this is that once the database server is compromised, the other applications managed by that server are also compromised. Essentially, this is a generic problem with shared infrastructure — you can think of it as the “own one, own all” problem. This is a situation in which the economics of network infrastructure work in the attacker's favor. If there are N applications, which each take a minimum of E effort to compromise, the ideal configuration would mean that the attacker would have to expend at least N * E effort to compromise all of the applications. Because the applications are deployed on shared infrastructure, the attacker only has to expend E effort, where E is the effort required to break the weakest application on the server.
Probably the most common deployment scenario for database servers in recent years has been as the backend to a web site. Be it e-Commerce, a technical support forum, a web content management solution, a customer database for product registration, or as the central management point for access to other data feeds, the database-oriented web application is now ubiquitous. Recent years have thrown up a bewildering variety of Web application environments, scripting languages, and management solutions, all of which have their own security problems.
In many ways, the level of security required of a web back-end database server is higher than that of an internal system, mainly because of the possibility of compromise over the Internet. The following list describes the likely security problems with a web back-end Sybase server:
https://sybase.example.com:8182/invokemethod?type=execute&username=sa&password=&service=SYBASE&sql=select%20@@version&output=All
This query will return the @@version string.
The bane of the security administrator's life is the development team. If there is a single part of any organization that has the most open security posture, this is it. Again, the economics of the situation act in the attacker's favor. Developers have a very limited amount of time to get their code running. They don't want to have to spend time performing a 30-step Sybase server lockdown procedure; they just want to install their app, install their stored procedures, and see the whole thing running. If there's a problem with their code, they don't want to have to wait for the database administrator to get back from lunch before they can fix it; they want administrative control of the database server now. Consequently, as an attacker, you'll often find default installations of everything in a development environment. In terms of Sybase, because of the popularity of Windows, that means blank sa passwords with the database server running as “local system” every time.
The major security challenges with the deployment of databases in a development environment are:
Most of the preceding discussion has been fairly generic; it's time to discuss some Sybase-specific firewall configurations.
By default, Sybase services listen on the TCP ports that are listed in Table 13.1.
SERVICE | TCP PORT |
SQL Server | 5000 |
Backup server | 5001 |
Monitor server | 5002 |
Web Services | 8181 |
Web Services (SSL) | 8182 |
XP Service | 5004 |
It's not normally necessary for every machine in your enterprise to connect to your database server. In general, only a restricted number of machines will be connecting to a database server and the server should have a firewall rule set that enforces this policy. Several databases have been found to have serious flaws in their authentication protocols — in some cases giving an attacker total control over the server — so it really does make sense to firewall off your databases.
If a dedicated firewall would be too costly, consider deploying a host-based firewall rule set specific to the operating system you are running. For example, the ability to specify complex IPSec filtering rulesets has been built into Windows since Windows 2000, and the IPTables mechanism in Linux can also make an extremely effective firewall. If you are going to the trouble of setting up a database server you might as well do the small amount of extra work it would take to partition it off from the rest of the network. When the next database worm or disgruntled developer comes along, you'll be glad you made the effort.
The communication protocol used by both SQL Server and Sybase is known as Tabular Data Stream, or TDS. Sybase supports SSL for encryption and additional authentication.
Generally client applications communicate with Sybase via the Sybase-supplied client software, normally via ODBC or JDBC. Third-party clients are available, however, including a number of open source ones. “FreeTDS” is in Beta at the time of writing, but provides an interesting insight into the structure of the TDS protocol. You can find the homepage of the FreeTDS project at http://www.freetds.org.
The default configuration of Sybase permits authentication with passwords transported in plaintext across the network, though Sybase configuration documentation does suggest that this should be changed as soon as possible, when configuring a coordinated Sybase authentication policy. Sybase permits a number of authentication mechanisms, including Kerberos, DCE, Windows NT LanMan, and Sybase native authentication. The recommended policy is to use the most convenient mechanism for your organization that permits encrypted communication of credentials.
Sybase has a fairly complex privilege model, permitting a wide variety of configurations and allowing role-based partitioning of accounts, as well as dividing users into groups and enforcing column- and row-level security on tables.
SQL Server version 11.0.6 passed the security evaluation by the National Security Agency (NSA) at the Class C2 criteria (the Orange Book). The evaluated configuration was HP 9000 HP-UX, and certain features, such as remote procedures and direct updates to system tables, were excluded from the evaluated configuration. In terms of practical security, this doesn't really mean a great deal. Generally an attacker will compromise a server using one of the following:
Neither of these types of attack are really relevant to the number or type of formal security evaluations that a database has; the first because the trusted channel has deliberately compromised discretionary access controls for business reasons (for example, a web application must be able to update certain tables), the second because the attacker has control of the system that enforces the discretionary access controls.
Each user of Sybase requires a login to connect to the database. Each login has a password, and is a member of certain roles. Each database in Sybase has a “sysusers” table that determines which user accounts can use that database. Each login may have a different alias in each database.
The process for adding a new user generally goes like this:
The administrator and database owners grant the user (or the roles he belongs to) permissions on various database objects.
Each login account has a password. Sybase can enforce rules for password complexity; there are two default mechanisms for this.
sp_configure 'check password for digit', 1
will apply a system-wide check that ensures all new passwords have at least one digit.
sp_configure 'minimum password length', 6
will apply a system-wide check that ensures all new passwords are at least six characters in length. This setting can also be applied per-user or per-role, via options on the sp_addlogin and sp_modifylogin procedures and the “create role” and “alter role” statements.
You can also specify password expiration on accounts in Sybase, so that a given password must be changed after a certain period of time. Again, the administrator uses the sp_modifylogin procedure and “alter role” statement to achieve this.
The default roles in Sybase, along with their purpose, are listed in Table 13.2.
ROLE | PURPOSE |
sa_role | System Administrator role |
sso_role | System Security Officer — the “security” administrator |
oper_role | Backup and restore databases |
sybase_ts_role | Using most DBCC commands (Sybase Technical Support role) |
navigator_role | Management of Navigation Server |
replication_role | Gives a user rights to manage the Sybase replication server |
dtm_tm_role | Distributed Transaction Manager role, required to participate in distributed transactions |
ha_role | High Availability, required to perform cluster management operations |
mon_role | Used to access MDA tables (Monitoring Data Access) |
js_admin_role | Job Scheduler Administration |
messaging_role | Administration of Real Time Messaging Services (RTMS) |
Sybase uses a flexible filesystem layout to store data. It is possible to configure Sybase to use raw partitions, as well as the default behavior of using a single file per “device.” In Sybase, databases are created within devices. The sp_helpdevice stored procedure will list the available devices. Devices can be created using the disk init command, for example, in Windows:
disk init name='testdisk', physname='f:sybasedata estdisk.dat', size='10M'
In Unix, the dsync flag allows control over write buffering. This is useful because allowing Sybase control over the disk writes allows greater resilience, because Sybase will be able to recover data if the system fails. Of course, writing data to the disk immediately with no caching can impact performance, so in some circumstances you may favor speed over resilience (especially if you're using replication).
Each disk device is managed by Sybase, using a highly optimized storage structure. Multiple databases can be stored in a single device — though allocation of databases to devices is definitely another performance tuning issue — and the choice of the layout of devices is largely dictated by your performance, resilience, and backup requirements.
In terms of security, the standard DBMS/File system rules still hold — if attackers can read the files that back the database, they have the data. In Windows, Sybase does not hold the file-based devices open with the DENY_READ flag, so an attacker can copy the files or open them using some tool that requires only read access. Of course, the files are large, so transporting them away from a compromised host may pose problems. The general idea is that the attacker can transport the device files to a remote host under his control, load the database, and then manipulate the data remotely.
A number of mechanisms exist that allow interaction directly with the configuration of the Sybase service. Two of these mechanisms are described in this section.
Stored procedures in Sybase are batches of Transact SQL commands that can be called as a single unit, and passed parameters. A stored procedure can do anything you could normally do in a SQL batch. Extended stored procedures are functions normally written in C/C++ that reside in dynamically loadable libraries (DLLs or .so files), and allow Sybase to interact with the operating system more closely. For example, the built-in system extended stored procedure xp_cmdshell allows you to execute a command-line command and receive the result within a Transact SQL query, like this:
xp_cmdshell 'net user' xp_cmdshell ----------- User accounts for \SYBTEST ------------------------------------------------------------------------ ------- ASPNET Administrator Guest IUSR_SYBTEST IWAM_SYBTEST NetShowServices SQLDebugger TsInternetUser VUSR_SYBTEST The command completed successfully.
In Sybase, extended stored procedures are executed by the XP Server, a separate process that runs on the same machine as the database. The idea behind running extended stored procedures in a separate process is to provide both privilege separation and resilience — if an extended stored procedure contains a programming error that causes the process hosting it to crash, this does not affect the core database process.
An interesting feature of Sybase is the ability to quickly and easily start listeners on various TCP ports. For example:
sp_listener start, '192.168.1.1:80'
will start a listening instance of Sybase on TCP port 80 on the specified IP address (the IP address must be an IP address of the host that the procedure is executing on). The implications of this for firewalls should be obvious — suppose the firewall ruleset for a DMZ permits traffic to TCP port 80 on any host in the DMZ. The Sybase server is notionally secure because it has no service listening on port 80. If an attacker can execute sp_listener, he can cause the Sybase server to listen on port 80 and thereby open the Sybase server up to direct connections via the Internet.
Clearly there are a lot of “ifs” here. To execute sp_listener, an attacker must be a member of sa_role, which implies pre-existing trust, a sophisticated SQL injection attack, or a truly terrible configuration. Still, it is worth bearing in mind when locking down Sybase hosts that if users can become sa, they can start listeners on TCP ports of their choice.
18.117.189.157