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

Enhance Apache and Lighttpd on Centos Linux: increasing file descriptors limit

Centos Linux servers typically have the maximum file descriptors at 1024. Other Linux distributions may have the same settings, but what is this file descriptor and how to increase the value? Please use linux man pages to get further details of the commands in this document. These are information for educational purposes.


Some web servers that have to work harder, may need to multitask more than others. This is when there will be warnings in /var/log/messages related to

can't have more connections than fds/2:  1024 1024

or Apache error logs showing

ulimit:error setting limit(Operation not permitted)


Another situation is when a software runs into an infite loop of process, such that is increase resource usage until the server comes to a complete stand still. Causing no other users to have access to the server. On a production server, this would be a disaster. What is really affected by these limits? Its the application performances such as database and web servers that are under heavy usage. Typical linux terminal users wont really need to concern with the limits.


View limits
Users can view file limits with the command;

$ ulimit 
$ ulimit -aH

Value of unlimited is common for development servers.

To view limit for current BASH open files of the user;

$ ulimit -n

To view max user processes

$ ulimit -Hn
$ ulimit -u

To view limits of any running process, identify the process ID ($PID) then run command;
$ grep 'open files' /proc/{$PID}/limits

Replace {$PID} with the PID to be checked.

Any user can reduce their file descriptor limit. This is useful to test problematic applications. Example to reduce to 1024.

$ ulimit -u 1024


Configuration file sysctl
This sysctl.conf and files in /etc/sysctl.d is an interface that allows you to make changes to a running Linux kernel. This file is extensively configured to harden a production Centos Linux server. Example, to reboot the linux after a kernel panic, the following values is used;

kernel.panic=10

With /etc/sysctl.conf you can configure various Linux networking and system settings. To view current values;

$ sudo sysctl -A
$ sudo sysctl fs.file-max
fs.file-max = 994255

Centos Linux

Check the current value of the file descriptor

$ ulimit -aHS
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 39253
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 4096
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

To view system configured default limit on file descriptors.

$ cat /etc/security/limits.d/20-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     4096
root       soft    nproc     unlimited


Here, the plan is to increase limits to 65530.

Change the hard and soft limits in configuration file.

Edit /etc/security/limits.conf with these values. The * refers to any user.

* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

In cases where the fs.file-max is below the 65535 value, then it can be set by editing /etc/sysctl.conf

fs.filemax = 65535

In both cases above, apply changes with the command;

$ sudo sysctl -p

Lighttpd

Edit /etc/lighttpd/lighttpd.conf

server.max-fds = 2048

Apache Httpd

The Centos, this document referring is with httpd version 2.4.6 (CentOS) and is using httpd compiled as preforked (non threaded).

Edit the file /etc/httpd/conf/httpd.conf

<IfModule prefork.c>
StartServers       4
MinSpareServers    3
MaxSpareServers   10
ServerLimit      256
MaxClients       256
MaxRequestsPerChild  10000
</IfModule>

Refer details at Apache.org

Selinux

When selinux is in mode Enforcing, it needs to allow changes. Check the status with

$ sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          permissive
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      31

$ getsebool httpd_setrlimit
httpd_setrlimit --> off

Temporary turn it on, then later view the status again;

$ setsebool httpd_setrlimit on

To make this change permanent;

$ sudo setsebool -P httpd_setrlimit on

MYSQL database

Edit /usr/lib/systemd/system/mysqld.service

LimitNOFILE=65535
LimitNPROC=65535

Edit /etc/my.cnf

table_open_cache=65535
open_files_limit=65535


Apply the changes;

$ sudo systemctl daemon-reload
$ sudo systemctl restart mysqld.service