Database administration with SQL

We saw in the lesson about database administration that the database administrator is responsible for creating users and giving privileges to these users. Here is the syntax to execute these actions using SQL.

Creating a users with SQL

To create a user, you need to define:

  • a user name. This is telling to the database exactly who you are. Humans use a person’s face and voice to recognize this person; in some situations, they use the other person’s ID card. Databases understand only user names.
  • a password.
  • the database server’s address. The database address is “localhost” in the case the database is installed on the same computer as the one used by the administrator. In the case the database is on another computer, you have to enter the IP address of the computer on which the database is installed. An IP address tells in which place the computer is on the network and has 4 groups of digits separated by dots (“.”). Example: 134.88.976.100 is an IP address.

Example:

CREATE USER 'arjunj'@'localhost' IDENTIFIED BY 'helloThisIsMyPassword!'

In this example:

  • arjunj is the user name.
  • helloThisIsMyPassword! is the password.

Syntax with Microsoft SQL server:

CREATE LOGIN arjunj
WITH PASSWORD = 'helloThisIsMyPassword!';
CREATE USER arjunj FOR LOGIN arjunj;

Grant privileges to users with SQL.

To grant privileges to a user, you need to type:

  • which kind of action you want to permit: update, select, delete, insert, or just all actions.
  • the database name or * for all databases.
  • the name of the table on which the action is done. In case you want to give a right for all tables, use a star (*) instead of a table name.

To let the user doing everything on the clients table (part of the database called marketing):

GRANT ALL ON marketing.clients TO 'arjunj'@'localhost'

Same but on all tables in the marketing database:

GRANT ALL ON marketing.* TO 'arjunj'@'localhost'

To let the user select information in a table (without updating):

GRANT SELECT ON marketing.clients TO 'arjunj'@'localhost'

To let the user select, update, delete and insert:

GRANT SELECT, UPDATE, DELETE, INSERT ON marketing.clients TO 'arjunj'@'localhost'

Prevent user from doing actions with SQL (revoking privileges)

Revoking a privilege is preventing the user from doing an action he can do currently.
The syntax is the same as above, but you use the keyword REVOKE instead of GRANT.

Example: you want to prevent the user arjunj from inserting lines in the clients table:

REVOKE INSERT ON marketing.clients FROM 'arjunj'@'localhost'

Delete a user with SQL

If a user is not going to use the database anymore, the administrator should take off its username from the database. The administrators then uses the following syntax:

DROP USER 'arjunj'@'localhost'

Rename a user with SQL

To change the username from arjunj to jarjun:

RENAME USER 'arjunj'@'localhost' TO 'jarjun'@'localhost'

Change the password initially set

In the example below, we change the password to “helloWorld123” for the user jarjun:

SET PASSWORD FOR 'jarjun'@'localhost' = PASSWORD('helloWorld123');

 

If you want to test your understanding of this article, take the quiz about database administration with SQL. You can also learn read about indexes, primary keys and unique keys.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: August 18, 2014