Access control for users and roles in SQL

Security is paramount to DBAs looking to protect their gigabytes of critical business data from the prying eyes of outsiders and insiders who try to overstep their authority. All relational database management systems provide some sort of internal security mechanisms designed to mitigate these threats. These range from the simple password protection provided by Microsoft Access to the complex user/role structure supported by advanced relational databases such as Oracle and Microsoft SQL Server. This article discusses the security mechanisms that apply to all databases that implement the Structured Query Language (or SQL). Together we go through the process to strengthen data access control and guarantee the security of your data.

Content
  1. users
  2. Roll
  3. Grant permissions
  4. Examples
  5. Remove permissions
  6. Examples

users

All server databases support a user concept similar to that of computer operating systems. If you are familiar with the user/group hierarchy in Microsoft Windows NT and Windows 2000, you will notice that the user/role groups supported by SQL Server and Oracle are very similar.

It is highly recommended that you create individual database user accounts for each person who has access to your database. It is technically possible to share accounts between users, or use just one account for each type of user that needs access to your database, but we strongly discourage this for two reasons. First, it removes individual liability – if a user makes changes to your database (for example, gifting yourself $5,000), you can’t trace them back to a specific person using audit logs. If a particular user leaves your organization and you want to remove his or her access to the database, you must also change the password that all users rely on.

The methods for creating user accounts vary from platform to platform and you should refer to the DBMS specific documentation for the exact procedure. Microsoft SQL Server users should investigate the use of the sp_adduser stored procedure. Oracle database administrators will find the CREATE USER command useful. You can also explore alternative authentication schemes. For example, Microsoft SQL Server supports using Windows NT’s built-in security. Under this scheme, users are identified in the database by their Windows NT account and they do not need to enter an additional user ID and password to access the database. This approach is extremely popular among DBAs because it shifts the burden of account management to network administration personnel and provides the convenience of single sign-on for end users.

Roll

If you are in an environment with a small number of users, you will probably find that creating user accounts and assigning permissions to them directly is enough for you. However, if you have a large number of users, you are likely to be overwhelmed with the burden of maintaining accounts and proper permissions. To alleviate this burden, relational databases support the concept of roles. Database roles work in the same way as Windows NT groups. User accounts are assigned to the role(s) and then permissions are assigned to the role as a whole rather than individual user accounts. For example, we can create a DBA role and then add our administrative staff user accounts to that role. Once that’s done, we can assign a specific permission to all current (and future) admins by simply assigning the permission to a role. Again, the roll creation procedures vary from platform to platform. MS SQL Server administrators should examine the sp_addrole stored procedure, while Oracle DBAs should use the CREATE ROLE syntax.

Grant permissions

Now that we’ve added users to our database, it’s time to strengthen security by adding permissions. Our first step is to grant our users appropriate access rights to the database. We do this with the SQL GRANT statement.

Here is the syntax of the statement:

 GRANT 
 [ON 
]

 TO 
 [С ПОДАРОЧНЫМ ВАРИАНТОМ] 

Now let’s look at this statement line by line. The first line, GRANT, allows us to specify specific permissions for the tables we grant. These can be table-level permissions (such as SELECT, INSERT, UPDATE, and DELETE) or database permissions (such as CREATE TABLE, ALTER DATABASE, and GRANT). More than one permission can be granted in a single GRANT statement, but table-level and database-level permissions cannot be combined in a single statement.

Second line, ON

, is used to specify the vulnerable table for table-level permissions. This line is omitted when we grant permissions at the database level. The third line specifies the user or role to which the permissions will be granted.

Finally, the fourth line, with GRANT OPTION, is optional. If this rule is included in the statement, the affected user may grant the same permissions to other users. Note that WITH GRANT OPTION cannot be specified when permissions are assigned to a role.

Examples

Let’s look at a few examples. In our first scenario, we recently hired a group of 42 data entry operators who will add and maintain customer records. They must have access to the information in the Customers table, who can modify information and add new records to the table. They may not be able to completely delete the record from the database. We need to create user accounts for each operator first and then add them all to the new DataEntry role. Then we need to use the following SQL statement to grant them the appropriate permissions:

 GRANT SELECT, INSERT, UPDATE 
 ПО клиентам 
 TO DataEntry 

And that’s all! Now let’s consider the case where we assign permissions at the database level. We want to allow members of the DBA role to add new tables to our database. In addition, we want them to be able to allow other users to do the same. Here is the SQL statement:

 GRANT CREATE TABLE 
 TO DBA 
 с опцией GRANT 

Note that we’ve included the WITH GRANT OPTION line so that our DBAs can assign this permission to other users.

Remove permissions

Once we have given consent, it is often necessary to withdraw it at a later date. Fortunately, SQL provides us with the REVOKE command to remove previously granted permissions. Here is the syntax:

 ОТМЕНИТЬ [GRANT OPTION FOR] 
 ON 
 ОТ 

You will notice that the syntax for this command is similar to that of the GRANT command. The only difference is that WITH GRANT OPTION is specified on the REVOKE command line, not at the end of the command. For example, let’s say we want to revoke Mary’s previously granted permission to delete records from the customer database. We would use the following command:

 ОТМЕНИТЬ УДАЛИТЬ 
 ПО клиентам 
 ОТ Мэри 

And that’s all! There is another mechanism supported by Microsoft SQL Server that is worth mentioning: the DENY command. This command can be used to explicitly deny a user access they would otherwise have in their current or future role membership. Here is the syntax:

 DENY 
 ON 
 TO 

Примеры

Возвращаясь к нашему предыдущему примеру, давайте представим, что Мэри также была членом роли Менеджеров, у которых также был доступ к таблице «Клиенты». Предыдущего оператора REVOKE было бы недостаточно, чтобы лишить ее доступа к таблице. Это приведет к удалению разрешения, предоставленного ей посредством заявления GRANT, предназначенного для ее учетной записи пользователя, но не повлияет на разрешения, полученные благодаря ее членству в роли Менеджеров. Однако, если мы используем оператор DENY, это заблокирует ее наследование разрешения. Вот команда:

 DENY DELETE 
 ПО клиентам 
 TO Mary 

The DENY command essentially creates a "negative permission" for database access controls. If we later decide to allow Mary to delete rows from the Customers table, we can't just use the GRANT command. This command is immediately overwritten by an existing DENY. Instead, we would first use the REVOKE command to remove the negative permission input like this:

 ОТМЕНИТЬ УДАЛИТЬ 
 ПО клиентам 
 ОТ Мэри 

You will notice that this command is exactly the same as the command used to remove the positive permission. Keep in mind that the DENY and GRANT commands work the same way: they both create permissions (positive or negative) in the database access control mechanism. The REVOKE command removes all positive and negative permissions for the specified user. Once this command is executed, Mary can delete rows from the table if she is a member of a role that has this permission. You can also issue a GRANT command to grant DELETE permission directly to her account.

In this article, you learned a lot about the access controls supported by the default query language. This introduction should serve as a good starting point, but I recommend checking the documentation for your DBMS to learn more about the strict security measures supported by your system. You will notice that many databases support more advanced access controls, such as granting permissions to specific columns.

Rate the article


solutics.ru

Add a comment cancel reply

















Leave a Reply

Your email address will not be published.