Help Center> Data Warehouse Service> Troubleshooting> Account, Password, and Permission> After the Permission for Querying Tables in a Schema Is Granted to a User, the User Still Cannot Query the Tables
View PDF

After the Permission for Querying Tables in a Schema Is Granted to a User, the User Still Cannot Query the Tables

Symptom

After an authorized user runs the grant select on all tables in schema schema_name to B command to grant the permission for querying tables in a schema to user B, user B still cannot access the tables.

Possible Causes

To authorize a user to access table or view objects in a schema, you also need to grant the USAGE permission of the schema to the user. Without this permission, the user can only view the names of the objects but cannot access them.

If you want to grant user B the permission on tables to be created in the schema, run the ALTER DEFAULT PRIVILEGES command to change the default permission.

Handling Procedure

Log in to the database as a user with the schema permission and run the following command to grant the table permission in the schema to a specified user:

1
2
 
GRANT USAGE ON SCHEMA schema_name TO B;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO B;

Run the following command to grant the permission on the tables to be created in the schema to a specified user:

1
 
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO B;

In the preceding SQL statements, GRANT SELECT indicates that the table query permission is assigned. If you want to assign other permissions to other users, see the GRANT syntax description.