We’ll start here, because Postgres generally sets the standard for relational databases.
In Postgres 9, users and groups were replaced with roles (Postgres have been very kind, and have maintained
group as aliases for
role for backwards compatibility). The difference between users and roles? Simply put, users can now be considered as roles with the ability to login.
- Membership: Any role can be a member of another role, so you can create a hierarchical structure with as many tiers as you’d like.
- Ownership: Any role can own relations.
- Privileges: Any role can have privileges granted to it.
- Inheritance: Up to you! Roles can be configured such that any members automatically inherit the privileges of the role, or can be configured to force a member to explicitly change their role in order to use those privileges.
Redshift forked from Postgres somewhere around version 8. As such, roles look more like an old-school Postgres database rather than the role-based world of Postgres 9 – on Redshift, users and groups are separate entities.
- Membership: Only a user can be a member of groups, i.e. a group cannot be a member of other groups, so you can only have a two-tiered structure.
- Ownership: Users, rather than groups, own relations.
- Privileges: Both users and groups can have privileges granted to them.
- Inheritance: Users automatically inherit all privileges of any groups they are a member of.
These are online, self-guided ways for developers wishing to gain more RDBMS understanding (or have a refresher course) in the two most ubiquitous platforms — MySQL and PostgreSQL. I add and remove from this list from time to time as material becomes outdated or better bits are found.
Disclaimer: I didn’t have a hand in creating any of these, but have recommended them in one format or another for several years.
All links open in a new tab.
MySQL for Developers
MySQL in 7 Days
Learn MySQL via TutorialsPoint (quite good, complete)
Simple SQL Overview (beginners, from W3Schools)
PostgreSQL Tutorial (also has you install it on your platform of choice)
15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips
MySQL Tutorial for Beginners [Full Course] (Programming with Mosh)
https://www.youtube.com/watch?v=7S_tz1z_5bA – (3h 10m)
MySQL Tutorial For Beginners (Intellipaat)
https://www.youtube.com/watch?v=WmGgxTpGs_8 – (2h 5m)
Learn PostgreSQL Tutorial – Full Course for Beginners
https://www.youtube.com/watch?v=qw–VYLpxG4 – (4h 20m)
Postgres for Beginners (Simplilearn)
https://www.youtube.com/watch?v=eMIxuk0nOkU – (1h 30m)