Hack 94. Create Users and Administrators

There is no standard way to create user and administrator accounts. Instead, you need to learn how to do it on each database platform.

To use your database system you will need administrator accounts, as well as accounts for normal users. Unfortunately, account handling is vendor specific, and often other key issues specific to each platform must be configured for each user.

Often you find that each database comes with a single "super" user who can do anything. Such "administrator" accounts are all-powerful. Now, if you are sharing your DBMS with other people, and they need to do "administrator" things, you could just give each of them the password to this one administrator account. However, the proper thing to do would be to create administrator accounts for everyone who needs those rights.

Of course, sharing administrator rights means that a considerable degree of trust has also been shared too. If you have a single account and one person changes the account in some way, all of the users could be easily locked out. If each user has his own administrator account it is most likely that a mistake by one administrator will simply disable that administrator's account. However, each administrator can destroy all that they can control, and an administrator controls everything.

11.5.1. MySQL

You need to create all users twice in MySQL if you want users to connect from the local machine and from remote hosts. The local machine user is known as the localhost user. When creating the users, the name of the computer from which the user is logging in goes after the @ sign, and the % is a wildcard meaning zero or more characters. You can define the remote machines as strings such as 10.0.0.% or 10.%, depending on the IP numbers in your domain. You can also use domain names such as %.grussell.org.

The following grant commands create an admin user and give it a password. Of course, you should have a more inventive password than mypassword:

GRANT ALL PRIVILEGES ON *.* TO 'grussell'@'localhost' IDENTIFIED BY mypassword WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'grussell'@'146.176.%' IDENTIFIED BY mypassword WITH GRANT OPTION;

This will allow anyone using the username grussell with the correct password to connect with super powers, provided they log in from the localhost or from a host with the IP 146.176.*in other words, anyone on the local network. If you are behind a NAT firewall, your IPs might be more like 10.%, or 192.168.%, or 172.16.%. MySQL allows you to specify user@'%', which is a user from any host except localhost. However, this is a security concern because it makes the database accessible to everyone.

Normal users will probably get all of the privileges possible on their own database (although it would be sensible to look at all the GRANT privileges to make sure that you agree with them). You can create a normal user using GRANT. Here it is assumed that the name of the database and the name of the user are identical:

GRANT ALL PRIVILEGES ON andrew.* TO 'andrew'@'localhost' IDENTIFIED BY hispassword;

11.5.2. Oracle

In Oracle, the DBA will, by default, create tables, views, indexes, and so forth, in the SYSTEM space. Normal users should never be allowed to create application data in SYSTEM. You can use DEFAULT TABLESPACE and TEMPORARY TABLESPACE commands to set this to the normal user defaults if a DBA account will also be creating applications. However, it is probably best to leave the defaults as they are, and not use DBA accounts for creating data. Instead, the DBA should create normal users for each data set, and use the CREATE parameters to make the defaults of that user appropriate to the data set being used:

CREATE USER grussell IDENTIFIED BY mypassword DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT,RESOURCE,DBA TO grussell;

To create a normal user you basically do the same thing, except instead of the dba role in the grant you need session:

CREATE USER andrew IDENTIFIED BY hispassword TEMPORARY TABLESPACE temp DEFAULT TABLESPACE users QUOTA UNLIMITED ON users QUOTA unlimited ON temp; GRANT CREATE SESSION,CONNECT,RESOURCE TO andrew;

If you want, you can set resource limits on each user as they are created (or use ALTER USER later). Here the limits have been set to unlimited on TEMP space and normal USER space.

11.5.3. PostgreSQL

To create a normal user and an associated database you can do:

CREATE USER andrew WITH PASSWORD 'hispassword'; CREATE DATABASE andrew; GRANT ALL PRIVILEGES ON DATABASE andrew to andrew;

To create a user with administrator rights you need to make a small change to the CREATE USER command:

CREATE USER grussell WITH PASSWORD 'mypassword' CREATEUSER;

You can also create a user account with extra rights to create new databases:

CREATE USER grussell WITH PASSWORD 'mypassword' CREATEDB;

11.5.4. SQL Server

Users in SQL Server can be authenticated either as Windows users or as SQL Server users. To allow a Windows user to log in to the database using his normal credentials, do this:

EXEC sp_grantlogin 'MYDOMAINandrew'

If, instead, you want the username to be purely for use within SQL Server, without having to create a user in Windows, you can run:

EXEC sp_addlogin 'andrew', 'hispassword'

To execute admin commands you need to be logged in with sufficient privileges, such as sysadmin or securityadmin. You will always be able to run the right commands when you have sysadmin rights.

To change a normal user into an administrator you need to add the correct rights to the user in question. Here you can give andrew all possible rights:

EXEC sp_addsrvrolemember 'andrew','sysadmin'

Or for a Windows user:

EXEC sp_addsrvrolemember 'MYDOMAINandrew','sysadmin'

A normal user will need a database and permissions to manage that database:

CREATE DATABASE andrewdb Use andrewdb EXEC sp_grantdbaccess 'MYDOMAINandrew'