Creating and Managing Database Users
Creating new users in SQL database and managing existing database users indicates who is using database features and functions with how much privilege in the existing SQL database. In which database users have appropriate access to your database, in which database user roles, multiple levels of database user privileges exist based on database user security requirements and best practices.
So let’s understand these tasks in SQL database in a better way.
For example, in multiple database systems like MySQL, PostgreSQL and SQL Server, you can manage database user creation, database user permissions, management and maintenance.
Creating new users in SQL database and managing existing users is necessary to control user access and privileges to a particular database. So let’s do the features and controls like creating privileged access to users in SQL database, managing and restricting database user rights and assigning database user rights.
Creating new database users in a SQL database.
To create a new database user in a SQL database, you use the CREATE USER SQL statement command.
Here is an example of a new database user creation process in SQL.
Creating a new user in a SQL database.
CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
- ‘username’ – The name of the new user to be created in the existing SQL database.
- ‘host’ – The host location in the existing database from which the user can connect to control and manage the database privileges and rights. Here ‘%’ is used for any host.
- ‘password’ – The password of the user account to be created in the existing database in SQL.
For example here, you have created a user harry with the new database user password testpassword, who can connect from any host.
CREATE USER ‘harry’@’%’ IDENTIFIED BY ‘testpassword’;
Granting permissions to a database user.
After creating a new user in SQL database, you need to provide specific privileges to the database user to perform database activities operations inside the database. Use GRANT SQL command statement to assign database user privileges.
Granting permissions Example.
GRANT SELECT, INSERT, UPDATE ON database_name.* TO ‘username’@’host’;
- SELECT, INSERT, UPDATE – These are specific database user rights or privileges in SQL database which you are providing to the database user, here you can provide different database user privilege rights to multiple users.
- database_name.* – Here you can indicate a particular database or table. Here using the select * asterisk symbol means that all the tables are present inside the existing database.
For example here, user harry is granted read and write privileges to a database named testdb in a SQL database.
GRANT SELECT, INSERT, UPDATE ON testdb.* TO ‘harry’@’%’;
Revoking permissions in a SQL database.
In a SQL database if you need to remove some database user rights or privileges from a particular user, you can use the REVOKE command statement in the SQL database.
REVOKE SELECT, INSERT ON database_name.* FROM ‘username’@’host’;
For example here, user harry is granted read and write privileges to a database named testdb in a SQL database.
REVOKE SELECT, INSERT ON testdb.* FROM ‘harry’@’%’;
Viewing user privileges in a SQL database.
To check what special privileges or rights a created user has in any SQL database, you can use the SHOW GRANTS command statement in a SQL database.
SHOW GRANTS FOR ‘username’@’host’;
Viewing user privileges Example.
SHOW GRANTS FOR ‘harry’@’%’;
Changing a user’s password in a SQL database.
To change a user’s password in an existing SQL database, you need to use the ALTER USER SQL command statement.
ALTER USER ‘username’@’host’ IDENTIFIED BY ‘new_password’;
For example, to change Harry’s password, you need to use the ALTER USER command statement.
ALTER USER ‘harry’@’%’ IDENTIFIED BY ‘newpass’;
Deleting a user in a SQL database.
If you need to delete a user from a database in your existing SQL database, use the DROP USER SQL command statement.
DROP USER ‘username’@’host’;
Example of deleting harry user in an existing SQL database.
DROP USER ‘harry’@’%’;
Managing user privileges for specific databases or tables in a SQL database.
You can grant a particular user privileges to a specific database or table in a SQL database. For example,
Granting full access to a specific database table.
GRANT ALL PRIVILEGES ON mydb. testtable TO ‘harry’@’%’;
Granting only SELECT privileges to a specific database in a SQL database.
GRANT SELECT ON testdb.* TO ‘harry’@’%’;
Flushing privileges in a SQL database.
After creating a user in a SQL database or modifying database user privileges, you may sometimes need to reload specific rights for a database user to indicate that they are immediately effective.
Flush privileges;
Using roles in a SQL database (optional).
Some SQL databases support user roles, which allow you to group database privileges. You can grant multiple permissions to a database role, and then handover the database role to a database user. Here’s an example using MySQL.
CREATE ROLE ‘read_only’;
GRANT SELECT ON testdb.* TO ‘read_only’;
GRANT ‘read_only’ TO ‘harry’@’%’;
These operations in a SQL database provide you with a fundamental process for creating new database users and managing database users. Here you can get more detailed information about the database including database privileges, indicating specific privileges on certain tables, columns, or even specific table rows, depending on the capabilities of the SQL database.
