User priviliges do not work as expected
joergschaber
Vertica Customer ✭
Hi,
I have created a role and assigned prviliges and a user to that role. However, the user does not seem to get the role's priviliges:
CREATE ROLE TestAdmin;
GRANT ALL ON SCHEMA public TO TestAdmin;
GRANT All ON ALL TABLES IN SCHEMA public TO TestAdmin;
CREATE USER TestUser IDENTIFIED BY 'test_pw';
GRANT TestAdmin TO TestUser;
-> SELECT grantor, privileges_description, object_schema, object_name, grantee
FROM grants WHERE grantee='TestAdmin' AND object_schema= 'public':
grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------------------------------------+---------------+------------------+-----------
dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES, TRUNCATE | public | Persons | TestAdmin
-> select user_name, all_roles, is_super_user from v_catalog.users where user_name = 'TestUser';
user_name | all_roles | is_super_user
-----------+-----------+---------------+
TestUser | TestAdmin | f
However, when I log in as user 'TestUser', I get
TestUser=> Select * from public.Persons;
ERROR 4367: Permission denied for relation Persons
Any idea, what the problem is?
Best Answer
-
Options
Jim_Knicely
- Select Field - Administrator
If you want the user to always use that role when they log in, you have to set the user's DEFAULT ROLE.
ALTER USER TestUser DEFAULT ROLE TestAdmin;0