leaf_yxj wrote: > For oracle, the normal user can't see all the system catalog. but for > postgresql, it looks like all the user can see the system catalog. Should > we limit the user read privilege to system catalog? You can try that, but things may break in unexpected ways. For example, psql's utility commands will probably stop working. I would test any such change thoroughly. Not all system catalogs are visible for everybody, tables and views containing passwords for example can only be read by superusers. PostgreSQL has fewer restrictions on reading system catalogs than Oracle. I can see how a seasoned Oracle DBA might feel uneasy if everybody can find out all user names on the database cluster. > In oracle, the system privilege has create table, create view,create > function. For postgresql database, how to control the user who only can > create table but can't create view. Based on the test I did, once the user > has the create privilege on the schema, the user will have any create > privilege on that schema. In postgresql, Rule is used to control that ??? > very confused! PostgreSQL's permission system is different from Oracle's. Oracle has a lot of "system privileges" which PostgreSQL does not have or need. In Oracle, every user automatically has a schema of the same name and there are no permissions on schema basis. So you need system privileges if you want to keep users from creating objects. In PostgreSQL you can use schema permissions. True, as soon as you have CREATE on a schema, you can create any kind of object there. That is, any kind of object that does not depend on anything else. To create a function, you need the USAGE privilege on the procedural function. You can revoke this right from PUBLIC and only give it to the users you want. To create a trigger, you need the TRIGGER privilege on the table involved and the EXECUTE privilege on the trigger function. So you see, most of what Oracle handles with system privileges is handled with object privileges in PostgreSQL. And you usually can assign permissions in a finer granularity that way. Of course it is confusing at first, but once you understand PostgreSQL's permission system, there are few meaningful things that you cannot achieve with it. What's the use case for granting somebody CREATE TABLE, but not CREATE VIEW? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
