Help me understanding the schema

Поиск
Список
Период
Сортировка
От Kiswono Prayogo
Тема Help me understanding the schema
Дата
Msg-id 99024f5f0911301619j33cad0f0i44ec88899c4cdf81@mail.gmail.com
обсуждение исходный текст
Ответы Re: Help me understanding the schema
Список pgsql-admin
hi, i've used postgresql for few months now, but i still don't understand about the schema/database part and it's security consideration..
was there any better documentation (with pictures ^^) than this: http://www.postgresql.org/docs/current/static/ddl-schemas.html

so, here's my current statement, if someone kind enough to correct me, please tell me if its right or wrong:
1. one database may contain many schema
2. one schema may contain many objects (tables, functions, etc)
3. multiple user can connect to single database
4. when the schema not defined on queries, it's always search from public schema
5. tables created on schema x, can be joined using query on other schema
6. schema are related to database not the user, so, if we create a schema, it shows on others user as well that having access to that database?
7. we can set the privilege of user x for schema y
  ie. database aaa contains schema a1, a2 and a3. user xx can query from schema a1 only, user yy can query from schema a2 only?
8. tables on one schema not related to other schema? it means when i create table on schema x, it won't show on schema y..
9. so the best practice of database security is:
 - create multiple schema, only relate schema to required user, eg. finance schema can only be accessed by finance people, humanresource schema can only be accessed by humanresource people..
 - do not allow queries/manipulation from users, always use views for queries/stored-procedure for manipulation/trigger for data integrity or protection and set the privilege to specific user?
 - on web application, set the running user (of the CGI process) to sameuser login type, so we don't need to store any password on file.

--
Regards,
Kiswono P
GB

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Marty Dinniss"
Дата:
Сообщение: Windows 7
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Replication solution