Re: execute permissions of stored procedures? - Mailing list pgsql-general

Oracle has a UNIX-file-system-like set of capabilities for allowing acess to
database objects and granting priviledges.

Users need to be granted the CONNECT right to connect. Tables and views
created under one user are only visible to that user. To share them, you
need to create a synonym, and then grant INSERT, UPDATE, SELECT or DELETE
rights depending on you're requirements.

A stored procedure is the same, with the execute right. Ditto for sequences,
and need the SELECT and ALTER right.

Finally, there are roles, which are an amalgam of rights. There are all
sorts of predifined roles that you normally grant once and never think about
again.

Some people use them, some don't. We use them, but they were poorly
implemented by a consulting company, and as a result, provide no security,
and big headaches. If you are working on an application where the user
connects directly to the database (client-server) rather than a web-based,
3-tier application, synonyms and rights are more important. But if every
user connects with the same user account via the connection pool in your
middle tier, then their usefulness drops off dramatically.

Yes - Oracle is a good product. Oracle is also an expensive product. We are
looking at $60,000 US for a 2-year licence for Oracle just for our
production servers (not counting Quality Assurance and Development). I'd say
that's worth a little client-side coding to restrict stored procedures, but
what do I know.

David


----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: <eric@datalink.nl>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, February 20, 2002 7:15 AM
Subject: Re: [GENERAL] execute permissions of stored procedures?


> If I understand right, many Oracle applications limit
> data access by users (thereby enhancing security) by
> allowing data access only via procedures.
>
> You appear to be saying that you like this feature,
> and you want it to be available in PostgreSQL.
>
> PostgreSQL has a different method of achieving the
> same result, as has already been pointed out (views
> with appropriate rewrite rules).  If there is not a
> strong desire among PostgreSQL users for the feature
> that you want, that is probably because the existing
> method works well for them.
>
> Remember that PostgreSQL is a project run and
> developed by volunteers.  The strongest vote for a new
> feature is the submission of code.  The developers are
> not insensitive to the requests of users, but: if
> development is left to them, don't be surprised that
> they use their own judgement about what is most
> important.
>
> I have never seen anyone on this list say that Oracle
> is not a good product.  If you want to use Oracle, and
> you (or your customer) have the money to pay, then go
> for it.
>
> --- eric@datalink.nl wrote:
> > On Tue, Feb 19, 2002 at 05:12:42PM +0100, Helge
> > Bahmann wrote:
> > > - if your data integrity and consistency
> > constraints cannot easily be
> > > expressed in relational terms, a relational
> > database may not be the best
> > > tool to solve your problem
> >
> > I don't see a real difference between a grant on a
> > table, view or sequence and
> > a procedure.  Grants have NOTHING to do at all with
> > ANY of the relations in the
> > database, so if this is your point of view the
> > grants shouldn't exist AT ALL.
> > But it seems that nody on this is willing to
> > understand what I want or why I
> > want it, so in one thing you are correct, PostgreSQL
> > is definitely not the
> > databse for me. It is useless to explain something
> > to someone who simply
> > doesn't want to understand you. It seems that I have
> > to use a real database
> > like Oracle after all.
> >
> > Signing off frm this mailinglist.
> >
> > --
> > #!perl #                       Life ain't fair, but
> > root passwords help.
> > # Eric Veldhuyzen
> > eric@terra.telemediair.nl
> > $!=$;=$_+(++$_);($:,$~,$/,$^,$*,$@)=$!=~
> >       # Perl Monger
> > /.(.)...(.)(.)....(.)..(.)..(.)/;`$^$~$/$: $^$*$@$~
> > $_>&$;`
> >
>
> > ATTACHMENT part 2 application/pgp-signature
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - Coverage of the 2002 Olympic Games
> http://sports.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)