| Lesson 4 || Limiting access to a role |
| Objective || Limit access to a role. |
Limiting access to Role using Security Schemes
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.
Limit access 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
SET ROLE ALL EXCEPT role_list
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;
Disable SET ROLE command
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