Roles introduce complexities in the granting of privileges of individual users.
Suppose user Joe has been individually granted SELECT and UPDATE privileges on a specific table. Joe is also a member of two roles, one of which has no access to the table at all, and one of which has only SELECT access. What are the privileges in effect for Joe?
This is how SAP Sybase IQ determines whether a user ID has privilege to carry out a specific action:
If the user ID has DBA privileges, he or she can carry out any action in the database. If the user has specific system privileges granted, he or she can have the privileges to carry out only those authorized tasks associated with the system privileges.
Otherwise, privilege depends on the privileges assigned to the individual user. If the user ID has been granted privilege to carry out the action, the action is allowed to proceed.
If no individual settings have been made for that user, privilege depends on the privileges of each of the roles of which the user is a member. If any of these roles has privilege to carry out the action, the user ID has privilege by virtue of membership in that role, and the action is allowed to proceed.
If you do not want a specific user to access a particular table, view, or procedure, do not make that user a member of a role that has privileges on that object.
This approach minimizes problems associated with the order in which privileges are set.