Fenced User

Fenced User

Post by Choc » Wed, 19 Dec 2001 21:35:04



Hi everybody,

I don't know what it the purpose of the fenced user.

I imagine that it was an account who can run fenced stored procedure on
behalf of the normal user. So, I think we can give more privileges to access
to tables,etc..  to this fenced user, and give restritcted access (
privilege to run stored procedures ) to the normal user.

But my first tests don't seem to go that way.

What is the procedure to :
- Give restricted privilege to a user, just authorizing to run some stored
procedures.
- Give full access to tables to the stored procedures.
Is this a doc somewhere on the web where I can find some information ?

Thx.

Choco.

 
 
 

Fenced User

Post by Dirk » Thu, 20 Dec 2001 03:55:07



Quote:> Hi everybody,

> I don't know what it the purpose of the fenced user.

> I imagine that it was an account who can run fenced stored procedure on
> behalf of the normal user. So, I think we can give more privileges to
access
> to tables,etc..  to this fenced user, and give restritcted access (
> privilege to run stored procedures ) to the normal user.

The when executing a fenced stored procedures you inherit DATABASE
priviledges from the user who calls the stored procedure.

However the SP gets all the OS privileges from the fenced userid. So if you
want to restrict access to to e.g. the file system in an SP, create a fenced
userid and make sure this user cannot write in e.g. anything else than
temporary directories.

Makes sense?

Regards, Dirk

 
 
 

Fenced User

Post by Knut Stol » Thu, 20 Dec 2001 05:09:51





>> Hi everybody,

>> I don't know what it the purpose of the fenced user.

>> I imagine that it was an account who can run fenced stored procedure on
>> behalf of the normal user. So, I think we can give more privileges to
> access
>> to tables,etc..  to this fenced user, and give restritcted access (
>> privilege to run stored procedures ) to the normal user.

> The when executing a fenced stored procedures you inherit DATABASE
> priviledges from the user who calls the stored procedure.

> However the SP gets all the OS privileges from the fenced userid. So if you
> want to restrict access to to e.g. the file system in an SP, create a fenced
> userid and make sure this user cannot write in e.g. anything else than
> temporary directories.

This holds true not only for stored procedures but also UDFs.

Besides the file system access, which essentially allows a not-fenced
routine to access any of the db2 instance owner's files, there is also the
access to the process space.  If your routine is buggy and runs not-fenced,
you can bring down various processes.  Db2 tries to catch as many cases as
possible, but getting it absolutely tight is not possible, or it would
impact performance quite a bit.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab

 
 
 

Fenced User

Post by Dirk » Thu, 20 Dec 2001 09:21:51




> > The when executing a fenced stored procedures you inherit DATABASE
> > priviledges from the user who calls the stored procedure.

> > However the SP gets all the OS privileges from the fenced userid. So if
you
> > want to restrict access to to e.g. the file system in an SP, create a
fenced
> > userid and make sure this user cannot write in e.g. anything else than
> > temporary directories.

> This holds true not only for stored procedures but also UDFs.

> Besides the file system access, which essentially allows a not-fenced
> routine to access any of the db2 instance owner's files, there is also the
> access to the process space.  If your routine is buggy and runs
not-fenced,
> you can bring down various processes.  Db2 tries to catch as many cases as
> possible, but getting it absolutely tight is not possible, or it would
> impact performance quite a bit.

Was it only 99%? ;-)

Dirk

 
 
 

Fenced User

Post by Knut Stol » Thu, 20 Dec 2001 10:48:24






>> > The when executing a fenced stored procedures you inherit DATABASE
>> > priviledges from the user who calls the stored procedure.

>> > However the SP gets all the OS privileges from the fenced userid. So if
> you
>> > want to restrict access to to e.g. the file system in an SP, create a
> fenced
>> > userid and make sure this user cannot write in e.g. anything else than
>> > temporary directories.

>> This holds true not only for stored procedures but also UDFs.

>> Besides the file system access, which essentially allows a not-fenced
>> routine to access any of the db2 instance owner's files, there is also the
>> access to the process space.  If your routine is buggy and runs
> not-fenced,
>> you can bring down various processes.  Db2 tries to catch as many cases as
>> possible, but getting it absolutely tight is not possible, or it would
>> impact performance quite a bit.

> Was it only 99%? ;-)

;-)))

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab

 
 
 

Fenced User

Post by Choc » Thu, 20 Dec 2001 19:22:23


Thx all for your answer...
So the fenced user is used to protect my OS environnement...

Further, I have another question ;o)

Let's imagine that I need to update a table : TABLE1
I create a Stored Procedure to do it, with a user USERSP. With this user,
the SP runs OK.

My goal is that an user NORMALUSER, with no privilege to update TABLE1 can
use the SP to update the table.
Is it possible ?

When I try, I get message : user  NORMALUSER has no UPDATE PRIVILEDGE on
table TABLE1.
And I need to grant update privilege to NORMALUSER to make my SP do the work
!

Thx.
Choco.

 
 
 

Fenced User

Post by Dirk » Fri, 21 Dec 2001 03:58:54



Quote:> Further, I have another question ;o)

> Let's imagine that I need to update a table : TABLE1
> I create a Stored Procedure to do it, with a user USERSP. With this user,
> the SP runs OK.

> My goal is that an user NORMALUSER, with no privilege to update TABLE1 can
> use the SP to update the table.
> Is it possible ?

> When I try, I get message : user  NORMALUSER has no UPDATE PRIVILEDGE on
> table TABLE1.
> And I need to grant update privilege to NORMALUSER to make my SP do the

work

You might be able to do that with embedded SQL where you tell the package to
run as user USERSP. But I don't know much about that.

Any other takers?

Dirk

 
 
 

Fenced User

Post by Knut Stol » Fri, 21 Dec 2001 08:11:45





>> Further, I have another question ;o)

>> Let's imagine that I need to update a table : TABLE1
>> I create a Stored Procedure to do it, with a user USERSP. With this user,
>> the SP runs OK.

>> My goal is that an user NORMALUSER, with no privilege to update TABLE1 can
>> use the SP to update the table.
>> Is it possible ?

>> When I try, I get message : user  NORMALUSER has no UPDATE PRIVILEDGE on
>> table TABLE1.
>> And I need to grant update privilege to NORMALUSER to make my SP do the
> work

> You might be able to do that with embedded SQL where you tell the package to
> run as user USERSP. But I don't know much about that.

That's the VALIDATE BIND option (as opposed to VALIDATE RUN) for the
precompile.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab

 
 
 

1. Fenced User - permissions

I've read that the permissions of the fenced user control the UDF's en
Stored Procedures...

So i think i can say wich UDF's or SP's can be used by it!

De instance-user, can he use UDF's or SP's.  Is it depending on the
permissions of the Fenced User?

How do i change those permissions?

2. Convert characterset on database

3. Fenced User

4. setup wizard problem!! HELP!!

5. Fenced User ID ?

6. B-Trees: aim for something better?

7. Fenced and Not Fenced

8. Problem with delete

9. Oracle 9i Solaris 8 SGA Memory Fencing, Pagins, Optimization

10. SP in C NOT FENCED

11. Fenced UDF

12. question about fence ID on db2

13. Not Fenced SQL procedure