Granted Role privileges not working

Granted Role privileges not working

Post by michael.cook » Sat, 13 Jul 2002 07:00:16



Currently running Oracle 7.3.3 NT4 SP3
Developer 2000 Forms 4.5 reports 2.5
have come across aa couple of occurences where table privileges will not
work in a role only when granted directly to the user.
has anyone else come across this problem? is it documented any where? or is
it likely to be a problem with our application?

thanks for any constructive suggestions

 
 
 

Granted Role privileges not working

Post by Sybrand Bakke » Sat, 13 Jul 2002 07:32:25


On Thu, 11 Jul 2002 23:00:16 +0100, "michael.cooke"


>Currently running Oracle 7.3.3 NT4 SP3
>Developer 2000 Forms 4.5 reports 2.5
>have come across aa couple of occurences where table privileges will not
>work in a role only when granted directly to the user.
>has anyone else come across this problem? is it documented any where? or is
>it likely to be a problem with our application?

>thanks for any constructive suggestions

usenet is flooded with answers on this one

roles are volatile so they are ignored when compiling sp's

regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

Granted Role privileges not working

Post by Vladimir M. Zakharyche » Tue, 16 Jul 2002 18:45:29


This IS documented in Oracle documentation (I believe it's in PL/SQL Developer's
Guide) - roles are disabled when compiling stored procedures. There's no way
around this on 7.3. 8i (and 8.0?) added invoker rights stored procedures (aka
AUTHID CURRENT_USER), for which roles are active when they are executed
(but still disabled when they are initially compiled.) This question in various
forms pops up approximately once a week here, so it's a common problem and
I believe every Oracle FAQ has an answer to it. :)

--

Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


Quote:> Currently running Oracle 7.3.3 NT4 SP3
> Developer 2000 Forms 4.5 reports 2.5
> have come across aa couple of occurences where table privileges will not
> work in a role only when granted directly to the user.
> has anyone else come across this problem? is it documented any where? or is
> it likely to be a problem with our application?

> thanks for any constructive suggestions

 
 
 

1. finding privileges granted to roles - Urgent!

Hi,

We are having a permissions problem. There are two database instances on

separate machines, which are the supposed to be the same structure.

One instance works and one doesn't

To find out what is wrong I need to identify and compare two databases
in
each database

I need to identify what objects these roles have been granted priviliges
on
(select, insert, update,delete) and then compare them.

I propose a sql statement spooled to a file which would list each role
and
each object that it has privileges on.

The at the unix level diff the files.

Can any one help me with the correct SQL statement to gather the info I
need?

Oracle is  7344
OS is Digital unix V4.0e

TIA

Brian

2. Help with string -> date conversion

3. Directly or via role granted object privileges

4. ADO vs DAO and dbSQLPassthrough

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

6. Problems with running the Oracle Application Server 4.0.7

7. privileges won't work through a Role

8. direct grant vs. grant via role

9. Difference between grant to user and grant to role

10. Roles are not granted in PL/SQL ???

11. Default Roles vs Granted Roles

12. Possible Bug - Grant Object Permissions by User and Group Not Working