| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: GRANT question [with ROLES]
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 19:46:09 GMT, you wrote:
>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?
>
No not really, this is the defined, expected behaviour. From chapt 20 in the admin guide:
<quote>
When Do Grants and Revokes Take Effect?
Depending on what is granted or revoked, a grant or revoke takes effect at different times:
All grants/revokes of system and object privileges to anything (users, roles, and PUBLIC) are immediately observed.
All grants/revokes of roles to anything (users, other roles, PUBLIC) are only observed when a current user session issues a SET ROLE statement to re–enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.
</quote>
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
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. Received on Thu Oct 29 1998 - 09:14:48 CST
 |
 |