SQL Admins (SourceMod)

2008-09-13T08:41:03Z

HiJacker: /* Management Commands */

SourceMod has support for loading and managing admins via SQL, using any of the supported SQL drivers provided (currently, MySQL and SQLite). This can be very advantageous if you have a large number of admins, you wish to manage your admins via external tools, or you wish to share admins across servers.

This article will explain how to set up your server to use admins through SQL. No knowledge of SQL is required for basic administration; however, by using this feature you should realize that SourceMod does not provide easy functionality for full SQL control. You will need to use a third party tool or learn SQL yourself if you need greater functionality.

However, it is assumed that you:
*Have access to an SQL database;
*Know how to access the SQL database;
*Know how to execute commands or scripts on the SQL database.

=Configuration=
==MySQL==
Open <tt>configs/databases.cfg</tt> on your server. You should see a section called <tt>"default"</tt>. You can either use this section, or create a new one called <tt>"admins"</tt> if you wish to separate your connections. You must fill out the <tt>driver</tt>, <tt>host</tt>, <tt>database</tt>, <tt>user</tt>, and <tt>pass</tt> fields. If you do not need a field, use empty quotation marks as the value (<tt>""</tt>).

Example:
<pre>
"default"
{
"driver" "mysql"
"host" "localhost"
"database" "sourcemod"
"user" "myaccount"
"pass" "mypassword"
}
</pre>

==SQLite==
Add a section to your server's <tt>configs/databases.cfg</tt> file as follows:

<pre>
"admins"
{
"driver" "sqlite"
"database" "admins-sqlite"
}
</pre>

You may choose to name the database any valid file name. We chose "admins-sqlite" because a pre-made database, "admins-sqlite.sq3" is available in <tt>configs/sql-init-scripts/sqlite</tt>.

=Database Setup=
==Automatic==
Make sure <tt>sql-admin-manager.smx</tt> is loaded, and then run the <tt>sm_create_adm_tables</tt> command from your '''server console'''. If using MySQL, you must have <tt>CREATE</tt> permissions on your database.

==Manual==
===MySQL===
If you have not already created a database on your MySQL server, do so.

Find the script <tt>configs/sql-init-scripts/mysql/create_admins.sql</tt> in the SourceMod distribution. Then, using either the command line or a tool such as [http://www.phpmyadmin.net/ phpMyAdmin], run its contents. This will create the necessary tables.

===SQLite===
SourceMod distributes a pre-made SQLite database file with all of the admin tables created. If you wish to manually create a database or add tables to a pre-existing database, use the contents of the <tt>configs/sql-init-scripts/sqlite/create_admins.sql</tt> script.

Copy the <tt>configs/sql-init-scripts/sqlite/admins-sqlite.sq3</tt> binary from the SourceMod distribution into your server's <tt>data/sqlite</tt> folder. If you chose to rename the database in <tt>databases.cfg</tt>, you should rename this file as well.

=Enabling Plugins=
By default, all of the SQL plugins are located in the <tt>plugins/disabled</tt> folder on your server -- they are not loaded by default. To enable plugins, move them from the <tt>plugins/disabled</tt> folder and into <tt>plugins</tt>.

You will usually want to enable <tt>sql-admin-manager.smx</tt>. This plugin adds some helpful console commands for very basic admin management. However, you should only enable '''ONE''' of the other two SQL plugins:

<ol>
<li><tt>admin-sql-prefetch.smx</tt>: This plugin is ideal for small to medium sized databases without much connection lag. It loads the entire database admin contents at map load (or whenever a refresh is requested). If you have serious database delay or want to be able to add/delete/edit admins without refreshing the per-map cache on each server, this isn't the plugin for you.
<ul>
<li>PROS: Very simple plugin with no extra complexity.</li>
<li>CONS: Gameserver will stall whenever the cache is refreshed (mapload or when using <tt>sm_reloadadmins</tt>). If you add, remove, or edit admins, the changes are not reflected until the cache is refreshed.</li>
</ul>
</li>

<li><tt>admin-sql-threaded.smx</tt>: This plugin is completely threaded. This makes it much more complex, and thus it is currently "experimental" (although it has been tested to work fine). It pre-caches all group and override information. Admin-lookup is done completely dynamically.
<ul>
<li>PROS: A faulty/slow database connection will never halt the server. If you add/remove/edit admins, the changes will be reflected as soon as the player connects to the server.</li>
<li>CONS: It is very complex and thus not trivial to edit for custom changes. It also may have unexpected functionality (for example, <tt>sm_reloadadmins</tt> will have a slight delayed reaction because the plugin is multi-threaded).</li>
</ul>
</li>
</ol>

Once you have enabled the manager, and enabled ONE of the <tt>admin-sql</tt> plugins, you are ready to go!

=Updating Tables=
==Automatic==
Make sure <tt>sql-admin-manager.smx</tt> is loaded, and run <tt>sm_update_adm_tables</tt> from your server console. If using MySQL, you must have <tt>CREATE</tt> and <tt>ALTER</tt> permissions for the database.

==Manual==
If you are using an older version of the SourceMod table layouts, it is important to update the table layout for the latest schema.

In summary:
*Back up all of your data.
*Find what revision your tables were created against. Unfortunately the only way to do this right now is by remembering the version you used.
*Find all of the <tt>update_admins-rXXXX</tt> scripts in your driver's folder, where <tt>XXXX</tt> is higher than the version from the second step, and <tt>XXXX</tt> is lower than or equal to the current version you're using.
*Execute each matching script in order of increasing revision number (lowest first, highest last).
*Upgrade the SQL plugin you use, and any 3rd party software that relies on the admin tables.

Scripts for updating table structures are in <tt>configs/sql-init-scripts/<driver>/update_admins-rXXXX</tt>, where XXXX is the revision number.

A potential scenario is provided below (it is 100% fabricated; actual revision numbers are entirely different):

Your tables were created with SourceMod r2978. The current version is 3512. Say the following files might exist:
*<tt>update_admins-r2500.sql</tt>
*<tt>update_admins-r3243.sql</tt>
*<tt>update_admins-r3444.sql</tt>

In this case, you should run the scripts for <tt>3243</tt> and <tt>3444</tt>, in that order, to bring your table up to date. All data will be fully retained, but it is always recommended that you backup your data before proceeding with database changes.

=Management Commands=
As a convenience, SourceMod provides a few basic SQL admin management commands via the <tt>sql-admin-manager.smx</tt> plugin. All of the commands require the <tt>root</tt> admin flag.

The following conventions are used:
*When <authtype> is requested, it means one of the following three values: <tt>steam</tt>, <tt>ip</tt>, or <tt>name</tt>.
*When <identity> is requested, it should be the unique string to be paired with the <tt>authtype</tt>. For example, a <tt>steam</tt> identity would be a Steam ID. An <tt>ip</tt> identity would be an IP address, and a <tt>name</tt> identity would be a Half-Life 2 player name.

Note: if a string has spaces, it must be in quotes!

Note: A colon is a break character and Steam IDs must be in quotes!

:{|
|- class="t2th"
| Command
| Format
| Description
|- class="t2td"
| sm_sql_addadmin
| <nowiki><alias> <authtype> <identity> <flags> [immunity] [password]</nowiki>
| Adds a new entry to the <tt>sm_admins</tt> table. The <tt>alias</tt> can be any value and is usually used to assign a readable name to an IP/SteamID.
|- class="t2td"
| sm_sql_deladmin
| <nowiki><authtype> <identity></nowiki>
| Removes an admin.
|- class="t2td"
| sm_sql_addgroup
| <nowiki><name> <flags> <immunity></nowiki>
| Adds a new group with the specified flags and immunity
|- class="t2td"
| sm_sql_delgroup
| <nowiki><name></nowiki>
| Removes the specified group. Quotation marks are optional if the name has odd characters.
|- class="t2td"
| sm_sql_setadmingroups
| <nowiki><authtype> <identity> [group1] ... [group N]</nowiki>
| Sets an admin's group list. The inheritance order is the order the groups are specified in. This sets, and does not add; thus specifying no groups removes the user from all groups. Example:
<tt>sm_sql_setadmingroups steam "STEAM_0:1:16" "Full Admins"</tt>
|- class="t2td"
| sm_create_adm_tables
|
| Creates the administration tables for SourceMod. Can only be run from the server console.
|- class="t2td"
| sm_update_adm_tables
|
| Updates the administration tables to the latest schema, preserving data as applicable. Can only be run from the server console.
|}

=Frequently Asked Questions=
Q: Can I use <tt>admin-sql</tt> and <tt>admin-flatfile</tt> at the same time?
A: Yes. The data will be merged together in SourceMod's cache.

Q: Can I use both the <tt>threaded</tt> and <tt>prefetch</tt> SQL plugins at the same time?
A: No.

Q: Can duplicate groups/admins be in the flat files and the SQL database?
A: Yup. They will be merged safely.

=Schemas=
This section documents the required portions of the admin table schema. You do not need to read or learn this unless you plan to use SQL directly.

The exact schemas for each driver are located in <tt>configs/sql-init-scripts</tt>. The purpose of this document is to explain the fields rather than list the exact structures.

==sm_admins==
This table is used to store administrators. Although the primary key is <tt>id</tt>, applications should enforce that <tt>authtype</tt> and <tt>identity</tt> have no combined duplicates.

Primary key: <tt>id</tt> (auto increments).

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| id
| integer
| Unique integer identifying the row.
|- class="t2td"
| authtype
| string NOT NULL
Constrained to 'steam', 'name', or 'ip'
| Authentication type the identity is against.
|- class="t2td"
| identity
| string NOT NULL
| Steam ID, name, or IP address.
|- class="t2td"
| password
| string
| Password, if any, the admin must use.
|- class="t2td"
| flags
| string NOT NULL
| Permission flag string.
|- class="t2td"
| name
| string NOT NULL
| Alias used for external tools (all but ignored in SourceMod).
|- class="t2td"
| immunity
| integer NOT NULL
| Immunity level value.
|}

==sm_groups==
This table is used to store all groups entries. Although the primary key is <tt>id</tt>, applications should enforce that the <tt>name</tt> field stays unique.

Primary key: <tt>id</tt> (auto increments).

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| id
| integer
| Unique integer identifying the row.
|- class="t2td"
| flags
| string NOT NULL
| Permissions flag string.
|- class="t2td"
| name
| string NOT NULL
| Unique name of the group.
|- class="t2td"
| immunity_level
| integer NOT NULL
| Immunity level value.
|}

==sm_admins_groups==
This table is used to map admins to the groups they will inherit.

Primary key: <tt>admin_id</tt>, <tt>group_id</tt>

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| admin_id
| integer
| Reference to the <tt>sm_admins.id</tt> field. Specifies the admin inheriting the group.
|- class="t2td"
| group_id
| integer
| Reference to the <tt>sm_groups.id</tt> field. Specifies the group the admin is inheriting.
|- class="t2td"
| inherit_order
| integer NOT NULL
| Order of inheritance for the given admin. Lower means earlier inheritance.
|}

==sm_group_immunity==
This table is used to map which groups are immune from other groups.

Primary key: <tt>group_id</tt>, <tt>other_id</tt>

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| group_id
| integer
| Reference to the <tt>sm_groups.id</tt> field. Specifies the group gaining immunity.
|- class="t2td"
| other_id
| integer
| Reference to the <tt>sm_groups.id</tt> field. Specifies who <tt>group_id</tt> is becoming immune from.
|}

==sm_group_overrides==
This table is used to specify group-based command overrides.

Primary key: <tt>group_id</tt>, <tt>type</tt>, <tt>name</tt>

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| group_id
| integer
| Reference to the <tt>sm_groups.id</tt> field. Specifies the group the override is for.
|- class="t2td"
| type
| string NOT NULL
Constrained to 'command' or 'group'.
| Specifies whether the override is a command or a command group.
|- class="t2td"
| name
| string NOT NULL
| Command name.
|- class="t2td"
| access
| string NOT NULL
Constrained to 'allow' or 'deny'.
| Whether the command is allowed or denied to this group.
|}

==sm_overrides==
This table is used to specify global command overrides.

Primary key: <tt>type</tt>, <tt>name</tt>

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| type
| string NOT NULL
Constrained to 'command' or 'group'.
| Specifies whether the override is a command or a command group.
|- class="t2td"
| name
| string NOT NULL
| Command name.
|- class="t2td"
| flags
| string NOT NULL
| Permissions flag string.
|}

==sm_config==
This table is used to specify configuration options.

Primary key: <tt>cfg_key</tt>.

:{|
|- class="t2th"
| Field
| Type
| Purpose
|- class="t2td"
| cfg_key
| string NOT NULL
| Configuration key.
|- class="t2td"
| cfg_value
| string NOT NULL
| Configuration value.
|}

''Note:'' As of this writing, the only configuration value is <tt>admin_version</tt>, and it specifies the revision the schema last changed.

[[Category:SourceMod Documentation]]

AlliedModders Wiki - User contributions [en]

SQL Admins (SourceMod)