Companies are reluctant to share their data and that’s a good thing. But in many cases we forget that we can grant limited access to our database to our partners and stakeholders. Managing users privileges is often cumbersome, but it can save you a lot of time. How many companies do you know still sending CSV files around?
Here are there are a few simple SQL statements to be able to give access to (and permission to modify) only a few tables. Or better said, what we are doing with these statements it to hide the content tables from new users.
1- Create the new user
2- Create a new schema to move all the existing tables
If you want to move all existing tables in the public schema to the new schema, try this PL/SQL script.
3- Gran permissions to the already existing users to that schema
4- Make sure that new users can’t access the private schema unless permission is granted
4- Make sure users can still find the tables in the new schema
4- Creating new tables
From now, you will only have to be careful when creating new tables. I strongly recommend to use only schema-qualified table names when creating new tables.
Troubleshooting: no permissions to query tables in public schema
Some users seem to have problems querying tables in the public schema after those changes:
ERROR: permission denied for relation public_table_name
If that’s your case, you can solve it with the following queries: