Обсуждение: How do I revoke CREATE TABLE and other privileges?
I would like for one role to be able to login, and execute a couple of functions and nothing else. I've tried to revoke access to CREATE on the database, schema, and tablespace but when I tested it, the user was still allowed to create tables. regards,
On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote:
> I would like for one role to be able to login, and execute a couple of
> functions and nothing else. I've tried to revoke access to CREATE on
> the database, schema, and tablespace but when I tested it, the user was
> still allowed to create tables.
From the REVOKE documentation:
Note that any particular role will have the sum of privileges
granted directly to it, privileges granted to any role it is
presently a member of, and privileges granted to PUBLIC.
If PUBLIC still has privileges on the objects then the role still
has privileges, even if you've attempted to revoke them. You'll
probably need to alter the privileges that PUBLIC has, which might
also require altering other roles' privileges to compensate.
--
Michael Fuhr
Michael Fuhr wrote: > On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote: > > I would like for one role to be able to login, and execute a couple of > > functions and nothing else. I've tried to revoke access to CREATE on > > the database, schema, and tablespace but when I tested it, the user was > > still allowed to create tables. > > From the REVOKE documentation: > > Note that any particular role will have the sum of privileges > granted directly to it, privileges granted to any role it is > presently a member of, and privileges granted to PUBLIC. > > If PUBLIC still has privileges on the objects then the role still > has privileges, even if you've attempted to revoke them. You'll > probably need to alter the privileges that PUBLIC has, which might > also require altering other roles' privileges to compensate. > Hi, Revoking PUBLIC worked. I can now login to the database and it will not allow me to create new tables. However when I gave (as postgres) the restricted user permission to execute one function it says it cannot find the function when I try to execute it. regards,
On 6 Jul 2006 09:46:48 -0700 "Karen Hill" <karen_hill22@yahoo.com> wrote: > > Michael Fuhr wrote: > > On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote: > > > I would like for one role to be able to login, and execute a > > > couple of functions and nothing else. I've tried to revoke > > > access to CREATE on the database, schema, and tablespace but when > > > I tested it, the user was still allowed to create tables. > > > > From the REVOKE documentation: > > > > Note that any particular role will have the sum of privileges > > granted directly to it, privileges granted to any role it is > > presently a member of, and privileges granted to PUBLIC. > > > > If PUBLIC still has privileges on the objects then the role still > > has privileges, even if you've attempted to revoke them. You'll > > probably need to alter the privileges that PUBLIC has, which might > > also require altering other roles' privileges to compensate. > > > > Hi, > > Revoking PUBLIC worked. I can now login to the database and it will > not allow me to create new tables. However when I gave (as postgres) > the restricted user permission to execute one function it says it > cannot find the function when I try to execute it. > > regards, > > > ---------------------------(end of > broadcast)--------------------------- TIP 3: Have you checked our > extensive FAQ? > > http://www.postgresql.org/docs/faq Karen, You hopefully just need to edit your search path. See page 56 of the manual for details. Type 'show search_path;' into pgsql and see what the value is currently set to. Then use set to make it include the schema with your function. John Purser -- I must have a prodigious quantity of mind; it takes me as much as a week sometimes to make it up. -- Mark Twain, "The Innocents Abroad"
On Thu, Jul 06, 2006 at 09:55:40AM -0700, John Purser wrote: > "Karen Hill" <karen_hill22@yahoo.com> wrote: > > Revoking PUBLIC worked. I can now login to the database and it will > > not allow me to create new tables. However when I gave (as postgres) > > the restricted user permission to execute one function it says it > > cannot find the function when I try to execute it. > > You hopefully just need to edit your search path. See page 56 of the > manual for details. Type 'show search_path;' into pgsql and see what > the value is currently set to. Then use set to make it include the > schema with your function. The user probably needs USAGE on the schema. If a user has no privileges on a schema then he or she can't access objects in that schema regardless of search_path or the privileges on the objects themselves. -- Michael Fuhr