Re: GRANT question [with ROLES]

Thomas:

I think I've figured out why I'm getting different results. If I grant a privilege, like SELECT, to a user, and then revoke it, all works as you say. If I do the same with ROLES, however, it doesn't. For example:

I've created a role, and assigned it a privilege:

CREATE ROLE TEST_ROLE;
GRANT SELECT ON TEST_TABLE TO TEST_ROLE; which works fine. Then I grant that role to a user:

GRANT TEST_ROLE TO TEST_USER; Now I can log on as TEST_USER and do a select on TEST_TABLE. No problem, everything works. If I open a second window, log in as me, and revoke the role:

REVOKE TEST_ROLE FROM TEST_USER;
COMMIT; I can to back to the first window, and still do a select on the table. Logging off and back on fixes it, so that TEST_USER can no longer do that select.

It appears that while Privileges are immediate, ROLES are resolved at connect. So let me rephrase the question:

Is there a way to make granting and revokation of a ROLE effective immediately?

Thanks,

Bob

In article <363b3d01.9233276_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to meyer_b_at_rocketmail.com
> (if that email address didn't require changing)
> On Wed, 28 Oct 1998 15:34:27 GMT, you wrote:
>
> >Ordinarily, if I change the grants I've given someone, that change won't take
> >effect until the next time they log on. Is it possible to force the change
to
> >take effect immediately, even if the user is currently logged on?
> >
>
> thats not right. Consider the following example:
>
> SQL> select * from scott.bonus;
> select * from scott.bonus
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> SQL> l
> 1* select * from scott.bonus
> SQL> /
>
> no rows selected
>
> The only thing that happened between the 1'st and 2'cnd select was that in
> another window, I granted SELECT on BONUS to my account. GRANTS and REVOKES
are
> immediate...
>
> >Thanks much,
> >
> >Bob Meyer
> >
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 28 1998 - 13:46:09 CST