Before MySQL 9.2.0 only users with the SUPER privilege were allowed to create or modify spatial reference systems (SRSs). That was inconvenient, especially for HeatWave MySQL where typically users do not have the SUPER privilege. MySQL 9.2.0 changes that by introducing a new dynamic privilege: CREATE_SPATIAL_REFERENCE_SYSTEM. This privilege enables users to create, replace, and drop custom SRSs, allowing for greater spatial data customization across MySQL environments without needing SUPER access–a welcomed change for HeatWave MySQL users without the SUPER privilege. Spatial reference systems are fundamental when working with spatial data since they define how data are interpreted as described in a previous blog post.

In this post, we’ll explain the use of this privilege and provide an example to demonstrate its application. The new privilege is available in MySQL Community and Enterprise Editions and HeatWave MySQL.

Why This Privilege Matters

With the new CREATE_SPATIAL_REFERENCE_SYSTEM privilege, MySQL users can:

  1. Define a custom SRS suited to their data needs without requiring SUPER privileges.
  2. Easily modify an existing SRS to adapt to evolving project requirements.
  3. Seamlessly manage spatial data even in HeatWave MySQL, where SUPER privileges are typically restricted.

Using the CREATE_SPATIAL_REFERENCE_SYSTEM Privilege

Let’s look how to use this privilege to create a new SRS.

Step 1 – Granting the privilege: First, an administrator grants the CREATE_SPATIAL_REFERENCE_SYSTEM privilege to a user. As an example, we’ll grant it to a user named Bob on localhost:

GRANT CREATE_SPATIAL_REFERENCE_SYSTEM ON *.* TO 'Bob'@'localhost';

This command gives Bob the permission to create and manage a custom SRS in the database without requiring SUPER privileges.

Step 2 – Creating a custom SRS: Now that Bob has the necessary privilege, he can create a new SRS. For instance, he wants to define an SRS based on the GRS 1980 spheroid with Transverse Mercator projection and SRID equals to 33000. The following command demonstrates how he’d do this:

CREATE SPATIAL REFERENCE SYSTEM 33000
NAME 'MAGNA-SIRGAS 2018'
ORGANIZATION 'EPSG'
IDENTIFIED BY 33000
DEFINITION 'PROJCS["MAGNA-SIRGAS 2018 / Origen-Nacional",GEOGCS["MAGNA-SIRGAS 2018",DATUM["Marco_Geocentrico_Nacional_de_Referencia_2018",SPHEROID["GRS 1980",6378137,298.257222101],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","20046"]],PROJECTION["Transverse_Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["latitude_of_origin",4],PARAMETER["central_meridian",-73],PARAMETER["scale_factor",0.9992],PARAMETER["false_easting",5000000],PARAMETER["false_northing",2000000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","33000"]]';

This command creates a new spatial reference system named “MAGNA-SIRGAS 2018” with SRID 33000, defining a coordinate system suitable for certain regions in Colombia based on the EPSG standard. He can now query the newly created SRS.

SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS where SRS_ID=33000;

By having the CREATE_SPATIAL_REFERENCE_SYSTEM privilege the user can also replace and drop an SRS. For more details on creating a custom SRS we refer to a previous blog post.

Error Handling

If a user without the necessary privilege attempts to create or modify an SRS, MySQL will return an error message indicating that SUPER or CREATE_SPATIAL_REFERENCE_SYSTEM privilege is required:

ERROR HY000: Command requires either SUPER or CREATE_SPATIAL_REFERENCE_SYSTEM privilege.

Note that the use of CREATE_SPATIAL_REFERENCE_SYSTEM privilege is intended to supersede the use of SUPER for this purpose.

Summary

The new CREATE_SPATIAL_REFERENCE_SYSTEM privilege in MySQL expands the flexibility of spatial data management by removing the SUPER privilege dependency. With this enhancement, users can create, replace, and drop custom SRSs across MySQL environments, making it especially useful for HeatWave MySQL, where users don’t have the SUPER privilege. By enabling finer control over spatial data configurations, MySQL offers improved support for applications that rely on accurate geographic and projected coordinate systems, enhancing precision and ease of use in spatial data handling. The new privilege is available in MySQL Community and Enterprise Editions and HeatWave MySQL.