Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Revoking Privileges from User

Hello,
Revoking privilege of a user as DBADMIN.

SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee like 'hs6834IU';

GRANTOR privileges_description object_schema object_name grantee
dwh_replication SELECT dwh d_shipping_provider hs6834IU

revoke select on table dwh.d_shipping_provider from hs6834IU;

But still when i run query again it shows-
dwh_replication SELECT dwh d_shipping_provider hs6834IU

Also i tried revoke all privileges but no luck.What am i missing?

Answers

  • Nimmi_guptaNimmi_gupta Employee
    edited March 18

    @SK21
    What's the vertica version?
    Check the below example and it's working fine in V10.x
    dbadmin=> select version();
    version
    Vertica Analytic Database v10.0.1-7

    dbadmin=> CREATE USER tom;
    CREATE USER
    dbadmin=> GRANT CREATE, USAGE ON SCHEMA PUBLIC to tom;
    GRANT PRIVILEGE
    dbadmin=> GRANT SELECT, INSERT, UPDATE ON TABLE public.test TO tom WITH GRANT OPTION;
    ROLLBACK 4912: Table/View with name 'test' does not exist
    dbadmin=> GRANT SELECT, INSERT, UPDATE ON TABLE public.test1 TO tom WITH GRANT OPTION;
    GRANT PRIVILEGE
    dbadmin=>
    dbadmin=> select * from grants where grantee ilike 'tom';
    dbadmin=> select * from grants where grantee ilike 'tom';
    grantor | privileges_description | object_schema | object_name | object_type | grantee
    -------------------+-------------------+---------+---------------------------+---------------+-------------+----------
    dbadmin | USAGE | | general | RESOURCEPOOL | tom
    dbadmin | USAGE, CREATE | | public | SCHEMA | tom

    dbadmin | INSERT*, SELECT*, UPDATE* | public | test1 | TABLE | tom

    (3 rows)
    dbadmin=> \c - tom
    You are now connected as user "tom".
    dbadmin=> select count(*) from test1;
    count
    3
    dbadmin=> \c - dbadmin
    You are now connected as user "dbadmin".
    dbadmin=> REVOKE SELECT ON test1 FROM tom;
    REVOKE PRIVILEGE
    dbadmin=> select * from grants where grantee ilike 'tom';
    dbadmin=> select grantor, privileges_description, object_schema, object_name, object_type, grantee from grants where grantee ilike 'tom';
    grantor | privileges_description | object_schema | object_name | object_type | grantee
    ---------+------------------------+---------------+-------------+--------------+---------
    dbadmin | USAGE | | general | RESOURCEPOOL | tom
    dbadmin | USAGE, CREATE | | public | SCHEMA | tom

    dbadmin | INSERT*, UPDATE* | public | test1 | TABLE | tom

    (3 rows)

    dbadmin=> \c - tom
    You are now connected as user "tom".
    dbadmin=> select count(*) from test1;
    ERROR 4367: Permission denied for relation test1
    dbadmin=>

  • Hello @Nimmi_gupta the version is vertica-9.0.1-8. Seems like a bug in this version?
    Also in my case orignal grantor was another user ie dwh_replication.
    And i revoked it with DBADMIN user.---revoke select on table dwh.d_shipping_provider from hs6834IU;
    But i still saw these result when i checked privileges again after revoking-
    SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee like 'hs6834IU';

    GRANTOR privileges_description object_schema object_name grantee
    dwh_replication SELECT dwh.d_shipping_provider hs6834IU

  • Yes that could be a bug in that version. After revoking is that user still able to run the select query on that table? Can you check?
    or just the record not getting removed from the grants table?

  • Ok will check and update.

  • I ran the another test on the same version you are and I don't see any problem with revoke command. Provide the steps would be helpful.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.