Revoking Privileges from User

SK21

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?

Nimmi_gupta

@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=>

SK21

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

Nimmi_gupta

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?

SK21

Ok will check and update.

Nimmi_gupta

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.