0

is there a way to create a new admin user/role that can have access to all the existing tables. If another user create a new table, the admin user should be able to also have permissions to the new table.

Right now, the only way is giving explicitly the list of the tables:

mclient -f "csv" -s "select name from sys.tables where system=false" | xargs -I '{}' mclient -s 'set schema "'$MONETDB_SCHEMA'"; grant select on "{}" to "pm-usecase"'

but if a new table is created by another user, then we need to grant access to the new table again.

Daniel RG
  • 1

2 Answers2

0

We wondered the same thing but the only option is to make a role:

CREATE ROLE test_role;

Create a schema with the role authorisation present:

CREATE SCHEMA new_schema AUTHORIZATION test_role;

Grant the role to the users using the schema:

GRANT test_role TO test_user;

Now if the user test_user uses the set role test_role command in a sql session he will be able to access tables created by other users without having to be granted the privilege.

At the moment there seems to be no blanket option to make a user able to do this in all schema's. A solution could be to create all tables with one specific table role authorization.

Insomniac
  • 639
  • 3
  • 19
Pannekoek
  • 1
0

There is a built-in role called sysadmin:

grant sysadmin to <user>

However, you would still need to set the role on your connection to activate the permissions.

muhmud
  • 4,474
  • 2
  • 15
  • 22