|SQL*Plus User's Guide and Reference
Part Number A88827-02
Security, 4 of 5
You can use SQL commands to create and control access to roles to provide security for your database tables.
By creating a role and then controlling who has access to it, you can ensure that only certain users have access to particular database privileges.
Roles are created and used with the SQL CREATE, GRANT, and SET commands:
From SQL*Plus, users can submit any SQL command. In certain situations, this can cause security problems. Unless you take proper precautions, a user could use SET ROLE to access privileges obtained via an application role. With these privileges, they might issue SQL statements from SQL*Plus that could wrongly change database tables.
To prevent application users from accessing application roles in SQL*Plus, you can use the PUP table to disable the SET ROLE command. You also need to disable the BEGIN and SQL*Plus EXECUTE commands to prevent application users setting application roles through a PL/SQL block. This allows a SQL*Plus user only those privileges associated with the roles enabled when they started SQL*Plus. For more information about the creation and usage of user roles, see your Oracle9i SQL Reference and Oracle9i Administrator's Guide.
To disable a role for a given user, insert a row in the PUP table containing the user's username in the Userid column, "ROLES" in the Attribute column, and the role name in the Char_Value column.
The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:
PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- SQL*Plus HR ROLES ROLE1 SQL*Plus PUBLIC ROLES ROLE2
During login, these table rows are translated into the command
To ensure that the user does not use the SET ROLE command to change their roles after login, you can disable the SET ROLE command. See "Disabling SET ROLE" earlier in this appendix.
To reenable roles, delete the row containing the restriction.