The security similarities and differences between SQL Server and Oracle database

Oracle database and SQL Server database are two commonly used databases, in the industry, Oracle database security is generally considered to be higher than SQL Server database, but the actual Oracle database and SQL The server database has how the difference, the following author to talk about the two kinds of database in the security design of the similarities and differences. Mastering these contents is of great importance to our design and management of database security.

One, the role to the user's authorization.

Now a lot of application software, including database system, all adopt the role to user's authorization system. That is, a role is authorized first, and then the user is added to the role, giving it permission to the role.

In the database design, we usually need at least two roles, one is the role of the system administrator, this role has the database system management, such as the establishment and deletion of the table, the user's establishment and modification and so on. Another is the role of ordinary users, which can insert, modify, delete records, and so on. Then, we set up two accounts, one is the database administrator account, let it belong to the Administrator role, and then set up a user account to belong to the user role. So, instead of setting special permissions on the user, they inherit the relevant permissions from their role. This is a role-based-user privilege Management system.

This authority management system, both kinds of databases are supported, but the specific name is different.

In the SQL Sever database, the Microsoft operating system is used as the name, the role of the group. In fact, when we define the user and permissions for the operating system, we also use this method, we first set up a group, then give the group a specific permission, and then create a user account to join the group. Although the names of the two databases are different, they are essentially the same.

The difference between the two is mainly reflected in the delegation of specific roles.

Second, the role of authorization.

Although two databases are identical in "role-authorization", there are still significant differences in the allocation of specific role rights. In a word, Oracle is more fine-grained than a SQL Server database for assigning permissions. Oracle can basically refine to each step in terms of permissions control.

In the case of user management, Oracle databases can give a group the right to create a user account, but the group does not have the right to delete the account. In Microsoft's database, it is generally the user management permissions, including user creation and deletion as a privilege assigned to a group, but can not separate them. In other words, if a group has user account management rights, it can not only create user accounts, but also can delete user accounts. That is to say, Microsoft's SQL Server database is not as fine-grained as Oracle in terms of privilege design. Because of this, Oracle databases are more flexible than SQL Server databases on rights management.

Not only in account management, the database many object permissions management, there are similar phenomena. For example, for the management of stored procedures, for table management, and so on. Oracle databases in Oracle are finer than other databases in terms of authority partitioning.