{"id":81935,"date":"2018-12-07T03:08:16","date_gmt":"2018-12-07T03:08:16","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81935"},"modified":"2021-04-29T15:25:07","modified_gmt":"2021-04-29T15:25:07","slug":"how-to-linux-for-sql-server-dbas-part-3","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/how-to-linux-for-sql-server-dbas-part-3\/","title":{"rendered":"How to Linux for SQL Server DBAs — Part 3"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. How to Linux for SQL Server DBAs \u2014 Part 1<\/a><\/li>\n
  2. How to Linux for SQL Server DBAs \u2014 Part 2<\/a><\/li>\n
  3. How to Linux for SQL Server DBAs \u2014 Part 3<\/a><\/li>\n
  4. Navigate Linux for SQL Server DBAs<\/a><\/li>\n<\/ol>\n\n

    As you jump into Part 3, you\u2019ll begin to realize how involved your education will be with Linux, and how it is taking over the world. The previous articles just scratched the surface with your new Docker container with Linux and SQL Server. You have embraced how users are created in a Linux system and what a group is. You\u2019ve begun to understand the power of the Linux kernel and the pure simplicity of it. This takes you to the next step in your education: ownership and permissions.<\/p>\n

    Unlike Windows, there\u2019s no registry layer when working with Linux. As an operating system, Linux expects that if you\u2019ve been granted rights to perform a task, you should have the knowledge and skills to be trusted to perform the task. Every object in Linux is treated as a file, and permissions are at the core of this. Understanding how permissions work is essential, and this is the next step in your Linux journey.<\/p>\n

    In the following section, you\u2019ll create:<\/p>\n

      \n
    • A Unix user<\/li>\n
    • A home directory<\/li>\n
    • A lesson directory<\/li>\n
    • And empty files to work with ownership and permission assignments.<\/li>\n<\/ul>\n

      As you proceed into the next sections of the article, you may need to check users on your Linux host and what groups exist. You can do this by typing in the following command after you open a bash shell to your container:<\/p>\n

      >cat \/etc\/passwd<\/pre>\n
      To view groups, you can do this by cat\u2019ing the group file:<\/p>\n
      >cat \/etc\/group<\/pre>\n
      The following commands will take you through these steps. The first step will be to create a user for the test, with a home directory, using the adduser<\/code> command:<\/p>\n
      >adduser jsmith2<\/pre>\n
      Fill in the password, etc., for jsmith2<\/em> and retain the password for future logins.<\/p>\n
      <\/p>\n
      SU<\/code>, (switch user) to jsmith2.<\/em><\/p>\n
      >su jsmith2<\/pre>\n
      <\/p>\n
      Check your current location and verify that you\u2019re in the \/home\/jsmith2<\/em> directory:<\/p>\n
      >pwd<\/pre>\n
      If you find yourself at the root directory, (\/) perform the following:<\/p>\n
      >cd <\/pre>\n
      Now check with the pwd<\/code> command again to verify that you\u2019re in the \/home\/smith2<\/em> home directory.<\/p>\n
      <\/p>\n
      Create a new directory in jsmith2<\/em>\u2019s home directory and set permissions<\/p>\n
      >mkdir part3\r\n>chmod 744 part3<\/pre>\n
      I\u2019ll explain more about the chmod<\/code> command and numeric value later in the article. Switch over to the new directory:<\/p>\n
      >cd part3<\/pre>\n
      Create a new file:<\/p>\n
      >touch test.txt\r\n>ls -la<\/pre>\n
      You should see the following in your new directory:<\/p>\n
      <\/p>\n
      Identity Crisis<\/h2>\n
      There are two main commands that are connected to permissions:<\/p>\n
      Change Owner chown Changes the owner of a file(s)<\/p>\n
      Change Modify chmod Modifies permissions of a file(s)<\/p>\n
      Each file will have three designations of ownership. One will be the owner; the second will be the group assignment, (which can also be assigned to the owner) and lastly, all others. The third designation isn\u2019t an assignment, but the first two, owner and group, are.<\/p>\n
      When creating a file, ownership and group are defaulted to the user that created it. To reassign permissions, you must first have the rights to do so. Secondly, the user and group must exist. If you are the owner of the file, you can do both, but if the file is owned by another and you don\u2019t have permissions, you will have to use \u201csuper user\u201d (su) to perform the task. To perform an ownership change, you would run the following:<\/p>\n
      >sudo chown <new owner>:<new group> <filename\/folder> <\/pre>\n
      As root, (domain owner) you would be able to change the owner of any file or switch permissions. You can also do this as the user if they have SUDO<\/code>, (Switch User to Domain Owner). As you\u2019re currently logged in as jsmith2, exit out of the current shell from user jsmith2 back to root.<\/p>\n
      The second choice, if you want to run everything with the SUDO<\/code> command, is to grant SUDO<\/code> to jsmith2<\/em> as ROOT<\/em>. To do either, you must exit out of the current shell back to ROOT.<\/em><\/p>\n
      >exit<\/pre>\n
      <\/p>\n
      Note that the prompt returns to displaying the root login at the very left. If you want to grant SUDO<\/code>, it can be done with the following command. Recognize that jsmith2<\/em> now has root privileges on the host and, although his sudo<\/code> executions can be audited, this user will now have these privileges.<\/p>\n
      >usermod -a -G sudo jsmith2<\/pre>\n
      You can now change the owner and group of the files in the existing directory. The example displayed below demonstrates what would be required if you weren\u2019t root and using the SUDO<\/code> command as jsmith2<\/em>, but if you are logged in as root, the SUDO <\/code>is left off the command. In this example, you\u2019ll change the owner to jdoe1<\/em> and the group to sqlinstall<\/em>. Note that the account and group were created in the last article.<\/p>\n
      >cd \/home\/jsmith2\/part3\r\n>sudo chown jdoe1:sqlinstall test.txt<\/pre>\n
      Or as ROOT<\/em>, then you\u2019d simply leave the SUDO<\/code> off the command:<\/p>\n
      >chown jdoe1:sqlinstall test.txt<\/pre>\n
      <\/p>\n
      If the SUDO<\/code> command returns an error about not being available, you will have to install it:<\/p>\n
      >apt-get install sudo<\/pre>\n
      This is all there is to changing the ownership on a file, as well as the group in one command.<\/p>\n
      Permissions and Modes<\/h2>\n
      As a DBA, you put in significant time into understanding database level security and permissions. It\u2019s as important to understand how OS level permissions impact the security of your database residing on the host. On Linux, you change permissions to files and folders by using the change modify, (chmod<\/code>) command. There are different categories of user ownership and escalation of privileges to each file in Linux. The categories of user ownership are:<\/p>\n
        \n
      • User\/Owner=u<\/li>\n
      • Group=g<\/li>\n
      • Other\/all users=o<\/li>\n
      • ALL=A<\/li>\n<\/ul>\n
        Similar to Microsoft Windows, there are three main categories of ownership that can be assigned singularly or in combination to create the correct allocation of ownership to any file. There\u2019s also a global grant to \u201cALL\u201d that encompasses all categories as one.<\/p>\n
        From here, permissions are granted to perform tasks. As with a database, the least required privilege(s) to perform the action should be considered. Although three simple grants are all that are available, in combination, they create the complexity required to secure the users internal to the OS.<\/p>\n
          \n
        • Read<\/li>\n
        • Write<\/li>\n
        • Execute<\/li>\n<\/ul>\n
          There are multiple modes to allocate permissions to files and directories. I\u2019ll discuss the two main ones- numeric<\/strong> and alpha<\/strong> mode.<\/p>\n
          Numeric Mode<\/h2>\n
          In this mode, there is a numerical value that is assigned to each permission to simplify grants. Where this might sound confusing at first, when you see the values clearly displayed by grant, it begins to make sense.<\/p>\n
          Read,(r)= 4<\/p>\n
          Write,(w)= 2<\/p>\n
          Execute, (x)=1<\/p>\n
          For the first run through the permissions, log back in as a jsmith2 user and switch to the user\u2019s home directory if you are not already there:<\/p>\n
          >su jsmith2\r\n>cd \r\n>cd part3<\/pre>\n
          The numerical values for permissions are used in conjunction with the chmod<\/code> command to assign privileges to the owner, group and other for an individual or group of files. Using the example file, test.txt, you can request that permissions be set for the following to meet requirements:<\/p>\n
            \n
          • Owner has read, write and execute<\/li>\n
          • Group has read and execute<\/li>\n
          • Other has no privileges to the file.<\/li>\n<\/ul>\n
            The command would be:<\/p>\n
            >chmod 750 test.txt <\/pre>\n
            If you typed this command in, you should receive the following error, but why?<\/p>\n
            chmod: changing permissions of 'test.txt': Operation not permitted<\/pre>\n
            Check the ownership and group permissions on the file:<\/p>\n
            >ls -la <\/pre>\n
            <\/p>\n
            The file is no longer owned or even part of a group that jsmith2 <\/em>is part of. You could switch back the ownership of the file, but for this exercise, you\u2019ll understand the importance of ownership and create a new file to work with, this time calling it test.sh and changing the permissions on this new file.<\/p>\n
            >touch test.sh\r\n>chmod 740 test.sh\r\n>ls -la<\/pre>\n
            <\/p>\n
            This command comes from the numerical values per grant in the list above, calculated for each category- owner, group and other and results in the following:<\/p>\n
              \n
            • owner has 4+2+1=7<\/li>\n
            • group has 4+0+0=4<\/li>\n
            • other has 0+0+0=0<\/li>\n<\/ul>\n
              Unlike a standard ls, (list) command, a ls -la<\/code>, (list all) command will display the permissions for the files contained in any directory broken down by owner, group, other and by individual grants.<\/p>\n
              jsmith2@cd6a623ef493:~\/part3$ ls -la\r\ntotal 8\r\ndrwxr--r-- 2 jsmith2 jsmith2    4096 Oct 30 17:29 .\r\ndrwxr-xr-x 4 jsmith2 jsmith2    4096 Oct 30 17:38 ..\r\n-rwxr----- 1 jsmith2 jsmith2       0 Oct 30 18:27 test.sh\r\n-rwxr----- 1 jdoe1   sqlinstall    0 Oct 30 17:29 test.txt<\/pre>\n
              A \u201cd\u201d in the beginning columns signifies that this is a directory, vs. a file. Notice that there are ten fields that are populated by the output- one for type and nine for permissions to the three categories, owner\/user, group and other.<\/p>\n
              In the example above, you\u2019ll note the directories without a name, just marked with \u201c.\u201d. These are directory identifiers. The first \u201c.\u201d is to identify the current directory and the privileges granted to it. To translate this to a numeric mode command, it would look like the following:<\/p>\n
              >chmod 744 .<\/pre>\n
              The directory identified with \u201c..\u201d is the directory above the current one and Linux is displaying that it has more privileges to the groups and other categories, both read and execute. If you translated this to numeric mode, it would result in the following:<\/p>\n
              >chmod 755 ..<\/pre>\n
              You can view how changes to permissions via numeric mode occur by again, changing the permissions on the shell script:<\/p>\n
              >chmod 764 test.sh\r\n>ls -la test.sh<\/pre>\n
              <\/p>\n
              The owner has read, write and execute on the file, the group has read and write to the file and all others are only allowed read access. Understanding these privileges is essential to a DBA so you can manage the privileges to system and data files, subsequently securing your database server at the OS level.<\/p>\n
              Now that you know how to change the owner and grant privileges on a file, it may be helpful to have a reminder on a few ways to create a file. Below are the most common ways users create a file, most often for write at the time or later.<\/p>\n
              Create an empty file touch <filename><\/p>\n
              Create empty file and open for edit vi(m) <filename><\/p>\n
              Common editor to create files nano <filename><\/p>\n
              You may choose any of these methods, vi(m) being an older method, and nano, emacs and other editors being newer ones. Touch is most often used to verify write privileges to a location, as it efficiently creates an empty file in the directory and will fail if privileges are missing.<\/p>\n
              Alpha Mode<\/h2>\n
              The secondary mode for granting privileges is Alpha mode, which uses letters to identify the category of user and privilege.<\/p>\n
              You will again work with your jsmith2 <\/em>user and part3<\/em> directory from the home\/jsmith2\/part3 location.<\/p>\n
              The difference when working with alpha mode, is that the permissions are a bit more complicated in their execution. Unlike numeric mode that simply rewrites over existing permissions, alpha requires a + or a -, along with the initial of the permissions to grant:<\/p>\n
                \n
              • r(Read)<\/li>\n
              • w(Write)<\/li>\n
              • x(Execute)<\/li>\n<\/ul>\n
                This method can be used to remove permissions as easily as grant them, simply executing the chmod <\/code>command, a \u201c-\u201c, the alpha mode permission and the file or directory name to remove privileges to the owner. To assist in why you would want to remove privileges from yourself, a use case is a file or directory you want to protect yourself from an accidental write or execution of, you can change the permissions to help ensure this.<\/p>\n
                This command ends with the name of the file, and when no user\/group\/category is added, it only addresses permissions for the user executing the chmod <\/code>command.<\/p>\n
                >chmod -wx test.sh<\/code><\/pre>\n
                You would then be required to change the permissions and add read and write on the file for the owner before you\u2019d be able to perform either or if you have SUDO<\/code>, then this would suffice. Always remember, SUDO<\/code>, (Switch User Domain Owner is GOD.)<\/p>\n
                The next command adds execute to the owner, group and other category for file ownership. This would result in everyone being able to execute the shell script.<\/p>\n
                Example:<\/p>\n
                >chmod +x test.sh<\/pre>\n
                At this point in the process, exit back out and become root to perform the next commands.<\/p>\n
                >exit<\/pre>\n
                The root user should be at the left of your prompt if you\u2019re using the Docker image to work along with this article.<\/p>\n
                >cd \/home\/jsmith2\/part3\r\n>chmod -w test.sh <\/pre>\n
                This would remove jsmith2<\/em>\u2019s write privileges to the test.sh file. This might seem counter intuitive. Why would you wish to remove write privileges from your own file? Removing write or execute could protect from mistakes in processing or remind the user which script is the active one, while retaining historical code.<\/p>\n
                To perform permissions changes using alpha mode for a group, or other, then you use the initial format and add the category before the permissions change:<\/p>\n
                Example:<\/p>\n
                >chmod g+x test.sh\r\n>chmod g-w test.sh<\/pre>\n
                The above commands have added execute to the test.sh file for the group assigned, but then removed write from the test.sh file. You can do the same for o, (others) by changing its permissions to read and no write or execute on the file:<\/p>\n
                >chmod o+r test.sh\r\n>chmod o-wx test.sh<\/pre>\n
                You can combine the privilege change for owner, group and other at once. Just as:<\/p>\n
                >chmod 777 test.sh<\/pre>\n
                This would result in the owner, group and other having read, write and execute on a file, the same would be in alpha mode:<\/p>\n
                >chmod ugo+rwx test.sh<\/pre>\n
                This also could be done with the a(All) category:<\/p>\n
                >chmod a=rwx test.sh<\/pre>\n
                At the end of this exercise, all users- owner, group and other have permissions to read, write and execute the shell script test.sh:<\/p>\n
                <\/p>\n
                Group Shift<\/h2>\n
                Beyond chmod<\/code>, is chgrp<\/code>, (Change Group) which unlike chown<\/code>, (change owner) that allows for a group allocation at the time of the command, can change the group with a separate command.<\/p>\n
                >chgrp sqlinstall .\/test.sh\r\n>chgrp sqlinstall ..\/part3<\/pre>\n
                <\/p>\n
                Notice that you can change the group on a file or a directory, (remember, everything is treated as a file in Linux) as easily as I can change owner, permissions, and groups for a single file. You are able to do all of this from the current directory, using the \u201c.\u201d and \u201c..\u201d to tell Linux that you want to work in the current directory or the directory one up from where you are.<\/p>\n
                Wildcards are permissible for all these commands as well. If you wish to change the owner of all the files in a directory:<\/p>\n
                >chown jsmith2:sqlinstall *<\/pre>\n
                You can change the permissions on all files with the .sql extension in a directory:<\/p>\n
                >chmod 744 *.sql<\/pre>\n
                You can also grant ownership to all files and subfolders with the -R, (recursive) argument.<\/p>\n
                If you go up one folder:<\/p>\n
                >cd ..\r\n>chmod 774 .\r\n>chown -R jsmith2:sqlinstall .<\/pre>\n
                The above command just granted read, write and execute to the owner and the group, and read to others. The second command changed ownership of everything in the current folder, (.) and files in subfolders to jsmith2<\/em>.<\/p>\n
                Conclusion<\/h2>\n
                As you can see, there\u2019s a lot more to permissions than meets the eye. Learning how to change permissions, either by alpha or numeric mode is enough, but make sure you understand the permissions and how they translate in whichever mode you\u2019re going to use. A secure database system is essential down to the operating system level, so as a DBA your understanding of how Linux does this allow you to secure your Linux host for SQL Server and help everyone sleep at night.<\/p>\n
                The next article will start working with navigation and processes in Linux. There\u2019s a lot to come and, hopefully, you\u2019re able to work through these scenarios on your Docker image.<\/p>\n","protected":false},"excerpt":{"rendered":"
                DBAs running SQL Server on Linux will not only need to understand SQL Server security well, they will also need to understand how security works on Linux. In this article, Kellyn Pot’Vin-Gorman walks you through several examples, explaining the permissions and ownership of files and directories in Linux.…<\/p>\n","protected":false},"author":316206,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53],"tags":[95506],"coauthors":[48576],"class_list":["post-81935","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81935","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316206"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=81935"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81935\/revisions"}],"predecessor-version":[{"id":81948,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81935\/revisions\/81948"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81935"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}