9.5 Grants Two types of grants will be made. First, we give table access to the roles; then the roles are granted to the users. Remember from the previous section that the login roles already have the CREATE SESSION privilege and EXECUTE privilege on the TSEC package. These grants follow the matrix, so we'll provide only a few examples here. doc grant table and view access to the roles. # -- general access to the views. GRANT select ON approvers_v TO cdgenaccess; GRANT select ON card_holder_v TO cdgenaccess; etc. -- specific object grants. PROMPT ************* cdholder ************* PROMPT PROMPT s,i,u,d PROMPT GRANT select, insert, update, delete ON comment_log TO cdholder; etc. PROMPT s,i,u PROMPT GRANT select, insert, update ON discrep TO cdholder; etc. PROMPT s,i PROMPT PROMPT none PROMPT s PROMPT GRANT select ON card_trans_b TO cdholder; GRANT select ON dispute_code TO cdholder; GRANT select ON dispute_sub_code TO cdholder; GRANT select ON err_log TO cdholder; GRANT select ON card_trans TO cdholder; GRANT select ON def_cost_center TO cdholder; GRANT select ON card_seq TO cdholder; -- views GRANT select ON dept_def_cc_v TO cdholder; PROMPT other grants PROMPT GRANT select, update (reconcile, receive_date, reconcile_date, parent_id, parent_item, reconciled_by_emp) ON card_trans_item_b TO cdholder; PROMPT ************* cdsysadm ************* PROMPT PROMPT s,i,u,d PROMPT GRANT select, insert, update, delete ON def_cost_center TO cdsysadm; etc. PROMPT s,i,u PROMPT GRANT select, insert, update ON dispute_code TO cdsysadm; GRANT select, insert, update ON dispute_sub_code TO cdsysadm; etc. PROMPT s,i PROMPT PROMPT none PROMPT s PROMPT GRANT select ON dispute_code TO cdsysadm; GRANT select ON dispute_sub_code TO cdsysadm; GRANT select ON err_log TO cdsysadm; GRANT select ON card_trans TO cdsysadm; GRANT select ON def_cost_center TO cdsysadm; GRANT select ON card_trans_item TO cdsysadm; -- sequences GRANT select ON card_seq TO cdsysadm; GRANT select ON temp_card_nbr_seq TO cdsysadm; PROMPT other grants PROMPT none etc. 9.5.1 Grant the Roles to the Users The GRANT statement is simple, but like everything else, it should be scripted. (A script provides documentation of what you did and is repeatable.) When a role is granted to a user, it becomes one of that user's default roles and is enabled at login time even if a password is required. We do not want all of a user's roles enabled when he logs in, so after granting all of the roles, the login role is identified as the user's default. When you identify a set of roles as the default for a user, all other roles granted to the user that you do not name become non-default and are not automatically enabled at login time. The following statements grant mary all of the roles she will need: GRANT cdholder TO mary; GRANT cdgenaccess TO mary; GRANT cduser TO mary; We only want the last two roles to be enabled at login time. The CDHOLDER role is a password-protected role and allows direct access to the CARD_TRANS_B and CARD_TRANS_ITEM_B tables for performance purposes. Those tables are to be accessed only through the application. By declaring only the last two roles as defaults for mary , the CDHOLDER role will become non-default. Set the default roles as follows : ALTER USER mary DEFAULT ROLE cduser, cdgenaccess; Now only these two roles will be activated when mary logs into the database. The CDHOLDER role will have to be set manually and requires a password that mary does not know. 9.5.2 Limitation of Grants and Roles By now it should be clear that the default Oracle access control mechanism is object-based . If a user is granted the SELECT privilege on a table, all rows of the table are available to that user. But most security plans specify that row access control must be implemented that only certain parts of the table, a horizontal cut, should be accessible to the user, and that is to be based on characteristics of the user. Thus far, row-level access has been controlled through the use of views. Some situations, however, require complex decisions to be made based on conditions at the moment, to determine what type of rows can be viewed or updated, or which columns may be updated. These situations are usually addressed by the applications. The problem in these complex cases is controlling the moment that access is allowed. As we've noted, the access grants become effective when made and are in effect regardless of the tool being used. As previously illustrated with user mary , we have granted her a role that can only be enabled by providing a password a password she does not know. If we give the user the passwords, then the user can enable the role at any time. That defeats the purpose of having passwords on the roles, and is not satisfactory. Some part of the application will have to pass the enabling command to the database. We can embed the command along with the password in the application, but hardcoding a password is never a good idea, and we recommend against it. In addition, if the application is being run in an environment where the password may be sent over a network, the possibility of compromise exists. The application is the only place where password access can, therefore, be done. The next section describes one method of solving the problems we have identified here by: -
Embedding the command in the application -
Not hardcoding the password -
Keeping the password off the network  |
