difference between roles and users

Question

What is the differences beween roles and users.

Answer 1

A role can have many users. An example might be an Admin role, which could mean someone who's assigned and their backup. Both would have the same rights, which are embodied in the role. Individuals can come and go, but roles remain.

Answer 2

A role typically defines a business function (or set of functions) performed by one or more users. Examples would be 'customer service agent' or 'business analyst'. A user is an individual person who is included in the role - Bob, Nancy, and Steve might be assigned to the customer service agent role.

This makes is easier to assign permissions to database objects. You can assign permissions to the role, and any user who belongs to that role inherits the same set of permissions.

Answer 3

On a technical level, see other answers. On a practical level, when you have large user set with fluid permissioning needs due to changing roles, the difference is that assigning per-user permissions means any change in user responsibilities necessitates permissions changes on MULTIPLE database objects that user needs to be added to/removed from perms.

Whereas if the perms are assigned to the roles, the only change is in the role membership.

The latter is both significantly less resource taxing on DBAs, and due to less work needed, significantly less likely to suffer from operator error (e.g. less work to do => less chance to screw it up) and thus more secure.