Re: GRANT question [with ROLES]

From: Darryl E Balaski <darryl_e_balaski_at_groton.pfizer.com>
Date: Thu, 29 Oct 1998 15:37:55 -0500
Message-ID: <3638D223.C66@groton.pfizer.com>

Why grant and revoke -- it takes a lot of work to really do security that way with roles plus it is a big security hole.

Have you considered the SET ROLE option?

Chapter 13 of the Oracle7 Server Application Developer's Guide has a good discussion on it.

In a nutshell,

1) Create the roles you desire, grant privs to the role.
2) Grant the roles to the user
3) once the roles are granted, alter use the user so that application

roles are not default roles (enabled at login time) 4) Once the user logs in, use the command to enable role for the current session example:
set role APPLICATION_READONLY;
now the role is enabled for that user's session, if the user is logs into oracle a second time, then the he/she must repeat this step otherwise access will not be granted.

I think this is a far better option than granting and revoking roles on the fly.
1) Less changes to system tablespace data 2) Better and tighter security.

If you reply on a application to grant and revoke roles, what happens is the user is accidently disconnected before your application can issue a revoke command? They next time they log into the database (say with sqlplus) they still will have all privs associated with the role. Also, once the role is granted explicitly to the user, if they log a second time after the grant has been issued (say with sqlplus) then they will get all the role's privs.

With the SET ROLE <role> command, if the user disconnects the session, the privs are automatically reset. If they user tries to log in while in the application, they will not inherit enabled privs (set role enables only per SESSION per USER, NOT simply per USER).

You can protect roles with a password, so only a user who knows the password can enable a role. This is also good in an application.

This is my $.02 worth.

Good Luck,
darryl dB Balaski
Pfizer Central Research

Thomas Kyte 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 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
>
> ----------------------------------------------------------------------------
> 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.
Received on Thu Oct 29 1998 - 14:37:55 CST