From Newsgroup: comp.databases.mysql

Hi Guys,

I need to create a db to manage a crew of users who are for example the
owners of a company.

users
---------------
id_user name
1 John
2 Peter
3 Sam

companies
--------------
id_company name
1 Car rental USA
2 Restaurant Mr. X
3 Hotel Last way

crews
------------------------------------
id_crew FK_id_company FK_id_user
1 1 1
2 1 2
3 1 3
4 2 2
5 2 3

I'd like to know how to add/delete data from the table crews, like what
happen in Whatsapp when someone create a group it should be an
administrator who can add/delete members in this case from crews...

I think I should create a fourth table to manage permits because I'd
like to give this "power" to the end user.

Regards.
^Bart
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 10/31/2019 4:40 PM, ^Bart wrote:

Hi Guys,

I need to create a db to manage a crew of users who are for example the owners of a company.

users
---------------
id_usera name
1aaaa John
2aaaa Peter
3aaaa Sam

companies
--------------
id_company name
1aaaaaa Car rental USA
2aaaaaa Restaurant Mr. X
3aaaaaa Hotel Last way

crews
------------------------------------
id_crew FK_id_company FK_id_user
1aaa 1aaaaaaaaa 1
2aaa 1aaaaaaaaa 2
3aaaaaa 1aaaaaaaaaaaa 3
4aaaaaa 2aaaaaaaaaaaa 2
5aaa 2aaaaaaaaaaaa 3

I'd like to know how to add/delete data from the table crews, like what happen in Whatsapp when someone create a group it should be an
administrator who can add/delete members in this case from crews...

I think I should create a fourth table to manage permits because I'd
like to give this "power" to the end user.

Regards.
^Bart

That's what the security rules in the database are for. Give everyone
who has access SELECT authority but only administrators ADD, UPDATE and DELETE.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

That's what the security rules in the database are for.a Give everyone
who has access SELECT authority but only administrators ADD, UPDATE and DELETE.

I created another table named "userpermits" to manage user's rules.

Regards.
^Bart

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 03/11/2019 14.31, ^Bart wrote:

That's what the security rules in the database are for.-a Give everyone
who has access SELECT authority but only administrators ADD, UPDATE
and DELETE.

I created another table named "userpermits" to manage user's rules.

I think Jerry was more talking about the database grants, you should
have a setup of database users who has different access in the database,
for example for the general visitor who just are there for looking at
the data (not adding and editing), should use a database user who only
has select privileges.

You may have a different site or different section of the site for the
people who works with the tool, but ain't administrators, should have
another database user who has access to write (INSERT) and update
(UPDATE) a few tables, those they need to be able to edit.

Then you have a site or a different section of the site which is fore
the administrators, here you use a database user who has the access to
do even more things, for example insert/update/delete in the users and companies tables.

The idea is that if someone compromises the system, that they won't have access to destroy the whole database (this of course requires you to
have 3 different sites on 3 different servers, or else they may find the credential for the other database users).

If you use a database with built in encryption key management like
sybase, then you may have columns that are encrypted, and you will only
allow specific database users to have the access to read the content of
those columns, say you have a creditcard number column, you want only a database user who really need to read the creditcard to have the right
to read and store in it, so that user you give access to the decryption
key. An administrator of a system seldom has any need to read creditcard numbers, so that user wouldn't have access to a key. So it may be a
special background service (running on a machine with no direct internet connection) which uses the database user who has the right to read and
store creditcard number, those if someone breaches the web site and uses
any database credential found there, they wouldn't be able to read the creditcard numbers, neither would the DBA be able and suddenly you may
be PCI DSS complaint.


A user permits table is good, as you can handle in finer details what a
user can do on your site, to make it easier to administrate, you don't
want to put permissions on user level but on group level, for if you
have 200 customers, which all will need to be able to do the same
things, you don't want to spend hours to see to that each customer has
the same permissions, you just apply them a group (one or more), and the settings for each group determines what a user can do.
--

//Aho





--- Synchronet 3.21d-Linux NewsLink 1.2