Help Center > > Developer Guide> Database Security Management> Managing Users and Their Permissions> User Permission Setting

User Permission Setting

Updated at: Jul 15, 2020 GMT+08:00
  • To grant permissions for an object to a user, use GRANT.

    When permissions for a table or view in a schema are granted to a user or role, the USAGE permission of the schema must be granted together. Otherwise, the user or role can only see the names of the objects but cannot actually access them.

    In the following example, permissions for the schema tpcds are first granted to the user joe, and then the SELECT permission for the tpcds.web_returns table is also granted.

    1
    2
    GRANT USAGE ON SCHEMA tpcds TO joe;
    GRANT SELECT ON TABLE tpcds.web_returns to joe;
    
  • Granting a role to a user allows the user to inherit the object permissions of the role.
    1. Create a role.

      Create a role lily and grant the system permission CREATEDB to the role.

      1
      CREATE ROLE lily WITH CREATEDB PASSWORD "Bigdata@123";
      
    2. Grant object permissions to the role by using GRANT.

      For example, first grant permissions for the schema tpcds to the role lily, and then grant the SELECT permission of the tpcds.web_returns table to lily.

      1
      2
      GRANT USAGE ON SCHEMA tpcds TO lily;
      GRANT SELECT ON TABLE tpcds.web_returns to lily;
      
    3. Grant the role permissions to a user.
      1
      GRANT lily to joe;
      

      When the permissions of a role are granted to a user, the attributes of the role are not transferred together.

  • To revoke user permissions, use REVOKE.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel