Sybernet / Supplied Procedures Reference
Release 3.00
Oct 14, 2002

SCHEMA.SP_HTML_GRANT

This application program allows you to control the permissions associated with either a stored procedure or the permissions associated with a table. You can do so from any username that has execute permission granted on this application.

There are several ways to grant and revoke permissions. You might want to choose from the list of users and grant execute permission to that user from the list of stored procedures, or you may select the list of stored procedures (the default) and then assign or revoke execute permission from the list of users.

In an ideal world, no users or schemas are granted direct access to objects in your database. Instead, roles are used for this purpose. Once a role is correctly established, the maintenance (adding or droping users) is far less complicated. sp_html_grant allows you to use both.

When this application initializes, you are presented with several options:

Options

Option	Description
procedures	This option displays all stored procedures from the current schema. Clicking a procedure name displays all schemas, roles, and users in your database. Each name is preceded with a checkbox. When checked, it indicates that this name has execute permission. The permission, however, may be a direct reference or an indirect reference.
roles	This option displays all roles in the current schema. Clicking a role name displays all schemas, roles, and users in your database. Each name is preceded with a checkbox. When checked, it indicates that this name is assigned to this role. A role can be assigned to a role. A user can be assigned to a role.
schemas	This option displays all schemas in the current database. A schema is any username that is not a user. Clicking a schema name lists all stored procedures from the current schema. Each schema is preceded with a checkbox. When checked, it indicates that this schema has execute permission. The permission, however, may be a direct reference or an indirect reference.
tables	This option displays all tables in the current database that belong to this schema. Clicking a table name lists all schemas, roles, and users in your database. Each name is preceded with a checkbox. When checked, it indicates that this name has one or more privileges associated with this table. A privilege might be one or more of delete, insert, select, references, or update. The privilege, however, may be a direct reference or an indirect reference.
users	This option displays all users in the current database. Users include the list of usernames that exist in the HTTP.PEOPLESOFT table. Clicking a user lists all stored procedures from the current schema. Each name is preceded by a checkbox. When checked, it indicates that this user has execute permission. The permission, however, may be a direct reference or an indirect reference.
views	This option displays all views in the current database that belong to this schema. Clicking a view name lists all schemas, roles, and users in your database. Each name is preceded with a checkbox. When checked, it indicates that this name has one or more privileges associated with this table. A privilege might be one or more of delete, insert, select, references, or update. The privilege, however, may be a direct reference or an indirect reference.

Assigning and Revoking Permissions

When you select an option item from the left margin, the list of names associated with this option (displayed in the right margin) includes a checkbox that precedes this name. There are 3 possible states for this checkbox:

The checkbox is not checked. This means that you can grant a permission to the specified name.

The checkbox is checked, but is checked because this is a indirect reference. You may not uncheck this box because the permission was assigned to a role. If you want to revoke permission, you must either drop this user from this role or revoke execute from this role. If access is granted by a role, the role is displayed to the right of this name.

The checkbox is checked because this is a direct assignment. This means that you can revoke permission on this object.

Normally, you will be granting and revoking permissions on stored procedures. Here the change is simple because checking a checkbox grants execute permission while unchecking a checkbox reokokes execute permission. In the case of tables and views, things get slightly more complicated because the permissions associated with a table or view can include delete, insert, references, select, and update. In this case a new window is created that displays each option (with checkboxes that indicate whether or not this permission has been granted). Simply check (or uncheck) the option you wan to change and click submit.

Example 1

The following illustrates a typical display for the procedure sp_html_genled:


    ACCOUNTING_MGMT (ROLE) 
    E16745 (Beers,Melissa A) (ACCOUNTING_MGMT) 
    E16783 (Bates,Loretta A) (PA_CA_GROUP) 
    E17227 (Clements,Barbara J) 
    E17256 (Sevilla,Ronald) 
    LABOR_REQ_GROUP (ROLE) 
    PA_CA_GROUP (ROLE)

Here is an explanation of each:

    ACCOUNTING_MGMT (ROLE) 

        This role can execute procedure. 
        E16745 can execute because she is in this role. 
        Clicking the checkbox will revoke execute from this role. 

    E16745 (Beers,Melissa A) (ACCOUNTING_MGMT) 

        Can execute because in ACCOUNTING_MGMT role which can execute. 
        Cannot revoke execute on the procedure, but can revoke from role. 
        Clicking the checkbox does nothing. 

    E16783 (Bates,Loretta A) (PA_CA_GROUP) 

        Can execute because in PA_CA_GROUP role which can execute. 
        Cannot revoke execute on the procedure, but can revoke from role. 
        Clicking the checkbox does nothing. 

    E17227 (Clements,Barbara J) 

        Cannot execute. 
        Although she is in LABOR_REQ_GROUP, LABOR_REQ_GROUP cannot execute. 
        Clicking the checkbox grants execute to E17227 (not the role she is in). 

    E17256 (Sevilla,Ronald) 

        Can execute because grant was given to E17256. 
        Clicking the checkbox will revoke execute from E17256. 
        You can tell you can revoke because no role name is associated with this permission. 

    LABOR_REQ_GROUP (ROLE) 

        Cannot execute. 
        Clicking the checkbox will grant execute to this role. 

    PA_CA_GROUP (ROLE) 

        This role can execute procedure. 
        E16783 can execute because she is in this role. 
        Clicking the checkbox will revoke execute from this role.

In Oracle, a user can belong to zero or more roles. If a user belongs to two roles and both roles can execute a procedure, only one role name is displayed when the username (EXXXXX) is displayed. For example, Suppose E16783 belongs to PA_CA_GROUP and ACCOUNTING_MGMT. To disable execute to her, you would have to revoke execute from both roles.

Example 2

Roles can also be granted to roles. SP_HTML_GRANT will recurse through nested roles. Suppose we revoke execute permission from the role ACCOUNTING_MGMT.

    ACCOUNTING_MGMT (ROLE) 
    E16745 (Beers,Melissa A)

Then grant PA_CA_GROUP to this role. The display will change to this:

    ACCOUNTING_MGMT (ROLE) (PA_CA_GROUP) 
    E16745 (Beers,Melissa A) (ACCOUNTING_MGMT)

This tells us that E16745 (Beers,Melissa A) can execute this procedure. Melissa can execute this procedure because she belongs to the group ACCOUNTING_MGMT. ACCOUNTING_MGMT has this permission because it was granted the role PA_CA_GROUP. The role PA_CA_GROUP was given direct access to this procedure.