SQL Admins (SourceMod)

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

Example:

	"default"
	{
		"driver"			"mysql"
		"host"				"localhost"
		"database"			"sourcemod"
		"user"				"myaccount"
		"pass"				"mypassword"
	}

SQLite

Add a section to your server's configs/databases.cfg file as follows:

	"admins"
	{
		"driver"			"sqlite"
		"database"			"admins-sqlite"
	}

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 configs/sql-init-scripts/sqlite.

Database Setup

Automatic

Make sure sql-admin-manager.smx is loaded, and then run the sql_create_adm_tables command from your server console. If using MySQL, you must have CREATE permissions on your database.

Manual

MySQL

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

Find the script configs/sql-init-scripts/mysql/create_admins.sql in the SourceMod distribution. Then, using either the command line or a tool such as 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 configs/sql-init-scripts/sqlite/create_admins.sql script.

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

Enabling Plugins

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

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

1. admin-sql-prefetch.smx: 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.
   • PROS: Very simple plugin with no extra complexity.
   • CONS: Gameserver will stall whenever the cache is refreshed (mapload or when using sm_reloadadmins). If you add, remove, or edit admins, the changes are not reflected until the cache is refreshed.
2. admin-sql-threaded.smx: 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.
   • 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.
   • CONS: It is very complex and thus not trivial to edit for custom changes. It also may have unexpected functionality (for example, sm_reloadadmins will have a slight delayed reaction because the plugin is multi-threaded).

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

Updating Tables

Automatic

Make sure sql-admin-manager.smx is loaded, and run sm_update_adm_tables from your server console. If using MySQL, you must have CREATE and ALTER 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 update_admins-rXXXX scripts in your driver's folder, where XXXX is higher than the version from the second step, and XXXX 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 configs/sql-init-scripts/<driver>/update_admins-rXXXX, 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:

• update_admins-r2500.sql
• update_admins-r3243.sql
• update_admins-r3444.sql

In this case, you should run the scripts for 3243 and 3444, 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 sql-admin-manager.smx plugin. All of the commands require the root admin flag.

The following conventions are used:

• When <authtype> is requested, it means one of the following three values: steam, ip, or name.
• When <identity> is requested, it should be the unique string to be paired with the authtype. For example, a steam identity would be a Steam ID. An ip identity would be an IP address, and a name identity would be a Half-Life 2 player name.
• When [immunity] is requested, it means one of the following three values: none, default, or global.

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!

Command	Format	Description
sm_sql_addadmin	<alias> <authtype> <identity> <flags> [password]	Adds a new entry to the sm_admins table. The alias can be any value and is usually used to assign a readable name to an IP/SteamID.
sm_sql_deladmin	<authtype> <identity>	Removes an admin.
sm_sql_addgroup	<name> <flags> [immunity]	Adds a new group with the specified flags and immunity
sm_sql_delgroup	<name>	Removes the specified group. Quotation marks are optional if the name has odd characters.
sm_sql_setadmingroups	<authtype> <identity> [group1] ... [group N]	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: sm_sql_setadmingroups steam "STEAM_0:1:16" "Full Admins"
sm_create_adm_tables		Creates the administration tables for SourceMod. Can only be run from the server console.
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 admin-sql and admin-flatfile at the same time? A: Yes. The data will be merged together in SourceMod's cache.

Q: Can I use both the threaded and prefetch 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 configs/sql-init-scripts. 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 id, applications should enforce that authtype and identity have no combined duplicates.

Primary key: id (auto increments).

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

sm_groups

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

Primary key: id (auto increments).

Field	Type	Purpose
id	integer	Unique integer identifying the row.
flags	string NOT NULL	Permissions flag string.
name	string NOT NULL	Unique name of the group.
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: admin_id, group_id

Field	Type	Purpose
admin_id	integer	Reference to the sm_admins.id field. Specifies the admin inheriting the group.
group_id	integer	Reference to the sm_groups.id field. Specifies the group the admin is inheriting.
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: group_id, other_id

Field	Type	Purpose
group_id	integer	Reference to the sm_groups.id field. Specifies the group gaining immunity.
other_id	integer	Reference to the sm_groups.id field. Specifies who group_id is becoming immune from.

sm_group_overrides

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

Primary key: group_id, type, name

Field	Type	Purpose
group_id	integer	Reference to the sm_groups.id field. Specifies the group the override is for.
type	string NOT NULL Constrained to 'command' or 'group'.	Specifies whether the override is a command or a command group.
name	string NOT NULL	Command name.
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: type, name

Field	Type	Purpose
type	string NOT NULL Constrained to 'command' or 'group'.	Specifies whether the override is a command or a command group.
name	string NOT NULL	Command name.
flags	string NOT NULL	Permissions flag string.

sm_config

This table is used to specify configuration options.

Primary key: cfg_key.

Field	Type	Purpose
cfg_key	string NOT NULL	Configuration key.
cfg_value	string NOT NULL	Configuration value.

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