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 trueorfalse); SELECT metastore.mrevoke(privilege,object_type,object_name,role, grant_option_only trueorfalse);
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:
GRANTprivilegeON metastore.proxy_table_nameTOrole[WITH GRANT OPTION]; REVOKE [GRANT OPTION FOR]privilegeON metastore.proxy_table_nameFROMrole;
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;