Hour 18. Managing Database Users
What You’ll Learn in This Hour:
• Types of users
• User management
• The user’s place in the database
• The user versus the schema
• User sessions
• Altering a user’s attributes
• User profiles
• Dropping users from the database
• Tools utilized by users
In this hour, you learn about one of the most critical administration functions for any relational database: managing database users. Managing users ensures that your database is available to the required people and application while keeping external entities out. Considering the amount of sensitive commercial and personal data that is stored in databases, this hour is definitely one that you should pay careful attention to.
User Management in the Database
Users are the reason for the season—the season of designing, creating, implementing, and maintaining any database. Their needs are considered when the database is designed, and the final goal in implementing a database is making the database available to users, who in turn utilize the database that you, and possibly many others, have had a hand in developing.
Some believe that if there were no users, nothing bad would ever happen to the database. Although this statement reeks with truth, the database was actually created to hold data so users could function in their day-to-day jobs.
Although user management is often the database administrator’s implicit task, other individuals sometimes take a part in the user management process. User management is vital in the life of a relational database and is ultimately managed through the use of SQL concepts and commands, although they vary from vendor to vendor. The ultimate goal of the database administrator in terms of user management is to strike the proper balance between giving users access to the data they need and maintaining the integrity of the data within the system.
By the Way: Roles Vary Widely
Titles, roles, and duties of users vary widely (and wildly) from workplace to workplace, depending on the size of each organization and each organization’s specific data processing needs. One organization’s database administrator might be another organization’s “computer guy.”
Types of Users
There are several types of database users:
• Data entry clerks
• Programmers
• System engineers
• Database administrators
• System analysts
• Developers
• Testers
• Managers
• End users
Each type of user has a unique set of job functions (and problems), all of which are critical to the user’s daily survival and job security. Furthermore, each type of user has different levels of authority and a special place in the database.
Who Manages Users?
A company’s management staff is responsible for the day-to-day management of users; however, the database administrator (DBA) or other assigned individuals are ultimately responsible for the management of users within the database.
The DBA usually handles creating the database user accounts, roles, privileges, and profiles, as well as dropping those user accounts from the database. Because it can become an overwhelming task in a large and active environment, some companies have a security officer who assists the DBA with the user management process.
The security officer, if one is assigned, is usually responsible for the paperwork, relaying to the DBA a user’s job requirements and letting the DBA know when a user no longer requires access to the database.
The system analyst, or system administrator, is usually responsible for the operating system security, which entails creating users and assigning appropriate privileges. The security officer also might assist the system analyst in the same way he does the database administrator.
Maintaining an orderly way in which to assign and remove permissions as well as to document the changes makes the process much easier to maintain. Documentation also enables you to have a paper trail to point to when the security of your system needs to be audited either internally or externally. We expand on the user management system throughout this hour.
The User’s Place in the Database
By the Way: Make Sure You Follow a Systematic Approach to User Management
User account management is vital to the protection and success of any database; when not managed systematically, it often fails. User account management is one of the simplest database management tasks, theoretically, but it is often complicated by politics and communication problems.
A user should be given the roles and privileges necessary to accomplish her job. No user should have database access that extends beyond the scope of her job duties. Protecting the data is the entire reason for setting up user accounts and security. Data can be damaged or lost, even if unintentionally, if the wrong user has access to the wrong data. When the user no longer requires database access, that user’s account should be either removed from the database or disabled as quickly as possible.
All users have their place in the database, yet some have more responsibilities and duties than others. Database users are like parts of a human body—all work together in unison to accomplish some goal.
How Does a User Differ from a Schema?
A database’s objects are associated with database user accounts, called schemas. A schema is a collection of database objects that a database user owns. This database user is called the schema owner. Often schemas logically group like objects in a database and then assign them to a particular schema owner to manage. You could think of it in terms of possibly grouping all the personnel tables under a schema called HR for human resources. The difference between a regular database user and a schema owner is that a schema owner owns objects within the database, whereas most users do not own objects. Most users are given database accounts to access data that is contained in other schemas. Because the schema owner actually owns these objects, he has complete control over them.
Microsoft SQL Server actually goes one step further by having a database owner. The database owner basically owns all objects within the database and has complete control over everything stored within. Within the database are one or more schemas. The default schema is always dbo and is normally the default for the database owner. There may be as many schemas as necessary to logically group the database objects and assign schema owners.
By the Way: User Creation and Management Varies Between Systems
You must check your particular implementation for the creation of users. Also refer to company policies and procedures when creating and managing users. The following section compares the user creation processes in Oracle, MySQL, and Microsoft SQL Server.
The Management Process
A stable user management system is mandatory for data security in any database system. The user management system starts with the new user’s immediate supervisor, who should initiate the access request and then go through the company’s approval authorities. If management accepts the request, it is routed to the security officer or database administrator, who takes action. A good notification process is necessary; the supervisor and the user must be notified that the user account has been created and that access to the database has been granted. The user account password should only be given to the user, who should immediately change the password upon initial login to the database.
Creating Users
The creation of database users involves the use of SQL commands within the database. There is no one standard command for creating database users in SQL; each implementation has a method for doing so. The basic concept is the same, regardless of the implementation. There are several graphical user interface (GUI) tools on the market that can be used for user management.
When the DBA or assigned security officer receives a user account request, the request should be analyzed for the necessary information. The information should include your particular company’s requirements for establishing a user account.
Some items that should be included are Social Security number, full name, address, phone number, office or department name, assigned database, and, sometimes, a suggested user account name.
Syntactical examples of creating users compared among the different implementations are shown in the following sections.
Creating Users in Oracle
Following are the steps for creating a user account in an Oracle database:
- Create the database user account with default settings.
- Grant appropriate privileges to the user account.
The following is the syntax for creating a user:
CREATE USER USER_ID
IDENTIFIED BY [PASSWORD | EXTERNALLY ]
[ DEFAULT TABLESPACE TABLESPACE_NAME ]
[ TEMPORARY TABLESPACE TABLESPACE_NAME ]
[ QUOTA (INTEGER (K | M) | UNLIMITED) ON TABLESPACE_NAME ]
[ PROFILE PROFILE_TYPE ]
[PASSWORD EXPIRE |ACCOUNT [LOCK | UNLOCK]
If you are not using Oracle, do not overly concern yourself with some of the options in this syntax. A tablespace is a logical area managed by the DBA that houses database objects, such as tables and indexes. The DEFAULT TABLESPACE is the tablespace in which objects created by the particular user reside. The TEMPORARY TABLESPACE is the tablespace used for sort operations (table joins, ORDER BY, GROUP BY) from queries the user executes. The QUOTA is the space limit placed on a particular tablespace to which the user has access. PROFILE is a particular database profile that has been assigned to the user.
The following is the syntax for granting privileges to the user account:
GRANT PRIV1 [ , PRIV2, ... ] TO USERNAME | ROLE [, USERNAME ]
By the Way: Even the CREATE USER Command Has Differences
You can use the preceding syntax for creating users to add a user to an Oracle database, as well as a few other major relational database implementations.
MySQL does not support the CREATE USER command. Users can be managed using the mysqladmin tool. After a local user account is set up on a Windows computer, a login is not required. However, you should set up a user for each user requiring access to the database in a multiuser environment using mysqladmin.
The GRANT statement can grant one or more privileges to one or more users in the same statement. The privilege(s) can also be granted to a role, which in turn can be granted to a user(s).
In MySQL, the GRANT command can grant users access on the local computer to the current database. For example:
GRANT USAGE ON *.* TO USER@LOCALHOST IDENTIFIED BY 'PASSWORD';
Additional privileges can be granted to a user as follows:
GRANT SELECT ON TABLENAME TO USER@LOCALHOST;
For the most part, multiuser setup and access for MySQL is required only in multiuser environments.
Creating Users in Microsoft SQL Server
The steps for creating a user account in a Microsoft SQL Server database follow:
- Create the login user account for SQL Server, and assign a password and a default database for the user.
- Add the user to the appropriate database(s) so that a database user account is created.
- Grant appropriate privileges to the database user account.
The following is the syntax for creating the user account:
SP_ADDLOGIN USER_ID ,PASSWORD [, DEFAULT_DATABASE ]
By the Way: There’s a Lot More to Assigning Privileges
The discussion of privileges within a relational database is further elaborated on in Hour 19, “Managing Database Security.”
The following is the syntax for adding the user to a database:
SP_ADDUSER USER_ID [, NAME_IN_DB [, GRPNAME ] ]
As you can see, SQL Server distinguishes between a login account that is granted access to log into the SQL Server instance and a database user account that grants access to database objects. You can view this for yourself by looking at the security folders in SQL Server Management Studio after you have created the login account and then at the database level when you issue the SP_ADDUSER command. This is an important distinction with SQL Server because you can create a login account that does not have access to any of the databases on the instance.
A common error when creating accounts on SQL Server is forgetting to assign them access to their default database. So when you set up accounts, ensure that they have access to at least their default database or you might be setting up the users to receive an error when logging into your system.
The following is the syntax for granting privileges to the user account:
GRANT PRIV1 [ , PRIV2, ... ] TO USER_ID
Creating Users in MySQL
The steps for creating a user account in MySQL follow:
- Create the user account within the database.
- Grant the appropriate privileges to the user account.
The syntax for creating the user account is similar to the syntax used in Oracle:
SELECT USER user [IDENTIFIED BY [PASSWORD] 'password']
The syntax for granting the user’s privileges is also similar to the Oracle version:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type]
{tbl_name | * | *.* | db_name.* | db_name.routine_name}
TO user
Creating Schemas
Schemas are created via the CREATE SCHEMA statement.
The syntax is as follows:
CREATE SCHEMA [ SCHEMA_NAME ] [ USER_ID ]
[ DEFAULT CHARACTER SET CHARACTER_SET ]
[PATH SCHEMA NAME [,SCHEMA NAME] ]
[ SCHEMA_ELEMENT_LIST ]
The following is an example:
CREATE SCHEMA USER1
CREATE TABLE TBL1
(COLUMN1 DATATYPE [NOT NULL],
COLUMN2 DATATYPE [NOT NULL]...)
CREATE TABLE TBL2
(COLUMN1 DATATYPE [NOT NULL],
COLUMN2 DATATYPE [NOT NULL]...)
GRANT SELECT ON TBL1 TO USER2
GRANT SELECT ON TBL2 TO USER2
[ OTHER DDL COMMANDS ... ]
The following is the application of the CREATE SCHEMA command in one implementation:
CREATE SCHEMA AUTHORIZATION USER1
CREATE TABLE EMP
(ID NUMBER NOT NULL,
NAME VARCHAR2(10) NOT NULL)
CREATE TABLE CUST
(ID NUMBER NOT NULL,
NAME VARCHAR2(10) NOT NULL)
GRANT SELECT ON TBL1 TO USER2
GRANT SELECT ON TBL2 TO USER2;
Schema created.
The AUTHORIZATION keyword is added to the CREATE SCHEMA command. This example was performed in an Oracle database. This goes to show you, as you have also seen in this book’s previous examples, that vendors’ syntax for commands often varies in their implementations.
Implementations that do support the creation of schemas often assign a default schema to a user. Most often this is aligned with the user’s account. So a user with the account BethA2 normally has a default schema of BethA2. This is important to remember because objects are created in the user’s default schema unless otherwise directed by providing a schema name at the time of creation. If we issue the following CREATE TABLE statement using BethA2’s account, it is created in the BethA2 schema:
CREATE TABLE MYTABLE(
NAME VARCHAR(50) NOT NULL );
This might not be the desired location. If this is SQL Server, we might have permissions to the dbo schema and want to create it there. In that case, we need to qualify our object with the schema as shown here:
CREATE TABLE DBO.MYTABLE(
NAME VARCHAR(50) NOT NULL):
It is important to remember these caveats when creating users and assigning them permissions so that you can maintain proper order within your database systems without having unintended consequences.
Dropping a Schema
Watch Out!: CREATE SCHEMA Is Not Always Supported
Some implementations might not support the CREATE SCHEMA command. However, schemas can be implicitly created when a user creates objects. The CREATE SCHEMA command is simply a single-step method of accomplishing this task. After a user creates objects, the user can grant privileges that allow access to the user’s objects to other users.
MySQL does not support the CREATE SCHEMA command. A schema in MySQL is considered to be a database. So you use the CREATE DATABASE command to essentially create a schema to populate with objects.
You can remove a schema from the database using the DROP SCHEMA statement. You must consider two things when dropping a schema: the RESTRICT option and the CASCADE option. If RESTRICT is specified, an error occurs if objects currently exist in the schema. You must use the CASCADE option if any objects currently exist in the schema. Remember that when you drop a schema, you also drop all database objects associated with that schema.
The syntax is as follows:
DROP SCHEMA SCHEMA_NAME { RESTRICT | CASCADE }
By the Way: There Are Different Ways to Remove a Schema
The absence of objects in a schema is possible because objects, such as tables, can be dropped using the DROP TABLE command. Some implementations have a procedure or command that drops a user and can also drop a schema. If the DROP SCHEMA command is not available in your implementation, you can remove a schema by removing the user who owns the schema objects.
Altering Users
An important part of managing users is the ability to alter a user’s attributes after user creation. Life for the DBA would be a lot simpler if personnel with user accounts were never promoted, never left the company, or if the addition of new employees was minimized. In the real world, high personnel turnover and changes in users’ responsibilities are a reality and a significant factor in user management. Nearly everyone changes jobs or job duties. Therefore, user privileges in a database must be adjusted to fit a user’s needs.
The following is Oracle’s example of altering the current state of a user:
ALTER USER USER_ID [ IDENTIFIED BY PASSWORD | EXTERNALLY |GLOBALLY AS 'CN=USER']
[ DEFAULT TABLESPACE TABLESPACE_NAME ]
[ TEMPORARY TABLESPACE TABLESPACE_NAME ]
[ QUOTA INTEGER K|M |UNLIMITED ON TABLESPACE_NAME ]
[ PROFILE PROFILE_NAME ]
[ PASSWORD EXPIRE]
[ ACCOUNT [LOCK |UNLOCK]]
[ DEFAULT ROLE ROLE1 [, ROLE2 ] | ALL
[ EXCEPT ROLE1 [, ROLE2 | NONE ] ]
You can alter many of the user’s attributes in this syntax. Unfortunately, not all implementations provide a simple command that allows the manipulation of database users.
MySQL, for instance, uses several means to modify the user account. For example, you use the following syntax to reset the user’s password in MySQL:
UPDATE mysql.user SET Password=PASSWORD('new password')
WHERE user='username';
Additionally, you might want to change the username for the user. You could accomplish this with the following syntax:
RENAME USER old_username TO new_username;
Some implementations also provide GUI tools that enable you to create, modify, and remove users.
By the Way: Some Databases and Tools Obscure the Underlying Commands
Remember that the syntax varies between implementations. In addition, most database users do not manually issue the commands to connect or disconnect from the database. Most users access the database through a vendor-provided or third-party tool that prompts the user for a username and password, which in turn connects to the database and initiates a database user session.
User Sessions
A user database session is the time that begins at database login and ends when a user logs out. During the user session, the user can perform various actions that have been granted, such as queries and transactions.
Upon the establishment of the connection and the initiation of the session, the user can start and perform any number of transactions until the connection is disconnected; at that time, the database user session terminates.
Users can explicitly connect and disconnect from the database, starting and terminating SQL sessions, using commands such as the following:
CONNECT TO DEFAULT | STRING1 [ AS STRING2 ] [ USER STRING3 ]
DISCONNECT DEFAULT | CURRENT | ALL | STRING
SET CONNECTION DEFAULT | STRING
User sessions can be—and often are—monitored by the DBA or other personnel having interest in user activities. A user session is associated with a particular user account when a user is monitored. A database user session is ultimately represented as a process on the host operating system.
Removing User Access
You can remove a user from the database or disallow a user’s access through a couple of simple commands. Once again, however, variations among implementations are numerous, so you must check your particular implementation for the syntax or tools to accomplish user removal or access revocation.
Following are methods for removing user database access:
• Change the user’s password
• Drop the user account from the database
• Revoke appropriate previously granted privileges from the user
You can use the DROP command in some implementations to drop a user from the database:
DROP USER USER_ID [ CASCADE ]
The REVOKE command is the counterpart of the GRANT command in many implementations, allowing privileges that have been granted to a user to be revoked. An example syntax for this command for SQL Server, Oracle, and MySQL is as follows:
REVOKE PRIV1 [ ,PRIV2, ... ] FROM USERNAME
Tools Utilized by Database Users
Some people say that you do not need to know SQL to perform database queries. In a sense, they are correct; however, knowing SQL definitely helps when querying a database, even when using GUI tools. Even though GUI tools are good and should be used when available, it is most beneficial to understand what is happening behind the scenes so you can maximize the efficiency of utilizing these user-friendly tools.
Many GUI tools that aid the database user automatically generate SQL code by navigating through windows, responding to prompts, and selecting options. There are reporting tools that generate reports. Forms can be created for users to query, update, insert, or delete data from a database. There are tools that convert data into graphs and charts. Certain database administration tools monitor database performance, and others allow remote connectivity to a database. Database vendors provide some of these tools, whereas others are provided as third-party tools from other vendors.
Summary
All databases have users, whether one or thousands. The user is the reason for the database.
There are three necessities for managing users in the database. First, you must be able to create database user accounts for the proper individuals and services. Second, you must be able to grant privileges to the accounts to accommodate the tasks that must be performed within the database. Finally, you must be able to either remove a user account from the database or revoke certain privileges within the database from an account.
Some of the most common tasks of managing users have been touched on; much detail is avoided here, because most databases differ in how users are managed. However, it is important to discuss user management due to its relationship with SQL. The American National Standards Institute (ANSI) has not defined or discussed in detail many of the commands to manage users, but the concept remains the same.
Q&A
Q. Is there an SQL standard for adding users to a database?
A. ANSI provides some commands and concepts, although each implementation and each company has its own commands, tools, and rules for creating or adding users to a database.
Q. Can user access be temporarily suspended without removing the user ID completely from the database?
A. Yes. You can temporarily suspend user access by simply changing the user’s password or revoking privileges that allow the user to connect to the database. You can reinstate the functionality of the user account by changing and issuing the password to the user or granting privileges to the user that might have been revoked.
Q. Can a user change his own password?
A. Yes, in most major implementations. Upon user creation or addition to the database, a generic password is given to the user, who must change it as quickly as possible to a password of his choice. After the user changes his password, even the DBA does not know the new password.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. Which command establishes a session?
2. Which option drops a schema that still contains database objects?
3. Which command in MySQL creates a schema?
4. Which statement removes a database privilege?
5. Which command creates a grouping or collection of tables, views, and privileges?
6. What is the difference in SQL Server between a login account and a database user account?
Exercises
1. Describe how you would create a new user 'John' in your learnsql database.
2. How would you grant access to the Employee_tbl to your new user 'John'?
3. Describe how you would assign permissions to all objects within the learnsql database to 'John'.
4. Describe how you would revoke the previous privileges from 'John' and then remove his account.