Mysql – grant access to user ‘root’ to all databases, but least specific one

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.