Обсуждение: User access

Hi,

I have just started looking at roles on a database.

I have a few schemas and would like to  revoke access for a user on
some functions and tables in the schema and grant access to other
functions and tables in the schema.

I have tried to grant the access to the functions but then when that
user tries to call the funciton it says that he does not have access
to the schema. So I've tried to GRANT USAGE on the schema but then the
user have access to all the functions in the schema. So lastly I have
treid to revoke usage from a function but the user can still call the
function.

How should I approach this?

It seems to work fine for tables. To GRANT USAGE on the schema and
then GRANT or REVOKE SELECT on some of the tables.

--
Carel Combrink
s25291930@tuks.co.za

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.

"Carel Combrink" <s25291930@tuks.co.za> writes:
> I have a few schemas and would like to  revoke access for a user on
> some functions and tables in the schema and grant access to other
> functions and tables in the schema.

> I have tried to grant the access to the functions but then when that
> user tries to call the funciton it says that he does not have access
> to the schema. So I've tried to GRANT USAGE on the schema but then the
> user have access to all the functions in the schema. So lastly I have
> treid to revoke usage from a function but the user can still call the
> function.

The default privileges on functions include public execute access.
To restrict usage of a function that's in an open schema, you'd need to
REVOKE EXECUTE ... FROM PUBLIC, then grant execute privilege to just the
people who should have it.

            regards, tom lane