What You’ll Learn in This Hour:
▶ Identifying types of users
▶ Managing users
▶ Identifying the user versus the schema
▶ Understanding the importance of user sessions
▶ Altering a user’s attributes
▶ Dropping users from the database
▶ Maximizing tools utilized by users
▶ Defining database security
▶ Understanding security versus user management
▶ Assigning database system privileges
▶ Assigning database object privileges
▶ Granting privileges to users
▶ Revoking privileges from users
▶ Understanding security features in the database
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 applications while keeping out external entities. 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.
You also learn the basics of implementing and managing security within a relational database using SQL and SQL-related commands. Each major implementation differs in syntax with its security commands, but the overall security for the relational database follows the same basic guidelines discussed in the ANSI standard. Check your particular implementation for syntax and any special guidelines for security.
Users are the reason for 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 then utilize the database that you, and possibly many others, had a hand in developing.
Some people 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 that users can function in their day-to-day jobs.
User management is often the database administrator’s implicit task, but other individuals sometimes play 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 among vendors. The ultimate goal of the database administrator for user management is to strike a proper balance between giving users access to the data they need and maintaining the integrity of the data within the system.
Note
Roles Vary Widely
Titles, roles, and duties of users vary widely (and wildly) among workplaces, 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 guru.”
Multiple types of database users exist, including these:
▶ Data entry clerks
▶ Programmers
▶ System engineers
▶ Database administrators
▶ System analysts
▶ Developers
▶ Testers
▶ Managers
▶ End users
▶ Functional users
▶ Customers and consumers
▶ Organizational stakeholders
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.
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 managing 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 this 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 completing paperwork, relaying a user’s job requirements to the DBA, 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 or she does the database administrator.
Maintaining an orderly way in which to assign and remove permissions, as well as document the changes, makes the process much easier to maintain. Documentation also results in a paper trail that points to when the security of the system needs to be audited, either internally or externally. This hour expands on the user management system.
Note
Follow a Systematic Approach to User Management
Organizations often have blurry lines between different types of users and administrators. Small organizations might have a one-man IT shop, whereas larger organizations might be equipped with a deep bench of IT professionals and backups, especially when 24×7 support is required.
Users should be given the roles and privileges necessary to accomplish their job. No user should have database access that extends beyond the scope of his or her job duties. Protecting the data is the entire reason for setting up user accounts and security. Data can be damaged or lost, even 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.
Note
Follow a Systematic Approach to User Management
User account management is vital to the protection and success of any database. When this process is not managed systematically, it often fails. User account management is one of the simplest database management tasks in theory, but it is often complicated by politics and communication problems.
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 similar objects in a database and then assign them to a particular schema owner to manage. For example, all the personnel tables might be grouped 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. Users generally are given database accounts to access data that is contained in other schemas. Because the schema owner actually owns these objects, that user has complete control over them.
Every database typically has a database creator or owner. The database owner has access to all schemas and objects within the database. Additionally, users with administrative access, such as database administrators, typically have access to every schema and object within the database so that they can effectively manage all users and objects.
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 be given only to the user, who should immediately change the password upon initial login to the database.
The creation of database users involves using SQL commands within the database. No single standard command works for creating database users in SQL; each implementation has a method for doing so. The basic concept is the same, regardless of the implementation. Several graphical user interface (GUI) tools on the market 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. This information should include the company’s particular requirements for establishing a user account.
Some items that should be included are the Social Security number, full name, address, phone number, office or department name, assigned database, and sometimes a suggested user account name.
Note
User Creation and Management Varies Between Systems
Check your particular implementation for details on how to create 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 to show some of the similarities and differences between implementations.
Following are the steps for creating a user account in an Oracle database:
1. Create the database user account, with default settings.
2. Grant the 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 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 ]
Note
Implementation Differences for CREATE USER
MySQL does not support the CREATE USER command. Instead, 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, in a multiuser environment, you should use mysqladmin to set up a user for each user that requires access to the database.
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 can then be granted to a user(s).
In MySQL, the GRANT command can grant users access to the current database on the local computer. 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.
The steps for creating a user account in a Microsoft SQL Server database follow:
1. Create the login user account for SQL Server and assign a password and a default database for the user.
2. Add the user to the appropriate database(s) so that a database user account is created.
3. Grant appropriate privileges to the database user account. This hour discusses privileges within a relational database.
Following is the syntax for creating the user account:
SP_ADDLOGIN USER_ID ,PASSWORD [, DEFAULT_DATABASE ]
This 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 by looking at the security folders in SQL Server Management Studio after you create 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, make sure 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.
Following is the syntax for granting privileges to the user account:
GRANT PRIV1 [ , PRIV2, ... ] TO USER_ID
The steps for creating a user account in MySQL follow:
1. Create the user account within the database.
2. 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
BIRDS DatabaseYou might recall the following SQL code to create a username for yourself to get set up for this book. You can also find this in Hour 4, “Setting Up Your Database.” To do this, you logged in as a system user in the database. Remember that, to create users in a database, you must have the appropriate administrative privileges, which vary between implementations.
SQL> alter session set "_ORACLE_SCRIPT"=true; Session altered. SQL> create user your_username 2 identified by your_passwd; User created. SQL> grant dba to your_username; Grant succeeded. SQL> connect your_username Connected. SQL> SQL> show user USER is "YOUR_USERNAME" SQL>
Next is an example for creating two additional user accounts in the database you are using. Keep in mind that this syntax is specific to Oracle.
SQL> create user bob_smith 2 identified by new_password; User created. SQL> grant connect to bob_smith; Grant succeeded. SQL> create user bird_owner 2 identified by new_password 3 default tablespace users 4 quota 10m on users; User created. SQL> grant connect, resource to bird_owner; Grant succeeded.
You might notice the GRANT commands. The CONNECT role was granted to Bob Smith. The user BIRD_OWNER was set up with a default tablespace to create tables and was given a quota, or limit, of 10MB. This user was also granted the CONNECT and RESOURCE privileges, which bestow the ability to connect to the database and perform basic operations such as selects, and also allow a user to create objects such as tables within the database.
Schemas can be created via the CREATE SCHEMA statement for implementations that have this feature. Schemas can also be created by simply creating a user and granting the appropriate pivot privileges for that user to create objects in the database. Then the user connects to the database and simply creates objects. That is a schema also.
The syntax for CREATE SCHEMA follows:
CREATE SCHEMA [ SCHEMA_NAME ] [ USER_ID ]
[ DEFAULT CHARACTER SET CHARACTER_SET ]
[PATH SCHEMA NAME [,SCHEMA NAME] ]
[ SCHEMA_ELEMENT_LIST ]
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 ... ]
Take a look at the application of the CREATE SCHEMA command in the example database using Oracle:
SQL> connect bird_owner/new_password; Connected. SQL> create schema authorization bird_owner 2 create table new_birds 3 (id number not null, 4 bird varchar2(20) not null) 5 grant select on new_birds to bob_smith; Schema created. SQL> insert into new_birds 2 values (1, 'Pterodactyl'); 1 row created. SQL> commit; Commit complete. SQL> disconnect; Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
The schema was created with a table and a row of data, and access to the new table was granted to Bob Smith. The user then disconnected from the database. After this, you connected as Bob Smith and performed a select on the NEW_BIRDS table. Notice that Bob Smith had to qualify the name of the table by the owner of the table: BIRD_OWNER.NEW_BIRDS.
SQL> connect bob_smith/new_password;
Connected.
SQL> select * from bird_owner.new_birds;
ID BIRD
---------- --------------------
1 Pterodactyl
1 row selected.
The AUTHORIZATION keyword is added to the CREATE SCHEMA command. This example was performed in an Oracle database. You can see (as you have also seen in this book’s previous examples) that vendor syntax for commands often varies among 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. Therefore, 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 you issue the following CREATE TABLE statement using the account for BethA2 account, it is created in the BethA2 schema.
Caution
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 method for accomplishing this task in a single step. After a user creates objects, the user can grant privileges to other users that allow access to the user’s objects.
MySQL does not support the CREATE SCHEMA command; a schema in MySQL is considered to be a database. Thus, 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. When dropping a schema, you must consider two options: RESTRICT and CASCADE. 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 follows:
DROP SCHEMA SCHEMA_NAME { RESTRICT | CASCADE }
Take a look at an example:
SQL> connect ryan/ryan; Connected. SQL> alter session set "_ORACLE_SCRIPT"=true; Session altered. SQL> drop user bird_owner cascade; User dropped.
Note
Different Ways to Remove a Schema
A schema might have no objects 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.
An important part of managing users is the capability 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 or left the company, or if the new employees rarely joined the organization. In the real world, high personnel turnover and changes in users’ responsibilities are a significant factor in user management. Nearly everyone changes jobs or job duties at some point. Therefore, user privileges in a database must be adjusted to fit a user’s needs.
Following is an example of altering the current state of a user in Oracle:
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 ] ]
One of the most common applications of the alter user command is to reset a user’s password, as shown in the following example:
SQL> alter user bob_smith 2 identified by another_password; User altered.
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, offers several ways 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’;
In addition, you might want to change the username for the user. You can 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.
A user database session is the time between when a database user logs in and when the user logs out. During the user session, the user can perform various actions that have been granted, such as queries and transactions.
After establishing the connection and initiating 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 that are interested 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.
Note
Some Databases and Tools Obscure the Underlying Commands
Remember that syntax varies among implementations. In addition, most database users do not manually issue the commands to connect to 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, and then connects to the database and initiates a database user session.
You can remove a user from the database or disallow a user’s access using a couple of simple commands. Again, however, variations among implementations are numerous, so check your particular implementation for the syntax or tools to accomplish user removal or access revocation.
The 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 ] SQL> drop user bob_smith cascade; User dropped.
The REVOKE command is the counterpart of the GRANT command in many implementations, enabling you to revoke privileges that have been granted to a user. An example syntax for this command for SQL Server, Oracle, and MySQL follows:
REVOKE PRIV1 [ ,PRIV2, ... ] FROM USERNAME SQL> revoke insert on new_birds from bob_smith; Revoke succeeded.
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. GUI tools work great when they are available, but understanding what happens behind the scenes is beneficial so that you can maximize the efficiency of 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. Reporting tools generate reports. Forms can be created for users to query, update, insert, or delete data from a database. Tools can 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 other vendors offer them as third-party tools.
Database security is simply the process of protecting the data from unauthorized usage. Unauthorized usage includes data access by database users who should have access to part of the database, but not all parts. This protection also includes policing against unauthorized connectivity and distributing privileges. Many user levels exist in a database, from the database creator, to individuals responsible for maintaining the database (such as the database administrator [DBA]), to database programmers, to end users. Although end users have the most limited access, they are the users for which the database exists. Users should be granted the fewest number of privileges needed to perform their particular jobs.
You might be wondering what the difference is between user management and database security. After all, the previous hour discussed user management, which seems to cover security. User management and database security are definitely related, but each has its own purpose. The two work together to achieve a secure database.
A well-planned and well-maintained user management program goes hand in hand with the overall security of a database. Users are assigned user accounts and passwords that give them general access to the database. The user accounts within the database should be stored with information such as the user’s actual name, the office and department where the user works, a telephone number or extension, and the database name to which the user has access. Personal user information should be accessible only to the DBA. A DBA or security officer assigns an initial password for the database user; the user should change this password immediately. Remember that the DBA does not need to know, and should not want to know, the individual’s password. This ensures a separation of duties and protects the DBA’s integrity in case problems arise with a user’s account.
If a user no longer requires certain privileges, those privileges should be revoked. If a user no longer requires access to the database, the user account should be dropped from the database.
Generally, user management is the process of creating user accounts, removing user accounts, and keeping track of users’ actions within the database. Database security goes a step further by granting privileges for specific database access, revoking certain privileges from users, and taking measures to protect other parts of the database, such as the underlying database files.
Privileges are authority levels used to access the database, access objects within the database, manipulate data in the database, and perform various administrative functions within the database. Privileges are issued using the GRANT command and are taken away using the REVOKE command.
Just because a user can connect to a database does not mean that the user can access data within a database. Access to data within the database is handled through these privileges. The two types of privileges are system privileges and object privileges.
Note
Database Security Involves More Than Just Privileges
Because this is a SQL book, not a database book, it focuses on database privileges. However, keep in mind that database security also involves other aspects, such as the protection of underlying database files; that duty is just as important as the distribution of database privileges. High-level database security can become complex and differs immensely among relational database implementations. If you would like to learn more about database security, you can find information on The Center for Internet Security’s web page: www.cisecurity.org/.
System privileges enable database users to perform administrative actions within the database, such as creating a database, dropping a database, creating user accounts, dropping users, dropping and altering database objects, altering the state of objects, altering the state of the database, and other actions that can result in serious repercussions if they are not carefully used.
System privileges vary greatly among the different relational database vendors, so check your particular implementation for all the available system privileges and their correct use.
Following are some common system privileges in SQL Server:
▶ CREATE DATABASE—Create a new database
▶ CREATE PROCEDURE—Create stored procedures
▶ CREATE VIEW—Create views
▶ BACKUP DATABASE—Control backup of the database system
▶ CREATE TABLE—Create new tables
▶ CREATE TRIGGER—Create triggers on tables
▶ EXECUTE—Execute given stored procedures within the specific database
Following are some common system privileges in Oracle:
▶ CREATE TABLE—Create new tables in the specified schema
▶ CREATE ANY TABLE—Create tables in any schema
▶ ALTER ANY TABLE—Alter table structure in any schema
▶ DROP TABLE—Drop table objects in the specified schema
▶ CREATE USER—Create other user accounts
▶ DROP USER—Drop existing user accounts
▶ ALTER USER—Make alterations to existing user accounts
▶ ALTER DATABASE—Alter database properties
▶ BACKUP ANY TABLE—Back up data from any table in any schema
▶ SELECT ANY TABLE—Perform a select on any table from any schema
Object privileges are authority levels on objects, meaning that you must have been granted the appropriate privileges to perform certain operations on database objects. For example, to select data from another user’s table, the user must first grant you access to do so. Object privileges are granted to users in the database by the object’s owner. Remember that this owner is also called the schema owner.
The ANSI standard for privileges includes the following object privileges:
▶ USAGE—Authorizes usage of a specific domain
▶ SELECT—Allows access to a specific table
▶ INSERT(column_name)—Inserts data into a specific column of a specified table
▶ INSERT—Inserts data into all columns of a specific table
▶ UPDATE(column_name)—Updates a specific column of a specified table
▶ UPDATE—Updates all columns of a specified table
▶ REFERENCES(column_name)—Allows a reference to a specified column of a specified table in integrity constraints; this privilege is required for all integrity constraints
▶ REFERENCES—Allows references to all columns of a specified table
Tip
Some Privileges Are Granted Automatically
The owner of an object is automatically granted all privileges that relate to the objects owned. These privileges are also granted with the GRANT OPTION, which is a nice feature available in some SQL implementations. This feature is discussed in the “GRANT OPTION” section, later this hour.
Most implementations of SQL adhere to the standard list of object privileges for controlling access to database objects.
You should use these object-level privileges to grant and restrict access to objects in a schema. These privileges can protect objects in one schema from database users who have access to another schema in the same database.
A variety of other object privileges are available among different implementations but are not listed in this section. The capability to delete data from another user’s object is another common object privilege available in many implementations. Check your implementation documentation for all the available object-level privileges.
The DBA is usually the one who issues the GRANT and REVOKE commands, although a security administrator, if one exists, might have the authority to do so. The authority to grant or revoke certain privileges comes from management and normally should be carefully tracked to ensure that only authorized individuals are given these types of permissions.
The owner of an object must grant privileges to other users in the database on the object. Even the DBA cannot grant database users privileges on objects that do not belong to the DBA (although there are ways to work around that).
User access is primarily controlled by a user account and password, but that is not enough to access the database in most major implementations. Creating a user account is only the first step in allowing and controlling access to the database.
After the user account has been created, the database administrator, security officer, or designated individual must assign appropriate system-level privileges to a user before that user can perform actual functions within the database, such as creating tables or selecting from tables. Furthermore, the schema owner usually needs to grant database users access to objects in the schema so that users can do their jobs.
Two commands in SQL allow database access control involving the assignment of privileges and the revocation of privileges. The GRANT and REVOKE commands distribute both system and object privileges in a relational database.
GRANT CommandThe GRANT command grants both system-level and object-level privileges to an existing database user account.
The syntax follows:
GRANT PRIVILEGE1 [, PRIVILEGE2 ][ ON OBJECT ] TO USERNAME [ WITH GRANT OPTION | ADMIN OPTION]
This is the syntax for granting one privilege to a user:
SQL> grant select on new_birds to bob_smith; Grant succeeded.
The syntax for granting multiple privileges to a user follows:
SQL> grant select, insert on new_birds to bob_smith; Grant succeeded.
Notice that, when granting multiple privileges to a user in a single statement, each privilege is separated by a comma.
The syntax for granting privileges to multiple users follows:
SQL> grant select, insert on new_birds to bob_smith, ryan; Grant succeeded.
Note
Be Sure to Understand the Feedback the System Gives You
Notice the phrase Grant succeeded, denoting the successful completion of each GRANT statement. This is the feedback that you receive when you issue these statements in the implementation used for the book examples (Oracle). Most implementations have some sort of feedback, although the phrase used might vary.
GRANT OPTIONGRANT OPTION is a powerful GRANT command option. When an object’s owner grants privileges on an object to another user with GRANT OPTION, the new user can also grant privileges on that object to other users, even though the user does not actually own the object. An example follows:
SQL> grant select on new_birds to bob_smith with grant option; Grant succeeded.
ADMIN OPTIONADMIN OPTION is similar to GRANT OPTION, in that the user who has been granted the privileges also inherits the capability to grant those privileges to another user. GRANT OPTION is used for object-level privileges, whereas ADMIN OPTION is used for system-level privileges. When a user grants system privileges to another user with ADMIN OPTION, the new user can also grant the system-level privileges to any other user. An example follows:
GRANT CREATE TABLE TO USER1 WITH ADMIN OPTION; Grant succeeded.
Caution
Dropping a User Can Drop Granted Privileges
When a user who has granted privileges using either GRANT OPTION or ADMIN OPTION has been dropped from the database, the privileges that the user granted are disassociated with the users to whom the privileges were granted.
REVOKE CommandThe REVOKE command removes privileges that have been granted to database users. The REVOKE command has two options: RESTRICT and CASCADE. When the RESTRICT option is used, REVOKE succeeds only if the privileges specified explicitly in the REVOKE statement leave no other users with abandoned privileges. The CASCADE option revokes any privileges that would otherwise be left with other users. In other words, if the owner of an object grants USER1 privileges with GRANT OPTION, USER1 grants USER2 privileges with GRANT OPTION, and then the owner revokes USER1’s privileges. CASCADE also removes the privileges from USER2.
Abandoned privileges are privileges that are left with a user who was granted privileges with the GRANT OPTION from a user who has been dropped from the database or had those privileges revoked.
The syntax for REVOKE follows:
REVOKE PRIVILEGE1 [, PRIVILEGE2 ] [ GRANT OPTION FOR ] ON OBJECT
FROM USER { RESTRICT | CASCADE }
Following is an example:
SQL> revoke insert on new_birds from bob_smith; Revoke succeeded.
Instead of granting object privileges (INSERT, UPDATE, or DELETE) on a table as a whole, you can grant privileges on specific columns in the table, to restrict user access. Consider the following example:
SQL> grant update (bird) on new_birds to bob_smith; Grant succeeded.
PUBLIC Database AccountThe PUBLIC database user account is a database account that represents all users in the database. All users are part of the PUBLIC account. If a privilege is granted to the PUBLIC account, all database users have the privilege. Likewise, if a privilege is revoked from the PUBLIC account, the privilege is revoked from all database users, unless that privilege was explicitly granted to a specific user. Following is an example:
SQL> grant select on new_birds to public; Grant succeeded.
Caution
PUBLIC Privileges Can Grant Unintended Access
Use extreme caution when granting privileges to PUBLIC; all database users acquire the privileges granted. Therefore, by granting permissions to PUBLIC, you might unintentionally give access to data to users who have no business accessing it. For example, giving PUBLIC access to SELECT from the employee salary table gives everyone who has access to the database the rights to see what everyone in the company is paid.
Some implementations have groups of privileges in the database. These groups of permissions are referred to with different names. Having a group of privileges allows simplicity for granting and revoking common privileges to and from users. For example, if a group consists of 10 privileges, the group can be granted to a user instead of individually granting all 10 privileges.
Note
Database Privilege Groups Vary Among Systems
Each implementation differs in its use of groups of database privileges. If this feature is available, it should be used for ease of database security administration.
Oracle has groups of privileges that are called roles. Oracle includes the following groups of privileges with its implementations:
▶ CONNECT—Allows a user to connect to the database and perform operations on any database objects to which the user has access
▶ RESOURCE—Allows a user to create objects, drop objects he or she owns, grant privileges to objects he or she owns, and so on.
▶ DBA—Allows a user to perform any function within the database. The user can access any database object and perform any operation with this group.
Consider this example for granting a group of privileges to a user:
GRANT DBA TO USER1; Grant succeeded.
SQL Server has several groups of permissions at the server level and the database level. Some of the database-level permission groups are listed here:
▶ DB_DDLADMIN—Allows the user to manipulate any of the objects within the database through any legal data definition language command
▶ DB_DATAREADER—Allows the user to select from any of the tables within the database from which it is assigned
▶ DB_DATAWRITER—Allows the user to perform any data manipulation syntax (INSERT, UPDATE, or DELETE) on any of the tables within the database
A role is an object created in the database that contains grouplike privileges. Roles can reduce security maintenance by not having to grant explicit privileges directly to a user. Group privilege management is much easier to handle with roles. A role’s privileges can be changed, and such a change is transparent to the user.
If a user needs SELECT and UPDATE table privileges on a table at a specified time within an application, a role with those privileges can temporarily be assigned until the transaction is complete.
When a role is created, it has no real value other than being a role within a database. It can be granted to users or other roles. Say that a schema named BIRD_OWNER grants the SELECT table privilege to the PHOTOGRAPHER_SELECT role on the NEW_BIRDS table. Any user or role granted the PHOTOGRAPHER_SELECT role now has SELECT privileges on the NEW_BIRDS table.
Likewise, if BIRD_OWNER revokes the SELECT table privilege from the PHOTOGRAPHER_SELECT role on the NEW_BIRDS table, any user or role granted the PHOTOGRAPHER_SELECT role no longer has SELECT privileges on that table.
When assigning permissions in a database, ensure that you think through what permissions a user needs and whether other users need the same sets of permissions. For example, several members of an accounting team might need to access a set of accounting tables. In this case, unless they each need drastically different permissions to these tables, it is far easier to set up a role, assign the role the appropriate conditions, and then assign the users to the role.
If a new object is created and needs to have permissions granted to the accounting group, you can do it in one location instead of having to update each account. Likewise, if the accounting team brings on a new member or decides that someone else needs the same access to its tables, you must assign the role to only the new user; then you are good to go. Roles are an excellent tool to enable the DBA to work smarter, not harder, when dealing with complex database security protocols.
Note
Roles Are Not Supported in MySQL
MySQL does not support roles. The lack of role usage is a weakness in some implementations of SQL.
CREATE ROLE StatementA role is created with the CREATE ROLE statement:
CREATE ROLE role_name;
Granting privileges to roles works the same as granting privileges to a user, as shown in the following example:
SQL> create role photographers_select; Role created. SQL> SQL> grant select on new_birds to photographers_select; Grant succeeded. SQL> grant photographers_select to bob_smith; Grant succeeded.
DROP ROLE StatementA role is dropped using the DROP_ROLE statement:
DROP ROLE role_name;
Consider an example:
SQL> drop role photographers_select; Role dropped.
SET ROLE StatementA role can be set for just the user’s current SQL session using the SET_ROLE statement:
SET ROLE role_name;
The following is an example:
SQL> SET ROLE PHOTOGRAPHER_SELECT; Role set.
You can set more than one role at once:
SQL> SET ROLE PHOTOGRAPHER_SELECT, PHOTOGRAPHER_UPDATE; Role set.
Note
SET ROLE Is Not Always Used
In some implementations, such as Microsoft SQL Server and Oracle, all roles granted to a user are automatically default roles, which means they are set and available to the user as soon as the user logs into the database. The SET ROLE syntax here is shown so that you can understand the ANSI standard for setting a role
As you learned in this hour, all databases have users, whether one or thousands. The user is the reason for the database.
Managing users in the database involves three necessities. First, you must create database user accounts for the proper individuals and services. Second, you must grant privileges to the accounts to accommodate the tasks that must be performed within the database. Finally, you must either remove a user account from the database or revoke certain privileges within the database from an account.
This hour touched on some of the most common tasks of managing users; extraneous detail was avoided because most databases differ in how users are managed. However, it is important to discuss user management because of its relationship with SQL. The American National Standards Institute (ANSI) has not defined or discussed in detail many of the commands for managing users, but the concept remains the same.
This hour also showed you the basics of implementing security in a SQL database or a relational database. After a user is created, the user must be assigned certain privileges that give him or her access to specific parts of the database. ANSI allows the use of roles as discussed during this hour. Privileges can be granted to users or roles.
The two types of privileges are system and object privileges. System privileges allow the user to perform various tasks within the database, such as actually connecting to the database, creating tables, creating users, and altering the state of the database. Object privileges give a user access to specific objects within the database, such as granting the capability to select data or manipulate data in a specific table.
Two commands in SQL allow a user to grant privileges to and revoke privileges from other users or roles in the database: GRANT and REVOKE. These two commands control the overall administration of privileges in the database. Although many other considerations come into play when implementing security in a relational database, this hour discussed the basics that relate to SQL.
Q. Is there a 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 completely removing the user ID 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 by granting privileges to the user that might have been revoked.
Q. Can users change their own passwords?
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 or her choice. After the user changes the initial password, even the DBA does not know the new password.
Q. If users forget their passwords, what should they do to gain access to the database again?
A. Users should go to their immediate management or an available help desk. A help desk generally can reset a user’s password. If not, the DBA or security officer can do so. The user should change the password to a new password as soon as the password is reset. Sometimes the DBA can affect this by setting a specific property that forces the user to change the password at the next login. Check your particular implementation’s documentation for specifics.
Q. What can I do if I want to grant CONNECT to a user, but the user does not need all the privileges that are assigned to the CONNECT role?
A. You would simply not grant the full CONNECT role to the user, but only the privileges required. If you do grant CONNECT and the user no longer needs all the privileges that go with it, simply revoke CONNECT from the user and grant the specific privileges required.
Q. Why is it so important for the new user to change the password when received from whoever created the new user?
A. An initial password is assigned when the user ID is created. No one, not even the DBA or management, should know a user’s password. The password should be kept a secret at all times, to prevent another user from logging on to the database under another user’s account.
The following workshop consists 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 give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Which command establishes a session?
2. Which option drops a schema that still contains database objects?
3. Which statement removes a database privilege?
4. Which command creates a grouping or collection of tables, views, and privileges?
5. What option must a user have to grant another user privileges on an object that the user does not own?
6. When privileges are granted to PUBLIC, do all database users acquire the privileges or only specified users?
7. What privilege is required to look at data in a specific table?
8. What type of privilege is SELECT?
9. What option revokes a user’s privilege to an object, as well as the other users that they might have granted privileges to, by use of the GRANT option?
1. Describe how you would create the new user John in your sample database.
2. Explain the steps you would take to grant access to the BIRDS table to your new user, John.
3. Describe how you would assign permissions to all objects within the BIRDS database to John.
4. Describe how you would revoke the previous privileges from John and then remove his account.
5. Create a new database user as follows:
Username: Steve Password: Steve123
6. Create a role for your new database user, Steve, from the previous exercise. Call the role bird_query and give the role SELECT on just the BIRDS table. Assign Steve to this role.
7. Connect as Steve and query the BIRDS table. Be sure to qualify the BIRDS table because Steve is not the owner (owner.table_name).
8. Connect back as your original user.
9. Now revoke Steve’s SELECT access from the other tables in the database. Now connect to the database as Steve and try to select from the EMPLOYEES, AIRPORTS, and ROUTES tables. What happened?
10. Connect to the database once again as Steve and try to query the BIRDS table.
11. Experiment on your own with your database.