The database administrators create the subordinate users and the tables associated with their fields of application. They also have the task to assign privileges for their data.
To install the user structure, the DBA 'travel10' creates the usergroup 'travel10group'. The users belonging to this group are created afterwards.
CREATE USERGROUP travel10group
RESOURCE
CREATE USER travel11
PASSWORD t11
USERGROUP travel10group
CREATE USER travel12
PASSWORD t12
USERGROUP travel10group
CREATE USER travel13
PASSWORD t13
USERGROUP travel10group
The groupname cannot be used for connecting to the database. This must be done using the name of the group member. Objects, such as tables, however, are stored with the groupname.
'travel20' creates single users who do not belong to a group. 'travel21' and 'travel22' cannot be combined to form a group, because they are differently authorized. 'travel22' as STANDARD user shall not be authorized to create own tables.
CREATE USER travel21
PASSWORD t21
RESOURCE
CREATE USER travel22
PASSWORD t22
STANDARD
The management of the table 'customer' is assigned to the DBA 'travel10'. The DBA decides that the members of his usergroup shall be authorized to maintain the customer data, but not to alter the table structure.
GRANT SELECT, UPDATE, DELETE, INSERT
ON customer
TO travel10group
All users stored in the system shall be authorized to read the data. The granting of the privileges need not be done for each single user, it can be done using the keyword PUBLIC.
GRANT SELECT
ON customer
TO PUBLIC
'travel20' is responsible for the tables 'hotel', 'room', and 'reservation'. This DBA grants different privileges for his tables to his users 'travel21' and 'travel22'.
GRANT SELECT, UPDATE, DELETE, INSERT
ON hotel, room, reservation
TO travel21
GRANT SELECT, UPDATE
ON hotel
TO travel22
The usergroup shall obtain the right to operate on these three tables. 'travel20' wants to entrust the granting of the privileges to the DBA to whom the group belongs. Therefore 'travel20' enables 'travel10' to operate on his tables as well as to grant privileges for them. The specification WITH GRANT OPTION informs the system about that.
GRANT ALL
ON hotel, reservation, customer
TO travel10
WITH GRANT OPTION
'travel10' received all rights for the table 'hotel' and passes some of them on to the usergroup. As this table does not belong to him, the name specification must be completed by the owner name.
GRANT SELECT, UPDATE, DELETE, INSERT
ON travel20.hotel
TO travel10group
GRANT SELECT
ON travel20.reservation
TO travel10group