User And Permissions Management In MySQL

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. FLUSH PRIVILEGES; 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: CREATE USER [IF NOT EXISTS] user_specification [, user_specification] ... [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... user_specification: user [ auth_option ] auth_option: { IDENTIFIED BY ‘auth_string’ | IDENTIFIED BY PASSWORD ‘hash_string’ | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY ‘auth_string’ | IDENTIFIED WITH auth_plugin AS ‘hash_string’ } tsl_option: { SSL | X509 | CIPHER ‘cipher’ | ISSUER ‘issuer’ | SUBJECT ‘subject’ } resource_option: { MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count } password_option: { PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY } lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }

Troubleshoot Spotlight Indexing Issues Using mddiagnose

Spotlight just kinda’ works. Except when it doesn’t. Which is luckily pretty rare, for the use cases that Spotlight was designed for. But when it doesn’t work, you have a few tools that I’ve highlighted over the years to help you out, including articles on shared volumes, manually indexing, disabling Spotlight, and a few others. But what if you need to go in more depth to isolate an issue? For this, Apple has provided us with a tool called mddiagnose, in /usr/bin. In the following command, we’ll run an mddiagnose to dump a bunch of system statistics that we can then look at. Here, we’ll do that to a folder called test in our current working directory: /usr/bin/mddiagnose -f test The output is then test.mdsdiagnostic, a directory with a CrashReporter, spindump, Samples, DiagnosticReports, a few system.log exports, and a diagnostic.log. You can then view your log using the more command (or cat or less or whatevers) more ~/test.mddiagnostic/diagnostic.log Here, you’ll see the output of a bunch of scripts that were run. I find that this is the most informational aspect of what I get from the mddiagnose output. Every time I’ve actually fixed an issue here, it’s been with this output. The other aspect of mddiagnose that I’ve found useful is checking permissions and paths. Here, you can answer the simple question of whether mdutil has permissions to check a path. We’ll do so using the -p option: mddiagnose -p /Library/Application\ Support/Appifitizer Enjoy! Screen Shot 2015-12-09 at 11.11.01 AM

Setting Up Wikis In OS X Server 5

A wiki is a repository of dynamically created and managed content, or content created or edited by multiple users collaboratively. This article is about using the wiki service in OS X Server (the Apple Server app running on 10.10 and 10.11). I reference file services with WebDAV because it is a very nice integration piece that I think a lot of people will find pretty beneficial. To get started with the Wiki service, first turn it on. This one isn’t heavily dependent on host names (other than being able to access the server from a browser) or directory services (other than being able to authenticate users, but local accounts are perfectly functional) and it doesn’t require the Websites service to be running as well. One should always have good working directory services and host names, still… To enable the service, open the Server app and click on Wiki in the list of SERVICES in the List Pane. Screen Shot 2015-09-25 at 9.57.06 PM There are two configuration options. The first is to select who is able to create wikis. Use the “Wikis can be created by” drop-down list to select “all users” if anyone with an account on the server should be able to create a wiki or “only some users” to bring up the Wiki Creators screen. Screen Shot 2015-09-25 at 9.57.51 PM If only some users can create new wikis, use the plus sign (“+”) at the Wiki Creators screen to add users and/or groups to the list of users that can create wikis. Click on OK when all users and groups that can create wikis are added. In a school I would imagine that only teachers or IT staff would be able to create wikis. Once a wiki is created, pages inside the wiki can still be created by non-wiki creators. The other option available is the handy dandy WebDAV interface to the wikis. When you enable this option, you can connect to a server from OS X or iOS via WebDAV and access files in each wikis document repository. To be clear, this option doesn’t provide access to the user documents, but does provide access to the wiki documents. We’re going to check the box for “Enable WebDAV access to Wiki files” and then click the ON button. Once the service starts, click on the View Wiki link in the Wiki workspace in Server app. Screen Shot 2015-09-25 at 10.00.51 PM Here, click on the Log in button and enter a user with access to the server, preferably one who can create wikis. Screen Shot 2015-09-25 at 10.01.43 PM At the Wikis page, you will then see a list of all wikis you have access to. Note that the previous screen showed one wiki and now we see two. That’s because one of the wikis has permissions that allow “All unauthenticated users” access to the wiki, which we’ll describe shortly. The first thing most administrators will do is create a wiki. To do so, click on the plus sign (“+”) icon on the web page and at the resultant screen, click on New Wiki. Screen Shot 2015-09-25 at 10.02.35 PM At the “Create a new wiki” prompt, provide a name for the wiki and a brief description for it. Screen Shot 2015-09-25 at 10.03.12 PM Click on Continue. Screen Shot 2015-09-25 at 10.03.53 PM At the Set permissions screen, enter each user or group to provide access to edit and view wiki pages. Here, you’ll have the options for Read & Write (users can view and edit pages in the wiki), Read only (users can only view the contents of your pages) and No access (users have no access to the wiki). There is a group for All logged in users, which includes every user with access to the server and another for All unauthorized users, which includes guests to the server. Once you’ve given the appropriate permissions, click on Continue. Note: You don’t have to get this perfect now as you can always edit these later. Screen Shot 2015-09-25 at 10.04.23 PM At the Set Appearance screen, you can choose an icon for the wiki (shown in the wiki list and when you open the wiki) as well as a color scheme for the wiki. Choose the appropriate appearance for your wiki (again, you can always change this later) and then click on the Create button. Screen Shot 2015-09-25 at 10.04.53 PM Once the setup is finished, you’ll see the Setup complete modal. Here, you can click on Go to Wiki button. Screen Shot 2015-09-25 at 10.05.33 PM Once you’ve created your first wiki, let’s edit it and customize the content. To do so, click on it from the list of available wikis. Click on the cog-wheel icon and then Wiki Settings… to bring up the Wiki Settings page. Screen Shot 2015-09-25 at 10.05.59 PM Here, you’ll see the previously entered name and description as well as options to enable Calendar (only available if Calendar Server is running on the server) and Blog, which enables  a blog service for the wiki (wiki administrators can post blog entries to the wiki). Click on Appearance. Screen Shot 2015-09-25 at 10.06.26 PM Here, you will have the previous two options as well as the ability to upload a banner (which should be 62 pixels high) and background for each wiki. Screen Shot 2015-09-25 at 10.07.02 PM Click on Permissions. Here, you’ll see the permissions previously configured as well as options to configure who can comment on articles (nobody disables comments completely) in the wiki and whether comments require approval (moderation). Click on Save. Now, let’s edit the splash page. To do so, click the pencil icon in the top navigation bar. Screen Shot 2015-09-25 at 10.07.45 PM At the edit screen, the top nav bar is replaced by a WYSIWIG editor for managing the page. Here you can justify, link, insert media and of course edit the text you see on the screen. I recommend spending some time embedding links, inserting tables, making text look like you want it to and editing the content to reflect the purpose of the wiki. Click Save when you’re done. Click the pencil again to edit it, and let’s create a new wiki page. Keep in mind that link wikipedia, each page should be linked to from other pages in the order they should be read. Unlike most wikis, there’s actually an index page of all the articles, which can come in handy. Screen Shot 2015-09-25 at 10.08.34 PM From the edit page, to create a new page and link to it, enter some text (or lasso some) that you’ll use as the link to access the new page you’re creating. Then click on the arrow and select “New page.” Note: Use Enter URL to link to an existing page or an external website, instead of creating a new page. Screen Shot 2015-09-25 at 10.09.03 PM At the New Page screen, provide a name for the new page (the lasso’d text automatically appears as the Page Title) and click on the Add button. Screen Shot 2015-09-25 at 10.09.40 PM Click Save and then click on the newly created link. You can now edit the new page the same way you edited the previous pages. Click on the disclosure triangles in the right sidebar to Comment on articles, link articles to related articles, tag articles and view editing history. Screen Shot 2015-09-25 at 10.12.44 PM Now for the fun part. Click on Documents. Here, you’ll see the pages you already created. Click on the plus sign and select the option to Upload File to the wiki. Screen Shot 2015-09-25 at 10.13.48 PM At the Upload File dialog, click on Choose File and then select a file to upload. Screen Shot 2015-09-25 at 10.14.36 PM Click Upload when selected. Screen Shot 2015-09-25 at 10.15.35 PM Then from the Finder of an OS X client, use the Go menu to select “Connect to Server”. Enter the name or IP of the server and then click on Connect. Assuming you can access the server, you should then be prompted for a username and password. Enter it and click Connect. Eventually, the file(s) will display (it can take awhile according to your network speeds and how many files are in the directory). You can connect to this same screen through an iPad using a 3rd party WebDAV client or the build in options in Pages. Managing wikis is as easy as its ever been, with the new options for appearance being a nice add-on. Active Directory integration is as easy as binding the server to Active Directory and using the accounts listed in Permissions of pages. Overall, the ability to edit, upload and view documents from the Wiki is a great new feature in OS X Yosemite Server, worthy of checking out if you haven’t already! Now that iOS devices can edit wikis and many of the traditional word processing options are available in the wiki editor, consider what the Wiki can be. Could it replace text editing apps for iOS? Could the Wiki allow for more collaborative documents than a Word or other document editor? Could it keep from getting eaten like the rest of the homework? Could the comments in the Wiki be a good way for teachers to have students write responses to materials? Could the Wiki and the document management features allow your workers to access human resources documents and employee manuals? I know plenty a tech firm that use wikis to track information about the systems they manage. Once you have all of this information, upgrading can seem downright scary. But fear not, there’s Carbon Copy Cloner. And once you’ve cloned, there’s wikiadmin. When doing an upgrade in place, the Wiki service is pretty straight forward to upgrade, but in many cases, due to aging hardware, wiki services are moving from an older computer to a newer computer. This can be done in one of two ways. The first is to “migrate” the data by copying the Collaboration folder onto the new system. The second is to “export” and “import” the data. I usually recommend doing a migrate where possible, so we’ll start with that method. Note: Before getting started, make sure that the directory services side of things is good. If a user or group lookup for an object that owns, edits or has commented on a wiki fails then that wiki probably shouldn’t be migrated. Use the dscl or id commands to confirm that lookups are functioning as intended. To migrate wikis from one server to another, first copy the Collaboration directory to the new server. In this example, the directory has been dropped onto the desktop of the currently logged in user. To migrate the data once copied, use the wikiadmin command, along with the migration option. The option requires the path to the Collaboration folder, defined with -r, as follows: sudo wikiadmin migrate -r ~/Desktop/Collaboration
 When moving wikis, you can take the opportunity to get rid of a few you don’t want (such as that test wiki from way back when). Or administrators may just choose to move a single wiki to a new server in order to split the load across multiple hosts. When doing so, use the same command as earlier, along with the name of each wiki that is being moved, along with the -g option. For example, if moving the Legal wiki: sudo wikiadmin migrate -r ~/Desktop/Collaboration -g Legal
 The second way of moving wikis around is to export and then import them. To do so, first export wikis on the old server, using the wikiadmin command along with the export option, which requires an –exportPath option and needs to be done, on a wiki-by-wiki basis. So to export that Legal wiki to a file called LegalWikiTMP on the desktop: sudo wikiadmin export -g Legal --exportPath ~/Desktop/LegalWikiTMP
 Next, copy the wiki to the new server and import it, using the import option along with –importPath to identify where the file being imported is located. Using the same location, the command would then be: sudo wikiadmin import -g Legal --importPath ~/Desktop/LegalWikiTMP Note: The ability to import a wiki also allows for an API of sorts, as you can programmatically create wikis from other sources. The ability to export also provides a way to move into another wiki tool if you happen to outgrow the options provided in OS X Server and need to move to something more robust. There is another way to move wikis, using pg_dump, copying the data and then using pg_restore to import the data once you’ve created the tables.  This way is, in my opinion, the last resort if the standard wikiadmin commands aren’t working. In my experience, if I’m doing the migration this way then I’ve got other, bigger issues that I need to deal with as well. These commands work best when the wiki service has been started so that the databases are fully built out. To start the wiki service from the command line, use the serveradmin command instead of the wikiadmin command. The serveradmin command is used with the start option and then wiki is used to indicate the wiki service, as follows: sudo serveradmin start wiki
 The service can also be stopped, swapping out the start option with a stop option: sudo serveradmin stop wiki
 In a few cases (this is the main reason I’m writing this article), the attachments to wikis don’t come over during a migration. To migrate the files that are used for QuickLook, downloading attachments, etc, use the serveradmin command to locate the directory that these objects are stored in: sudo serveradmin settings wiki:FileDataPath
 The output identifies the directory where these objects are stored. Placing the contents in the same relative path as they are to the output of the same command on the target server usually results in restoring them. Once moved, use the fixPermissions option to repair the permissions of any files from the source (if any changes to account IDs are encountered such as an export/import rather than an archive/restore in OD this can lead to odd issues: sudo wikiadmin fixPermissions
 Also use the rebuildSearchIndex option with the wikiadmin command to fix any indexing, once the permissions have been repaired: sudo wikiadmin rebuildSearchIndex And finally use resetQuicklooks to clear any cached Quicklook representations of objects that have been inserted into a wiki and might not display properly using Quicklook (you know you might need to do this if they look fine when downloaded but look bad with Quicklook even though QuickLook on the server can view the files just fine): sudo wikiadmin resetQuicklooks
 When done properly the migration can take awhile. Keep in mind that every tag, every article, every edit to every article and basically everything else is tracked inside the tables that you’re moving. While there might not be a ton of data in the Collaboration directory or in an export, all of the data needs to go to the right location. This can take a little time in environments that have a lot of articles, even if they’re really short articles…

Configure File Shares In Windows Server 2012

As I mentioned in an earlier article, the File and Storage Role is installed by default in Windows Server 2012. This means you can create a file share with a very minimal amount of work on a brand new server. To get started, as with many things regarding Server 2012, open Server Manager. Screen Shot 2013-06-05 at 6.42.41 PM From Server Manager, click on File and Storage Services in the Server Manager sidebar. Then click on Shares. From the Shares screen, click on the Shares drop-down list and then click on New Share. Screen Shot 2013-06-05 at 6.44.16 PM This will open the New Share Wizard. From here, select a type of share. For the purposes of this article, we’ll create a very basic SMB share, so click on “SMB Share – Quick.” Then click on the Next button. Screen Shot 2013-06-05 at 6.51.48 PM At the Share Location screen, I like to click on “Type a custom path” and then click on the Browse button. Screen Shot 2013-06-05 at 7.13.32 PM At the Select Folder screen, browse to the folder you’d like to share out and then click on the Select Folder button. Then click on Next back at the Share Location screen. Screen Shot 2013-06-05 at 7.14.36 PM At the Share Name screen of the New Share Wizard, enter the name you want users to see when accessing the share in the Share Name field and the description (if any) that users will see in the screen when connecting to the share. You’ll also see the local path used to connect to that share on the server as well as the path that will be used to connect remotely in this screen. Click Next once you’ve entered the information. Screen Shot 2013-06-05 at 7.25.15 PM At the Other Settings screen, you have 4 options (checkboxes):
  • Enable Access Based Enumeration: If you have Mac clients this is often a bad idea. This feature ends up not showing people objects they don’t have access to. It’s great in a purely Windows environment, thought.
  • Allow Caching of Share: Allows Windows clients to right-click on a share and choose to cache it.
  • Enable Branch Cache on the File Server: Allows a computer in a branch office to act as a Branch Cache server/workgroup server.
  • Encrypt data access: Encrypts traffic to the share.
Most of these options are pretty irrelevant to the Mac and Linux, but can be helpful in purely Windows environments, especially if you need additional security or want your users caching data from the share. Once you’ve chosen the options that best work for you, click Next. Screen Shot 2013-06-05 at 7.32.11 PM At the Permissions screen, choose who has access to connect to the share. Note that controlling permissions to access objects from inside the share is done separately through the share and this option is just used to configure who can mount/map to the share. Click Next once only the users you want to access the share have the appropriate level of access. Screen Shot 2013-06-05 at 7.32.52 PM At the Confirmation screen, verify that all the settings for the share are correct and then click on the Create. Screen Shot 2013-06-05 at 7.38.28 PM Once the share is created, click on Close button. Screen Shot 2013-06-05 at 7.38.28 PM Then connect to the share and verify that the settings are as appropriate. Once done, create the subdirectories for the root level and configure permissions as appropriate.

Automatically assign admin rights in OS X based on Active Directory group membership

Thanks to Tedd Kidd for the following article, on automatically managing administrative privileges based on Active Directory groups!
This is a quick and easy way to assign any user to the local admin group in OS X based on their group membership in your Active Directory. This should also work with Open Directory or eDirectory groups if your workstations are bound to those directory services. You’ll need to include this code in the workstation login script so that it runs as root but uses the $@ variable to determine the user that is logging in. #!/bin/bash # Set group name to check against groupname=”domain admins” if [ “`/usr/bin/dsmemberutil checkmembership -U $@ -G $groupname`” == “user is a member of the group” ]; then /usr/bin/dscl . merge /Groups/admin GroupMembership $@ fi This works in both Snow Leopard and Lion. If you work for a school (like me) the groupname variable could be changed to staff or teachers, which would allow any staff member or teacher to have admin rights if run on student workstations.

Kerio: Permissions

Sometimes when you’re setting up permissions for certain folders using Microsoft Entourage, the process will fail.  If it does you can still set permissions using the web portal.  To do so, log into your webmail.  Then control-click the folder in question and click on the Access Rights… button.  Here, you will be able to define who can read, write or delete items.  Make sure that if you’re giving someone access to a folder that you don’t forget to give access to the parent folder (eg – the parent folder to INBOX is the root of your email hierarchy).  This is one of the more common mistakes we see there.