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?
jl66Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
when user B granted userA select, possibly granting it with the ADMIN option.

or

The VERY DANGEROUS "ANY".

I say very dangerous because it is literally ANY in ANY schema.

In other words:
Don't do it!!!

If you need this, have user B create a procedure that does the actual grants and give userA execute on that procedure.

Much safer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
woolmilkporcCommented:
User B:

grant select on sequence1 to A with grant option;

User A.

grant select on B.sequence1 to r1;
0
Mark GeerlingsDatabase AdministratorCommented:
A "normal" Oracle user can *NEVER* grant permission on an object that he/she does not own.  So, user A can never grant permission on any of user B's objects to himself or to anyone else *UNLESS* user B has first granted permission to user A, *AND* included the "with admin option" in the grant to user "A".  Even then, user A cannot grant direct access on user B's objects to someone else.  User A can create a view or a procedure or function that references users B's object(s) then grant select or execute on those objects to others, so they can use user B's objects indirectly then (via user A's objects).

An Oracle DBA account is allowed to grant access on objects that the DBA does not own.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

woolmilkporcCommented:
There is "with GRANT option", not only "with ADMIN option"!
0
slightwv (䄆 Netminder) Commented:
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.
0
jl66Author Commented:
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?
0
Mark GeerlingsDatabase AdministratorCommented:
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?
0
woolmilkporcCommented:
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?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
jl66Author Commented:
--woolmilkporc,
Yes.
"4) You logged out." I logged out from A and B.
0
woolmilkporcCommented:
>> 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?
0
jl66Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.