The CREATE USER statement allows us to create a new user account in the SQL database. Almost all the database platforms provide GUI to create new users. However CREATE USER statement is a standard in SQL and basic syntax for creating user in SQL is given below,
CREATE USER username
[IDENTIFIED BY password]
[other options];
Parameters
username: Name of the user account you wish to create.
password: Password for the created user account. This will prevent other users from gaining access into your account.
How to Create a New User Account?
The following MySQL statement can be used to create a new user account
CREATE USER ‘Emma’@’localhost’ IDENTIFIED BY ‘password’;
The above statement will create a new user account but it won’t have any privileges to work in a database. Even MySQL shell will not be available to the user “Emma”. So GRANT statement is used to privileges to the user “Emma”. The following SQL statement will provide all privileges to the user.
GRANT ALL PRIVILEGES ON * . * TO ‘Emma’@’localhost’;
The asterisks in the statement represents the database and tables accessed by the user. After finalizing the privileges provided to the user it can be effected in to the database by loading the privileges using the following statement.
- Read: More Interview Questions
FLUSH PRIVILEGES
Some of the common privileges granted to a user are given below
- ALL PRIVILEGES - All privileges are given to the user. Mostly the privileges are given for a
- particular database. If no database is mentioned in the statement then
- privileges are given across the system.
- CREATE - Allows the user to create new databases or tables
- DROP - Privilege provided to the user to delete a table or database.
- DELETE - Permits the user to delete rows from tables.
- INSERT - Allows the user to insert new rows in the table.
- SELECT - Permission to use SELECT command across the database is provided to the user.
- UPDATE - Allows the user to update rows in the table.
- GRANT OPTION - Allows the user to remove or grant the user privileges.
The basic SQL syntax for granting the user privileges is given below.
GRANT [privilege_type] ON [database_name].[table_name] TO ‘[Emma]’@’localhost’;
Asterisks are used instead of database name to provide privileges across the system. Using “FLUSH PRIVILEGES” command is necessary to save the privilege changes made to user account.
The privileges granted to a user account can be revoked by using the following SQL statement
REVOKE [privilege_type] ON [database_name].[table_name] FROM ‘[Emma]’@‘localhost’;
The user in a system can be deleted by using DROP statement. The following statement is used to delete the user Emma from the system.
DROP USER ‘Emma’@‘localhost’;
Quit statement can be used to logout a user account while mysql -u [Emma]-p is used during the login.
The syntax for granting privileges for a user account is varied across different platform. The syntax for granting privileges to a user account named “Emma” is given below.
SQL Syntax
GRANT CONNECT TO Emma IDENTIFIED BY password
Parameter
Emma - Username provided by the user.
password - password provided by the user
DB2 Syntax
GRANT privilege_name ON table_name TO USER Emma
Parameter
privilege_name - privilege assigned to the user Emma.
table_name - Table on which the privilege is assigned to the user.
Emma - Username provided by the user.
Oracle Syntax
CREATE USER Emma IDENTIFIED BY pass_wrd
Parameter
Emma - Username provided by the user.
pass_wrd - password provided to access the user
PostgreSQL Syntax
CREATE USER Emma WITH SYSID u_id | CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER | IN GROUP grp_name [, …] |
[ ENCRYPTED | UNENCRYPTED ]PASSWORD ‘pass_wrd’ | VALID UNTIL ‘time’
Parameters
Emma - Username provided by the user.
U_id - Using the SYSID clause, a new user id is assigned to the user.
CREATEDB NOCREATEDB -These clauses are used to define the capacity of the user.
CREATEUSER NOCREATEUSER - The clause CREATE USER will make the user into
superuser, a superuser can override all restrictions.
grp_name - An existing group’s name to which the user Emma is added as a new member.
pass_wrd - Password for the user Emma.
ENCRYPTED UNENCRYPTED - These keywords control the password of user Emma.
time - The password for the user Emma will be no longer valid after the expiry of absolute timer.