Users and Roles
For database administrators, security issues are critical and they have been trying to protect countless important business data, this prevents internal or external personnel who do not have the right but attempt to surpass the permission from looking at the leak. All relational database systems provide an internal security mechanism design to minimize these threats. These security mechanisms include simple password protection provided by Microsoft Access to complex user/role structures supported by advanced relational databases such as Oracle and Microsoft SQL Server. This article focuses on common security mechanisms for all databases that execute SQL languages. At the same time, we will also take a look at the mechanisms to strengthen data access control and ensure data security. Server-based databases support the user concept, similar to the user concept used in computer operating systems. If you are familiar with user/group hierarchies in Microsoft Windows NT and Windows 2000, you will find that the user/role groups supported by SQL Server and Oracle are very similar. We strongly recommend that you create a user account for each person accessing your database. Although it is technically feasible to share accounts between different users or create a user account for each type of users who only need to access the database, this approach is not appropriate for database security, there are two reasons: first, this approach will eliminate personal responsibilities-If a malicious user tampered with your database (for example, he paid 10 thousand yuan for his own bank account ), you cannot view the audit log to find the specific person who did the job. Second, if a specific user leaves your organization, if you want to delete the database access permission, you can only force the logon password used by all users in the group. Different operating platforms use different methods to create user accounts. You may need to check the instructions of your specific database management system to find the exact creation program. Users of Microsoft SQL Server should refer to the specific usage of the sp_adduser storage program. For Oracle Database administrators, the "create user" command may be useful. You should also learn about the optional logon authentication solution. For example, Microsoft SQL Server supports integrated Security Settings Using Windows NT. In this security solution, the Windows NT user account is used to identify the user connected to the database. Therefore, you do not need to enter the User Logon account and password when accessing the database. This mode is favored by database administrators, because it puts the burden of Account Management on network administrators and facilitates single-point logon for end users. If you only have a small number of users in the running environment, you may find that directly creating user accounts for them and assigning permissions can meet your needs. However, if you have a large number of users, you may be overwhelmed by the burden of maintaining accounts and assigning proper permissions to management. The role concept supported by relational databases is used to relieve this burden. The administrator can first set user accounts in the same group to a role unit, and then assign permissions to the role as a whole, instead of assigning permissions to each user. For example, you can create a database administrator role and add the user accounts of all our administrators to this role. After this step is completed, we only need to assign permissions to this role, which is equivalent to assigning specific permissions to all existing (and future) Administrators. Similarly, the procedures for creating roles vary depending on the operating platform. The Administrator of the ms SQL Server should refer to the sp_addrole storage program, and the database administrator of Oracle should use the CREATE ROLE command. Once you have users and roles in your database, it is time to assign permissions. The following describes how to assign permissions.
Permission settingsNow we have added all users to our database. It is time to set user permissions to enhance database security. The first step is to grant appropriate permissions to our database users. This task can be completed by executing the SQL GRANT statement. Syntax:
Grant select, INSERT, UPDATE
ON MERs
TO DataEntry |
GRANT CREATE TABLE
TO DBA
WITH GRANT OPTION |
REVOKE DELETE
ON MERs
FROM Linlin |
REVOKE [grant option for] <permissions>
ON <table>
FROM <user/role> |
You may have noticed that the syntax of this command is similar to the GRANT command. The only difference is that with grant option is the declaration made on the REVOKE command line, not at the end of the command. Let's explain through an example. Assume that we revoke the permission granted to Linlin to delete records from the Customers database. The command is as follows:
REVOKE DELETE
ON MERs
FROM Linlin |
It is worth mentioning that Microsoft SQL Server also supports a similar Revocation Mechanism-DENY (DENY) command. Real-time users now or in the future have certain permissions through the relationship set by the role. This command can also be used to explicitly deny the user to execute this permission. The command syntax is as follows:
Back to the example above, we assume that Linlin is also a member of the Managers role that has the same permission to access the MERs table. The preceding REVOKE command may not be enough to prevent him from accessing the data table. You can use the GRANT statement to revoke the permissions granted to a user account, but this does not affect the permissions granted to the account through the Administrator role group. If we use the DENY statement, we can block the permissions inherited from the role. The command is as follows: in fact, the DENY command creates a "negative permission" in the database access control ". If we subsequently decided to GRANT Linlin the permission to delete rows from MERs, we could not simply use the GRANT command. The existing DENY command has a higher priority than the GRANT command. Instead, we need to use the REVOKE command to REVOKE the entered negative permissions. The command is as follows: You probably have found that this command is the same as the command used to REVOKE the positive permission. Remember that the DENY and GRANT commands run in the same way. They all create permissions (whether positive or negative) in the database access control mechanism ). The REVOKE command can REVOKE all the positive and negative permissions of a specific user. If Linlin is a member of the role that has the permission to delete rows from a data table, he has the permission when the command is executed.
After reading all the above, I believe you have learned a lot about the access control mechanism using SQL. Even so, you should refer to your database management system manual to learn more about how to strengthen security protection measures. You will find that many databases support more advanced access control mechanisms, such as how to set permissions for specific columns.
DENY <permissions>
ON <table>
TO <user/role> |
DENY DELETE
ON MERs
TO Linlin |
REVOKE DELETE
ON MERs
FROM Linlin |
GRANT <permissions>
[ON <table>]
TO <user/role>
[With grant option] |
