Introduction
Security in enterprise applications is a critical and increasingly complex subject because they manage the enterprise data. Security deals with different aspects of the enterprise application such as network transport, network access, and physical protection of database servers. If your database system has no security, then anyone can see and change the data without authorization. Security tasks are (in addition to backup and recovery) the most important one that DBA must perform.
Security in Microsoft SQL Server
Beginning with SQL Server 2005, new concepts related to security come up. The main objects are classified as:
- Principals. These are objects (for example a user login or a role) that may be granted permission to access particular database objects.
- Securables. These are objects (for example a table or a view) to which access can be controlled.
- Permissions. These are the individual rights granted (or denied) to principals to access securables.
Principals fall into three different types: Windows principals, SQL Server principals and database principals.
Windows principals are principals based on Windows authentication. SQL Server enables creating Windows logins based on domain user accounts, domain groups, local user account, and local groups. To grant permissions based on Windows logins, you need to create a database user and associated to this login. In SQL Server 2000, you can add a Windows group and user login by the sp_grantlogin stored procedure. In SQL Server 2005, a new statement was added to do this task and the syntax is as follows in Listing 1.
| CREATE LOGIN login_name FROM WINDOWS [WITH DEFAULT_DATABASE=database_name I DEFAULT_LANGUAGE= language] | CERTIFICATE=certname | ASYMETRIC KEY= key_name |
Listing 1
Now let's create a new Windows login for a local Windows user (see Listing 2).
create login [john_comp\john]
from windows
with default_database=AdventureWorks, default_language=English; |
Listing 2
You can also create a login for based on a Windows group using the same statement but replacing John name with the name of the group.
In SQL Server 2000, you need to use several stored procedures to modify and drop logins, now you can use simple ALTER LOGIN and DROP LOGIN statements. For example, le's drop the created login (see Listing 3).
| drop login [john_comp\john]; |
Listing 3
SQL Server principals are SQL Server level logins and fixed server roles. SQL logins are created within SQL Server and have a login name and a password which stored in the database. Because this method relies on explicit passwords, it's less secure than the Windows counterpart. Server roles are groupings of instance level permissions. As with Windows logins, you must create a database user and map it to the login in order to grant or deny permissions.
In SQL Server 2000, to add a new logic the sp_addlogin stored procedure was used. In SQL Server 2005, the CREATE LOGIN statement is used as shown in Listing 4.
| CREATE LOGIN login_name [WITH PASSWORD='password' [HASHED][MUST_CHANGE],SID=sid], DEFAULT_DATABASE=database_name, DEFAULT_LANGUAGE=language |
Listing 4
Let's illustrate this statement with an example as shown in Listing 5.
create login [john]
with password='my_password', default_database=AdventureWorks, default_language=English; |
Listing 5
Fixed server roles are pre-defined SQL groups that permissions in related to the instance scope. You cannot create fixed server roles, but you can add or remove membership to that role.
Database principals are database users, database roles (fixed and user-defined), and application roles. Whereas a login is a method for authentication, database users are mapping for logins in order to grant or deny permissions to securables within the database.
Once a login is created, it can be mapped to a database user. In SQL Server 2000, you can do this using the sp_grantdbaccess stored procedure. In SQL Server 2005, the CREATE USER statement was introduced (see Listing 6).
| CREATE USER user_name [FOR LOGIN login_name ][WITH DEFAULT_SCHEMA=schema_name] |
Listing 6
Now let's illustrate with an example (see Listing 7).
use master;
create login [john]
with password='my_password', default_database=AdventureWorks, default_language=English;
use AdventureWorks;
create user [john] for login [john]; |
Listing 7
You can modify or drop the user using the ALTER USER and DROP USER statements. Fixed database roles have database scoped permissions assigned to them. You cannot create fixed database roles, but you can add or remove membership to that role.
User defined database roles enables managing the permissions to database objects more easily than if you had to individually grant the same permissions to multiple database users. Instead, you create a database role, grant permissions to it and then add database users to the database role. In SQL Server 2000, the sp_addrole stored procedure was used to create a new database role. In SQL Server 2005, you can use the CREATE ROLE statement as shown in Listing 8. It's remarkable to say that in SQL Server there is a pseudo database role named PUBLIC that includes every
user.
CREATE ROLE role_name [AUTHORIZATION owner_name] |
Listing 8
You can change and drop the role by using the ALTER ROLE and DROP ROLE statements. You can use the sp_addrolemember stored procedure to add database users to that database role.
An application role is a hybrid between a login and database role. You can grant permissions to applications role, but they don't have members. Instead, an application role is activated by sending a password using the sp_setapprole stored procedure. Once an application role is activated, any existing database user and role permissions are overridden. You can create an application role by following the syntax (see Listing 9):
| CREATE APPLICATION ROLE application_role_name WITH PASSWORD='password'[, DEFAULT_SCHEMA=schema_name] |
Listing 9
Now let's talk about securables. Securables in SQL Server 2005 fall into three categories:
- Server level. Such as logins, databases and endpoints.
- Database level. Such as database users, database roles, certificates and schemas.
- Schema level. Such as tables, views, functions and procedures.
Permissions are the mechanisms to allow principals access the securables. The statements to achieve this goal are GRANT, DENY, REVOKE. Grant is used to enable access to securables. DENY is used to disable access to securables. And REVOKE is used to remove a specific permission on securables.
The GRANT statement has the following syntax as shown in Listing 10:
GRANT {ALL [PRIVILEGES]}[ON securable_name]TO[principal_name][WITH GRANT OPTION][AS principal]
|
Listing 10
There are two types of permissions: server permission and database permission.
Server permissions are assigned to DBA to allow them to perform administrative tasks such as shutdown the instance, create a new database and change the instance settings.
Database permissions enables to access database objects. For example, they enable SELECT, INSERT, DELETE, UPDATE operations on tables and EXECUTE on stored procedures.
Security in Oracle database
In Oracle database, the security falls into two category: data security and system security. Data security is concerned to granting or denying permissions to several objects of the database. System security is concerned the login process and management of the resources of the system.
In order to connect to Oracle database, the user must have an account. When you create an account, you may specify the password, a profile, default tablespaces and disk quotas. You can create an account using the CREATE USER statement (see Listing 11).
| CREATE USER user_name <options>; |
Listing 11
Let's create a user and assign a password (see Listing 12).
| create user john identified by john_password; |
Listing 12
You can create a user to be authenticated externally (for example, by the operating system) rather than supplying a password (see Listing 13).
| create user domain$user identified externally; |
Listing 13
You can also change the user's properties using the ALTER USER statement. One common use is to assign a profile to a user. A profile is a set of predefined resource parameters that can be used to monitor and control several database resources for example concurrent connections to the database, maximum failed login attempts before the account is locked, etc. When an account is created by default is assigned to the DEFAULT profile with unlimited use of resources. Let's supposed we want to disconnect the user john after 15 minutes of idle time. First of all, let's create a profile as shown in Listing 14.
| create profile prf_idle_time limit idle_time 15; |
Listing 14
Then apply this profile to the user (see Listing 15).
| alter user john profile prf_idel_time; |
Listing 15
Once the user is authenticated to the database, the user needs to perform actions. Privileges are the rights to perform such actions. There are two types of privileges: system privileges and object privileges. The GRANT statement allocates system and object privileges to users and roles.
The REVOKE statement removes the privileges from the user and roles.
A role is group of users which have a common set of privileges. This is an easy way to manage the security of the Oracle databases. Every Oracle database has pseudo role named PUBLIC that includes every user.
System privileges allow users to do certain things such as create session and drop users. In Oracle 10g, there are more than 160 privileges. For example, the CREATE SESSION privilege is very important in order to log in the Oracle database. Let's grant CREATE SESSION privilege to the
created user (see Listing 16).
grant create session to john; |
Listing 16
Object privilege allows users to manipulate the content of database objects in other schemas. In short, the owner of the object in the schema has all the privileges on the object and grants privileges on the object to other users. Typical object privileges on objects includes: select, insert, update, delete, execute and index. In addition to the ability of the user to grant privileges on objects to other users, it's possible to grant the privilege for the grantee to subsequently grant the same privilege to other users using the WITH GRANT OPTION clause of the GRANT statement.
Let's grant SELECT privilege on the scott.emp table to the john user (see Listing 17).
| grant select on scott.emp to john; |
Listing 17
You can revoke the privilege (see Listing 18).
| revoke select on scott.emp from john; |
Listing 18
As explained before, roles make easy the administration of the security concerning several users on database systems. Let's create a new role (see Listing 19).
Listing 19
Now let's grant SELECT privilege to the hr_dept role on the scott.emp table (see Listing 20).
| grant select on scott.emp to hr_dept; |
Listing 20
Now let's add the user john to the role hr_dept (see Listing 21).