hello,
i find it difficult that with the creation of a new user in the database, the user automatically gets the 'Public' role assigned.
this automatically seems to grant insert/update-rights on all tables to the new user which i really would like to avoid.
however when revoking 'PUBLIC' from [new user] it can't read the configuration-database anymore ...
what is the best way to deal with this situation?

i would like to give the lowest possible rights by default to a user.

thank you in advance

Gruetzmacher

>hello,
>i find it difficult that with the creation of a new user in the database, the user automatically gets the 'Public' role assigned.
>this automatically seems to grant insert/update-rights on all tables to the new user which i really would like to avoid.
>however when revoking 'PUBLIC' from [new user] it can't read the configuration-database anymore ...
>what is the best way to deal with this situation?
>
>i would like to give the lowest possible rights by default to a user.

I don't know a way to prevent a user being created with Public but I do know

REVOKE ALL PRIVILEGES ON DATABASE yourdb FROM PUBLIC

which should have the same effect

Roy Lambert

thank you roy!
i will try this out ... pretty sure i didn't try it that way.
would be great


Roy Lambert wrote:

Gruetzmacher

>hello,
>i find it difficult that with the creation of a new user in the database, the user automatically gets the 'Public' role assigned.
>this automatically seems to grant insert/update-rights on all tables to the new user which i really would like to avoid.
>however when revoking 'PUBLIC' from [new user] it can't read the configuration-database anymore ...
>what is the best way to deal with this situation?
>
>i would like to give the lowest possible rights by default to a user.

I don't know a way to prevent a user being created with Public but I do know

REVOKE ALL PRIVILEGES ON DATABASE yourdb FROM PUBLIC

which should have the same effect

Roy Lambert

roy,
this is really good.

just in case someone is trying to play around with the security levels on table:
in order to fully grant specific table rights to a user:
(you have to be part of the Administrators Role to do this)
- revoke all rights from role PUBLIC for this specific database since all users get granted public automatically and PUBLIC has update rights on all tables
- grant select on database level to the user - this enabled the user to read the metadata - otherwise it does not see the database at all
- grant specific rights on table level to the user