Thread: Dynamic views/permissions

I'm trying to permit users access to their own records in a database.
A sample would be:

create table logins
(       login char(8),       name char(32),       primary key (login)
);

When a login is added an SQL user is created simultaneously.  Now I
want the user to be able to view her own data:

create function userid() returns text as 'select current_user::text;' language sql;

create view loginview as select * from logins where logins.login=userid();

grant select on loginview to public;
[OR grant select on loginview to <userid>...]

However, a select * from loginview; doesn't return any rows.  Is what
I'm trying not possible at all, or am I just trying to implement it
wrong?

Regards,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F                     It is the mind that moves
 

On Friday 30 May 2003 21:31, Raj Mathur wrote:
> I'm trying to permit users access to their own records in a database.
> A sample would be:
>
> create table logins
> (
>         login char(8),
>         name char(32),
>         primary key (login)
> );
>
> When a login is added an SQL user is created simultaneously.  Now I
> want the user to be able to view her own data:
>
> create function userid() returns text as 'select current_user::text;'
>   language sql;
>
> create view loginview as select * from logins where logins.login=userid();
>
> grant select on loginview to public;
> [OR grant select on loginview to <userid>...]
>
> However, a select * from loginview; doesn't return any rows.  Is what
> I'm trying not possible at all, or am I just trying to implement it
> wrong?

What happens if you define logins.login as VARCHAR instead of CHAR?

See also:
http://www.postgresql.org/docs/faqs/FAQ.html#4.14


Ian Barwick
barwick@gmx.net