Removing access

1. Removing access

Having seen how to create roles and provide them with certain privileges, we will now see how to remove privileges.

2. Example: rolling back privileges

Your cousin, a promising Computer Science student is visiting. Over dinner, you mention the work that you have been doing in maintaining a personal database for your finances. Your cousin who has not had practical experiences with databases is curious to learn more. You give him access to the PostgreSQL command line and go to make a phone call. You forgot that you were logged in as the superuser and in the 15 minutes that you were gone, your cousin managed to delete all of the data in your database. You don't panic because you had recently backed everything up. But if you are going to let him continue to use the database, you realize you need to take more care with his database access. Its been a busy day and you decide to create a new account for him and add this user account to the "family" group. You use the GRANT ALL PRIVILEGES command that you recently learned about believing that it will save time in setting up a new user. Then you go to sleep. The next morning, he has managed to delete all of the finance data again. Realizing that you need to be more careful, you set out to work to restrict your cousin's access to the database.

3. Example: rolling back privileges

Most concerned with data being removed from the database, you begin to take away privileges from your cousin's account. PostgreSQL provides a command which can be used to REVOKE previously granted privileges. REVOKE is used analogously to GRANT. For instance, to remove the cousin user's ability to DELETE records and TRUNCATE tables, the REVOKE command displayed here can be used. After executing this command, the user will no longer be able to remove any data from the finances database.

4. Example: rolling back privileges

After further consideration, it occurs to you that your cousin should not be inserting new data into this database; being able to read from this database using select statements is sufficient for learning purposes. You decide to start the privilege granting process from the beginning. REVOKE ALL PRIVILEGES resets all privileges for a user for a database object. The command displayed here would remove all privileges for the cousin user for every table in the finances database. The SELECT privilege can then be restored for the "cousin" user. REVOKE can also be used to remove users from groups. You decide to use this command to remove the cousin user from the family group to enforce the restricted privileges that were just applied. Now, you can feel confident that your finance data will finally be safe from your cousin's experimentation.

5. Let's practice!

Now that you have seen how to undo the granting of privileges and group membership, it is time to practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.