Teach Yourself SQL in 21 Days, Second Edition
  
- Day 12 -
Database Security
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:
- Create users
- Change passwords
- Create roles
- Use views for security purposes
- Use synonyms in place of views
Wanted: Database Administrator
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.
Popular Database Products and Security
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:
- Microsoft FoxPro database management system is a powerful database system that
is used primarily in single-user environments. FoxPro uses a limited subset of SQL.
No security measures are provided with the system. It also uses an Xbase file format,
with each file containing one table. Indexes are stored in separate files.
- Microsoft Access relational database management system implements more of SQL.
Access is still intended for use on the PC platform, although it does contain a rudimentary
security system. The product enables you to build queries and store them within the
database. In addition, the entire database and all its objects exist within one file.
- Oracle7 relational database management system supports nearly the full SQL standard.
In addition, Oracle has added its own extension to SQL, called PL*SQL. It contains
full security features, including the capability to create roles and assign permissions
and privileges on objects in the database.
- Sybase SQL Server is similar in power and features to the Oracle product. SQL
Server also provides a wide range of security features and has its own extensions
to the SQL language, called Transact-SQL.
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.
How Does a Database Become Secure?
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:
- Who gets the DBA role?
- How many users will need access to the database?
- Which users will need which privileges and which roles?
- How will you remove users who no longer need access to the database?
Personal Oracle7 and Security
Oracle7 implements security by using three constructs:
Creating Users
Users are account names that are allowed to log on to the Oracle database. The
SQL syntax used to create a new user follows.
SYNTAX:
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:
INPUT/OUTPUT:
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:
SYNTAX:
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:
INPUT/OUTPUT:
SQL> ALTER USER Bryan
2 IDENTIFIED BY ROSEBUD;
User altered.
To change the default tablespace, type this:
INPUT/OUTPUT:
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:
SYNTAX:
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.
Creating Roles
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:
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:
SYNTAX:
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:
- Connect
- Resource
- DBA (or database administrator)
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
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.
INPUT/OUTPUT:
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
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.
INPUT/OUTPUT:
SQL> GRANT RESOURCE TO Bryan;
Grant succeeded.
The DBA Role
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.
INPUT/OUTPUT:
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:
INPUT/OUTPUT:
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.
User Privileges
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:
SYNTAX:
GRANT system_privilege TO {user_name | role | PUBLIC}
[WITH ADMIN OPTION];
WITH ADMIN OPTION enables the grantee to grant this privilege to someone
else.
User Access to Views
The following command permits all users of the system to have CREATE VIEW
access within their own schema.
INPUT:
SQL> GRANT CREATE VIEW
2 TO PUBLIC;
OUTPUT:
Grant succeeded.
ANALYSIS:
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.
Table 12.1. System privileges in Oracle7.
| 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.
Table 12.2. Object privileges enabled under 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:
SYNTAX:
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:
SYNTAX:
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]
From Creating a Table to Granting Roles
Create a table named SALARIES with the following structure:
INPUT:
NAME, CHAR(30)
SALARY, NUMBER
AGE, NUMBER
SQL> CREATE TABLE SALARIES (
2 NAME CHAR(30),
3 SALARY NUMBER,
4 AGE NUMBER);
OUTPUT:
Table created.
Now, create two users--Jack and Jill:
INPUT/OUTPUT:
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.
ANALYSIS:
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:
INPUT/OUTPUT:
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.
INPUT/OUTPUT:
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.
INPUT/OUTPUT:
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.
Qualifying a Table
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.
INPUT:
SQL> SELECT * FROM SALARIES;
SELECT * FROM SALARIES
*
OUTPUT:
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:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM Bryan.SALARIES;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
ANALYSIS:
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).
INPUT/OUTPUT:
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.
INPUT/OUTPUT:
SQL> INSERT INTO Bryan.SALARIES
2 VALUES('JOE',85000,38);
INSERT INTO Bryan.SALARIES
*
ERROR at line 1:
ORA-01031: insufficient privileges
ANALYSIS:
This operation did not work because Jill does not have INSERT privileges
on the SALARIES table.
INPUT/OUTPUT:
SQL> UPDATE Bryan.SALARIES
2 SET AGE = 42
3 WHERE NAME = 'JOHN';
UPDATE Bryan.SALARIES
*
ERROR at line 1:
ORA-01031: insufficient privileges
ANALYSIS:
Once again, Jill tried to go around the privileges that she had been given. Naturally,
Oracle caught this error and corrected her quickly.
INPUT/OUTPUT:
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
ANALYSIS:
You can see now that the update works as long as Jill abides by the privileges
she has been given.
Using Views for Security Purposes
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.
A Solution to Qualifying a Table or View
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:
INPUT:
SQL> SELECT *
2 FROM Bryan.SALARIES;
OUTPUT:
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.
INPUT/OUTPUT:
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
ANALYSIS:
The preceding query returned the same values as the records returned from Bryan.SALARIES.
Using Synonyms in Place of Views
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.
SYNTAX:
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:
INPUT/OUTPUT:
SQL> CREATE PUBLIC SYNONYM SALARY FOR SALARIES
Synonym created.
Then log back on to Jack and type this:
INPUT/OUTPUT:
SQL> SELECT * FROM SALARY;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 35000 55
Using Views to Solve Security Problems
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.
INPUT/OUTPUT:
SQL> CREATE VIEW JACK_SALARY AS
2 SELECT * FROM BRYAN.SALARIES
3 WHERE NAME = 'JACK';
View created.
INPUT/OUTPUT:
SQL> CREATE VIEW JILL_SALARY AS
2 SELECT * FROM BRYAN.SALARIES
3 WHERE NAME = 'JILL';
View created.
INPUT/OUTPUT:
SQL> GRANT SELECT ON JACK_SALARY
2 TO JACK;
Grant succeeded.
INPUT/OUTPUT:
SQL> GRANT SELECT ON JILL_SALARY
2 TO JILL;
Grant succeeded.
INPUT/OUTPUT:
SQL> REVOKE SELECT ON SALARIES FROM JACK;
Revoke succeeded.
INPUT/OUTPUT:
SQL> REVOKE SELECT ON SALARIES FROM JILL;
Revoke succeeded.
Now log on as Jack and test out the view you created for him.
INPUT/OUTPUT:
SQL> SELECT * FROM Bryan.JACK_SALARY;
NAME SALARY AGE
---------- ---------- ----
Jack 35000 29
INPUT/OUTPUT:
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:
INPUT/OUTPUT:
SQL> SELECT * FROM Bryan.JILL_SALARY;
NAME SALARY AGE
------------------ ------------- ----
Jill 48000 42
ANALYSIS:
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
SYNTAX:
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.
Using the WITH GRANT OPTION Clause
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:
SYNTAX:
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:
INPUT:
SQL> GRANT SELECT, UPDATE(SALARY)
2 ON Bryan.SALARIES TO JILL
3 WITH GRANT OPTION;
OUTPUT:
Grant succeeded.
Jill could then log on and issue the following command:
INPUT/OUTPUT:
SQL> GRANT SELECT, UPDATE(SALARY)
2 ON Bryan.SALARIES TO JACK;
Grant succeeded.
Summary
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.
Q&A
- Q I understand the need for security, but doesn't Oracle carry it a bit too
far?
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.
Workshop
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."
Quiz
- 1. What is wrong with the following statement?
SQL> GRANT CONNECTION TO DAVID;
- 2. True or False (and why): Dropping a user will cause all objects owned
by that user to be dropped as well.
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;
- 5. Is the following SQL statement correct?
SQL> alter RON
identified by RON;
- 6. Is the following SQL statement correct?
SQL> grant connect, resource to RON;
- 7. If you own a table, who can select from that table?
Exercise
- 1. Experiment with your database system's security by creating a table
and then by creating a user. Give this user various privileges and then take them
away.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
