How to fix Oracle grant errors
Have a question as follows:
sqlplus login as user A, want to grant another user B's sequence to a role, that is,
grant select on b.sequence1 to r1;
Got the error:
ERROR at line 1:
ORA-01031: insufficient privileges
What kind of privilege does user A need to grant another user's sequence to a role?
Please, can any guru shed some light on this?
sqlplus login as user A, want to grant another user B's sequence to a role, that is,
grant select on b.sequence1 to r1;
Got the error:
ERROR at line 1:
ORA-01031: insufficient privileges
What kind of privilege does user A need to grant another user's sequence to a role?
Please, can any guru shed some light on this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
woolmilkporc
There is "with GRANT option", not only "with ADMIN option"!
I just tested it which is what I should have done before I posted. Not sure what I was thinking with the ANY approach.
The "WITH GRANT OPTION" posted by woolmilkporc does appear to work on my 11gR2 database.
The "WITH GRANT OPTION" posted by woolmilkporc does appear to work on my 11gR2 database.
ASKER
Thanks a lot for the tip.
I did "grant select on sequence1 to A with grant option;". It worked. However I exited sqlplus, and login again. The grant disappears. When I ran the sql, I got ORA-01031: insufficient privileges again. The process is repeatable. Why? How to fix it?
I did "grant select on sequence1 to A with grant option;". It worked. However I exited sqlplus, and login again. The grant disappears. When I ran the sql, I got ORA-01031: insufficient privileges again. The process is repeatable. Why? How to fix it?
What worked? To do this: "grant select on sequence1 to A with grant option;" you need to be logged in as the owner of sequence1, which is someone other than user "A" correct?
When you logged in again, who did you log in as? Was that as the owner of sequence1?, Or, was it user A, or someone else?
When you logged in again, who did you log in as? Was that as the owner of sequence1?, Or, was it user A, or someone else?
So you say that:
1) You logged in as user B and issued "grant select on sequence1 to A with grant option" successfully.
2) You logged in as user A and issued "grant select on B.sequence1 to r1;" successfully.
3) You logged in as a user with role "r1" and were able to successfully run a select statement against the said sequence.
4) You logged out.
5) You logged in as user A again and reissued the "grant select on B.sequence1 to r1;" statement (same as step (2) above). This time you got the "ORA-01031: insufficient privileges" error.
Is the above a correct description of your actions?
1) You logged in as user B and issued "grant select on sequence1 to A with grant option" successfully.
2) You logged in as user A and issued "grant select on B.sequence1 to r1;" successfully.
3) You logged in as a user with role "r1" and were able to successfully run a select statement against the said sequence.
4) You logged out.
5) You logged in as user A again and reissued the "grant select on B.sequence1 to r1;" statement (same as step (2) above). This time you got the "ORA-01031: insufficient privileges" error.
Is the above a correct description of your actions?
>>I exited sqlplus, and login again.
I'm not able to reproduce this. I reran the complete test I set up from before and logged out of all three accounts. Logged back in and could still select from the sequence.
I'm not able to reproduce this. I reran the complete test I set up from before and logged out of all three accounts. Logged back in and could still select from the sequence.
ASKER
--woolmilkporc,
Yes.
"4) You logged out." I logged out from A and B.
Yes.
"4) You logged out." I logged out from A and B.
>> I logged out from A and B. >>
You should have used three accounts: A, B and and a third, independent account having role "r1" to test a "select" from "b.sequence1". Did you do that? What was the result?
You should have used three accounts: A, B and and a third, independent account having role "r1" to test a "select" from "b.sequence1". Did you do that? What was the result?
ASKER
Thanks a lot for everyone. I know it does not make sense that it does not work, but for time constraint, I made a comprise. I may revisit the similar issues later.