Directly or via role granted object privileges

Directly or via role granted object privileges

Post by Stjepan Brbo » Wed, 17 Jul 2002 02:00:28



What is the difference between object privileges granted directly to
user or granted to user via it's role (firstly particular object
privilege is granted to role and after that role is assigned to user)? I
ask this because sometimes it is emphasized (in some installations,
creation of repositories etc.) that some privileges should be granted
directly to user, not via role!

--

Stjepan Brbot

 
 
 

Directly or via role granted object privileges

Post by Howard J. Roger » Thu, 18 Jul 2002 08:08:42


I think the big difference is that privileges granted via a role don't work
if the user involved then runs a package or procedure which tries to work on
the object.

In other words, I have a procedure X which says 'update scott.emp....'.

You have the ROLE Y, which includes the 'update on scott.emp' privilege.

You run the procedure, it won't work.

I grant you the 'update on scott.emp' privilege directly, you run the
procedure and now it does work.

Regards
HJR


Quote:> What is the difference between object privileges granted directly to
> user or granted to user via it's role (firstly particular object
> privilege is granted to role and after that role is assigned to user)? I
> ask this because sometimes it is emphasized (in some installations,
> creation of repositories etc.) that some privileges should be granted
> directly to user, not via role!

> --

> Stjepan Brbot


 
 
 

Directly or via role granted object privileges

Post by Sybrand Bakke » Thu, 18 Jul 2002 14:29:49


On Wed, 17 Jul 2002 09:08:42 +1000, "Howard J. Rogers"


>I think the big difference is that privileges granted via a role don't work
>if the user involved then runs a package or procedure which tries to work on
>the object.

>In other words, I have a procedure X which says 'update scott.emp....'.

>You have the ROLE Y, which includes the 'update on scott.emp' privilege.

>You run the procedure, it won't work.

>I grant you the 'update on scott.emp' privilege directly, you run the
>procedure and now it does work.

>Regards
>HJR

So this means, preferably, as best practice, the code should be owned
by the owner of the object, or in 8i and higher, created with authid
invoker.
In both cases there is no need to muck around with direct grants.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

1. direct grant vs. grant via role

hi folks,
i'm searching for differences between direct grants vs. grants via
roles. so far i've the following points:
- perms. for objects for create views have to be direct
- perms. for objects used in stored procs/funcs/triggs have to be
  direct
- grant INDEX,REFERENCES,UNLIMITED TABLESPACE have to be direct
- no grant WITH GRANT OPTION possible via roles
are there any other limitations/pitfalls?
tia
michael

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

2. Q: Not able to connect to certain servers from some clients anymore

3. Granted Role privileges not working

4. update src/interfaces/libpq/zh_TW.po

5. finding privileges granted to roles - Urgent!

6. Help! Password dialog

7. does granting privileges to a role automatically cascade to users

8. pgsql-server/ oc/FAQ_DEV oc/src/FAQ/FAQ_DEV.ht ...

9. Grant object privileges problem

10. Grant all Object Privilege from User to User

11. Grant object privileges problem

12. grant object privilege

13. Granting privileges to objects in a schema