Best practice is the same across RDBMS platforms, utilizing the "Least Privileges" principle:
- Create the table owned by a close-held login account (e.g., "HIGHWAY_DEPT")
- Create ROLEs for appropriate access (e.g., "HIGHWAY_BROWSE","HIGHWAY_EDITOR")
- GRANT appropriate role access to the the table ("GRANT select ON highway_dept.streets TO highway_browse")
- Create logins for each staff member
- Create logins for each discrete web application (e.g., "web_app1") -- NEVER use the data owner to publish data!!
- GRANT appropriate roles to each staff member's login ("GRANT highway_editor TO vinny_boombatz")
- GRANT appropriate roles to each staff member's login ("GRANT highway_browse TO web_app1")
- Enable EDITOR_TRACKING on each table (as the owner)
I guess there needs to be one other:
- Sanction any user who releases a password (their own, or the owner login) outside the those with approved access
Depending on the organization, data can be developed using the personal login/schema, and once deemed "ready" (by review board, common acclaim, or personal attestation), the "admin" can recreate the table and contents with the "department" login and grant appropriate access.
Note: I do this myself, even if I'm the only data team member, so web services can't see anything that isn't "ready". If the table definitions are dynamic, I'll number the tables ("streets_t1") and use views to expose them ("streets_v10"). If a new column is necessary, I'll expose it, in necessary order, via "streets_v11". Major changes get a version kick ("streets_t2" + "streets_v20").
If you're using the Wild West approach now, it can take some time to adjust, but the benefits to implementing rational access only increase as the project team adds members.
- V