Got it

[GaussDB 200] How to Allow a User to Have Permissions on All Tables in the Schema Highlighted

Latest reply: Nov 22, 2021 02:48:37 790 10 14 0 0

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:

schema

schema

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?

grant usage on schema shemaname to username

grant usage on schema shemaname to username

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:

permission to access the tables

permission to access the tables

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.

schema

schema

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.

shemaname grant

shemaname grant

That's all, thanks!


The post is synchronized to: Huawei Cloud Computing Case

  • x
  • convention:

user_4322891
Created Nov 10, 2021 07:12:30

t_0006.gif

View more
  • x
  • convention:

user_4322891
Created Nov 10, 2021 07:13:01

Thanks for sharing
View more
  • x
  • convention:

little_fish
Admin Created Nov 15, 2021 07:07:20

Thanks.
View more
  • x
  • convention:

olive.zhao
Admin Created Nov 15, 2021 09:06:20

Thanks for your sharing!
View more
  • x
  • convention:

wissal
MVE Created Nov 16, 2021 12:34:01

Detailed explanation
View more
  • x
  • convention:

phuta
Created Nov 16, 2021 14:21:19

Good one
View more
  • x
  • convention:

Sole_Survivor
Created Nov 16, 2021 21:04:50

Great
View more
  • x
  • convention:

Sole_Survivor
Created Nov 16, 2021 21:04:59

.
View more
  • x
  • convention:

user_4358465
Created Nov 21, 2021 14:23:12

Clear & useful post..
View more
  • x
  • convention:

12
Back to list

Comment

You need to log in to comment to the post Login | Register
Comment

Notice: To protect the legitimate rights and interests of you, the community, and third parties, do not release content that may bring legal risks to all parties, including but are not limited to the following:
  • Politically sensitive content
  • Content concerning pornography, gambling, and drug abuse
  • Content that may disclose or infringe upon others ' commercial secrets, intellectual properties, including trade marks, copyrights, and patents, and personal privacy
Do not share your account and password with others. All operations performed using your account will be regarded as your own actions and all consequences arising therefrom will be borne by you. For details, see " User Agreement."

My Followers

Login and enjoy all the member benefits

Login

Block
Are you sure to block this user?
Users on your blacklist cannot comment on your post,cannot mention you, cannot send you private messages.
Reminder
Please bind your phone number to obtain invitation bonus.
Information Protection Guide
Thanks for using Huawei Enterprise Support Community! We will help you learn how we collect, use, store and share your personal information and the rights you have in accordance with Privacy Policy and User Agreement.