Request a Demo
22.4. Granting and Revoking Privileges (metastore.mgrant, metastore.mrevoke)
Prev UpChapter 22. Access ManagementHome Next

22.4. Granting and Revoking Privileges (metastore.mgrant, metastore.mrevoke) #

Required privileges:

  • For the metastore.mgrant stored procedure: GRANT privilege on the proxy table.

  • For the metastore.mrevoke stored procedure: REVOKE privilege on the proxy table.

You can grant and revoke privileges on pgpro_metastore objects using the following commands: 

  SELECT metastore.mgrant(privilege, object_type, object_name, role, with_grant_option true or false);
  SELECT metastore.mrevoke(privilege, object_type, object_name, role, grant_option_only true or false);

Where:

  • privilege: Privilege that is granted or revoked.

    Possible values:

    • ALL

    • INSERT

    • SELECT

  • object_type: The type of the pgpro_metastore object on which the privilege is granted or revoked.

    Possible values:

    • TABLE for analytical tables

    • FOLDER for shared directories

  • object_name: The name of the pgpro_metastore object on which the privilege is granted or revoked.

    Analytical table names are contained in the table_name column of the pga_table metadata table. Shared directory names are contained in the folder_name column of the pga_folder metadata table.

  • role: The role the privilege is granted to or revoked from.

  • (For the metastore.mgrant command) with_grant_option: Specifies whether the role can grant the privilege to other roles.

  • (For the metastore.mrevoke command) grant_option_only: Specifies whether to only revoke the ability to grant the privilege to other roles, leaving the privilege itself.

Example 22.5. 

  • Grant the role user1 the INSERT privilege on the hits analytical table: 

      SELECT metastore.mgrant('INSERT','TABLE','hits','user1');

  • Revoke from the role user1 the INSERT privilege on the hits analytical table: 

      SELECT metastore.mrevoke('INSERT','TABLE','hits','user1');

Alternatively, you can grant or revoke privileges directly on proxy tables of pgpro_metastore objects using the following queries: 

  GRANT privilege ON metastore.proxy_table_name TO role [WITH GRANT OPTION];
  REVOKE [GRANT OPTION FOR] privilege ON metastore.proxy_table_name FROM role;

Where:

  • (For the REVOKE command) GRANT OPTION FOR: Specifies that only the ability to grant the privilege to other roles is revoked, leaving the privilege itself.

  • privilege: Privilege that is granted or revoked.

    Possible values:

    • ALL

    • INSERT

    • SELECT

  • proxy_table_name: The proxy table name.

    Proxy table names are contained in the proxy_table_name column of the pga_proxy_table metadata table. For more information about retrieving proxy table names, refer to Section 22.3.

  • role: The role the privilege is granted to or revoked from.

  • (For the GRANT command) WITH GRANT OPTION: Specifies that the role can grant the privilege to other roles.

Example 22.6. 

  • Grant the role user1 the INSERT privilege on the proxy table associated with the hits analytical table: 

      GRANT INSERT ON metastore.t_hits_00001 TO user1;

  • Revoke from the role user1 the INSERT privilege on the proxy table associated with the hits analytical table: 

      REVOKE INSERT ON metastore.t_hits_00001 FROM user1;

Prev Up Next
22.3. Queries to Proxy Tables Home 22.5. Privilege Management Scenario
epub pdf