New Document
Computer Science
Computer Catlog
Oracle - TOC

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.

• Create users

• Change passwords

• Create roles

• Use views for security purposes

• Use synonyms in place of views


Wanted: Database Administrator


    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?

Database Product and Security


    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.

    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:

• Users

• Roles

• Privileges


Creating Users


    Users are account names that are allowed to log on to the Oracle database.

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:

SQL> CREATE USER Bryan IDENTIFIED BY CUTIGER;

    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.

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:

SQL> ALTER USER Bryan IDENTIFIED BY ROSEBUD;

To change the default tablespace, type this:

INPUT:

SQL> ALTER USER RON DEFAULT TABLESPACE USERS;

    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:

SQL> GRANT CONNECT TO Bryan;

    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:

SQL> GRANT RESOURCE TO Bryan;

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:

SQL> GRANT DBA TO Bryan;

    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:
I

INPUT:

SQL> REVOKE CONNECT FROM Bryan;

SQL> REVOKE RESOURCE FROM Bryan;

    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. 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 TO PUBLIC;

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.
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 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 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]

Using Views for Security Purposes


    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.

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 * FROM Bryan.SALARIES;

    If you were to create a view named SALARY-VIEW, a user could simply select from that view.

INPUT:

SQL> CREATE VIEW SALARY-VIEW AS SELECT * FROM Bryan.SALARIES;

SQL> SELECT * FROM SALARY-VIEW;

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.

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:

SQL> CREATE PUBLIC SYNONYM SALARY FOR SALARIES

Drop Synonym


SYNTAX:

SQL> drop [public] synonym synonym-name;

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 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. Just remember that no matter what product you are using, it is important to enforce some level of database security.


Google