Create User in Mysql

 

MySQL offers for protecting data in the tables against deliberate or accidental unauthorized use: SQL users, passwords,and privileges.

New SQL users are not allowed to access tables belonging to other SQL users,not even with the SELECT statement. Nor can they immediately create their own tables. New SQL users must explicitly be granted privileges. We can indicate, for example, that an SQL user is allowed to query a certain table or change specific columns of a table. Another SQL user might be allowed to create tables, and another user might be allowed to create and remove complete databases.

ADDING AND REMOVING USERS

DEFINITION
<create user statement> ::=
CREATE USER <user specification>
[ , <user specification> ]...
<user specification> ::=
<user name> [ IDENTIFIED BY [ PASSWORD ] <password> ]
<user name> ::=
<name> | '<name>' | '<name>'@'<host name>'
<password> ::= <alphanumeric literal>

In a CREATE USER statement, a user name and a password are entered. In most
SQL products, the user name and password are just names consisting of letters and
numbers.
Example 28.1: Introduce two new users: SIVA with the password SIVASEC
and GANESH with the password LUAP.

CREATE USER
'SIVA'@'localhost' IDENTIFIED BY 'SIVASEC',
'GANESH'@'localhost' IDENTIFIED BY 'LUAP'

Explanation: Behind the user name, the term localhost is specified. This term
specifies the host from which the user creates a connection with MySQL. We return
to this topic later. If the name of a user or host contains special characters, quotation
marks must be placed before and after it—for example 'SIVA'@'localhost'
or 'SIVA'@'xxx.r20.com'. Quotation marks must always be placed before and
after the password.

 

DROP USER statement to remove users from the system, and all their privileges are also removed automatically.

DEFINITION
<drop user statement> ::=
DROP USER <user name> [ , <user name> ]...
<user name> ::=
<name> | '<name>' | '<name>'@'<host name>'

Drop the user SIVA

DROP USER SIVA

If the removed user has created tables, indexes, or other database objects, they
remain because MySQL does not register who created the objects.