--00504502ca69d1212304799fb64a
Content-Type: text/plain; charset=UTF-8
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.ht ml
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
--00504502ca69d1212304799fb64a
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
hi, i've used postgresql for few months now, but i still don't unde=
rstand about the schema/database part and it's security consideration..=
<div>was there any better documentation (with pictures ^^) than this:=C2=A0=
<a href=3D"http://www.postgresql.org/docs/current/static/ddl-schemas.html">=
http://www.postgresql.org/docs/current/static/ddl-schemas.ht ml</a></div>
<div><br></div><div>so, here's my current statement, if someone kind en=
ough to correct me, please tell me if its right or wrong:</div><div>1. one =
database may contain many schema</div><div>2. one schema may contain many o=
bjects (tables, functions, etc)</div>
<div>3. multiple user can connect to single database</div><div>4.=C2=A0when=
the schema not defined on queries, it's always search from public sche=
ma</div><div>5. tables created on schema x, can be joined using query on ot=
her schema</div>
<div>6. schema are related to database not the user, so, if we create a sch=
ema, it shows on others user as well that having access to that database?<b=
r>7. we can set the privilege of user x for schema y</div><div>=C2=A0=C2=A0=
ie. database aaa contains schema a1, a2 and a3. user xx can query from sche=
ma a1 only, user yy can query from schema a2 only?</div>
<div>8. tables on one schema not related to other schema? it means when i c=
reate table on schema x, it won't show on schema y..</div><div>9. so th=
e best practice of database security is:</div><div>=C2=A0- 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 h=
umanresource people..</div>
<div>=C2=A0- do not allow queries/manipulation from users, always use views=
for queries/stored-procedure for manipulation/trigger for data integrity o=
r protection and set the privilege to specific user?</div><div>=C2=A0- on w=
eb application, set the running user (of the CGI process) to sameuser login=
type, so we don't need to store any password on file.<br>
<div><br>-- <br>Regards,<br>Kiswono P<br>GB<br>
</div></div>
--00504502ca69d1212304799fb64a--
