AUTHORIZATION in SQL  ---  "Discretionary Access Control"  DAC

https://beginner-sql-tutorial.com/sql-grant-revoke-privileges-roles.htm
Connelly & Begg: 7.6, 20.2

GRANT privilege_name
ON    object_name
TO    {user_name | PUBLIC | role_name }
[WITH GRANT OPTION];

privilege_name is access right or privilege
    e.g.  ALL, EXECUTE, SELECT
object_name is name of database object
    e.g.  TABLE, VIEW, STORED PROC
user_name is name of user being granted privilege
PUBLIC  is all users
ROLES   is set of users grouped together
WITH GRANT OPTION allows a user to grant access right to other users.

GRANT SELECT
ON    employee
TO    user1;

GRANT INSERT
ON    employee
TO    user1
WITH GRANT OPTION;    // <--  user1 is given the authority to grant INSERT
                      //      on employee to other users

GRANT ALTER
ON    parts
TO    mr_president;

GRANT INSERT
ON    testing_log
TO    test_group;   // <-- if test_group is a role, must create the role before
                    //     granting privileges to it.  See below.



REVOKE  privilege_name
ON      object_name
FROM    { user_name | PUBLIC | role_name }

REVOKE  SELECT
ON      employee
FROM user1;

/**************************************************************************
 * Note the security hole related to WITH GRANT OPTION
 *  Say the creator of employee grants INSERT privilege to user1 WITH GRANT OPTION.
 *  Then user1 grants that privilege to user2 and
 *       user1 grants that privilege to user3 WITH GRANT OPTION
 *  Sometime later, creator REVOKEs the privilege from user2:
 *      REVOKE INSERT
 *      ON employee
 *      FROM user1
 *  BUT! user2 and user3 STILL have INSERT privilege!

PRIVILEGES

Object privileges:
  INSERT  lets users insert rows into table
  SELECT  lets users select data from object (eg table or view)
  UPDATE  lets users update data
  EXECUTE lets users execute a stored procedure or a function
  
System privileges: CREATE, ALTER, DROP


Role: collection of privileges or access rights.

Predefined roles:
  Role name   privileges
  CONNECT     CREATE TABLE, CREATE VIEW, ...
  RESOURCE    CREATE TRIGGER
  DBA         all system privileges
  
Creating roles:

CREATE ROLE role_name
[IDENTIFIED BY pwd];

CREATE ROLE clients;    // create a role

Adding privileges to Role, adding users to Role:

GRANT SELECT          // grant privileges to role clients
ON    parts
TO    clients;

GRANT INSERT
ON    parts
TO    clients;

// add users to role clients
GRANT clients         // role name: clients
TO    user1;          //  is given users: user1, user2

GRANT clients
TO    user2;

// revoking privileges 

REVOKE  INSERT            // revoke INSERT ON parts privilege from clients
ON      parts
FROM    clients; 

//Drop users from Role:

DROP user1
FROM   clients;

// Dropping roles:

DROP ROLE role_name;

E.g.
DROP ROLE clients;