Difference between grant to user and grant to role

Difference between grant to user and grant to role

Post by Anker M?lle » Sat, 01 Aug 1998 04:00:00



Hi,

When I grant object priviliges to a user, the user can reference the object
both in SQL and PL/SQL.

When I grant object privliges to a role and grant the role to a user, the
user can reference the object in SQL, but not in PL/SQL.

Can it be right? and why?

--
Kind regards
Anker M?ller
Tele Danmark Internet - Internal Systems


Phone......... +45 89 37 62 86
Fax.............. +45 86 78 38 00

 
 
 

Difference between grant to user and grant to role

Post by Jerry Gitome » Sat, 01 Aug 1998 04:00:00


Hi Anker,

        It's right, but I don't know why.

regards

Jerry


> Hi,

> When I grant object priviliges to a user, the user can reference the object
> both in SQL and PL/SQL.

> When I grant object privliges to a role and grant the role to a user, the
> user can reference the object in SQL, but not in PL/SQL.

> Can it be right? and why?

> --
> Kind regards
> Anker M?ller
> Tele Danmark Internet - Internal Systems


> Phone......... +45 89 37 62 86
> Fax.............. +45 86 78 38 00

--
Jerry Gitomer           ICT Group



 
 
 

Difference between grant to user and grant to role

Post by Arjan van Bente » Sat, 01 Aug 1998 04:00:00


In fact, anonymous PL/SQL blocks can use the grants they got through a role.
However, for packages, this is not true.

It has to be that way, due to some early binding Oracle performs when
converting your PL/SQL code to P-code. So, Oracle performs the security
checks at compile time. When a grant or revoke is issued, the last_ddl_time
of the object changes, causing the PL/SQL package to get invalid, and
automatically recompiled at the next run. So, no security problems here.
However, when a role changes, the last_ddl_time of the objects is not
changed, and thus the PL/SQL package does not require recompiling. This way,
possible security issues might exist. Therefore, Oracle requires that you
explicitly grant the rights, not using a role.

Arjan.

 
 
 

Difference between grant to user and grant to role

Post by Oraclegur » Sat, 01 Aug 1998 04:00:00


You probably implied it when you said that you need explicit grants for
packages.

You also need to grant explicit privileges for stored procedures and
functions.

Regards !!!

Oracleguru



Quote:

> In fact, anonymous PL/SQL blocks can use the grants they got through a
role.
> However, for packages, this is not true.

> It has to be that way, due to some early binding Oracle performs when
> converting your PL/SQL code to P-code. So, Oracle performs the security
> checks at compile time. When a grant or revoke is issued, the
last_ddl_time
> of the object changes, causing the PL/SQL package to get invalid, and
> automatically recompiled at the next run. So, no security problems here.
> However, when a role changes, the last_ddl_time of the objects is not
> changed, and thus the PL/SQL package does not require recompiling. This
way,
> possible security issues might exist. Therefore, Oracle requires that you
> explicitly grant the rights, not using a role.

> Arjan.

 
 
 

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. HELP- User Not defined as valid user of trusted SQL Server connection

3. GRANT ROLE TO USER

4. Determine last update of a Sybase table

5. Can't Use User Manager,Grant Manager Role Manger on WebDB

6. How to determine current connect string?

7. query for roles granted to users

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

9. Default Roles vs Granted Roles

10. create proc grant execute with grant

11. Grant access to procedure and DENY/GRANT to view

12. grant update with grant option