473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

insert through function only

How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
8 1597
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@cs.ucsd.edu> wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


You need to use SECURITY DEFINER so that the function runs with the
access of its definer instead of its invoker.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@cs.ucsd.edu> wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


You need to use SECURITY DEFINER so that the function runs with the
access of its definer instead of its invoker.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3
On Mon, May 03, 2004 at 03:12:00PM -0700, Marvin McNett wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


Was the function created with SECURITY DEFINER?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #4
On Mon, May 03, 2004 at 03:12:00PM -0700, Marvin McNett wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


Was the function created with SECURITY DEFINER?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
Thanks Bruno,

This is exactly what I needed to know.

Cordially,
Marvin

Bruno Wolff III wrote:
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@cs.ucsd.edu> wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.

You need to use SECURITY DEFINER so that the function runs with the
access of its definer instead of its invoker.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6
Thanks Bruno,

This is exactly what I needed to know.

Cordially,
Marvin

Bruno Wolff III wrote:
On Mon, May 03, 2004 at 15:12:00 -0700,
Marvin McNett <mm*****@cs.ucsd.edu> wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.

You need to use SECURITY DEFINER so that the function runs with the
access of its definer instead of its invoker.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7


On Mon, 3 May 2004, Marvin McNett wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


Maybe something along these lines?

Have the function grant insert to the user prior to inserting & revoke it
afterwards. The user will have generic insert access while the function is
running, but if this is a problem, have the function lock the table
during the insert operation, then revoke the insert permision before
unlocking the table.

Brent Wood
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #8


On Mon, 3 May 2004, Marvin McNett wrote:
How do I go about ensuring that data is only added to a table through a
function? I've tried granting execute persission on the function which
inserts data, but can't get it to work unless the user also has insert
permission on the table. I don't want the user to be able to
arbitrarily insert data.


Maybe something along these lines?

Have the function grant insert to the user prior to inserting & revoke it
afterwards. The user will have generic insert access while the function is
running, but if this is a problem, have the function lock the table
during the insert operation, then revoke the insert permision before
unlocking the table.

Brent Wood
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jason | last post by:
How does one loop through the contents of a form complicated by dynamic construction of checkboxes which are assigned a 'model' and 'listingID' to the NAME field on the fly in this syntax:...
2
by: george | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, on an NT box, Active Server pages with Javascript, using ADO objects. ...
8
by: Johannes A. Brunner | last post by:
Got a simple problem. I code some site and because Im a freak I made my own session-handling. When a user open up my site it will check if there is a ssid in the url if not generate one. this will...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
0
by: ImraneA | last post by:
Hi there I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K. Wish to provide some knowledge gained back to community - hopefully help others. 1.Question how do you test stored...
3
by: Andrew Clark | last post by:
*** post for FREE via your newsreader at post.newsfeed.com *** it's been a while since i have poseted to this newsgroup, but for a long time i was not programming at all. but now that i am out of...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.