user management – Tidbits from the Web

MySQL user management

Create a user that is allowed to log in from anywhere (with or without password ‘123’):

CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
CREATE USER 'testuser'@'%';

Create a user that is allowed to log in only from localhost:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123';

Remove a user:

DROP USER 'testuser'@'%';

Show permissions for a user:

SHOW GRANTS FOR 'testuser'@'localhost';

Grant all privileges on a specific database to a user:

CREATE DATABASE testdb;
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

Grant all privileges on all databasesto a user (optionally with right to grant to other users):

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;

Remove permissions of a user (w.r.t. a specific database):

REVOKE ALL PRIVILEGES FROM 'testuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

Remove permissions of a user globally:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'%';

The following Bash snippet outputs the users of a Unix system sorted by their user id:

cat /etc/passwd | sed 's/:/ /g' | sort -k 3 -n | awk '{print $3 " " $1}'

cat outputs the content of /etc/passwd to standard output
sed replaces all colons (default field separator) with a whitespace
sort orders the lines by the third, whitespace separted field which is the user id
awk prints out only the user name and the correspondent user id, separated by a whitespace

Show one’s own/other’s groups

groups
groups LOGIN

Show all groups on the system (with group id):

cat /etc/group | less

Create new group

groupadd GROUP

Add user to group

usermod -G GROUP -a USER

Assign a specific home directory and shell to a user

usermod -s /bin/bash USER
usermod -d /tmp USER

If the user shall only be allowed to do SCP/SFTP transfers, then an appropriate login shell is scponly.