User Management and Database Security - Java Database Programming Bible - page 593

CREATE ROLE FINANCE_ACCESS
11. Assign the role to the FINANCE group:
12. GRANT FINANCE_ACCESS
13. ON FINANCE_DATA, EMPLOYEE_DATA
TO FINANCE
14. Finally, create a MANAGEMENT_ACCESS role, and assign ALL privileges for all tables to the role:
CREATE ROLE MANAGEMENT_ACCESS
15. Assign the role to the MANAGEMENT group:
16. GRANT MANAGEMENT_ACCESS
17. ON EMPLOYEE_DATA, FINANCE_DATA, COMPANY_DATA
18. TO MANAGEMENT
19. WITH ADMIN OPTION
The creation of roles is a very database dependent task. MS SQL server has a stored procedure call to
do this, whereas Oracle has a CREATE ROLE command. Thus, we are not explaining this syntax.
The GRANT Command
The GRANT command is used to give privileges to users so that they can perform certain tasks on the
database. Recall that there are many types of privileges and that they can be assigned at various
degrees of granularity (global, database, table, or column). It is important to note that the exact syntax
of this command might differ as per your database. Still, here is an example:
GRANT PRIVILEGE ON table_name TO user_name
The GRANT command is more powerful. For example, you can GRANT a privilege to a user and allow the
user to be able to grant that privilege to other users. Do this using the WITH GRANT OPTION clause.
Now the grantee can grant the privileges specified in the GRANT command to other valid users. The
following command gives user John Doe SELECT privileges on the Products Table and allows him to
GRANT this privilege to others:
GRANT SELECT ON PRODUCTS WITH GRANT OPTION TO jdoe
There is also a HIERARCHY option. However, this is not yet widely supported. It grants privileges on all
subtables and related tables. The complete syntax for the GRANT command is as follows:
GRANT priv_type [, priv_type]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [, user_name]
[WITH [GRANT OPTION | HIERARCHY OPTION]]
There is an equivalent GRANT ROLE command that enables you to grant roles instead of privileges. The
syntax is identical to that of the GRANT PRIVILIEGES command. The only difference is that instead of
GRANT OPTION , it is called ADMIN OPTION .
GRANT ROLE name [, role_name ]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [, user_name]
[WITH ADMIN OPTION]
The REVOKE Command
The REVOKE command is used to take away privileges from users so that they cannot perform certain
tasks on the database. Just like the GRANT command, this command can be applied at various levels. It