Found the answer. It is in this line in the ALTER DEFAULT PRIVILEGES documentation. You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. I was using alter default privileges from a different user than the one creating the tables. Make sure … Read more
You need to do 2 things: firstly, allow access to existing objects; and secondly, set the default access for new objects created from now on. Note that granting access to “TABLES” includes views, but does not include sequences (such as the auto-increment function for “SERIAL” columns), so you’ll probably want to grant access to those … Read more
The answer to your question is No, there’s no way to specify read-only access in the connection string. Alternatives are 1. Create sql user with read permission MVC3 Read-Only MySql Connection String 2. create views or stored procedures with permissions checking logic in them MS SQL Grant permission to only a view MySQL Grant a … Read more
GRANT SELECT ON database1.view1 TO ‘someuser’@’somehost’;
You need the ALTER permission: see the Permissions section here. Note that you can also use a stored procedure with EXECUTE AS, so that the user running the stored procedure does not need to even be granted the ALTER permission.
GRANTs on different objects are separate. GRANTing on a database doesn’t GRANT rights to the schema within. Similiarly, GRANTing on a schema doesn’t grant rights on the tables within. If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can’t access … Read more
A PostgreSQL “schema” is roughly the same as a MySQL “database”. Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.