Permissions

Mysql maintains access permissions on the basis of hosts, users, databases, and tables. For our case, we utilize the users and databases options. Specifically we specify read ("select") access to all star users from all machines. Then we can give specific users (or "group-accounts") write privilege to specific databases. We can do this via password protection, however, the Mysql C-API on which the STAR C++ API is built recognizes passwords only as an arguement to the connection c-function. This c-function is purposely hidden from the user codes. Now for other Mysql API's (e.g. perl & command line), the password will automatically be read from a ".rhosts" type file (.my.cnf) in the user's home directory. We will very likely have the C++ API independently parse this same file so that access with passwords have a common form. This is currently not implemented and passwords are not in place for processes that rely upon the C++ API for database access. Simply, one's user name is mapped to a privilege on a given database.

Command Line Interface

Mysql provides a command line client interface which is accessed via running "mysql" at the unix shell prompt. The mysql binary is installed via afs and made avialable in /opt/star/bin. It can be run with a number of command line arguments for purpose of identifying which server to connect with. Examples: connect to db1 server : mysql -h db1.star.bnl.gov connect to OnlTest4 server : mysql -h onlsun1.star.bnl.gov --port=3310 These command line arguments can also be put into a ".my.cnf" file located in the user's unix home directory. The format of the file (equivalent to the 2nd example above) is, [client] host=onlsun1.star.bnl.gov port=3310 With this file in one's home directory, the example becomes simply, connect to OnlTest4 server using .my.cnf file : mysql Once the connection is made, the user can "use" any databases of that server for which he/she has permission and perform any basic SQL queries of the tables in that database. For example, [sol] /etc > mysql -h www.star.bnl.gov Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2267 to server version: 3.22.21 Type 'help' for help. mysql> use RunParams Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables in RunParams | +---------------------+ | bytes | | dataIndex | | namedRef | | schema | | structure | | tcc_cluster_index | | tcl_tclpar | | tcl_tpc_index_type | | tdeparm | | tfc_fmtpar | | tfs_fsctrl | | tfs_fspar | | tpipar | | tpt_pars | | tpt_spars | | tss_tsspar | | tte_control | +---------------------+ 17 rows in set (0.00 sec) mysql> select * from dataIndex where beginTime>'1999-01-01 00:00:00'; +-------+--------+----------------+----------+---------------------+------- --+-----------+---------+--------+ | count | nameID | entryTime | schemaID | beginTime | version | elementID | numRows | dataID | +-------+--------+----------------+----------+---------------------+---------+-----------+---------+--------+ | 13 | 11 | 19991018230439 | 1 | 1999-06-15 00:00:00 | default | 0 | 1 | 2 | | 14 | 9 | 19991018230453 | 1 | 1999-06-15 00:00:00 | default | 0 | 1 | 3 | | 15 | 9 | 19991018230453 | 1 | 1999-06-15 00:00:00 | default | 1 | 1 | 4 | | 19 | 11 | 19991018230631 | 1 | 1999-10-01 00:00:00 | default | 0 | 1 | 3 | | 20 | 9 | 19991018230631 | 1 | 1999-10-01 00:00:00 | default | 0 | 1 | 5 | | 21 | 9 | 19991018230631 | 1 | 1999-10-01 00:00:00 | default | 1 | 1 | 6 | +-------+--------+----------------+----------+---------------------+---------+-----------+---------+--------+ 6 rows in set (0.01 sec) mysql> quit; Bye [sol] /etc >

Scripting Interface

There are several "scripting" interfaces available for Mysql. The most prominent in the perl interface. This interface is not Mysql-specific but has an Mysql driver implemented behind the interface. The interface is documented in some general Perl books (e.g. Perl Cookbook, Tom Christiansen & Nathan Torkington,O'Reilly, 1998) as well as in the Mysql Web documentation. The database administration for STAR is being done in Perl. Currently there is no documentation yet on the general set of Perl scripts used for this purpose, but this will be added as time and need allow. A very simplified scripting access avialabe via the command line interface and unix pipes. For example, the example query of the previous section could be done via, [sol] ~> echo "select * from dataIndex where beginTime>'1999-01-01 00:00:00'" | mysql -h www.star.bnl.gov --port=3306 -C RunParams > myresults [sol] ~> cat myresults count nameID entryTime schemaID beginTime version elementID numRows dataID 13 11 19991018230439 1 1999-06-15 00:00:00 default 0 1 2 14 9 19991018230453 1 1999-06-15 00:00:00 default 0 1 3 15 9 19991018230453 1 1999-06-15 00:00:00 default 1 1 4 19 11 19991018230631 1 1999-10-01 00:00:00 default 0 1 3 20 9 19991018230631 1 1999-10-01 00:00:00 default 0 1 5 21 9 19991018230631 1 1999-10-01 00:00:00 default 1 1 6