Postgresql – Grant access to all tables of a database

To grant privilege to create a view:

GRANT CREATE VIEW TO STD01;

To grant the DML privileges:

GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;

But that's not all of the object privileges. If you did:

GRANT ALL ON STD00.CUSTOMER TO STD01;

you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.

I've had to solve this specific problem and realized there's not way to say "all tables except these".

What I did to work around this was create an internal database, restricted_tables.

It had a couple of simple table schema:

CREATE TABLE `tables` (
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `authorized_users` (
  `authorization_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` char(16) NOT NULL,
  `table_schema` char(64) NOT NULL,
  `table_name` char(64) NOT NULL,
  `authorization_ticket` char(16) NOT NULL,
  `authorization_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `authorization_expiration` date DEFAULT NULL,
  PRIMARY KEY (`authorization_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The tables table you see is just a pair of table schema (db name) and table name you wish to exclude for the "everything but these" list.

The authorized users tables came in a second iteration to allow certain users access to otherwise default restricted tables. Some other columns there include a ticket column which is a reference to our internal ticketing system for audit trail purposes, and a couple of date columns for create and expire.

I then have a user maintence script which runs a query like

$q="select i.table_schema, i.table_name from information_schema.tables i left outer join
             restricted_tables.tables r using(table_schema, table_name)
                                                 where
            i.table_schema not in (".buildEscapedList($db,$excludedDbs).") and r.table_schema is null
        union
        select table_schema, table_name from restricted_tables.authorized_users where user=?";

(This is a perepared statement written in PHP if that helps). The $excludedDbs was just a list of schemas to completely ignore like mysql, information_schema, etc.

It then starts by running a "revoke all privileges" for the user (this just leaves the usage user@host identified by '' entry remaining.

It then loops through and grant select on every table getting returned by that query.

This effectively implements your everything but these

Things that suck

When a new table is added it's not explicitly included for users as a db.* would be. To mitigate that a version of the script runs on a cron to periodically readd grants to these users.

It makes looking at a show grants for user@host a little daunting to quickly grok what a user does or doesn't have access to (but nothing a little grep can't solve)

Known limitations

The script was purpose built for a scenario where accounts being managed by this would only need select access so there's no provisioning for various other CRUD privs per table

Alternate Solution

If you have a subset of tables in mydb that are restricted then move only those tables into a mydb_restricted databases. Then only can keep db.* style grants, giving mydb_restricted.* access to the privileged accounts.