Database Reference
In-Depth Information
It is a good practice to not "grant" these roles directly to users, but to use an intermediate
ROLE for collecting a set of privileges. Then, this role is granted to users who fit this role. For
example, a role "clerk" may have rights to both insert data and update existing data in table
user_account , but may have rights only to insert data in audit_log table.
Another aspect of database security is making sure that only the right people can access the
database, and that one can't see what other users are doing (unless you are administrator or
auditor,) that users can or cannot grant forward the roles granted to them.
Part of security is also making sure that database servers are in physically secure locations,
and that procedures to access these servers are secure. However, this is not a general
guide to securing your database, server machine, or network, which is too large a topic to
be covered here.
If you are serious about security, then read some of the available topics and articles on
security, or hire a security consultant. Database security is just a small piece in the overall
security puzzle.
Typical user role
The minimal production database setup contains at least two types of users, namely,
administrators and end-users, where administrators can do everything (are superusers),
and end-users can only do very little, usually just modify the data in a few tables, and read
from a few more.
It is not a good idea to let ordinary users create or change database object definitions,
meaning that they should not have the CREATE privilege on any schema, including PUBLIC .
There can be other roles for end-users, such as analysts, who can only select from a single
table or view, or execute a few functions.
Alternatively, there can also be a manager role, which can grant and revoke roles for other
users, but is not supposed to do anything else.
Revoking user access to a table
This recipe answers the question "How do I make sure that the user X cannot access
the table Y?"
Getting ready
The current user must either be a superuser, the owner of the table, or must have a GRANT
option for the table.
Also, you can't revoke rights from a user who is a superuser.
 
Search WWH ::




Custom Search