Logins and users in SQL Server
Before diving into enterprise geodatabase concepts, it's important to understand some basics of working with SQL Server, the DBMS that we'll be using on our Amazon cloud instances. Access to SQL Server itself is granted through logins. A login allows someone to authenticate with SQL Server and answers the question, "Who is connecting?" Access to an individual database (of which there could be several for a given SQL Server instance) is granted through users. Whereas logins are concerned with authentication, user accounts provide authorization to perform different tasks with a database. They answer the question, "What can this person do in the database?" As we'll see, logins must be mapped to database user accounts in order to work with database objects. This blog post provides further information.
Related to logins and users is the concept of roles. A role provides a way to group similar users together so that permissions can be granted more easily. Database administrators can define their own desired roles and make permission changes for those roles as they see fit. SQL Server also comes with a set of fixed server and database roles whose permissions cannot be changed. Among these is the sysadmin role, which as you might guess, has permissions that grant full control over the server.
All databases found on a SQL Server instance have a special user called DBO (short for database owner). Any member of the sysadmin fixed server role who uses a database is mapped to the DBO user and any object created by any member of the sysadmin fixed server role belongs to DBO automatically.
An important consideration when implementing an enterprise geodatabase in SQL Server is who will be the owner of the geodatabase -- the DBO user or the SDE user. While the DBO user is the person who administers the database at the SQL Server level (e.g., creating new users and roles), the SDE user is the person who administers the database within ArcGIS (e.g., updating database statistics and compressing the database). Esri's documentation includes a page that discusses the pros and cons of each [http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/comparison-geodatabase-owners-sqlserver.htm]. Generally speaking, if the SQL Server database administrator and the geodatabase administrator are the same person, then having the DBO user own the geodatabase is sensible. If on the other hand those two roles are filled by different people, then having the SDE user own the geodatabase is probably more advisable. In this course we're going to work with a DBO-owned geodatabase.
A reminder, if and when you restart your instance of the AMI
In the next section you will be back working, via your Remote Desktop Connection, on your instance of the Amazon Machine Image (AMI) that holds our enterprise geodatabase, etc.
If you STOP-ed your instance on purpose, or if your Remote Desktop Connection gets interrupted, you will need to re-START your instance via the EC2 Console.
After doing so, and before you can reestablish your Remote Desktop Connection, recall that you need to associate your Elastic IP with your instance and Reboot it.
And, if the IP address of the machine you're connecting with has changed since your last connection, remember that you'll need to edit the RDP rule to allow connection from your new IP address.
Changing authentication settings
Before we move on to setting up geodatabase users and roles, there are a couple of authentication settings we will change to make our lives a bit easier in this safe learning environment.
The first will be to set up SQL Server to allow mixed-mode authentication. By default, SQL Server only allows authentication through operating system logins. However, it is also possible to authenticate via user names and passwords stored in the database. In a real-world implementation, you may find it advantageous to utilize Windows authentication. But since that involves a bit of extra work on the instances we've just created, we'll go with SQL Server authentication.
The second setting we'll make will be to relax the default password standards on the Windows Server operating system that filter down to SQL Server. This will allow us to avoid the need to create lengthy, complex passwords.
- In your remote desktop window, launch the Microsoft SQL Server Management Studio. You'll find it under Start > Microsoft SQL Server 2016.
(You might consider right-clicking on the Management Studio application and selecting Pin to Start and/or Pin to Taskbar for quicker access to it later.)
- In the Connect to Server dialog that appears, enter LOCALHOST as the Server Name and click Connect.
- In the Object Explorer pane on the left side of the window, right-click on the LOCALHOST entry at the top of the tree and select Properties.
- Click on the Security page, then change the Server Authentication option from Windows Authentication mode to SQL Server and Windows Authentication mode. Click OK, and click OK to the note about needing to restart for changes to take effect.
- Right-click on the LOCALHOST server and choose Restart.
- While the server is restarting, open the Server Manager (found under the Start menu).
- In the upper right of the Server Manager window, select Tools > Local Security Policy.
- Under Account Policies > Password Policy, double-click on Password must meet complexity requirements and choose Disabled. Click OK, and close both the Local Security Policy dialog and the Server Manager.
We'll now be able to authenticate using simple, easy-to-remember passwords. Obviously in a real-world implementation you would want to think twice before lessening your database security like this.
Create an Enterprise Geodatabase
AMIs for earlier versions of ArcGIS Server came with a pre-configured enterprise geodatabase. The current version's AMI does not, so we will need to create one. This is just as well, since you'd probably need to do this in a real-world implementation anyway.
- Return to SQL Server Management Studio and Connect to the local server.
- Right-click on Databases and select New Database.
- Set the Database name to egdb (short for enterprise geodatabase).
- Click OK to create the database.
At this point this is an "ordinary" database. We'll next connect to the database in ArcCatalog, then use an Esri tool to turn it into a "geo" database.
Connect to the database as DBO
We'll start our work with the egdb database by connecting to it as the DBO user.
- On your remote connection desktop, open ArcCatalog.
- Expand the Database Connections entry in the Catalog Tree and double-click on Add Database Connection.
Choose SQL Server as the Database Platform if it's not selected already.
Specify LOCALHOST as the Instance.
For Authentication type, select Operating system authentication. You'll see that the User name and Password fields are grayed out. This is because you'll be authenticating based on how you logged in to the current Windows Server session (in this case as the user Administrator, who has system administrator privileges in both Windows and SQL Server).
Select egdb as the Database to connect to and click OK. A new ready-to-be-named connection will appear under the Contents tab. The user making the connection and the geodatabase being connected to, are important components of the connection, so I suggest including that information in the connection name.
- Name your connection dbo_egdb.sde. The user is dbo and the database is egdb.
- Note that after setting up and naming a database connection, you need to actually establish the connection. The red x on the icon indicates that the connection has not yet been made.
You can double-click on the connection name, or right-click on it and select Connect, to make the connection to the actual database.
Then you are able to see the contents of the database you're privileged to view through that user.
The egdb database is empty since you just created it, so you won't see anything listed in it yet.
Convert the database to a geodatabase
- Within ArcCatalog, open the ArcToolbox window. (It may take a while for ArcToolbox to open the first time.)
- Expand Data Management Tools > Geodatabase Administration and open the Enable Enterprise Geodatabase tool.
- Set the Input Database Connection parameter by clicking on the Browse button (folder icon) and selecting the dbo_egdb connection you just created.
- Set the Authorization File parameter by navigating to C:\Program Files\ESRI\License10.5\sysgen\keycodes.
- Click OK to execute the tool. If the tool executed successfully, the resulting dialog will tell you that it:
Created geodatabase tables and stored procedures.
Finished creating geodatabase schema.
Before adding some data to the geodatabase, let's discuss the importance of users and roles in a geodatabase context and lay out the scenario for the rest of our work in this section.
Users and roles in the geodatabase
Database roles enable the database administrator to improve security by assigning users or groups of users various levels of access to the database tables. Commonly used roles in an enterprise geodatabase include viewer, editor, etc. As the names of these roles imply, one user group might be limited to read-only access to data while another group might be allowed to both read and edit the data. While we're only getting to this topic now, you should keep in mind that similar strategies can also be applied to the kind of Postgres/PostGIS databases we talked about in previous lessons.
For illustration purposes, imagine you work in the U.S. federal government and that you are administering a geodatabase that will host data from a number of different departments: state boundaries from the Census Bureau, major highways from the Department of Transportation, rivers from the Department of the Interior, etc. You want each department to have stewardship over their data and to have just read-only access to other departments' data.
Click here to download to your machine the Lesson 6 data. In the steps below you will then copy said us_data.zip archive file from your local machine to your remote instance. The D:/data folder on your remote instance is a logical place to paste and unzip the data.
- Go to the taskbar on your remote desktop, and open the File Explorer (via the file-folders icon).
The File Explorer will open to the contents of This PC. In the Devices and drives list you’ll see the drives that exist on your local computer and the drives that are on your remote instance.
- Find the us_data.zip file that you just retrieved, and copy it to the Local Disk (D:)/Data folder.
- Unzip the us_data.zip file that you just uploaded.
There are four Shapefile datasets.
Note that in our enterprise database environment, vector data is stored as feature classes. So, later, the Shapefile datasets that I gave you will be uploaded and converted to feature classes.
One aspect of the data loading/creation process that's worth keeping in mind is that when a feature class or feature dataset is created in the geodatabase, its name in the ArcGIS applications has three parts:
<parent gdb>.<owner>.<feature class/feature dataset>
Some organizations set up users specifically to handle data loading/data creation so that the owner listed in the three-part name clearly conveys the department that maintains the data. Following on this practice, let's add a user to the database who will control the loading of the Census data.
Add a data loader/creator to the geodatabase
The Add User tool is one of several geodatabase administration tools available through the ArcGIS Desktop GUI.
- On your remote connection desktop, open ArcCatalog.
- Re-establish the dbo_egdb database connection (by double-clicking on it).
- Right-click on your dbo_egdb database connection and select Administration > Add User.
In the Create Database User dialog, the Input Database Connection field will be pre-populated based on the connection you had right-clicked on.
We're going to use database authentication for this user, so leave the Create Operating System Authentication User box unchecked.
Enter a Database User name of census.
To make this easy to remember, enter the same string (census) for the Database User Password.
Leave the Role field empty. We'll discuss roles shortly, but you should note that if any database roles existed, you would have the option of associating the new user with a role in this dialog.
Click OK to create the new user.
With the new user created, let's add a database connection based on that user's authentication parameters.
- As you did earlier, double-click on Add Database Connection.
The Database Platform and Instance should be correctly specified as SQL Server and LOCALHOST already.
Change the Authentication Type to Database authentication.
Enter the census:census User name and Password combination established above.
You will probably want to leave the Save user name and password box checked for your work here, though this is another setting you'd want to consider carefully in a real-world implementation.
If you entered the name and password properly, you should be able to select egdb from the Database dropdown.
Click OK to create the connection.
- Following on the convention suggested earlier, rename the connection to census_egdb.sde.
Note: You may see the label on your existing connection change to match what you just entered for this new one. If something buggy like this happens, just right-click and select Refresh, and the connection labels should be fixed.
With the census user created, let's work as that user to load the state and city data you downloaded above. Because feature datasets are frequently used to house feature classes, let's create one of those first.
- Right-click on the census_egdb.sde connection and select New > Feature Dataset.
- Assign a Name of usa, then click Next.
- The data you'll be importing are in geographic coordinates, NAD83 datum, so specify that as the coordinate system for this feature dataset and click Next.
- Click Next again to skip the Vertical coordinate system panel.
- Leave the tolerance settings at the default values, and click Finish.
You should see a new egdb.CENSUS.usa feature dataset appear in the Contents tab.
- In the Catalog Tree, right-click on Folder Connections and make a connection to the:
This PC / Local Disk (D:) / data folder.
- Now, expand the census_egdb.sde connection and right-click on the egdb.CENSUS.usa feature dataset and select Import > Feature Class (Multiple).
Browse to where you unzipped the us_data shapefiles and select the us_boundaries and us_cities shapefiles.
In the Feature Class to Geodatabase (multiple) dialog, click OK.
After a few moments, new egdb.CENSUS.us_boundaries and egdb.CENSUS.us_cities feature classes should appear in the egdb.CENSUS.usa feature dataset.
- For additional practice, repeat the steps above to create a new transportation user, connect as that user, and load the us_roads shapefile into the geodatabase.
An important point to note about the process we just went through is that users created via the Desktop Add User tool have the ability to load/create data. It's generally considered a best practice to restrict this ability to a small number of trusted administrators to avoid cluttering the database with unwanted data. We'll now see that following this best practice requires adding lower-permission users through a different process.
Adding data editors/viewers to the geodatabase
Returning to our scenario, the users in the departments are as follows:
To add these users, we'll need to return to the SQL Server Management Studio.
- If you closed your Management Studio window earlier, re-open it and connect to LOCALHOST using Windows Authentication.
- In the Object Explorer, expand the Security folder, then right-click on Logins and select New Login.
- In the Login – New dialog, enter a Login name of Moe.
Select SQL Server Authentication, then a Password of Moe (it's case-sensitive, same as the login name).
Uncheck each of the three checkboxes (Enforce password policy, Enforce password expiration, User must change password at next login). Again, this is a place where you might choose differently in a real-world implementation.
Set the Default database to egdb.
Click OK to create the new login.
- If you expand the Logins folder, you should now see a Moe login, along with census and transportation, which were created by the ArcGIS Add User tool earlier.
We'll now associate the new login with the egdb database that you created above.
- In the Object Explorer, navigate through the following folders: Databases > egdb > Security > Users. Again, you should see census and transportation here, listed as users in the egdb database.
- Right-click the Users folder and click New User. In the Database User – New dialog:
Enter a User name of Moe.
Enter a matching Login name of Moe. (You could also click the '...' button to the right to browse the list of logins associated with the SQL Server instance.)
Important: Leave the Default schema blank. If you wanted Moe to have the ability to own data in the geodatabase (i.e., to create new feature classes), then you would enter Moe as the default schema. The ArcGIS Add User tool makes this setting for the new user, which is why we're not using that tool to create lower-level users like Moe, but instead using SQL Server Management Studio as outlined here. The reasoning behind the different user creation process is found in Esri's documentation:
"Users who own data must also have schemas in the database that have the same name as the user name."
Click OK. Moe should now have access to the egdb database.
- Return to ArcCatalog and establish a database connection for user Moe following the steps used to create the census_egdb connection earlier.
Note: At this point, you're emulating what a non-administrative user of your database would go through to connect to the geodatabase. Earlier connections and data loading procedures are tasks that you would be more likely to carry out in a database administrator role.
After creating a connection for Moe and opening that connection, you'll find that Moe is not able to see any of the data that's been loaded into the database. We'll look at how to fix that shortly.
- Repeat the steps above to add Larry and Curly (and Lisa, Bart and Homer if you're so inclined) as egdb geodatabase users.
In order for users to have the ability to view and/or edit the data in a geodatabase, the data owner must grant those privileges. That can be done on a user basis (using an ArcGIS tool), but the process can be done more efficiently by assigning users to roles and assigning privileges to the roles. In this section, you'll create a viewer role and an editor role in the egdb database.
One important note on roles: while the privileges must be granted by the data owner (the census and transportation users in our scenario), the roles must be created by someone with sysadmin privileges. That is the DBO user in our implementation.
- Right-click on the dbo_egdb connection in ArcCatalog and select Administration > Create and Manage Roles.
- In the resulting dialog, the Input Database Connection should be pre-populated based on the connection you right-clicked on.
- For Role, enter viewer.
- Confirm that GRANT (and not REVOKE) is selected in the next dropdown.
- In the User Name(s) field, enter a list of the users you created in the section above who were listed as viewers. As it says in the Tool Help panel, the user list should be separated by commas (no spaces).
- Click OK.
- Repeat these steps to create an editor role for the appropriate users.
If you're paying close attention to the scenario, you might be thinking we should have separate editor roles for each of the three data owners, such that Moe can only edit his Census data, Lisa can only edit her Transportation data and Stan can only edit his Interior data. You're welcome to implement that sort of design if you wish, but for simplicity's sake, feel free to create just a single editor role.
Manage geodatabase privileges
Finally, we'll now work through the data owner connections to assign the proper privileges to go with the roles just created.
- Open the census_egdb connection.
- Right-click on the egdb.CENSUS.usa feature dataset and select Manage > Privileges.
- In the resulting Privileges dialog, click the Add button. A User/Role window will appear listing all of the users and roles associated with the egdb database.
- Check the box next to the viewer role and click OK. You should now see a viewer entry in the Privileges dialog.
Since you learned about SQL earlier in the course, you should be able to guess that viewing data corresponds to the SELECT privilege, while editing data corresponds to the INSERT, UPDATE and DELETE privileges.
Ensure that only the SELECT privilege is checked for the viewer role. While in this dialog, we may as well set the privileges for the editor role as well.
- Click the Add button again, select the editor role this time, and ensure that all four privileges are checked.
- Click OK to apply the changes. This will provide the same level of privileges across all feature classes housed within the usa feature dataset.
- Repeat these steps to assign similar privileges for the data owned by the transportation user.
The next logical step is to test the privileges you've just assigned. Before doing that, we'll need to change one of ArcMap's editing settings.
On the desktop of your remote connection find the shortcut icon for ArcMap and open the application.
Open the Editor toolbar and go to Editor > Options and click on the Versioning tab. Uncheck the "Edit a version of the database with the ability to undo and redo" checkbox. If we didn't uncheck this box we would need to set up our geodatabase for versioned editing which is a topic we'll cover later in the course. Non-versioned editing is sufficient for this simple little demo.
Click OK to dismiss the Editing Options dialog.
- Now, in ArcMap, test the settings you've made.
- Bart should be able to view all feature classes, but not edit them.
- Moe should be able to both view and edit all feature classes.
- Neither Bart, nor Moe should be able to import or create a new feature class.
(Hint: you make Database Connections via the Catalog pane in the ArcMap interface.)
- Privileges can be assigned at the feature class level but only if the feature class is not part of a feature dataset. When dealing with a feature dataset privileges must be assigned at the dataset level (as outlined above) and they will cascade to each child feature class.
- If you want to assign the same privileges to multiple feature classes you can select all of the desired feature classes before right-clicking and choosing the Privileges option.
- If your geodatabase contains standalone tables in addition to feature classes you must assign privileges to the tables and feature classes in separate steps (i.e., do not try to select a mix of tables and feature classes before opening the Privileges dialog.
This concludes the hands-on activities you'll be doing with your geodatabase instance in this lesson. Feel free to do some experimenting with your instance. When done, close the remote desktop connection to your instance, and most importantly, remember to stop your instance through the EC2 Console to avoid racking up unnecessary charges.
Go on to the next page to see this week's graded activity.
With that, we've finished our tutorial on creating a SQL Server geodatabase, adding users, loading some data, and assigning data privileges to the users. You weren't specifically instructed to add the Department of the Interior users and import the us_hydro shapefile, but you're welcome to do so if you're looking for additional practice.