When working with a database that has multiple users, it is important to properly handle various database permissions. Obviously, most databases have an administrator user, but does it always make sense to run your queries as the administrator? Additionally, would you want to provide all your junior developers and users with your administrator credentials in order to write their queries? Most likely not. The various possible permissions for your database depend on your DBMS, but there are common themes between them.
In MySQL, for example, typing “SHOW TABLES” will reveal a list of tables on your database, of which you will likely notice a ‘user’ table. Typing ‘DESC user’ will reveal that there are various fields on the user table. Along with a host, username and password, there is also a list of privileges that can be set for a user. Additionally, there is a ‘db’ table that governs more privileges for a specific database.
SQL Server provides the GRANT, DENY, and REVOKE statements to give or take away permissions from a user or role. Additionally, SQL Server provides roles such as db_writer, db_reader. Often, unknowledgeable developers grant these roles (as opposed to creating their own, custom roles) to other users, resulting in overall lowered database security, as well as the possibility of a user performing an unwanted operation.
Properly managing your database user permissions is essential to managing not only security, but also providing a foundation for faster development and protecting data integrity.