Tiny Deathstars of Foulness

By default, MySQL comes with a root user configured. You can also create additional users, change passwords for users, and assign what databases and tables they have access to. From MySQL, you can can create a basic user using the CREATE USER statement, providing a user, a location, and then using IDENTIFIED BY followed by a password. In production, this would look similar to the following, using krypted as the user and mysecretpassword as the password:

CREATE USER 'krypted'@'localhost' IDENTIFIED BY 'mysecretpassword';

Once you’ve created a user, you’ll want to assign what the user can access. Here, the * wildcard is pretty handy. In the following command, we’ll use the GRANT statement along with ALL PRIVILEGES to give this new krypted user access to all of the databases running on MySQL:

GRANT ALL PRIVILEGES ON * . * TO 'krypted'@'localhost';

Pretty easy so far. Just flush the permissions with the FLUSH PRIVILEGES statement and krypted’s now all good to access anything that exists in MySQL when the command was run.


Once you’ve flushed, you can see what a user can access using the SHOW GRANTS statement:

SHOW GRANTS FOR 'krypted'@'localhost';

If you create new databases, do so again. To login as the user, you can then just run mysql followed by the -u option to define the user:

mysql -u krypted -p

To remove permissions, use the REVOKE statement. Let’s remove the ALL PRIVILEGES from krypted:

REVOKE ALL PRIVILEGES ON *.* FROM 'krypted'@'localhost';

To delete a user, use the DROP statement, and then USER, followed by who we’re deleting:

DROP USER ‘krypted’@‘localhost’;

If you were then going to create the user and provide a different level of privileges you could replace ALL PRIVILEGES with one of the following:

  • ALL PRIVILEGES: Gives the user full access to a database (or all included with a wildcard)
  • CREATE: Gives a user the ability to create new databases or tables within a database the access is provided
  • DROP: Gives a user the ability to use the DROP statement to remove tables or databases
  • DELETE: Gives a user the ability to delete rows from tables
  • GRANT OPTION: Gives a user the ability to add and remove privileges for other users
  • INSERT: Gives a user the ability to create rows into tables using the INSERT statement
  • SELECT: Gives a user the ability to use SELECT statements (similar to read in POSIX)
  • UPDATE: Gives a user the ability to update table rows only

We can also string some of this together in one statement, such as if we wanted the krypted password to expire in 60 days:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 60 DAY;

A full list of options would include the following syntax, with options including maximum queries, max connections, ssl, auto-lock, etc:

user_specification [, user_specification] ...
[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...

user [ auth_option ]

auth_option: {
IDENTIFIED BY ‘auth_string’
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY ‘auth_string’
| IDENTIFIED WITH auth_plugin AS ‘hash_string’

tsl_option: {
| X509
| CIPHER ‘cipher’
| ISSUER ‘issuer’
| SUBJECT ‘subject’

resource_option: {

password_option: {

lock_option: {

February 20th, 2016

Posted In: SQL

Tags: , , , , , , , ,

These days, new services get introduced in OS X Server during point releases. OS X now has a Software Caching server built to make updates faster. This doesn’t replace Apple’s Software Update Server mind you, it supplements. And, it’s very cool technology. “What makes it so cool” you might ask, given that Software Update Server has been around for awhile. Namely, the way that clients perform software update service location and distribution with absolutely no need (or ability) for centralized administration.

Let’s say that you have 200 users with Mac Minis and an update is released. That’s 200 of the same update those devices are going to download over your Internet connection, at up to 2 to 3 gigs per download. If you’re lucky enough to have eaten at the Varsity in Atlanta, just imagine trying to drink one of those dreamy orange goodnesses through a coffee stirrer. Probably gonna’ be a little frustrating. Suck and suck and suck and it’ll probably melt enough to make it through that straw before you can pull it through. For that matter, according to how fast your Internet pipe is, there’s a chance something smaller, like an update to Expensify will blow out that same network, leaving no room for important things, like updates to Angry Birds!

Now, let’s say you have an OS X Server running the new Caching service. In this case, the first device pulls the update down and each subsequent device uses the WAN address to determine where the nearest caching service is. If there’s one on the same subnet, provided the subnet isn’t a Class B or higher, then the client will attempt to establish a connection to the caching service. If it can and the update being requested is on that server then the client will pull the update from the server once the signature of the update is verified with Apple (after all, we wouldn’t want some funky cert getting in the way of our sucking). If the download is stopped it will resume after following the same process on a different server, or directly from Apple. The client-side configuration is automatic so provides a seamless experience to end users.

Pretty cool, eh? But you’re probably thinking this new awesomeness is hard as all heck to install. Well, notsomuch. There are a few options that can be configured, but the server is smart enough to do most of the work for you. Before you get started, you should:

  • Be running Mountain Lion with Server 2.2 or better.
  • Install an APNS certificate first, described in a previous article I wrote here.
  • Have an ethernet connection on the server.
  • Have a hard drive with at least 50GB free in the server.
  • The server must be in a Class C or smaller LAN IP scheme (no WAN IPs can be used with this service, although I was able to multihome with the WAN off while configuring the service)

Once all of the requirements have been met, you will need to install the actual Caching Service. To do so, open from the /Applications directory and connect to the server with which you would like to install the Caching service.

Click on Caching from the SERVICES section of the Server sidebar. Here, you have 3 options you can configure before starting the service. The first is which volume with which to place updates. This should typically be a Pegasus or other form of mass storage that is not your boot volume. Use the Edit… button to configure which volume will be used. By default, when you select that volume you’ll be storing the updates in the Library/Server/Caching/Data of that volume.

The next button is used to clear out the cache currently used on the server. Click Reset and the entire contents of the aforementioned Data directory will be cleared.

Next, configure the Cache Size. Here, you have a slider to configure about as much space as you’d like, up to “Unlimited”. You can also use the command line to do some otherwise unavailable numbers, such as 2TB.

Once you’ve configured the correct amount of space, click on the ON button to fire up the service. Once started, grab a client from the local environment and download an update. Then do another. Time both. Check the Data folder, see that there’s stuff in there and enjoy yourself for such a job well done.

Now, let’s look at the command line management available for this service. Using the serveradmin command you can summon the settings for the caching service, as follows:

sudo serveradmin settings caching

The settings available include the following results:

caching:ReservedVolumeSpace = 25000000000
caching:SingleMachineMode = no
caching:Port = 0
caching:SavedCacheSize = 0
caching:CacheLimit = 0
caching:DataPath = "/Volumes/Base_Image/Library/Server/Caching/Data"
caching:ServerGUID = "FB78960D-F708-43C4-A1F1-3E068368655D"
caching:ServerRoot = "/Library/Server"

Don’t change the caching:ServerRoot setting on the server. This is derived from the root of the global ServerRoot. Also, the ServerGUID setting is configured automatically when connecting to Apple and so should not be set manually. When you configured that Volume setting, you set the caching:DataPath option. You can make this some place completely off, like:

sudo serveradmin settings caching:DataPath = "/Library/Server/NewCaching/NewData"

Now let’s say you wanted to set the maximum size of the cache to 800 gigs:

sudo serveradmin settings caching:CacheLimit = 812851086070

To customize the port used:

sudo serveradmin settings caching:Port = 6900

The server reserves a certain amount of filesystem space for the caching service. This is the only service I’ve seen do this. By default, it’s about 25 gigs of space. To customize that to let’s say, ‘around’ 50 gigs:

sudo serveradmin settings caching:ReservedVolumeSpace = 50000000000

To stop the service once you’ve changed some settings:

sudo serveradmin stop caching

To start it back up:

sudo serveradmin start caching

Once you’ve started the Caching service in OS X Server and familiarized yourself with the serveradmin caching options, let’s look at the status options. I always use fullstatus:

sudo serveradmin fullstatus caching

Returns the following:

caching:Active = yes
caching:state = "RUNNING"
caching:Port = 57466
caching:CacheUsed = 24083596
caching:TotalBytesRequested = 24083596
caching:CacheLimit = 0
caching:RegistrationStatus = 1
caching:CacheFree = 360581072384
caching:StartupStatus = "OK"
caching:CacheStatus = "OK"
caching:TotalBytesReturned = 24083596
caching:CacheDetails:.pkg = 24083596

The important things here:

  • An Active setting of “yes” means the server’s started.
  • The state is “STARTED” or “STOPPED” (or STARTING if it’s in the middle).
  • The TCP/IP port used 57466 by default. If the caching:Port setting earlier is set to 0 this is the port used by default.
  • The CacheUsed is how much space of the total CacheLimit has been used.
  • The RegistrationStatus indicates whether the server is registered via APNS for the service with Apple.
  • The CacheFree setting indicates how much space on the drive can be used for updates.
  • The caching:TotalBytesRequested option should indicate how much data has been requested from clients while the caching:TotalBytesReturned indicates how much data has been returned to clients.

Look into the /Library/Server/Caching/Config/Config.plist file to see even more information, such as the following:


There are also a number of other keys that can be added to the Config.plist file including CacheLimit, DataPath, Interface, ListenRanges, LogLevel, MaxConcurrentClients, Port and ReservedVolumeSpace. These are described further at

As you can see, this provides the host name of the server and path on that server that the Caching server requires access to, the last port connected to and the last date that the contents were flushed.

In the Data directory that we mentioned earlier is a SQLite database, called AssetInfo.db. In this database, a number of files are mentioned. These are in a file hierarchy also in that Data directory. Client systems access data directly from that folder.

Finally, the Server app contains a log that is accessed using the Logs option in the Server app sidebar. If you have problems with the service, information can be accessed here (use the Caching Service Log to access Caching logs).

The Caching Service uses the AssetCache service, located at


then starts as the new user _assetcache user. It’s LaunchDaemon is at


Note: In my initial testing it appeared that after rebooting devices, that iOS updates were being cached; however, several have reported that this is not yet possible. I’ll try and replicate and report my findings later.

December 17th, 2012

Posted In: iPhone, Mac OS X, Mac OS X Server, Mac Security, Mass Deployment, Network Infrastructure

Tags: , , , , , , , , , , , , , , ,