RelationalDBDesignRelationalDBDesign


User Profiles  «Prev  Next»
Lesson 4 Limiting access to a role
Objective Limit access to a role.
Roles are a powerful way to implement security schemes. They are widely used to grant entire sets of privileges to application users. However, just as you might want to allow only certain actions in the context of an application, you might want to allow access only to certain roles within an application context.

Limiting access to Role

Syntax

To limit access to a particular command, use the SQL statement
INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, userid, ‘ROLES’, role_name);

where userid is a character string representing the user ID for the user you wish to restrict and role_name is a character string representing the name of the role you wish to disable. You must issue this command while logged on as SYSTEM.

Caveats

SQL*Plus implements the limitations on roles in a fairly simplistic way. When SQL*Plus is started by a particular user, it checks the PRODUCT_PROFILE table for restricted roles and issues the following command:
SET ROLE ALL EXCEPT role_list;

where role_list is a list of the roles that have been specifically disabled in the table.
This implementation poses some potential problems. The first is that a user can reactivate a particular role once he or she is in SQL*Plus–even with the simple command:
SET ROLE ALL;

To prevent this, you should also disable the SET ROLE command for a user if you are limiting the roles the user will be allowed to use with SQL*Plus. It is possible that a user could enable a role through the use of PL/SQL, but this is somewhat complicated, so only a fairly determined or sophisticated user could use this workaround.
You should also make sure that you are disabling a role that exists for a particular user when you insert a restriction into the PRODUCT_PROFILE table. If the user does not have access to a particular role, the SET ROLE ALL EXCEPT command will fail, which in turn will fail to impose any role restrictions on the user. In the next lesson, you will learn how to use wildcards to specify restrictions.

Product Profiles - Quiz

Product Profiles - Quiz Click the Quiz link below to test your knowledge of product profiles.