Hello, friend!
The post will share with you how to allow a User to Have Permissions on All Tables in the Schema.
For example, an administrator creates a schema and creates a user, but the new user does not have the permission to access the schema, as shown in the following figure:
First, you give the user usage permission so that the user can access the schema.
Reference Statement:
grant usage on schema shemaname to username; (The create permission indicates that users can create objects in this mode. Change the values of shemaname and username based on the site requirements.)
Let's assign permissions to the user in this way and see if they can access the user?
As shown in the following figure, new users still cannot access the schema.
Secondly, grant the query permission on all tables in the current schema to the user.
Reference Statement:
grant select on all tables in schema shemaname to username;(Change the values of shemaname and username based on the site requirements.)
Run the permission statement according to step 2. The new user has the permission to access the tables in the schema：
Users can access all tables in the schema only after the two commands are executed.
However, there is another problem. After the administrator creates a table in the schema, the user does not have permission to access the table.
What should I do now?
And finally, set permissions on objects to be created in the future.
Reference Statement:
alter default privileges in schema shemaname grant select on tables to username (Change the values of shemaname and username based on the site requirements.)
As shown in the following figure, after the permissions applied to the objects to be created in the future are set, the user also has the read permission for the tables created in the current schema.
That's all, thanks!
