Обсуждение: Temporary tables privileges
Hi, I'm new at pg. I'll be using tmp tables in others rdbms. An user can create your own tmp tables (grant temporary tables) but can't drop it (I don't want to grant drop privileges). Other way it's using on commit; but I can't make this work. Example: CREATE TEMPORARY TABLE tmp ON COMMIT DROP AS SELECT code FROM mytable WHERE code BETWEEN 1 AND 10; shows error near ON Thanks. Alejandro.
Alejandro D. Burne wrote: > Hi, I'm new at pg. > I'll be using tmp tables in others rdbms. An user can create your own > tmp tables (grant temporary tables) but can't drop it (I don't want to > grant drop privileges). > Other way it's using on commit; but I can't make this work. > > Example: > CREATE TEMPORARY TABLE tmp ON COMMIT DROP AS > SELECT code FROM mytable WHERE code BETWEEN 1 AND 10; > > shows error near ON Our TODO has: * Add ON COMMIT capability to CREATE TABLE AS SELECT -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Thanks Bruce, then how can I grant an user to create tmp tables and drop then it, without gives him global drop priv (I can't grant drop priv for the tmp table because don't exist yet) Thnx, Alejandro Sorry 4 my english On Fri, 11 Mar 2005 10:52:05 -0500 (EST), Bruce Momjian <pgman@candle.pha.pa.us> wrote: > Alejandro D. Burne wrote: > > Hi, I'm new at pg. > > I'll be using tmp tables in others rdbms. An user can create your own > > tmp tables (grant temporary tables) but can't drop it (I don't want to > > grant drop privileges). > > Other way it's using on commit; but I can't make this work. > > > > Example: > > CREATE TEMPORARY TABLE tmp ON COMMIT DROP AS > > SELECT code FROM mytable WHERE code BETWEEN 1 AND 10; > > > > shows error near ON > > Our TODO has: > > * Add ON COMMIT capability to CREATE TABLE AS SELECT > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 >
Alejandro D. Burne wrote: > Thanks Bruce, then how can I grant an user to create tmp tables and > drop then it, without gives him global drop priv (I can't grant drop > priv for the tmp table because don't exist yet) Perhaps you need a SECURITY DEFINER function. You can set the permissions on the temp schemas too: GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] I have forgotten what you are trying to do. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073