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.
In this example:
- arjunj is the user name.
- helloThisIsMyPassword! is the password.
Syntax with Microsoft SQL server:
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):
Same but on all tables in the marketing database:
To let the user select information in a table (without updating):
To let the user select, update, delete and insert:
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:
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:
Rename a user with SQL
To change the username from arjunj to jarjun:
Change the password initially set
In the example below, we change the password to “helloWorld123” for the user jarjun: