Similarities and differences between SQL Server and Oracle databases in terms of security

In the industry, Oracle databases are generally considered to be more secure than SQL Server databases. Next I will talk about the similarities and differences between the two databases in terms of security design. Mastering these contents plays an important role in designing and managing database security. I. role-to-user authorization many applications, including databases

In the industry, Oracle databases are generally considered to be more secure than SQL Server databases. Next I will talk about the similarities and differences between the two databases in terms of security design. Mastering these contents plays an important role in designing and managing database security. I. role-to-user authorization many applications, including databases

In the industry, Oracle databases are generally considered to be more secure than SQL Server databases. Next I will talk about the similarities and differences between the two databases in terms of security design. Mastering these contents plays an important role in designing and managing database security.

1. role-to-user authorization

Nowadays, many application software, including database systems, use the role-to-user authorization system. That is to say, authorize a role first, and then add the user to the role so that it has the permissions of this role.
For example, when designing a database, we generally need at least two types of roles: one is the system administrator role, which can manage the database system, such as table creation and deletion, user creation and modification, etc. The other is the role of a common user, which can insert, modify, and delete records on the table. Then, we create two accounts: one is the database administrator account, which belongs to the administrator role; the other is to create a user account that belongs to the user role. In this way, users do not need to set special permissions, so they inherit the relevant permissions from their roles. This is a role-based user permission management system.

This permission management system supports both databases, but the specific names are different. In the SQL Server database, the role name is grouped by the Microsoft operating system name. In fact, this method is also used when we define users and permissions for the operating system. We should first create a group and then assign specific permissions to the group. Then, create another user account and add it to this group. Although the two databases have different names, they are essentially the same, and they don't need to be changed.

The differences between the two are mainly reflected in the authorization of specific roles.

2. Role authorization

Although the two databases are similar in "role-Authorization", there are still large differences in the allocation of specific role permissions. In one sentence, Oracle is more detailed in permission allocation than SQL Server databases. In terms of permission control, Oracle can basically refine each step.
For example, in user management, the Oracle database can grant the User Account creation permission to a group, but this group does not have the right to delete accounts. In Microsoft databases, user management permissions, including user creation and deletion, are generally granted to a group as a permission, but cannot be separated. That is to say, if a group has user account management permissions, it can not only create user accounts, but also delete user accounts. That is to say, Microsoft's SQL Server database is not as detailed as Oracle in terms of permission design. Because of this, Oracle databases are more flexible in permission management than SQL Server databases.
Not only in account management, but also in the management of many object permissions in the database. For example, you can manage stored procedures and tables. Oracle databases have lower permissions than other databases.

3. Independent user authorization

In addition to role-based authorization, Oracle databases can also directly authorize users at the user account level. Similar functions are available in SQL Server databases. However, in this regard, the former is more flexible than the latter. For example, the "Object authorization" function in Oracle databases cannot be implemented in SQL Server databases.

1. Oracle databases are more secure than SQL Server databases in terms of user passwords.
As we all know, user passwords and accounts are the first guarantee of database security. For example, when creating an Oracle database user name, it has Password Complexity verification settings by default. If we set the password to "123456" when creating the user name, the Oracle database will not accept it. The password is too simple and easy to crack. Microsoft SQL Server databases do not have such restrictions by default.

2. Users have different default permissions.
Both databases can create user names in at least two ways. First, create a user name and an account on the GUI. For example, Oracle databases can create user accounts through EM, that is, browsers, while SQL Server databases can create user accounts through Enterprise Manager. You can use the command line to create a user name through SQL statements.
From the perspective of SQL Server databases, there is no difference between the two creation methods. However, Oracle databases vary greatly, mainly because of the different default permissions. When a user account is created using a browser, the database connection permission is granted by default. If you create a user account in the command line, this account cannot be connected to the database unless you explicitly grant it the permission to connect to the database.

For example, we use commands to create a test user whose password is test111.
Create user test identified by test111;
Note: When you set the user name and password here, the database will not accept the password if it is a pure number.
Then, we use the following statement to connect to the database to see what happens.
Connet test/test111;
In this case, the database rejects the user from logging on to the database and prompts that the user has no permission to connect to the database. Unless we use the following command to authorize the user.
Grant connect to test;
After you authorize the user name, you can connect to the database. This difference is that many database administrators, especially those who have experience using SQL Server databases, are not easy to figure out when they first get in touch with the database. As a result, you may encounter some problems when learning or using it. I have participated in the training and examination of the SQL Server database administrator, so I was a bit confused when I used the Oracle database later. Now let's look back. Oracle is probably based on security considerations.

3. Object authorization
Assume that the following situations exist.
Now you have a user named test who has the permission to query the product table. Another user, test1, does not have any access permissions for this table. Now, test wants to grant the product Table query permission to test1 so that it can also query the product table in the database. Note that the user test here is only a common user, not a database administrator.
This requirement cannot be implemented in the SQL Server database because user test does not have the database management permission, but only has the Table query permission, it does not have the right to assign the query permission for this table to other users. However, in Oracle databases, this requirement can be achieved through "Object authorization.

Step 1: Use the system administrator account to grant "Object authorization" when assigning permissions.
For example, first log on to the system with the system administrator account, then query the table product right for the user test, and enable the object authorization mode. The command is as follows:
Grant select on product to test with grant option;
Grant select on product to test indicates that the user test has the permission to query the table product. The later with grant option indicates that the "Object authorization" mode is enabled for this user. Later, the test user can grant the query permission for the table product to other users.

Step 2: Use the test user to log on, and then grant the query permission to the table prodcut of the test1 user.
Grant select on product to test1;
This statement is the query right granted to table test1 product by user test. If the "Object authorization" mode is not enabled when we grant permissions to the test user, that is, the with grant option statement is not added to the end, when the common user test executes this statement, an error occurs, prompting that the user does not have this permission. However, if the "Object authorization" mode is enabled when the system administrator assigns permissions to the test user, the user test can grant the user the permission to query the test1 table. However, this is only for specific tables and specific operations. If the database administrator only uses the statement "Grant select on product to test with grant option" when assigning the test user permission, this means that the test user can only re-authorize the query permission for the table product. For example, if test1 wants to grant the product-bom query permission to test1, the database server will not be allowed because it does not have this right.

This is the object authorization function. Although it damages the uniformity of database permission management, it also improves the flexibility of database permission design. In the process of designing large databases, it is often used.
In addition to granting permissions to objects on tables, you can also grant permissions to processes, views, and other objects. Note that each step needs to be authorized during object authorization. For example, user test has the right to query, update, and delete table product records. However, only the Table query permission is "Object authorization ". In this case, the test user cannot grant the table's update and deletion rights to the user test1. Because account test re-authorizes the object, it only performs specific query operations.

In addition to object authorization, Oracle database also has a concept of "System Authorization. System Authorization is similar to object authorization, but it only applies to System Management permissions, such as account deletion or new operations. Note that system authorization still needs to be performed in different steps.

Http://news.newhua.com/news/2011/0214/115420.shtml