Remove User From MySQL

Basic database security involves having a password and user being assigned to  specific database, tables and their roles. This is also known as privileges in MySQL and Maria DB. In previous post, its shown how to list user permissions in MySQL database. But how do you remove that user?

The term MySQL in this post will refer to also MariaDb unless specifically mentioned otherwise.

The process to remove a user involves (1) to identify what privileges the user have, then (2)to revoke privileges and finally (3)remove the user from MySQL. In this tutorial, using the command line, a user with the name patrick is to be removed from the database quartz.

REVOKE statement

Firstly, here is the syntax;

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges
from MySQL accounts.

When the read_only system variable is enabled, REVOKE requires the
SUPER privilege in addition to any other required privileges described
in the following discussion.

Each account name uses the format described in
http://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name
part of '%' is used.

For details on the levels at which privileges exist, the permissible
priv_type, priv_level, and object_type values, and the syntax for
specifying users and passwords, see [HELP GRANT]

To use the first REVOKE syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.

To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named
user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER
privilege or the UPDATE privilege for the mysql database.

URL: http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Step 1 : Identify existing user privileges

Before any revoke, it is a practise to verify the user privileges.

> SHOW GRANTS FOR patrick@'localhost';

If @'localhost' is not used, the default will be @'%' which will look like this;

> SHOW GRANTS FOR patrick@'localhost';
+----------------------------------------------------------------------------+
| Grants for patrick@localhost                                                            |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'patrick'@'localhost'
| GRANT ALL PRIVILEGES ON `quartz`.* TO 'patrick'@'localhost' WITH GRANT OPTION     |
+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Step 2 : Revoke(remove) user privilege

I have not mentioned use of roles, but there are options to manage large number of users by roles. Could be another topic. The REVOKE only works at the next user login. Which means, a user that is currently connected online will still have the privilege unchanged.

To revoke privileges of user for a specific task like INSERT;

> REVOKE INSERT ON  'quartz'.'*' FROM patrick@'localhost';

If there are many users to be removed at the same time, just append with a comma separator.

> REVOKE INSERT ON  'quartz'.'*' FROM patrick@'localhost', username2, username3;

All privileges of a user is removed with the REVOKE ALL PRIVILEGES, GRANT OPTION.

If the user did not have any privileges to be revoked, and ERROR 1269 is returned.

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM patrick;
> SHOW GRANTS FOR patrick@'localhost';
+----------------------------------------------------------------------------+
| Grants for patrick@localhost                                |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'patrick'@'localhost'
+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Step 3. Remove(delete) user

The last step is to remove or delete the user.

> DROP USER patrick@'localhost';

The user is now safely removed from MySQL along with its privileges.

Practise

Here is an exercise which should reinforce the skills and knowledge from this tutorial. The user Patrick is assigned to retrieve data from a database named fruit_shop. Patrick will only be working on this database for 1 day and his user access is to be removed from the database once his task is done.

1. Login (as root) to MySQL and create a database named fruit_shop.
2. Create and apply a user named patrick who can only user the commands for SELECT and SHOW VIEW, with access from any host, using the password Banana1234 
3. Display list of user privileges on the system.
4. Remove all privileges for patrick.
5. Display list of user privileges on the system.
6. Remove user patrick.
7. Display list of user privileges on the system.

Display code in Blogger.com

This is how I display programme code and command line instructions on blogger.com.

<pre style="background: #f0f0f0; border: 1px dashed #cccccc; color: black; 
  font-family: "arial"; font-size: 12px;
  height: auto; line-height: 20px; overflow: auto; padding: 0px; 
  text-align: left; width: 99%;">
<code style="color: black; word-wrap: normal;">

Tutorial on File with Selinux Enforcing - part 2

Continuation from Part 1 post.

Apache HTTPD Not Allowed to Access Folder

It is possible to quickly implement security policy on a folder to allow application the required security. In this example, I am having the webserver Apache httpd to write log to a non standard folder /var/www/log. On a SELinux enabled Linux, permission is configured to allow read and write only for folders in /var/www/html.

On a Centos Linux with SELinux enabled, the HTPD server at start will show Failed to start The Apache HTTPD Server.

$ sudo systemctl stop httpd
$ sudo systemctl start httpd
$ sudo journalctl -xe

-- Unit httpd.service has begun starting up.
Jul 04 15:43:54 localdomain systemd[1]: httpd.service: main process exited, code=exited, status=1/FAILURE
Jul 04 15:43:54 localdomain kill[76313]: kill: cannot find process ""
Jul 04 15:43:54 localdomain systemd[1]: httpd.service: control process exited, code=exited status=1
Jul 04 15:43:54 localdomain systemd[1]: Failed to start The Apache HTTP Server.
-- Subject: Unit httpd.service has failed

The error dd not specifically mention its of SELinux nature, but disabling SELinux, the error is gone. Following are the permissions of the authorised folder /var/log and our custom folder.

The security context type should be var_log_t to allow system logging.

The tool chcon can be used to copy security context from the original folder as follows;

$ sudo chcon --reference /var/log /var/www/log

Restart the HTTPD server without errors.

Quick Command Reference

To complete this tutorial here are a few common commands and brief notes from man pages;

1. audit2allow 
• generate SELinux policy allow/dontaudit rules from logs of denied operations
2. audit2why
•  translates SELinux audit messages into a description of why the access was denied (audit2allow -w)
3. ausearch
• ausearch  is  a  tool  that  can  query the audit daemon logs based for events based on different search criteria.  The  ausearch  utility  can also  take  input  from stdin as long as the input is the raw log data. Each commandline option given forms an "and"  statement.  For  example, searching  with  -m  and  -ui  means  return  events that have both the requested type and match the user id given.
4. getenforce
• reports  whether  SELinux is enforcing, permissive, or disabled.
5. getsebool
•  reports  where  a  particular SELinux boolean or all SELinux booleans are on or off.
6. journalctl
•  to query the contents of the systemd(1) journal as written by systemd-journald.service
7. ls -Z
•   Display  security context so it fits on most displays.  Displays only mode, user, group, security context and file name.
8. ps auxZ
•  report a snapshot of the current processes. Z displays security context.
9. restorecon
•  primarily used to set the security context (extended attributes) on one or more files.
•  It can also be run at any other time to correct inconsistent labels, to add  support  for newly-installed policy or, by using the -n option, to passively check whether the file contexts are all set as  specified  by the active policy (default behavior).
10. semanage
•  SELinux Policy Management tool
11. semodule
•  manage SELinux policy modules, including installing, upgrading, listing and removing modules.
12. sestatus
•  get the status of a system running SELinux. It displays data about whether SELinux is enabled or disabled, location of key  directories, and the loaded policy with its status
13. setenforce
• Use Enforcing or 1 to put SELinux in enforcing mode.
• Use Permissive or 0 to put SELinux in permissive mode.
14. setsebool 
• sets  the current state of a particular SELinux boolean or a list of booleans to a given value. The value may be 1 or true or on  to enable the boolean, or 0 or false or off to disable it.
• If the -P option is given, all pending values are written to the policy file on disk. So they will be persistent across reboots.

The default configuration file is found in;
/etc/selinux/config

Tutorial on File with Selinux Enforcing - part 1

While deploying an application to a production Centos Linux 7 server, there were some problems relating to not being able to read folder. Even when it was set to full read and write by everyone (or in Linux terms 777). I won't go into details of Selinux security as its available in modern Linux systems, but just matters related to allowing access of the restricted folder.

Note: The title looks grammatically wrong, but the term is "enforcing" is correctly used here.
These commands are here for reference to my guided tutorials.

Problem

In a basic Linux system, each file and folder (or directory) is allowed the access by the owner, group or others. And this access is further controlled into permissions of being able to read(r), write(w) and executable(x). I will refer to the term file to include folders unless mentioned otherwise. Users and programs can change owners and permission of files, if they have the permission to do so. The system typically divides the users into 2 groups;

1. root = Linux Administrator
2. user = Every other user who is not an administrator

If everyone and every program can change their own settings, eventually there will be a chance of creating security hole. Common approaches for security includes (1) restrict user to certain file permissions (use of groups are common) and (2)processes are further restricted in a "chroot" jail. Even with all this, the root user will have trouble controlling breaches due to user file permissions as these users still can change their file permission to allow everyone.

There are 2 approaches to change SELinux policies, here I will be looking at compiling the policy as a module.

SELinux basics

A set of file policies is introduced in Linux where every file is denied and a series of exceptions to access files is built is what makes SELinux work. All logs are by default to /var/log/audit/audit.log and where it may be in any one of the following modes;

1. Enforcing - policies are enforced
2. Permissive - policies are logged
3. Disabled 

View the SELinux status with commands such as;

• sestatus
• getenforce 

Try these Example:

Enable SELinux

 $ setenforce 1
 $ getenforce 
 Enforcing

Disable SELinux but still continue logging

 $ setenforce 0
 $ getenforce
 Permissive

These policies are defined for files and processes (yes, running programmes) and each are known as "security context". These can be of scontext (source context) or tcontext (target context). Here are ways you can display security context of;

1. Files. ls -Z
2. Processes. ps auxZ

Example.

A security context have the format

user : role : type : mls


Where
system_u:object_r:httpd_sys_content_t:s0

• user - run as user (e.g. system_u)
• role - run as this role (e.g. object_r)
• type - domain type to run (e.g. httpd_dyd_content_t)
• mls - multi-level security, its hidden if not in use. (Lowest security level with no compartments e.g. s0, s0.c0.c5 to show )

The tool semanage allows configuration of the security. Example, to display list of user for security context;

Security contexts as stored as modules in /etc/selinux/targeted/active/modules.

Try these examples;
Compare the different security context results for folder /bin, /home, /media, /etc, /usr, /var, /tmp and /sbin;

 $ ls -Z /
system_u:object_r:bin_t:s0       bin -> usr/bin
system_u:object_r:etc_t:s0       etc
system_u:object_r:home_root_t:s0 home
system_u:object_r:mnt_t:s0       media
system_u:object_r:bin_t:s0       sbin -> usr/sbin
system_u:object_r:tmp_t:s0       tmp
system_u:object_r:usr_t:s0       usr
system_u:object_r:var_t:s0       var

Pre-requisites

Install policy makefile, audit2allow and audit2why

$ sudo yum install policycoreutils-python
$ sudo yum install policycoreutils-devel

The audit2allow will retrive data from /var/log/audit/audit.log and list existing rules with

$ sudo audit2allow -a

Step 1. Investigation

In this example, the application nrpe is not running as it should on a SELinux enabled system. I could start the nrpe process but the output did not work as expected. The tool ausearch and journalctl  are used to list the messages during start of a process, where the command is systemctl start nrpe.

Its found that the folder could not be open for reading. Listing with the command ls -l showed that it has the correct permission but when ls -Z, then it is seen that there isn't enough access. The process nrpe is by the user nrpe, not nagios. The audit2why is then used to translate the problem into an easier to understand term.

$ sudo ausearch -m avc -c nrpe

$ sudo ausearch -m AVC,USER_AVC,SELINUX_ERR -ts today

$ sudo journalctl -xe

$ ls -l
$ ls -Z

$ ps aux | grep nrpe

$ sudo grep "nrpe" /var/log/audit/audit.log |audit2why

The file localhost.cfg cannot be open for reading

scontext=system_u:system_r:nrpe_t:s0 tcontext=unconfined_u:object_r:nagios_etc_t:s0

The folder nrpe.d cannot be accessed

scontext=system_u:system_r:nrpe_t:s0 tcontext=unconfined_u:object_r:nagios_etc_t:s0 

Based on what I have covered before, it can be seen that each have a source context and a target context with their security as user:role:type:mls defined.

Check what context does these nrep and nagios have

$ sudo semanage fcontext -l |grep nagios
/etc/nagios(/.*)?                                  all files          system_u:object_r:nagios_etc_t:s0
/etc/nagios/nrpe\.cfg                              regular file       system_u:object_r:nrpe_etc_t:s0

The problem is, nrpe is an application that listens to requires from a remote server to execute Nagios plugins. At NRPE start up, it needs to access customised configuration files located at /etc/nagios/nrpe.d folder.

NRPE, poor thing doesn't have access to sub-folders of /etc/nagios but nagios does.

Step 2: Action

Get the tool audit2allow to generate the required policy.

$ sudo audit2allow -i /var/log/audit/audit.log

#============= nrpe_t ==============
allow nrpe_t nagios_etc_t:dir read;
allow nrpe_t nagios_etc_t:file { getattr open read };


Looks like this will allow nrpe to access nagios_etc context type. Generate the required policy and view the policy in local.te

$ sudo audit2allow -i /var/log/audit/audit.log -M local
$ cat local.te

Install the generate local.pp as a module

$ sudo semodule -i local.pp

Check the security policy again and notice that nrpe_t now have access to same type as nagios_etc_t

Stop, and start NRPE process. Check with the log indicate that the error is gone.

$ systemctl stop nrpe
$ systemctl start nrpe
$ journalctl -xe



References:
https://wiki.centos.org/HowTos/SELinux#head-faa96b3fdd922004cdb988c1989e56191c257c01
https://opensource.com/article/18/7/sysadmin-guide-selinux