Today we discuss database security. We specifically look at various SQL statements and constructs that enable you to administer and effectively manage a relational database. Like many other topics you have studied thus far, how a database management system implements security varies widely among products. We focus on the popular database product Oracle7 to introduce this topic. By the end of the day, you will understand and be able to do the following:
Security is an often-overlooked aspect of database design. Most computer professionals enter the computer world with some knowledge of computer programming or hardware, and they tend to concentrate on those areas. For instance, if your boss asked you to work on a brand-new project that obviously required some type of relational database design, what would be your first step? After choosing some type of hardware and software baseline, you would probably begin by designing the basic database for the project. This phase would gradually be split up among several people--one of them a graphical user interface designer, another a low-level component builder. Perhaps you, after reading this book, might be asked to code the SQL queries to provide the guts of the application. Along with this task comes the responsibility of actually administering and maintaining the database.
Many times, little thought or planning goes into the actual production phase of the application. What happens when many users are allowed to use the application across a wide area network (WAN)? With today's powerful personal computer software and with technologies such as Microsoft's Open Database Connectivity (ODBC), any user with access to your network can find a way to get at your database. (We won't even bring up the complexities involved when your company decides to hook your LAN to the Internet or some other wide-ranging computer network!) Are you prepared to face this situation?
Fortunately for you, software manufacturers provide most of the tools you need to handle this security problem. Every new release of a network operating system faces more stringent security requirements than its predecessors. In addition, most major database vendors build some degree of security into their products, which exists independently of your operating system or network security. Implementation of these security features varies widely from product to product.
As you know by now, many relational database systems are vying for your business. Every vendor wants you for short- and long-term reasons. During the development phase of a project, you might purchase a small number of product licenses for testing, development, and so forth. However, the total number of licenses required for your production database can reach the hundreds or even thousands. In addition, when you decide to use a particular database product, the chances are good that you will stay with that product for years to come. Here are some points to keep in mind when you examine these products:
The purpose behind describing these products is to illustrate that not all software is suitable for every application. If you are in a business environment, your options may be limited. Factors such as cost and performance are extremely important. However, without adequate security measures, any savings your database creates can be easily offset by security problems.
Up to this point you haven't worried much about the "security" of the databases you have created. Has it occurred to you that you might not want other users to come in and tamper with the database information you have so carefully entered? What would your reaction be if you logged on to the server one morning and discovered that the database you had slaved over had been dropped (remember how silent the DROP DATABASE command is)? We examine in some detail how one popular database management system (Personal Oracle7) enables you to set up a secure database. You will be able to apply most of this information to other database management systems, so make sure you read this information even if Oracle is not your system of choice.
TIP: Keep the following questions in mind as you plan your security system:
Oracle7 implements security by using three constructs:
Users are account names that are allowed to log on to the Oracle database. The SQL syntax used to create a new user follows.
CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]
If the BY password option is chosen, the system prompts the user to enter a password each time he or she logs on. As an example, create a username for yourself:
SQL> CREATE USER Bryan IDENTIFIED BY CUTIGER; User created.
Each time I log on with my username Bryan, I am prompted to enter my password: CUTIGER.
If the EXTERNALLY option is chosen, Oracle relies on your computer system logon name and password. When you log on to your system, you have essentially logged on to Oracle.
NOTE: Some implementations allow you to use the external, or operating system, password as a default when using SQL (IDENTIFIED externally). However, we recommend that you force the user to enter a password by utilizing the IDENTIFIED BY clause (IDENTIFIED BY password).
As you can see from looking at the rest of the CREATE USER syntax, Oracle also allows you to set up default tablespaces and quotas. You can learn more about these topics by examining the Oracle documentation.
As with every other CREATE command you have learned about in this book, there is also an ALTER USER command. It looks like this:
ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]
[DEFAULT ROLE { role [, role] ...
| ALL [EXCEPT role [, role] ...] | NONE}]
You can use this command to change all the user's options, including the password and profile. For example, to change the user Bryan's password, you type this:
SQL> ALTER USER Bryan 2 IDENTIFIED BY ROSEBUD; User altered.
To change the default tablespace, type this:
SQL> ALTER USER RON 2 DEFAULT TABLESPACE USERS; User altered.
To remove a user, simply issue the DROP USER command, which removes the user's entry in the system database. Here's the syntax for this command:
DROP USER user_name [CASCADE];
If the CASCADE option is used, all objects owned by username are dropped along with the user's account. If CASCADE is not used and the user denoted by user_name still owns objects, that user is not dropped. This feature is somewhat confusing, but it is useful if you ever want to drop users.
A role is a privilege or set of privileges that allows a user to perform certain functions in the database. To grant a role to a user, use the following syntax:
GRANT role TO user [WITH ADMIN OPTION];
If WITH ADMIN OPTION is used, that user can then grant roles to other users. Isn't power exhilarating?
To remove a role, use the REVOKE command:
REVOKE role FROM user;
When you log on to the system using the account you created earlier, you have exhausted the limits of your permissions. You can log on, but that is about all you can do. Oracle lets you register as one of three roles:
These three roles have varying degrees of privileges.
NOTE: If you have the appropriate privileges, you can create your own role, grant privileges to your role, and then grant your role to a user for further security.
The Connect role can be thought of as the entry-level role. A user who has been granted Connect role access can be granted various privileges that allow him or her to do something with a database.
SQL> GRANT CONNECT TO Bryan; Grant succeeded.
The Connect role enables the user to select, insert, update, and delete records from tables belonging to other users (after the appropriate permissions have been granted). The user can also create tables, views, sequences, clusters, and synonyms.
The Resource role gives the user more access to Oracle databases. In addition to the permissions that can be granted to the Connect role, Resource roles can also be granted permission to create procedures, triggers, and indexes.
SQL> GRANT RESOURCE TO Bryan; Grant succeeded.
The DBA role includes all privileges. Users with this role are able to do essentially anything they want to the database system. You should keep the number of users with this role to a minimum to ensure system integrity.
SQL> GRANT DBA TO Bryan; Grant succeeded.
After the three preceding steps, user Bryan was granted the Connect, Resource, and DBA roles. This is somewhat redundant because the DBA role encompasses the other two roles, so you can drop them now:
SQL> REVOKE CONNECT FROM Bryan; Revoke succeeded. SQL> REVOKE RESOURCE FROM Bryan; Revoke succeeded.
Bryan can do everything he needs to do with the DBA role.
After you decide which roles to grant your users, your next step is deciding which permissions these users will have on database objects. (Oracle7 calls these permissions privileges.) The types of privileges vary, depending on what role you have been granted. If you actually create an object, you can grant privileges on that object to other users as long as their role permits access to that privilege. Oracle defines two types of privileges that can be granted to users: system privileges and object privileges. (See Tables 12.1 and 12.2.)
System privileges apply systemwide. The syntax used to grant a system privilege is as follows:
GRANT system_privilege TO {user_name | role | PUBLIC}
[WITH ADMIN OPTION];
WITH ADMIN OPTION enables the grantee to grant this privilege to someone else.
The following command permits all users of the system to have CREATE VIEW access within their own schema.
SQL> GRANT CREATE VIEW 2 TO PUBLIC;
Grant succeeded.
The public keyword means that everyone has CREATE VIEW privileges. Obviously, these system privileges enable the grantee to have a lot of access to nearly all the system settings. System privileges should be granted only to special users or to users who have a need to use these privileges. Table 12.1 shows the system privileges you will find in the help files included with Personal Oracle7.
WARNING: Use caution when granting privileges to public. Granting public gives all users with access to the database privileges you may not want them to have.
| System Privilege | Operations Permitted |
| ALTER ANY INDEX | Allows the grantees to alter any index in any schema. |
| ALTER ANY PROCEDURE | Allows the grantees to alter any stored procedure, function, or package in any schema. |
| ALTER ANY ROLE | Allows the grantees to alter any role in the database. |
| ALTER ANY TABLE | Allows the grantees to alter any table or view in the schema. |
| ALTER ANY TRIGGER | Allows the grantees to enable, disable, or compile any database trigger in any schema. |
| ALTER DATABASE | Allows the grantees to alter the database. |
| ALTER USER | Allows the grantees to alter any user. This privilege authorizes the grantee to change another user's password or authentication method, assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile and default roles. |
| CREATE ANY INDEX | Allows the grantees to create an index on any table in any schema. |
| CREATE ANY PROCEDURE | Allows the grantees to create stored procedures, functions, and packages in any schema. |
| CREATE ANY TABLE | Allows the grantees to create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table. |
| CREATE ANY TRIGGER | Allows the grantees to create a database trigger in any schema associated with a table in any schema. |
| CREATE ANY VIEW | Allows the grantees to create views in any schema. |
| CREATE PROCEDURE | Allows the grantees to create stored procedures, functions, and packages in their own schema. |
| CREATE PROFILE | Allows the grantees to create profiles. |
| CREATE ROLE | Allows the grantees to create roles. |
| CREATE SYNONYM | Allows the grantees to create synonyms in their own schemas. |
| CREATE TABLE | Allows the grantees to create tables in their own schemas. To create a table, the grantees must also have space quota on the tablespace to contain the table. |
| CREATE TRIGGER | Allows the grantees to create a database trigger in their own schemas. |
| CREATE USER | Allows the grantees to create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement. |
| CREATE VIEW | Allows the grantees to create views in their own schemas. |
| DELETE ANY TABLE | Allows the grantees to delete rows from tables or views in any schema or truncate tables in any schema. |
| DROP ANY INDEX | Allows the grantees to drop indexes in any schema. |
| DROP ANY PROCEDURE | Allows the grantees to drop stored procedures, functions, or packages in any schema. |
| DROP ANY ROLE | Allows the grantees to drop roles. |
| DROP ANY SYNONYM | Allows the grantees to drop private synonyms in any schema. |
| DROP ANY TABLE | Allows the grantees to drop tables in any schema. |
| DROP ANY TRIGGER | Allows the grantees to drop database triggers in any schema. |
| DROP ANY VIEW | Allows the grantees to drop views in any schema. |
| DROP USER | Allows the grantees to drop users. |
| EXECUTE ANY PROCEDURE | Allows the grantees to execute procedures or functions (standalone or packaged) or reference public package variables in any schema. |
| GRANT ANY PRIVILEGE | Allows the grantees to grant any system privilege. |
| GRANT ANY ROLE | Allows the grantees to grant any role in the database. |
| INSERT ANY TABLE | Allows the grantees to insert rows into tables and views in any schema. |
| LOCK ANY TABLE | Allows the grantees to lock tables and views in any schema. |
| SELECT ANY SEQUENCE | Allows the grantees to reference sequences in any schema. |
| SELECT ANY TABLE | Allows the grantees to query tables, views, or snapshots in any schema. |
| UPDATE ANY ROWS | Allows the grantees to update rows in tables. |
Object privileges are privileges that can be used against specific database objects. Table 12.2 lists the object privileges in Oracle7.
| ALL |
| ALTER |
| DELETE |
| EXECUTE |
| INDEX |
| INSERT |
| REFERENCES |
| SELECT |
| UPDATE |
You can use the following form of the GRANT statement to give other users access to your tables:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
To remove the object privileges you have granted to someone, use the REVOKE command with the following syntax:
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]
Create a table named SALARIES with the following structure:
NAME, CHAR(30) SALARY, NUMBER AGE, NUMBER SQL> CREATE TABLE SALARIES ( 2 NAME CHAR(30), 3 SALARY NUMBER, 4 AGE NUMBER);
Table created.
Now, create two users--Jack and Jill:
SQL> create user Jack identified by Jack; User created. SQL> create user Jill identified by Jill; User created. SQL> grant connect to Jack; Grant succeeded. SQL> grant resource to Jill; Grant succeeded.
So far, you have created two users and granted each a different role. Therefore, they will have different capabilities when working with the database. First create the SALARIES table with the following information:
SQL> SELECT * FROM SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55
You could then grant various privileges to this table based on some arbitrary reasons for this example. We are assuming that you currently have DBA privileges and can grant any system privilege. Even if you do not have DBA privileges, you can still grant object privileges on the SALARIES table because you own it (assuming you just created it).
Because Jack belongs only to the Connect role, you want him to have only SELECT privileges.
SQL> GRANT SELECT ON SALARIES TO JACK; Grant succeeded.
Because Jill belongs to the Resource role, you allow her to select and insert some data into the table. To liven things up a bit, allow Jill to update values only in the SALARY field of the SALARIES table.
SQL> GRANT SELECT, UPDATE(SALARY) ON SALARIES TO Jill; Grant succeeded.
Now that this table and these users have been created, you need to look at how a user accesses a table that was created by another user. Both Jack and Jill have been granted SELECT access on the SALARIES table. However, if Jack tries to access the SALARIES table, he will be told that it does not exist because Oracle requires the username or schema that owns the table to precede the table name.
Make a note of the username you used to create the SALARIES table (mine was Bryan). For Jack to select data out of the SALARIES table, he must address the SALARIES table with that username.
SQL> SELECT * FROM SALARIES;
SELECT * FROM SALARIES
*
ERROR at line 1: ORA-00942: table or view does not exist
Here Jack was warned that the table did not exist. Now use the owner's username to identify the table:
SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55
You can see that now the query worked. Now test out Jill's access privileges. First log out of Jack's logon and log on again as Jill (using the password Jill).
SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55
That worked just fine. Now try to insert a new record into the table.
SQL> INSERT INTO Bryan.SALARIES
2 VALUES('JOE',85000,38);
INSERT INTO Bryan.SALARIES
*
ERROR at line 1: ORA-01031: insufficient privileges
This operation did not work because Jill does not have INSERT privileges on the SALARIES table.
SQL> UPDATE Bryan.SALARIES
2 SET AGE = 42
3 WHERE NAME = 'JOHN';
UPDATE Bryan.SALARIES
*
ERROR at line 1: ORA-01031: insufficient privileges
Once again, Jill tried to go around the privileges that she had been given. Naturally, Oracle caught this error and corrected her quickly.
SQL> UPDATE Bryan.SALARIES 2 SET SALARY = 35000 3 WHERE NAME = 'JOHN'; 1 row updated. SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
You can see now that the update works as long as Jill abides by the privileges she has been given.
As we mentioned on Day 10, "Creating Views and Indexes," views are virtual tables that you can use to present a view of data that is different from the way it physically exists in the database. Today you will learn more about how to use views to implement security measures. First, however, we explain how views can simplify SQL statements.
Earlier you learned that when a user must access a table or database object that another user owns, that object must be referenced with a username. As you can imagine, this procedure can get wordy if you have to write writing several SQL queries in a row. More important, novice users would be required to determine the owner of a table before they could select the contents of a table, which is not something you want all your users to do. One simple solution is shown in the following paragraph.
Assume that you are logged on as Jack, your friend from earlier examples. You learned that for Jack to look at the contents of the SALARIES table, he must use the following statement:
SQL> SELECT * 2 FROM Bryan.SALARIES;
NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
If you were to create a view named SALARY_VIEW, a user could simply select from that view.
SQL> CREATE VIEW SALARY_VIEW 2 AS SELECT * 3 FROM Bryan.SALARIES; View created. SQL> SELECT * FROM SALARY_VIEW; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
The preceding query returned the same values as the records returned from Bryan.SALARIES.
SQL also provides an object known as a synonym. A synonym provides an alias for a table to simplify or minimize keystrokes when using a table in an SQL statement. There are two types of synonyms: private and public. Any user with the resource role can create a private synonym. On the other hand, only a user with the DBA role can create a public synonym.
The syntax for a public synonym follows.
CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object[@dblink]
In the preceding example, you could have issued the following command to achieve the same results:
SQL> CREATE PUBLIC SYNONYM SALARY FOR SALARIES
Synonym created.
Then log back on to Jack and type this:
SQL> SELECT * FROM SALARY; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
Suppose you changed your mind about Jack and Jill and decided that neither of them should be able to look at the SALARIES table completely. You can use views to change this situation and allow them to examine only their own information.
SQL> CREATE VIEW JACK_SALARY AS 2 SELECT * FROM BRYAN.SALARIES 3 WHERE NAME = 'JACK'; View created.
SQL> CREATE VIEW JILL_SALARY AS 2 SELECT * FROM BRYAN.SALARIES 3 WHERE NAME = 'JILL'; View created.
SQL> GRANT SELECT ON JACK_SALARY 2 TO JACK; Grant succeeded.
SQL> GRANT SELECT ON JILL_SALARY 2 TO JILL; Grant succeeded.
SQL> REVOKE SELECT ON SALARIES FROM JACK; Revoke succeeded.
SQL> REVOKE SELECT ON SALARIES FROM JILL; Revoke succeeded.
Now log on as Jack and test out the view you created for him.
SQL> SELECT * FROM Bryan.JACK_SALARY; NAME SALARY AGE ---------- ---------- ---- Jack 35000 29
SQL> SELECT * FROM PERKINS.SALARIES;
SELECT * FROM PERKINS.SALARIES
*
ERROR at line 1: ORA-00942: table or view does not exist
Log out of Jack's account and test Jill's:
SQL> SELECT * FROM Bryan.JILL_SALARY; NAME SALARY AGE ------------------ ------------- ---- Jill 48000 42
You can see that access to the SALARIES table was completely controlled using views. SQL enables you to create these views as you like and then assign permissions to other users. This technique allows a great deal of flexibility.
The syntax to drop a synonym is
SQL> drop [public] synonym synonym_name;
NOTE: By now, you should understand the importance of keeping to a minimum the number of people with DBA roles. A user with this access level can have complete access to all commands and operations within the database. Note, however, that with Oracle and Sybase you must have DBA-level access (or SA-level in Sybase) to import or export data on the database.
What do you think would happen if Jill attempted to pass her UPDATE privilege on to Jack? At first glance you might think that Jill, because she was entrusted with the UPDATE privilege, should be able to pass it on to other users who are allowed that privilege. However, using the GRANT statement as you did earlier, Jill cannot pass her privileges on to others:
SQL> GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO Jill;
Here is the syntax for the GRANT statement that was introduced earlier today:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
What you are looking for is the WITH GRANT OPTION clause at the end of the GRANT statement. When object privileges are granted and WITH GRANT OPTION is used, these privileges can be passed on to others. So if you want to allow Jill to pass on this privilege to Jack, you would do the following:
SQL> GRANT SELECT, UPDATE(SALARY) 2 ON Bryan.SALARIES TO JILL 3 WITH GRANT OPTION;
Grant succeeded.
Jill could then log on and issue the following command:
SQL> GRANT SELECT, UPDATE(SALARY) 2 ON Bryan.SALARIES TO JACK; Grant succeeded.
Security is an often-overlooked topic that can cause many problems if not properly thought out and administered. Fortunately, SQL provides several useful commands for implementing security on a database.
Users are originally created using the CREATE USER command, which sets up a username and password for a user. After the user account has been set up, this user must be assigned to a role in order to accomplish any work. The three roles available within Oracle7 are Connect, Resource, and DBA. Each role has different levels of access to the database, with Connect being the simplest and DBA having access to everything.
The GRANT command gives a permission or privilege to a user. The REVOKE command can take that permission or privilege away from the user. The two types of privileges are object privileges and system privileges. The system privileges should be monitored closely and should not be granted to inexperienced users. Giving inexperienced users access to commands allows them to (inadvertently perhaps) destroy data or databases you have painstakingly set up. Object privileges can be granted to give users access to individual objects existing in the owner's database schema.
All these techniques and SQL statements provide the SQL user with a broad range of tools to use when setting up system security. Although we focused on the security features of Oracle7, you can apply much of this information to the database system at your site. Just remember that no matter what product you are using, it is important to enforce some level of database security.
A No, especially in larger applications where there are multiple users. Because different users will be doing different types of work in the database, you'll want to limit what users can and can't do. Users should have only the necessary roles and privileges they need to do their work.
Q It appears that there is a security problem when the DBA that created my ID also knows the password. Is this true?
A Yes it is true. The DBA creates the IDs and passwords. Therefore, users should use the ALTER USER command to change their ID and password immediately after receiving them.
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
SQL> GRANT CONNECTION TO DAVID;
3. What would happen if you created a table and granted select privileges on the table to public?
4. Is the following SQL statement correct?
SQL> create user RON
identified by RON;
SQL> alter RON
identified by RON;
SQL> grant connect, resource to RON;
© Copyright, Macmillan Computer Publishing. All rights reserved.