Java Reference
In-Depth Information
Database
privileges apply to all tables in a given database.
Table
privileges apply to all columns in a given table.
Column
privileges apply to single columns in a given table.
Generally, you need to worry about only global or database privileges, and in most cases it is sufficient
to assign privileges at one of these levels. Your major aim should be to assign the privileges at the
highest level possible so that you save administration time. However, you do have the power to restrict
access or to grant access to some critical columns or tables.
Next, we will talk about the management of user roles.
User Roles
Most database management systems support
user roles
, which are simply a grouping of user privileges.
User roles are a neat administrative feature that saves time for the database administrator. The concept
of roles is similar to that of groups. Just as groups can contain other groups, roles can contain other
roles. Thus, a typical scenario where you would use all these concepts of users, groups, privileges, and
roles can be explained as follows.
Putting It All Together
Imagine that Company A has 100 employees, five of which make up the management team, with
access to all the information in the database. Another five make up the finance department, with access
to all financial information. The remaining 90 are normal employees, with no specific access to the
database except for their individual employee records.
For our example, assume a very simple database model, with the three following tables:
COMPANY_DATA
FINANCE_DATA
EMPLOYEE_DATA
The management team has access to all the data (that is all the tables), the finance team has access to
FINANCE_DATA and EMPLOYEE_DATA, and the employees can only view the EMPLOYEE_DATA.
How would you organize this data using groups and roles? In this scenario, one solution is to follow
these steps:
1. Create the 100 users in the database with the following syntax:
2.
CREATE USER user1 with password 'temppassword'
CREATE USER user2 with password 'tempassword'
3. Create a
MANAGEMENT
group, and assign the five employees who are part of the management
team of the company to that group:
CREATE GROUP MANAGEMENT USERS user1, user2, user3, user4, user5
4. Create a
FINANCE
group, and assign the five employees who are part of the finance team to that
group:
CREATE GROUP FINANCE USERS user6, user7, user8, user9, user10
5. Create an
EMPLOYEES
group, and assign all the users to this group:
CREATE GROUP EMPLOYEES USERS user11, user12, user3, .. user100
6. Create an
EMPLOYEE_ACCESS
role, and assign the
SELECT
privilege for the EMPLOYEE_DATA
table to the role:
CREATE ROLE EMPLOYEE_ACCESS
7. Assign the role to the
EMPLOYEES
group:
8.
GRANT EMPLOYEE_ACCESS
9.
ON EMPLOYEE_DATA
TO EMPLOYEES
10. Similarily, create a
FINANCE_ACCESS
role, and assign
SELECT
and
UPDATE
privileges for the
EMPLOYEE_DATA and FINANCE_DATA Table to the role:
