MySQL stores user information in the tables of the mysql database (DB).

The CREATE USER command creates new MySQL users.

To use it, you must have the global CREATE USER privilege or the INSERT privilege on the mysql database. For each new account, CREATE USER creates a new row in the mysql.user table without specific privileges. An error occurs if the account already exists.

The account can optionally have a password using the IDENTIFIED BY clause.

STRUCTURE

CREATE USER nom_user [IDENTIFIED BY [PASSWORD] 'password'] [, nom_user [IDENTIFIED BY [PASSWORD] 'password']] ...

Example: CREATE USER ‘usuari_prova'@'localhost' identified by 'pwd';

To delete: DROP USER nom_user [, nom_user] ...

An account consists of a 'username' and a 'hostname', joined by '@'. The username is mandatory, but the hostname can be: an IP address, server name, local 'localhost', or any machine '%'.

If only the 'username' part is specified in the account, '%' is assigned as the hostname. Which is the same as 'user'@'%'

The GRANT statement allows system administrators to create MySQL user accounts and grant rights to them in a single statement.

GRANT STRUCTURE <privilegis> ON <objecte> TO <usuaris> [WITH GRANT OPTION];

To see the permissions an account has, use SHOW GRANTS

  1. Privilege examples can be: ALL PRIVILEGES: all privileges on the specified object.
  1. SELECT: [(columns)]: queries. Can be specified for specific columns.
  1. INSERT: insertions.
  1. UPDATE [(columns)]: modifications. Can be specified for specific columns.
  1. DELETE: deletions.

Object examples can be a table or a view.

Users can be everyone: PUBLIC, or a list of users we want to authorize.

The WITH GRANT OPTION option allows the authorized user to, in turn, authorize other users to grant that privilege.

To use GRANT, you must have the GRANT OPTION privilege, and the privileges being granted.

With REVOKE we can remove permissions from the account.

REVOKE [GRANT OPTION FOR] <privilegis> ON <objecte> FROM <usuaris> [RESTRICT|CASCADE];

  1. Privileges, object, and users are the same as for the GRANT statement.
  1. The GRANT OPTION FOR option would be used in case we wanted to revoke the right to authorize (WITH GRANT OPTION).
  1. RESTRICT / CASCADE The CASCADE option causes all users authorized by a user we authorized—who in turn may have granted further authorizations—to be revoked all at once.
  1. The RESTRICT option does not allow us to revoke a user if they have authorized others.
  • MySQL does not automatically remove any permission if a database or table is deleted.
  • REVOKE does not remove entries from the mysql.user table; that is, the user is not deleted!!! You must use DROP USER or DELETE to delete them.

GLOBAL PRIVILEGES

Global permissions apply to all databases (and all tables in the databases) on a given server. To assign global privileges, use the ON *.* syntax.

Other permissions can be granted globally or at more specific levels. Global permissions are stored in the mysql.user table.

GRANT ALL ON . TO 'usuari'@'host';

REVOKE ALL ON . TO 'usuari'@'host';

GRANT SELECT, INSERT ON . TO 'usuari'@'host';

Database privileges

Database privileges apply to all objects in a given database. To assign privileges at the database level, use ON db_name.*

Database privileges are stored in the mysql.db and mysql.host tables. GRANT and REVOKE affect the db table, but not the host table, which is rarely used.

GRANT ALL ON mydb.* TO 'usuari'@'host';

GRANT SELECT, INSERT ON mydb.* TO 'usuari'@'host';

Table privileges

Table permissions apply to all columns in a given table.

To assign privileges at the table level, use the syntax db_name.tbl_name

If you specify tbl_name instead of db_name.tbl_name, the statement applies to tbl_name in the default database.

Table permissions are stored in the mysql.tables_priv table.

GRANT ALL ON mydb.mytbl TO 'usuari'@'host';

GRANT SELECT, INSERT ON mydb.mytbl TO 'usuari'@'host';

Column and routine privileges

Column permissions apply to columns in a given table. Each privilege granted at the column level must be followed by the column or columns, enclosed in parentheses.

The permissible values for priv_type on a column (that is, when using a column_list clause) are INSERT, SELECT, and UPDATE.

Column permissions are stored in the mysql.columns_priv table.GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'usuari'@'host';

There are also privileges regarding stored procedures and they are saved in mysql.procs_privPASSWORDS

In the IDENTIFIED BY clause, the password must be given as a literal.

It is not necessary to use the PASSWORD() function

GRANT ... IDENTIFIED BY 'mypass';

If you do not wish to send the password in plain text and know the hash value that PASSWORD() would return for the passphrase, you can specify the hash value preceded by the PASSWORD keyword.

GRANT ... IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';WITH Clause

The WITH clause is used for several purposes:

  • To allow a user to grant privileges to other users
  • To specify resource usage limitations for a user
  • For when and how the user must use secure connections to the server

The WITH GRANT OPTION clause gives the user the ability to give other users the privileges that the user has at the specified level. Be careful who you give this privilege to, because two users with different privileges might be able to combine their privileges!

You cannot give another user a privilege you do not have.

Suppose you grant a user the INSERT privilege on a database. If you then give them SELECT permission on the database and specify WITH GRANT OPTION, the user can give other users not only SELECT permission, but also INSERT.

MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, and MAX_CONNECTIONS_PER_HOUR limit the number of queries, updates, and logins a user can perform during any one-hour period. If count is 0 (the default), it means there is no limitation for this user.

MAX_USER_CONNECTIONS limits the maximum number of simultaneous connections the account can make. If count is 0 (by default), the system variable max_user_connections determines the number of simultaneous connections for the account.

REQUIRE NONE indicates that the account has no SSL or X509 requirements.

This is the default option. Unencrypted connections are allowed if the username and password are valid. Connections can be encrypted, at the client's choice, if the client has the correct certificate and key files. That is, the client does not need to specify SSL command options, in which case the connection will be unencrypted. To use an encrypted connection, the -ssl-ca option must be specified, or all three of -ssl-ca, -ssl-key, and -ssl-cert.

Rename user RENAME USER old_user TO new_user [, old_user TO new_user] ...

However, it does not migrate objects or privileges.

To change the password:

SET PASSWORD [FOR user] = { PASSWORD('some password') | OLD_PASSWORD('some password') | 'encrypted password'}

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

UPDATE mysql.user SET PASSWORD = PASSWORD (‘nova_pwd’);

Password change for anonymous accounts:

shell>

mysql -u root

mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');EXAMPLES

$>

mysql – u root – p

GRANT ALL PRIVILEGES ON . TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON . TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT RELOAD, PROCESS ON . TO 'admin'@'localhost' GRANT USAGE ON . TO 'dummy'@'localhost';

Two of the accounts have a username of monty and a password of some_pass. Both accounts are root accounts with full permissions to do anything. One account ('monty'@'localhost') can only be used when connecting from the local machine. The other ('monty'@'%') can be used to connect from any other machine.

One account has a username of admin and has no password. This account can only be used from the local machine. It has the administrative privileges RELOAD and PROCESS. These allow the user admin to execute the commands mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx, as well as mysqladmin processlist. No authorization is given to access any database. One account has a username without a password. This account can only be used from the local machine. It has no privileges. The USAGE permission in the GRANT command allows creating an account without giving it any privileges. It is assumed that specific privileges will be granted later.

Changes in privileges

When mysqld starts, all contents of the grant tables are read and become effective in access control.

When the server loads the grant tables, privileges for existing client connections are affected as follows:

  • Changes in table and column privileges take effect on the client's next request.
  • Changes in database privileges take effect on the next USE db_name statement.
  • Global privilege changes and access keys take effect the next time the client connects.

If grant tables are modified using GRANT, REVOKE, or SET PASSWORD, the server notices the changes and reloads the grant tables into memory immediately.

If grant tables are modified directly using INSERT, UPDATE, or DELETE, changes will not take effect until the server is restarted, or notified to reload the tables.

To reload tables manually, execute the FLUSH PRIVILEGES command or the mysqladmin flushprivileges or mysqladmin reload commands. Otherwise, if tables are not reloaded manually, changes will not be effective until the next connection.ACTIVITIES

Company script.

  1. Create a user named ALUMNE and with password ALUMNE CREATE USER 'ALUMNE'@'localhost' identified by 'ALUMNE';
  1. User ALUMNE tries to access a table in the EMPRESA DB, can they do it? No, an error will occur because we do not have permission from the creator of the DB, root.
  1. Allow ALUMNE to select data from the empresa DB. GRANT SELECT ON empresa.* TO ALUMNE@'localhost';
  1. User ALUMNE tries to delete, modify, and insert data in PRODUCTE, can they do it? Allow them to: INSERT INTO empresa.PRODUCTE (PROD_NUM, DESCRIPCIO) VALUES (100866, 'ACE TENNIS RACKET II'); UPDATE empresa.PRODUCTE SET DESCRIPCIO='ACE TENNIS RACKET III' WHERE PROD_NUM=100866; DELETE FROM empresa.PRODUCTE WHERE PROD_NUM=100866; They cannot do it because they do not have the necessary permissions, to grant them: GRANT INSERT, UPDATE, DELETE ON empresa.* TO 'ALUMNE'@'localhost'; (with the root user)
  1. User ALUMNE tries to create the VENTES table with 2 fields (choose 2 columns), can they do it? Allow the user to create the above table and insert 3 tuples (can they insert? If not, allow them) They cannot do it, to do so we need to give the user (with root): GRANT CREATE ON empresa.* TO 'ALUMNE'@'localhost'; CREATE TABLE VENTES( DNI VARCHAR(10), Nom VARCHAR(20), Cognom VARCHAR(20), CONSTRAINT PRIMARY KEY (DNI) ); INSERT INTO VENTES VALUES('53666748-N','Francisco','Mece'); INSERT INTO VENTES VALUES('53666738-N','Francisca','Mici'); INSERT INTO VENTES VALUES('53666338-N','Franciscu','Moci');
  1. User ALUMNE tries to create a new user named "PROFE", can they do it? No, because they do not have CREATE USER permission.
  1. Make user ALUMNE able to create user "PROFE" with password "PROFE" and verify it GRANT CREATE USER ON . TO 'ALUMNE'@'localhost'; (with root) CREATE USER 'PROFE'@'localhost' identified by 'PROFE'; (with alumne)
  1. User ALUMNE grants full privileges on table 'VENTES' to user PROFE. Verify it. Can they delete it? It does not allow granting privileges because grant option is not active, to activate it we will have to put: GRANT GRANT OPTION ON empresa.VENTES TO 'ALUMNE'@'localhost'; (with root user) They will not be able to delete it because PROFE has no privileges. However, we cannot allow full privileges because we do not have that. GRANT SELECT, INSERT, UPDATE, DELETE ON empresa.* TO 'PROFE'@'localhost'; (this we can do because we have these permissions.)
  1. User ALUMNE creates a new user (CAP/CAP) CREATE USER 'CAP'@'localhost' identified by 'CAP';
  1. User ROOT gives user ALUMNE the privilege to grant privileges at the EMPRESA DB level GRANT GRANT OPTION ON EMPRESA.* TO 'ALUMNE'@'localhost';
  1. User ROOT gives user CAP all privileges on the VENTES table. GRANT ALL PRIVILEGES ON empresa.VENTES TO 'CAP'@'localhost'; (with root user) (If we put --all it works too.)
  1. User PROFE tries to pass the privilege of selecting and modifying data from the VENTES table to user CAP, can they do it? No, because they do not have grant option, in any case, it would be: GRANT SELECT,UPDATE,INSERT,DELETE ON empresa.VENTES TO 'CAP'@'localhost';
  1. User ALUMNE does what is necessary so user PROFE can pass the previous privilege to user CAP GRANT GRANT OPTION ON empresa.VENTES TO 'PROFE'@'localhost'; (with the user ALUMNE) GRANT SELECT,UPDATE,INSERT,DELETE ON empresa.VENTES TO 'CAP'@'localhost'; (with the user PROFE)
  1. User ALUMNE wants to revoke the privilege of deleting data from a table in the empresa DB (e.g. CLIENTS) from user CAP, can they do it? They cannot do it because user cap has no privileges on table CLIENTS of the empresa DB, in any case, it would be: REVOKE DELETE ON empresa.CLIENT FROM 'CAP'@'localhost';
  1. User ALUMNE wants to revoke the privilege of selecting data from ventes from user CAP, can they do it? Yes, they can. REVOKE SELECT ON empresa.VENTES FROM 'CAP'@'localhost';
  1. User ALUMNE revokes the privilege of passing privileges on the VENTES table from user PROFE. Can they do it? Can user CAP continue selecting data? REVOKE GRANT OPTION ON empresa.VENTES FROM 'PROFE'@'localhost'; (with the user ALUMNE) No, they cannot, because in previous exercises we already removed it.
  1. Do not allow modifying column codi_client (for example) of table clients (for example) to user CAP. Verify it REVOKE UPDATE(CLIENT_COD),INSERT(CLIENT_COD) empresa.CLIENT FROM 'CAP'@'localhost'; (with user PROFE)
  1. Show the permissions that different users have. GRANT GRANT OPTION ON empresa.* TO 'ALUMNE'@'localhost';

VIEWS AND PRIVILEGES EXERCISES

Company script.

  1. User ROOT creates a first view that allows seeing in table departaments all fields except Location. CREATE USER 'ALUMNE1'@'localhost' identified by 'ALUMNE1'; use empresa; -- departaments is dept. CREATE VIEW depcamps AS SELECT d.DEPT_NO, d.DNOM FROM DEPT d;
  1. User ROOT creates a second view that allows seeing client names, their code, and the order number. CREATE VIEW cli AS SELECT c.NOM,c.CLIENT_COD,co.COM_NUM FROM CLIENT c NATURAL JOIN COMANDA co;
  1. User ROOT allows full control to user ALUMNE on the first view. GRANT ALL ON depcamps TO 'ALUMNE1'@'localhost';
  1. Check if user ALUMNE can modify client names with the first view. UPDATE depcamps SET DNOM="I+D" WHERE DNOM="INVESTIGACIÓ"; Yes, they can.
  1. User ROOT allows selecting and inserting to user ALUMNE on the second view. GRANT INSERT,SELECT ON cli TO 'ALUMNE1'@'localhost';
  1. Check if user ALUMNE can modify client names in the second view and/or their code. UPDATE cli SET NOM="EVERY_MOUNTAIN" WHERE NOM="EVERY MOUNTAIN"; UPDATE cli SET CLIENT_COD="99" WHERE CLIENT_COD="102"; They will not be able to, they do not have UPDATE permissions.
  1. User ROOT creates a third view that allows seeing all fields of the CLIENT table, but only those not from Santa Clara. CREATE VIEW clisanta AS SELECT * FROM CLIENT WHERE CIUTAT!="SANTA CLARA";
  1. User ROOT creates a fourth view based on the third that allows seeing all fields, but only those for city Cupertino. CREATE VIEW cuper AS SELECT * FROM clisanta WHERE CIUTAT="CUPERTINO";
  1. User ROOT allows selecting and inserting to user ALUMNE on the third and fourth views. GRANT INSERT,SELECT ON clisanta TO 'ALUMNE1'@'localhost'; GRANT INSERT,SELECT ON cuper TO 'ALUMNE1'@'localhost';
  1. Check if user ALUMNE can modify client names with the third view. UPDATE clisanta SET NOM="TKB_SPORT_SHOP" WHERE NOM="TKB SPORT SHOP"; They will NOT be able to, UPDATE (modify) permissions were not given.
  1. Grant permissions so that it is possible. GRANT UPDATE (NOM) ON clisanta TO 'ALUMNE1'@'localhost'; UPDATE clisanta SET NOM="TKB_SPORT_SHOP" WHERE NOM="TKB SPORT SHOP"; Now it will be possible

THE ACCESS PRIVILEGE SYSTEM

  1. Connect as root. Show all users currently in the DBMS. SELECT User FROM mysql.user GROUP BY user;
  1. Creation of a DBA, named dba1. With permissions to do anything. dba1 must be able to connect from any machine, apart from the machine itself. Keep in mind that the default anonymous account for localhost takes precedence in the authentication system. CREATE USER 'dba1'@'localhost' identified by 'dba1'; CREATE USER 'dba1'@'%' identified by 'dba1'; GRANT ALL PRIVILEGES ON . TO 'dba1'@'%'; GRANT ALL PRIVILEGES ON . TO 'dba1'@'localhost'; GRANT GRANT OPTION ON . TO 'dba1'@'%'; GRANT GRANT OPTION ON . TO 'dba1'@'localhost';
  1. Connect as dba1 and verify that you can access the MySQL catalog. Show the privileges assigned to dba1@localhost and the user. mysql -u dba1 -p show databases; (for privileges:) show grants; (to see current user: ) select current_user;
  1. Connected as dba1 create a database named etpc. You can always know which user you are working with from the client using the statement “select current_user”; CREATE SCHEMA etpc; select current_user;
  1. Creation of user1. This user can only connect from the same network address where the server is located. The password will be user1. We assign it the global creation privilege. Keep in mind that the default anonymous account for localhost takes precedence in the authentication system, so you will need to create a user user1 for localhost. NOTE: use network address '192.168.1.0/255.255.255.0'. use etpc; CREATE USER 'usuari1'@'localhost' identified by 'usuari1'; CREATE USER 'usuari1'@'192.168.1.0/255.255.255.0' identified by 'usuari1'; GRANT CREATE ON etpc. TO 'usuari1'@'localhost'; GRANT CREATE ON etpc. TO 'usuari1'@'192.168.1.0/255.255.255.0';
  1. Creation of user2. This user can only connect from class C IP 192.168.1.10. Initially we will not assign any access password. We do not assign any permissions, only connection permission. Keep in mind that the default anonymous account for localhost takes precedence in the authentication system, so you will need to create a user user2 for localhost. use etpc; CREATE USER 'usuari2'@'localhost'; CREATE USER 'usuari2'@'192.168.1.10'; GRANT USAGE ON etpc. TO 'usuari2'@'localhost'; GRANT USAGE ON etpc. TO 'usuari2'@'192.168.1.10';
  1. Assignment/change of password for user2. SET PASSWORD FOR 'usuari2'@'localhost' = PASSWORD('usuari2'); SET PASSWORD FOR 'usuari2'@'192.168.1.10' = PASSWORD('usuari2');
  1. Verify the created users (host and user), assigned passwords, and the database where users are located. select user,host,db from mysql.db; select user,Password from mysql.user; select m.user,m.host,m.db,my.password from mysql.db m RIGHT OUTER JOIN mysql.user my using(User);
  1. As user1 create a table named ASI2 in the ETPC database. create table ASI2( Nom char(10) );
  1. As user2 create a table named ASI1 in the ETPC database. What is it that does not work? That we only have access to view the DB, not to make modifications or create tables.

THEORY QUESTIONS

  1. Difference between Restrict / Cascade The CASCADE option causes all users authorized by a user we authorized—who in turn may have granted further authorizations—to be revoked all at once. The RESTRICT option does not allow us to revoke a user if they have authorized others.
  1. Which command creates and grants permissions at the same time? GRANT
  1. Define three ways to create a user. How is a user deleted? And how do we remove their privileges? With the GRANT statement GRANT SELECT, INSERT ON test.* TO 'adolfo'@'localhost' IDENTIFIED BY 'pass_adolfo'; With the CREATE USER statement CREATE USER ‘usuari_prova'@'localhost' identified by 'pwd'; By inserting into the user table INSERT INTO user VALUES ('localhost','mariano',PASSWORD('pass_mariano'),'Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0); To delete a user DROP USER nom_user [, nom_user] … To remove privileges *REVOKE ALL ON BD.* from USER@localhost* Is deleting a user the same as removing their privileges? It is not the same because REVOKE does not remove entries from the mysql.user table; that is, the user is not deleted!!! You must use DROP USER or DELETE to delete them.

Check if you have "anonymous" users inside your mysql. Select user from mysql.user; In which DB does mysql store user information?

mysql.user

How do we see the permissions a user has? By putting show grants;

Look at your permissions

SHOW GRANTS;

How can a user grant the privileges they have? By setting GRANT OPTION; What is the difference between table privileges and Database privileges? That in table privileges you only have access to one table, and if you have Database privileges you can access all tables in it. Where are column privileges stored? mysql.columns_priv; Create a user with localhost access and a password, check that you can log in, change its password to another one, and check that you can log in again. CREATE USER 'USUARI'@'localhost' identified by 'USUARI';

mysql -u USUARI -p

SET PASSWORD FOR 'USUARI'@'localhost' = PASSWORD('USUARIO');

Which permission allows creating a new user without any privileges? The USAGE permission in the GRANT command allows creating an account without giving it any privileges. When are privilege tables reloaded automatically? With which command can we force it? They are reloaded on client request, to force it you must execute FLUSH PRIVILEGES. How is a user account structured? By a user, a host, and possibly a password. We can also find permissions or privileges. By default, when we create a user only with the username, with which Host is it created? It will be % = 'user'@'%' By default, when entering mysql only with the username, with which Host do we enter? We enter as localhost, although after performing some tests, if the creation of an external user is done with the same name as an internal user and it is created first, we will access with the external user.