1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
|
<html>
<head>
<title>SQL Relay - Getting Started With mSQL</title>
<link rel="stylesheet" href="../css/styles.css">
</head>
<body>
<span class="heading1">Getting Started With mSQL</span><br>
<ul>
<li><a href="#installation">Installation</a></li>
<ul>
<li><a href="#openserver">SCO OpenServer</a></li>
<li><a href="#configuring">Configuring mSQL</a></li>
<li><a href="#startingatboot">Starting the Database at Boot Time</a></li>
</ul>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
</ul>
<a name="installation"></a>
<span class="heading1">Installation</span><br>
<p>I've sucessfully installed mSQL on Linux, FreeBSD, NetBSD, OpenBSD, SCO
OpenServer and Solaris. I've had to compile it from source on all platforms
except SCO OpenServer. mSQL is available from the
<a href="http://www.hughes.com.au">mSQL home page</a>. It compiles on all
platforms using gcc prior to version 3.3 and yacc or GNU bison prior to version
1.50. If your platform comes with newer versions of gcc or bison, then you may
have to download and compile older versions and use them to compile msql.</p>
<p>By default, mSQL installs itself in /usr/local/Hughes. Note that you
should add /usr/local/Hughes/bin to your PATH environment variable.</p>
<p>When compiling from source, you should create a user named msql, assign it a
password, then compile and install mSQL as that user.</p>
<a name="openserver"></a>
<span class="heading2">SCO OpenServer</span><br>
<p>For SCO OpenServer, mSQL packages are available on the Skunkware CD or from
the SCO OpenServer support section of
<a href="http://www.caldera.com">the Caldera website</a>. SCO OpenServer
packages are often called VOL's because they come as a set of files named
VOL.000.000, VOL.000.001, etc. These VOLS can be installed using the Software
Manager (custom). On SCO OpenServer, when the package is installed, it creates
a msql user and sets proper permissions for everything.</p>
<a name="configuring"></a>
<span class="heading2">Configuring mSQL</span><br>
<p>Once mSQL is installed, you should examine it's configuration file
/usr/local/Hughes/msql.conf. Make sure that the mSQL_User parameter is set to
msql and the Admin_User is set to root. Also make sure that the directory
identified by the Inst_Dir paremater and /usr/local/Hughes/msqldb are writable
by msql. This should be the case if the msql user installed the software or
if it was installed from a package.</p>
<p>By default, mSQL is configured to allow access from clients on the local
machine only. To allow access to remote clients, set the Remote_Access
parameter to True. You should use caution when doing this though, mSQL is
<a href="http://www.insecure.org/sploits/msql.authentication.holes.html">exceptionally insecure</a> when remote access is enabled.</p>
<a name="startingatboot"></a>
<span class="heading2">Starting the Database at Boot Time</span><br>
<p>The package distribution of mSQL installs a script which starts the
database at boot time. If you compiled from source, you'll need to install a
script like the following to start the database at boot time.</p>
<blockquote>
<PRE>
<FONT color=#0000ff>#!/bin/sh</FONT>
<B><FONT color=#a62828>case</FONT></B> "$1" <B><FONT color=#a62828>in</FONT></B>
start<B><FONT color=#a62828>)</FONT></B>
su <FONT color=#6959cf>-</FONT> msql -c <B><FONT color=#a62828>"</FONT></B><FONT color=#ff00ff>/usr/local/Hughes/bin/msql2d > /dev/null 2> /dev/null &</FONT><B><FONT color=#a62828>"</FONT></B>
<B><FONT color=#a62828>;;</FONT></B>
stop<B><FONT color=#a62828>)</FONT></B>
<B><FONT color=#a62828>kill</FONT></B> <FONT color=#6959cf>`ps </FONT><FONT color=#6959cf>-efa</FONT><FONT color=#6959cf> </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> </FONT><B><FONT color=#a62828>grep</FONT></B><FONT color=#6959cf> msql2d </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> </FONT><B><FONT color=#a62828>grep</FONT></B><FONT color=#6959cf> </FONT><FONT color=#6959cf>-v</FONT><FONT color=#6959cf> </FONT><B><FONT color=#a62828>grep</FONT></B><FONT color=#6959cf> </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> awk </FONT><B><FONT color=#a62828>'</FONT></B><FONT color=#ff00ff>{print $2}</FONT><B><FONT color=#a62828>'</FONT></B><FONT color=#6959cf>`</FONT>
<B><FONT color=#a62828>;;</FONT></B>
*<B><FONT color=#a62828>)</FONT></B>
<B><FONT color=#a62828>echo</FONT></B><FONT color=#ff00ff> $</FONT><B><FONT color=#a62828>"</FONT></B><FONT color=#ff00ff>Usage: </FONT><FONT color=#a620f7>$0</FONT><FONT color=#ff00ff> {start|stop}</FONT><B><FONT color=#a62828>"</FONT></B>
<B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>1</FONT>
<B><FONT color=#a62828>esac</FONT></B>
<B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>0</FONT>
</PRE>
</blockquote>
<p>Install this script and run it with the "start" option to start up the
database. Running it with the "stop" option shuts the database down. To
access a database, it must be running.</p>
<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>
<p>After installation, mSQL is ready to use but to do any useful work, you'll
have to create a database.</p>
<p>The following command creates a database named <i>testdb</i>. This command
must be run as the Admin_User as defined in msql.conf, usually root.</p>
<blockquote>
<b>msqladmin create testdb</b>
</blockquote>
<p>You can log into your new database as follows.</p>
<blockquote>
<b>msql testdb</b>
</blockquote>
<p>Once you're logged in, you can create, drop and modify tables and run
queries in your database.</p>
<p>By default, mSQL is quite insecure. It allows any user to perform any
operation they like. When remote access is enabled, mSQL is
<a href="http://www.insecure.org/sploits/msql.authentication.holes.html">exceptionally insecure</a>. To secure an mSQL database, create an Access Control List
file called /usr/local/Hughes/msql.acl with the following contents.</p>
<blockquote>
database=testdb<br>
read=*<br>
write=testuser<br>
access=local<br>
host=*<br>
</blockquote>
<p>This example defines permissions for the <i>testdb</i> database. It allows
any user to read the database but only the <i>testuser</i> unix user to write
to it. Also, access is restricted to clients running on the same machine as
the database. Each block of parameters beginning with "database=" controls
access to that database, so access control can be defined for multiple
databases from a single /usr/local/Hughes/msql.acl file.</p>
<p>If you want to drop the database, you can do so with the following
command. This command must be run as the Admin_User as defined in msql.conf,
usually root.</p>
<blockquote>
<b>msqladmin drop testdb</b>
</blockquote>
<p>This should be enough to get you started. To set up more complex
configurations, consult the mSQL
<a href="http://www.hughes.com.au/library/msql/manual_20/">online
documentation</a>.</p>
<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>
<p>Accessing a mSQL database using the msql client tool is simple. For
example, to access a database called <i>testdb</i> on the local machine,
use the following command.</p>
<blockquote>
<b>msql testdb</b>
</blockquote>
<p>If you want to access a database on a remote machine, say on
<i>testhost</i>, use the -h option as follows.</p>
<blockquote>
<b>msql -htesthost testdb</b>
</blockquote>
<p>Note that in both cases, you'll have to be logged in as a user that is
allowed to access the database.</p>
<p>Once you're connected to the database, the msql client prompts you to
enter a query. Queries may be split across multiple lines. To run a query,
enter a \g on a line by itself. To exit, type \q.</p>
<p>A sample msql session follows.</p>
<blockquote>
<PRE>
[testuser@localhost testuser]$ msql testdb
Welcome to the miniSQL monitor. Type \h for help.
mSQL > <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
-> col1 <B><FONT color=#288a51>char</FONT></B>(<FONT color=#ff00ff>40</FONT>),
-> col2 <B><FONT color=#288a51>integer</FONT></B>
-> )
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>)
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>)
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>)
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
-> \g
Query OK. <FONT color=#ff00ff>3</FONT> row(s) modified or retrieved.
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| col1 | col2 |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| hello | <FONT color=#ff00ff>50</FONT> |
| hi | <FONT color=#ff00ff>60</FONT> |
| bye | <FONT color=#ff00ff>70</FONT> |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
mSQL > <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
-> \g
Query OK. <FONT color=#ff00ff>3</FONT> row(s) modified or retrieved.
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| col1 | col2 |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| hello | <FONT color=#ff00ff>50</FONT> |
| hi | <FONT color=#ff00ff>0</FONT> |
| bye | <FONT color=#ff00ff>70</FONT> |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
mSQL > <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable
-> \g
Query OK. <FONT color=#ff00ff>2</FONT> row(s) modified or retrieved.
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| col1 | col2 |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
| hi | <FONT color=#ff00ff>0</FONT> |
| bye | <FONT color=#ff00ff>70</FONT> |
+<FONT color=#0000ff>------------------------------------------+----------+</FONT>
mSQL > <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable
-> \g
Query OK. <FONT color=#ff00ff>1</FONT> row(s) modified or retrieved.
mSQL > \q
Bye!
</PRE>
</blockquote>
<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>
<p>Accessing mSQL from SQL Relay requires an instance entry in your
<i>sqlrelay.conf</i> file for the database that you want
to access. Here is an example <i>sqlrelay.conf</i> which defines an SQL Relay
instance called msqltest. This instance connects to the <i>testdb</i>
database on the local machine. Note how the runasuser and runasgroup attributes
are set to <i>testuser</i>, a user which is allowed to access <i>testdb</i>.</p>
<PRE>
<FONT color=#0000ff><?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>"1.0"</FONT><FONT color=#0000ff>?></FONT>
<FONT color=#008a8e><!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>"sqlrelay.dtd"</FONT><FONT color=#008a8e>></FONT>
<FONT color=#008a8e><instances></FONT>
<FONT color=#008a8e><instance id=</FONT><FONT color=#ff00ff>"msqltest"</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>"9000"</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>"/tmp/msqltest.socket"</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>"msql"</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>"3"</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>"5"</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>"0"</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>"1"</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>"60"</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>"commit"</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>"600"</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>"testuser"</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>"testuser"</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>"5"</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>"listener"</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>"pass"</FONT><FONT color=#008a8e>></FONT>
<FONT color=#008a8e><users></FONT>
<FONT color=#008a8e><user user=</FONT><FONT color=#ff00ff>"msqltest"</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>"msqltest"</FONT><FONT color=#008a8e>/></FONT>
<FONT color=#008a8e></users></FONT>
<FONT color=#008a8e><connections></FONT>
<FONT color=#008a8e><connection connectionid=</FONT><FONT color=#ff00ff>"msqltest"</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>"db=testdb"</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>"1"</FONT><FONT color=#008a8e>/></FONT>
<FONT color=#008a8e></connections></FONT>
<FONT color=#008a8e></instance></FONT>
<FONT color=#008a8e></instances></FONT>
</PRE>
<p>If you want to connect to a database on a remote machine, say on
<i>testhost</i>, you would need to add <i>host=testhost;</i> to the string
attribute of the connection tag.</p>
<p>Now you can start up this instance with the following command.</p>
<blockquote>
<b>sqlr-start -id msqltest</b>
</blockquote>
<p>To connect to the instance and run queries, use the following command.</p>
<blockquote>
<b>sqlrsh -id msqltest</b>
</blockquote>
<p>The following command shuts down the SQL Relay instance.</p>
<blockquote>
<b>sqlr-stop msqltest</b>
</blockquote>
</body>
</html>
|
