Hour 19. Managing Database Security
What You’ll Learn in This Hour:
• Database security
• Security versus user management
• Database system privileges
• Database object privileges
• Granting privileges to users
• Revoking privileges from users
• Security features in the database
In this hour, you learn the basics of implementing and managing security within a relational database using SQL and SQL-related commands. Each major implementation differs on syntax with its security commands, but the overall security for the relational database follows the same basic guidelines discussed in the ANSI standard. You must check your particular implementation for syntax and any special guidelines for security.
What Is Database Security?
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 the act of policing against unauthorized connectivity and distribution of 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. A user should be granted the fewest number of privileges needed to perform his particular job.
You might be wondering what the difference between user management and database security is. After all, the previous hour discussed user management, which seems to cover security. Although user management and database security are definitely related, each has its own purpose, and the two work together to achieve a secure database.
A well-planned and 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 in which the user works, a telephone number or extension, and the database name to which the user has access. Personal user information should only be accessible 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, and should not want to know, the individual’s password. This ensures a separation of duties and protects the DBA’s integrity should problems with a user’s account arise.
If a user no longer requires certain privileges granted to her, 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 is going 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.
By the Way: There Are More Aspects to Database Security Than Privileges
Because this is an SQL book, not a database book, it focuses on database privileges. However, you should keep in mind other aspects to database security, such as the protection of underlying database files, which holds equal importance with 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/.
What Are Privileges?
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 via the GRANT command and are taken away via 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.
System Privileges
System privileges are those that allow 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 could result in serious repercussions if not carefully used.
System privileges vary greatly among the different relational database vendors, so you must check your particular implementation for all the available system privileges and their correct usage.
The following are some common system privileges in SQL Server:
• CREATE DATABASE— Allows for the creation of a new database
• CREATE PROCEDURE— Allows for the creation of stored procedures
• CREATE VIEW— Allows for the creation of views
• BACKUP DATABASE— Allows the user to control backup of the database system
• CREATE TABLE— Allows the user to create new tables
• CREATE TRIGGER— Allows the user to create triggers on tables
• EXECUTE— Allows the user to execute given stored procedures within the specific database
The following are some common system privileges in Oracle:
• CREATE TABLE— Allows the user to create new tables in the specified schema
• CREATE ANY TABLE— Allows the user to create tables in any schema
• ALTER ANY TABLE— Allows the user to alter table structure in any schema
• DROP TABLE— Allows the user to drop table objects in the specified schema
• CREATE USER— Allows the user to create other user accounts
• DROP USER— Allows the user to drop existing user accounts
• ALTER USER— Allows the user to make alterations to existing user accounts
• ALTER DATABASE— Allows the user to alter database properties
• BACKUP ANY TABLE— Allows the user to backup data from any table in any schema
• SELECT ANY TABLE— Allows the user to perform a select on any table from any schema
The following are some common global (system) privileges in MySQL:
• CREATE— Allows the user to create a specific object type such as a database, table, or index
• DROP— Allows the user to delete a specific object type
• GRANT— Allows the user to grant permissions on specific object types
• RELOAD— Allows the user to perform a FLUSH operation to purge items such as log files
• SHUTDOWN— Allows the user to shut down the MySQL instance
By the Way: There Can Be Many Layers of Privileges
MySQL has global privileges and object privileges. Global privileges, similar to system privileges, deal with user access to all database objects.
Object Privileges
Object privileges are authority levels on objects, meaning 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)— Allows data insertion to a specific column of a specified table.
• INSERT— Allows insertion of data into all columns of a specific table.
• UPDATE(column_name)— Allows a specific column of a specified table to be updated.
• UPDATE— Allows all columns of a specified table to be updated.
• 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.
Did You Know?: Some Privileges Are Granted Automatically
The owner of an object has been automatically granted all privileges that relate to the objects owned. These privileges have also been 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 object privileges are available among different implementations not listed in this section. The capability to delete data from another user’s object is another common object privilege available in many implementations. Be sure to check your implementation documentation for all the available object-level privileges.
Who Grants and Revokes 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 on what to grant or revoke would come from management and normally should be carefully tracked to ensure that only authorized individuals are allowed access to 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.
Controlling User Access
User access is primarily controlled by a user account and password, but that is not enough to access the database in most major implementations. The creation of 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 be able to assign appropriate system-level privileges to a user for that user to be allowed to 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 the user can do his job.
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.
The GRANT Command
The GRANT command grants both system-level and object-level privileges to an existing database user account.
The syntax is as follows:
GRANT PRIVILEGE1 [, PRIVILEGE2 ][ ON OBJECT ]
TO USERNAME [ WITH GRANT OPTION | ADMIN OPTION]
Granting one privilege to a user is as follows:
GRANT SELECT ON EMPLOYEE_TBL TO USER1;
Grant succeeded.
Granting multiple privileges to a user is as follows:
GRANT SELECT, INSERT ON EMPLOYEE_TBL TO USER1;
Grant succeeded.
Notice that when granting multiple privileges to a user in a single statement, each privilege is separated by a comma.
By the Way: Be Sure to Understand the Feedback the System Is Giving 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.
Granting privileges to multiple users is as follows:
GRANT SELECT, INSERT ON EMPLOYEE_TBL TO USER1, USER2;
Grant succeeded.
GRANT OPTION
GRANT 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:
GRANT SELECT ON EMPLOYEE_TBL TO USER1 WITH GRANT OPTION;
Grant succeeded.
ADMIN OPTION
ADMIN OPTION is similar to GRANT OPTION in that the user who has been granted the privileges also inherits the ability 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.
The REVOKE Command
Watch Out!: 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.
The 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 granted USER1 privileges with GRANT OPTION, USER1 granted 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 her privileges revoked.
The syntax for REVOKE is as follows:
REVOKE PRIVILEGE1 [, PRIVILEGE2 ] [ GRANT OPTION FOR ] ON OBJECT
FROM USER { RESTRICT | CASCADE }
The following is an example:
REVOKE INSERT ON EMPLOYEE_TBL FROM USER1;
Revoke succeeded.
Controlling Access on Individual Columns
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, as shown in the following example:
GRANT UPDATE (NAME) ON EMPLOYEES TO PUBLIC;
Grant succeeded.
The PUBLIC Database Account
The 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. The following is an example:
GRANT SELECT ON EMPLOYEE_TBL TO PUBLIC;
Grant succeeded.
Groups of Privileges
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 ten privileges, the group can be granted to a user instead of individually granting all ten privileges.
Oracle has groups of privileges that are called roles. Oracle includes the following groups of privileges with their implementations:
• CONNECT— Allows a user to connect to the database and perform operations on any database objects to which the user has access.
By the Way: Database Privilege Groups Vary Between Systems
Each implementation differs on the use of groups of database privileges. If available, this feature should be used for ease of database security administration.
• RESOURCE— Allows a user to create objects, drop objects he owns, grant privileges to objects he 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.
Watch Out!: 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 would give everyone who has access to the database the rights to see what everyone in the company is being paid!
An example for granting a group of privileges to a user follows:
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
• DB_DDLADMIN
• DB_DATAREADER
• DB_DATAWRITER
The DB_DDLADMIN role allows the user to manipulate any of the objects within the database through any legal data definition language command. The DB_DATAREADER role allows the user to select from any of the tables within the database from which it is assigned.
The DB_DATAWRITER role allows the user to perform any data manipulation syntax—INSERT, UPDATE, or DELETE—on any of the tables within the database.
Controlling Privileges Through Roles
A role is an object created in the database that contains group-like 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. Let’s say that a schema named APP01 grants the SELECT table privilege to the RECORDS_CLERK role on the EMPLOYEE_PAY table. Any user or role granted the RECORDS_CLERK role now would have SELECT privileges on the EMPLOYEE_PAY table.
Likewise, if APP01 revoked the SELECT table privilege from the RECORDS_CLERK role on the EMPLOYEE_PAY table, any user or role granted the RECORDS_CLERK role would no longer have SELECT privileges on that table.
When assigning permissions in a database, ensure that you think through what permissions a user needs and if other users need the same sets of permissions. For example, a set of accounting tables might need to be accessed by several members of an accounting team. 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 now 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 someone else needs the same access to its tables, you only have to assign the role to the new user and you are good to go. Roles are an excellent tool to enable the DBA to work smarter and not harder when dealing with complex database security protocols.
The CREATE ROLE Statement
A role is created with the CREATE ROLE statement.
CREATE ROLE role_name;
Granting privileges to roles is the same as granting privileges to a user. Study the following example:
CREATE ROLE RECORDS_CLERK;
Role created.
GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE_PAY TO RECORDS_CLERK;
Grant succeeded.
GRANT RECORDS_CLERK TO USER1;
Grant succeeded.
The DROP ROLE Statement
A role is dropped using the DROP_ROLE statement:
DROP ROLE role_name;
The following is an example:
DROP ROLE RECORDS_CLERK;
Role dropped.
By the Way: Roles Are Not Supported in MySQL
MySQL does not support roles. The lack of role usage is a weakness in some implementations of SQL.
The SET ROLE Statement
A role can be set for a user SQL session using the SET_ROLE statement:
SET ROLE role_name;
The following is an example:
SET ROLE RECORDS_CLERK;
Role set.
You can set more than one role at once:
SET ROLE RECORDS_CLERK, ROLE2, ROLE3;
Role set.
By the Way: 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 in to the database. The SET ROLE syntax here is shown so that you can understand what the ANSI standard for setting a role is.
Summary
You were shown the basics on implementing security in an SQL database or a relational database. You learned the basics of managing database users. The first step in implementing security at the database level for users is to create the user. Then the user must be assigned certain privileges that allow 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. System privileges are those that 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 are those that allow a user access to specific objects within the database, such as the ability to select data or manipulate data in a specific table.
Two commands in SQL allow a user to grant and revoke privileges to and from other users or roles in the database: GRANT and REVOKE. These two commands control the overall administration of privileges in the database. Although there are many other considerations for implementing security in a relational database, this hour discussed the basics that relate to the language of SQL.
Q&A
Q. If a user forgets her password, what should she do to gain access to the database again?
A. The user should go to her immediate management or an available help desk. A help desk should be able to reset a user’s password. If not, the DBA or security officer can reset the password. The user should change the password to a password of her choosing as soon as the password is reset and the user is notified. Sometimes the DBA can affect this by setting a specific property that forces the user to change her password on 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 CONNECT, but only the privileges required. Should you ever 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 upon creation of the user ID. 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.
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. What option must a user have to grant another user privileges on an object not owned by the user?
2. When privileges are granted to PUBLIC, do all database users acquire the privileges, or only specified users?
3. What privilege is required to look at data in a specific table?
4. What type of privilege is SELECT?
5. 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?
Exercises
1. Log in to your database instance and switch the database instance to use the learnsql database if it is not set as your default.
2. Type the following at the database prompt to get a list of the default tables depending on your implementation:
MySQL: SHOW TABLES;
SQL Server: SELECT NAME FROM SYS.TABLES;
Oracle: SELECT * FROM USER_TABLES;
3. Create a new database user as follows:
Username: Steve
Password: Steve123
Access: learnsql database, SELECT on all tables
4. Get a list of all database users by typing the following depending on your implementation:
MySQL: SELECT * FROM USER;
SQL Server: SELECT * FROM SYS.DATABSE_PRINCIPALS WHERE TYPE='S';
Oracle: SELECT * FROM DBA_USERS;