Introduction
Security in enterprise applications is critical and increasingly complex because they manage 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 is not secure, anyone can see and change the data without authorization. Security tasks are (in addition to backup and recovery) the most important ones that DBA must perform.
Security in Microsoft SQL Server
Beginning with SQL Server, new concepts related to security come up. The main objects are classified as-
- Principals- These objects (for example, a user login or a role) may be granted permission to access particular database objects.
- Securables are objects (for example, a table or a view) to which access can be controlled.
- Permissions are the individual rights granted (or denied) to principals to access securable.
Principals fall into three different types: Windows principals, SQL Server principals, and database principals.
Windows principals in SQL Server
Windows principals are principals based on Windows authentication. SQL Server enables creating Windows logins based on domain user accounts, domain groups, local user accounts, and local groups. To grant permissions based on Windows logins, you must create a database user associated with this login.
In SQL Server, you can add a Windows group and user login using the sp_grantlogin stored procedure. In SQL Server, 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 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 based on a Windows group using the same statement but replacing John's name with the group's name.
In SQL Server, you must use several stored procedures to modify and drop logins; now, you can use simple ALTER LOGIN and DROP LOGIN statements. For example, let's drop the created login (see Listing 3).
drop login [john_comp\john];
Listing 3
SQL Server principals
SQL Server level logins and fixed server roles. SQL logins are created within SQL Server and have a login name and a password 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 to grant or deny permissions.
In SQL Server, the sp_addlogin stored procedure was used to add a new logic. The CREATE LOGIN statement is used in SQL Server, as listed 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 predefined SQL groups with permissions related to the instance scope. You cannot create fixed server roles, but you can add or remove members to that role.
Database principals
Database principals are database users, database roles (fixed and user-defined), and application roles. Whereas a login is an authentication method, database users are mapping for logins to grant or deny permissions to securable within the database.
Once a login is created, it can be mapped to a database user. In SQL Server, you can do this using the sp_grantdbaccess stored procedure. In SQL Server, 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
Fixed database roles have database-scoped permissions assigned to them. You cannot create fixed database roles, but you can add or remove members to that role. You can modify or drop the user using the ALTER USER and DROP USER statements.
User-defined database in SQL Server
User-defined database roles enable managing the permissions to database objects more quickly than if you had to grant the same permissions to multiple database users individually. Instead, you create a database role, grant permissions to it and then add database users to the database role.
The sp_addrole stored procedure was used in SQL Server to create a new database role. You can use the CREATE ROLE statement in SQL Server, 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 a 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 security. Securables in SQL Server falls into three categories:
- Server levels- Such as logins, databases, and endpoints.
- Database levels include database users, roles, certificates, and schemas.
- Schema levels- such as tables, views, functions, and procedures.
Permissions in SQL Server
Permissions are the mechanisms to allow principals access to the securable. The statements to achieve this goal are GRANT, DENY, and REVOKE. Grant is used to enable access to securable. DENY is used to disable access to securable. And REVOKE is used to remove specific permission on securable.
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
Server permissions are assigned to DBA to allow them to perform administrative tasks such as shutdown the instance, creating a new database, and changing the instance settings.
Database permissions
Database permissions enable access to database objects. For example, they enable SELECT, INSERT, DELETE, and UPDATE operations on tables and EXECUTE on stored procedures.
Security in Oracle database
In the Oracle database, security falls into two categories: data and system security. Data security is concerned with granting or denying permissions to several database objects. System security is concerned with the login process and management of the system's resources.
To connect to the Oracle database, the user must have an account. You can create an account using the CREATE USER statement (see Listing 11). When you create an account, you may specify the password, a profile, default tablespaces, and disk quotas.
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 monitor and control several database resources, such as concurrent connections to the database, maximum failed login attempts before the account is locked, etc.
Suppose we want to disconnect the user john after 15 minutes of idle time. First, let's create a profile, as shown in Listing 14. When an account is created by default is assigned to the DEFAULT profile with unlimited use of resources.
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 activities. 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 a group of users with a common set of privileges. This is an easy way to manage the security of Oracle databases. Every Oracle database has a pseudo role named PUBLIC that includes every user.
System privileges in SQL Server
System privileges allow users to do certain things, such as create sessions and drop users. In Oracle 10g, there are more than 160 privileges. For example, the CREATE SESSION privilege is crucial for logging into 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. Typical object privileges on objects include: select, insert, update, delete, execute, and index. In short, the object's owner in the schema has all the privileges on the object and grants privileges to other users.
In addition to the ability of the user to grant privileges on objects to other users, it's possible to grant the right 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 the administration of security concerning several users on database systems easy. Let's create a new role (see Listing 19).
create role hr_dept;
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).
grant hr_dept to john;
Listing 21
Conclusion
In this article, I've covered security principles and illustrated them through examples in the Oracle database and Microsoft SQL Server.