SECURITY

SECURITY

A database acts as a central repository of data for any organization. Therefore, security is an important concept for any database management system.

Security is to protect the data from unauthorized access and unwanted changes.

The primary goals of security are to ensure:

Data confidentiality: Only authorized individuals should be able to view data.
Data integrity: All authorized users should feel confident that the data presented to them is accurate and not improperly modified.
Data availability: Authorized users should be able to access the data they need, when they need it.

Database security can be divided into two parts:

System security
Data security

System security covers access and use of database at system level, such as username and password, and the systems operations that users can perform.

Data security covers access and use of database objects, and the operations that the users can perform on the objects.

Security in Oracle 9i

Oracle 9i provides features like standard user authentication and oracle advanced security to protect the data from unauthorized user access. These features offer data integrity, data privacy, authentication and authorization.

User Authentication and authorization:

Oracle 9i provides User Authentication and authorization. User Authentication is to check for the identity of a user. A user must supply a valid username and password to access a database. Authorization is to make sure that user has appropriate privileges to access an object. Authorization control is to check which user is allowed what operation on which database object.

These prevent unauthorized access.

Grant based security:

Profiles, privileges, and roles can be used in oracle to control user access.

Profile: It is a mechanism within the database which allows setting and controlling the password and resource limits for a user.

Oracle server automatically creates a DEFAULT profile, whenever a database is created.

Profile usage:

Allow assemble resource limits for similar users.
Make sure that user logs off the database when they have left their session idle for some time.
Manage resource usage in large, complex multiuser database systems.
Easily assign resource limits to users.
Restrict users from performing operations that require large amount of resources.
Control the use of passwords.

Oracle password management is controlled with profiles, and it provides greater control over database security.

Privileges: They are rights to control user access to database and its objects. With privileges, some users may be allowed only to view the data, while others may have the control to create and update the same data.

System privileges are to provide users with access to the database. System privileges should be used where security is not a major concern, because a single grant statement could remove all the security from the table.

Object privileges are to allow the users to manipulate the content of the objects in the database.

It is very complex form of providing security. Large number of individual grant statements may be required to assign security.

Roles: A role is a group of related privileges that can be granted to the user. A role makes it easier to maintain, revoke and grant the privileges.

Role is a way to manage the privileges. Earlier to grant user access to an object, it had to be done on a table-by-table basis which made it very difficult to manage the privileges. That is when oracle created role database object.

Roles provide security by defining a set of access rules or privileges and then assigning them to the users.

Invoker rights:

Invoker rights means that the user who creates the stored procedure is the only one with rights to a table affected by the procedure. These are rights to control privileges, one grants to the users. For example, if a procedure is created that is to update data in a table, users can run this procedure to perform update, but they have not been granted the update privilege. Only way for users to update data in table is by using the program.

Invoker rights take security to one step further. With invoker rights, methods that used to manipulate the data in a database can be controlled. And one does not need to worry if users can change data without any kind of control on your part.

Oracle Advanced Security

Oracle Advanced Security provides data privacy, encryption, integrity, authentication and single sign-on.

Encryption ensures data privacy by keeping the data to be transmitted between nodes as private.
Integrity is to ensure for secure transmission by checking that data is not changed or tempered with during transmission.
Single sign-on enables users to authenticate to multiple servers using a single username/password combination.
Authentication makes sure that users are authenticated or verified properly.

Auditing

Oracle 9i introduced a new feature called “fine-grained auditing. It allows auditing i.e. keeping track of activities down to column level in queries. By default, only instance startup, instance shutdown and connections to the database using privileges are audited.

Fine-grained auditing is not suggested for daily activities, but may be useful for specific auditing situations.

Firewalls in oracle

Oracle provides firewall support

Oracle Net Firewall Proxy Kit allows firewall vendors to provide connection support for Oracle environments.

Oracle supports two categories of firewalls:

Proxy- based firewalls
Stateful packet inspection firewalls

Virtual Private Database

“The Virtual Private Database (VPD) is the aggregation of server-enforced, fine-grained access control, together with a secure application context in the Oracle9i database server. It provides a flexible mechanism for building applications that enforce the security policies customers want enforced, only where such control is necessary.”

This feature enables to configure security at row level.

The Virtual Private Database offers the following benefits:

Lower cost of ownership. Organizations can reap huge cost savings by building security once, in the data server, instead of implementing the same security in each application that accesses data.

Elimination of the “application security problem.” Users cannot bypass security policies embedded in applications because the security policy is attached to the data. The same security policy is automatically enforced by the data server, no matter how a user accesses data, whether through a report-writing tool, a query, or through an application.

Application transparency. Virtual Private Database is enforced at the database layer and takes into account application-specific logic used to limit data access within the database. Both commercial off the- shelf applications and custom-built applications can take advantage of its granular access control, without the need to change any lines of application code.

New business opportunities. In the past, organizations couldn’t give customers and partners direct access to their production systems because there was no way to secure the data. Hosting companies couldn’t have data for multiple companies reside in the same data server, because they could not separate each company’s data. Now, all these scenarios are possible, because fine-grained access control gives you server-enforced data security with the assurance of physical data separation.

These benefits contribute to Oracle9i’s industry-leading security solutions.

Security in SQL Server 2000

Two main ways to implement security in SQL Server 2000 are Authentication and permissions.

Authentication

SQL Server 2000 provides two different security modes:

Windows Authentication Mode security
Mixed Mode security (both windows authentication and SQL Server authentication)

Windows Authentication Mode:

When SQL Server is run under windows 2000, security is checked in three different places, as you attempt to connect to SQL Server 2000. Validations are done by Windows 2000, SQL server itself (in the form of a SQL Server login), and then at the database level (in the form of a database username).

Windows Authentication: when connection is made from client computer to a windows NT/2000 computer running SQL Server 2000, Windows might require validation of network connection.

SQL server login Authentication: A valid SQL Server login name and password are required to connect to SQL Server.

SQL Server Database Username: A valid database username is required to use any database on your system.

Mixed Mode security:

In Mixed Mode security, a user can connect either using Windows authentication or SQL Server authentication.

SQL Server authentication: In this mode, SQL server accepts a login ID and password from a user, and without any help from Windows, checks whether the credentials are valid or not i.e. it checks whether it is a trusted connection or not. If it is a trusted connection, then SQL Server uses Windows authentication, otherwise uses SQL Server authentication (i.e. try to find the same login Id and password which user has passed)

In SQL Server authentication, SQL Server is fully responsible for authenticating a user and enforcing password and login restrictions.

Windows authentication is not available when SQL Server is running on Windows 9x computer. Hence, SQL Server authentication is always used on a windows 9x computer.

Windows authentication mode is the default authentication mode for SQL Server 2000. It provides advanced security features such as password expirations, password attributes, auditing and account lockouts.

For all trusted connections, use Windows authentication mode. Otherwise use mixed mode.

Permissions:

Permissions are final layer of security. These are explicit rights given to access database, either for read-only or modification operations.

Two types of permissions are granted:

Statement level permissions
Object level permissions

Statement level permissions are to allow a user to run a particular Transact-SQL command. These permissions allow users to create objects, create databases or back up databases.

Object level permissions are to allow a user to perform some operation like SELECT, INSERT, UPDATE or DELETE.

Permissions in SQL Server 2000 are managed by three terms: GRANT, DENY and REVOKE.

Permissions must be implemented very carefully to ensure the security of the data. In SQL Server, no database users have any inherent permission; they have only the permissions which are granted to them.

Roles:

Role in SQL Server 2000 is like a group to which individual users or logins can be added. Roles provide a flexible approach to manage security. SQL server provides 4 types of roles:

Fixed server roles
Fixed database roles
User defined roles
Application roles

Fixed server roles:

Logins or users can be added to these roles to assign the administrative permissions of this role. Instead of giving full system administrator functionality, these roles allow the DBA to provide only server level permissions that each user require. These are also called server wide roles. SQL Server 2000 provides a list of fixed server roles which cannot be altered and new server roles cannot be created.

This approach allows DBAs to keep better track of the members and their actions and provides highest level of security.

Fixed database roles:

Database users can be added to these roles to assign the database administrative permissions. Instead of giving full database owner functionality, these roles allow the DBA to provide only database level permissions to the user. These fixed server roles are unique within a database. SQL server provides a list of fixed database roles which cannot be altered, but new database roles can be created.

Database roles are database specific, and one cannot have roles that affect more than a single database at any time. These roles provide security at database level.

User defined roles:

Roles can be created and then users or roles can be assigned to the newly created roles. These roles allow the DBA or database owner to manage permissions in a more easy way.

This feature should be carefully used to avoid security conflicts.

Application roles:

Application roles are a very important and useful feature of SQL Server 2000. These roles are different from other roles due to the need of a password to be activated and they do not contain any users. These roles are also database specific.

After activation of these roles, users cannot access objects in other databases. If the user wants to run a cross-database transaction, the other database must have a guest user account enabled.

SQL Server 2000 Auditing:

Auditing has made SQL Server 2000 more secure and easier to administer. Auditing is not turned on by default. But by enabling auditing, SQL Server 2000 can audit user activity such as which tables users access, which queries users run, and which stored procedures users invoke.

Auditing can be enabled by clicking on the appropriate option under Audit level on the Security tab of the SQL Server Properties dialog box.

To keep a track of logins, it is recommended that both failure and success login records must be audited.

Encryption:

SQL Server encryption provides extra protection to secure the databases.

Windows 2000 introduced Encrypted File System (EFS) that allows encrypting individual files and folders on an NTFS partition. This feature can be used to encrypt SQL Server database files. The files must be encrypted using the service account of SQL Server. For changing the service account of SQL Server, the files must be decrypted, service account should be changed and the files should be encrypted again with the new service account.

Protocol encryption can be enabled for the server by selecting Force protocol encryption on the General tab of the Server Network Utility.

Comparison of security features in Oracle 9i and SQL Server 2000

Oracle 9i	SQL Server 2000
Authentication in Oracle 9i is done by providing a valid username and password.	Authentication in SQL Server 2000 is implemented by two ways: Windows Authentication Mode Mixed mode It is not that a basic concept as in Oracle.
Grant based security is implemented by profiles, privileges and roles.	Grant based security is implemented by permissions and roles. There are no profiles in SQL Server 2000.
Privileges in oracle 9i are the rights to control user access.	Permissions in SQL Server 2000 server the same purpose and controls the access to database.
Virtual Private Databases in Oracle 9iR2 takes security concept to row level.	In SQL Server 2000, row level permissions and security can be implemented using the views or stored procedures.
Oracle 9i provides fine- grained auditing feature for organizations to define specific targeted audit policies	SQL Server 2000 does not provide such a fine granularity of auditing functionality.
Oracle Advanced Security provides a better layer of security by data encryption.	In SQL Server 2000, encryption of database files is done using Windows 2000’s encrypted File system.
Oracle Net Firewall Proxy Kit allows firewall vendors to provide connection support for Oracle environments.	Computers using SQL Server 2000 databases must be protected using firewalls, proxies, etc.