var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Grants to a view
I have a user called S_KFC. This user has the role S_USER granted.
I have a DBA-user called VDS_VALIDATE which owns a view called TJEK01.
I have granted SELECT to both the user and the role
The view TJEK01 is based on a table DOKUMENTLINK, which I have also granted SELECT privileges to both the user and the role.
BUT the user gets the message ORA-01031: Insufficient privileges
I have a lot of users and views, so I need to find out how to solve the problem.
Check post #3 in
Perhaps it will not answer your question, so - let me try to add a few more words: if this user received an error message while working in PL/SQL, privileges received through roles do not apply in PL/SQL (in SQL they will work fine). Therefore, grant required privileges directly to the user (not through the role).
Last edited by Littlefoot; 09-11-06 at
I don't see the solution. Let be try to be more specific by describing the SQL:
CREATE OR REPLACE VIEW TJEK01
select * from dokumentlink where dead_link = 'T'
create or replace public synonym tjek01 for vds_validate.tjek01;
grant select on tjek01 to S_KFC;
grant select on tjek01 to S_USER;
select count(*) from tjek01;
ORA-01031: insufficient privileges
What is the result of (when connected as S_KFC)
SELECT * FROM dual;
select * from dual;
I see; obviously, my guess (that S_KFC needs SELECT ANY TABLE privilege) was wrong. Does it need any other privilege? Because, your scenario seems to work correctly on my 8i:
SQL> connect mig/mig@ora8i
SQL> create view v_zupanije as select * From zupanije;
SQL> create public synonym ps_zupanije for v_zupanije;
SQL> grant select on ps_zupanije to mig_jedan;
SQL> connect mig_jedan/mig_jedan@ora8i
SQL> select count(*) from ps_zupanije;
Third user involved
Actually there is a third user involved VDS. This user owns the table DOKUMENTLINK.
It seems to be the problem, because I can make a copy of the table in the VDS_VALIDATE scheme, and can afterwards grant the necessary rights with success.
But how shall I solve that problem?
Perhaps by including the WITH GRANT OPTION while granting privileges from VDS to VDS_VALIDATE?
WITH GRANT OPTION solved the problem