Grant and Revoke in SQL

10 Key excerpts on "Grant and Revoke in SQL"

• 

  eBook - ePub

  Handbook of Data Management

  1999 Edition

  • Sanjiv Purba(Author)
  • 2019(Publication Date)
  • Auerbach Publications

    (Publisher)

  There is, however, a security-related problem inherent in the use of the GRANT statement. If a user is granted rights without the GRANT option, he or she should not be able to pass GRANT authority on to other users. However, the system can be subverted by a user by simply making a complete copy of the relation. Because the user creating copy is now the owner, he or she can provide GRANT authority to other users. As a result, unauthorized users are able to access the same information that had been contained in the original relation. Although this copy is not updated with the original relation, the user making the copy could continue making similar copies of the relation and providing the same data to other users.

  The REVOKE statement functions similarly to the GRANT statement, with the opposite result. One of the characteristics of the use of the REVOKE statement is that it has a cascading effect. When the rights previously granted to a user subsequently are revoked, all similar rights are revoked for all users who may have been provided access by the originator.

  Other Relational Security Mechanisms

  Although views and GRANT/REVOKE statements are the most frequently used security measures in traditional RDBMSs, they are not the only mechanisms included in most security systems using the relational model. Another security method used with traditional relational database managers, which is similar to GRANT/REVOKE statements, is the use of query modification.

  This method involves modifying a user’s query before the information is retrieved, based on the authorities granted to the user. Although query modification is not incorporated within SQL, the concept is supported by the Codd-Date relational database model.

  Most relational database management systems also rely on the security measures present in the operating system of the host computer. Traditional RDMBSs such as DB2 work closely with the operating system to ensure that the database security system is not circumvented by permitting access to data through the operating system. However, many operating systems provide insufficient security. In addition, because of the portability of many newer database packages, the security of the operating system should not be assumed to be adequate for the protection of the wealth of information in a database.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Securing SQL Server

  Protecting Your Database from Attackers

  • Denny Cherry(Author)
  • 2015(Publication Date)
  • Syngress

    (Publisher)

  The syntax is the same as with the GRANT and DENY statements simply replacing GRANT or DENY with REVOKE. Revoking rights does exactly what it sounds like, it removes the specified right from the user or role. The right which is being removed could be either a GRANT or a DENY. For example if a user had been granted the SELECT right on the table dbo.Orders and had been denied the DELETE right on the same table, both of these rights could be removed using the REVOKE statement shown in Example 15.11. Example 15.11 Revoking multiple rights from a user in a single statement. The reason that this can be done in a single statement without specifying that a grant and a deny are being revoked is because you cannot have a grant and a deny for the same object for the same user. If a user is granted the select right, then denied the select right if you were to query the system catalog views for the permissions the user would only have the deny right listed. Rights can be revoked within SQL Server Management Studio’s Object Explorer as well as via T-SQL. Navigate to the properties page shown in Figure 15.1 earlier in this chapter. By unchecking any unwanted permissions and clicking the OK button any un-needed permissions will then be revoked from the object. Table and view Permissions Both tables and views have 10 permissions that can be granted on them, less in older versions of Microsoft SQL Server. Most of the permissions shown in Table 15.1 are available in all versions of Microsoft SQL Server

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  A Guide to SQL

  • Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
  • 2020(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  The database administrator usu- ally assigns privileges. Normally, when the database administrator grants a privilege to a Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Database Administration 219 user, the user cannot pass that privilege along to other users. When the user needs to be able to pass the privilege to other users, the GRANT command must include the WITH GRANT OPTION clause. This clause grants the indicated privilege to the user and permits the user to grant the same privileges (or a subset of them) to other users. The database administrator uses the REVOKE command to revoke privileges from users. The format of the REVOKE command is essentially the same as that of the GRANT command, but with two differences: the word GRANT is replaced by the word REVOKE, and the word TO is replaced by the word FROM. In addition, the clause WITH GRANT OPTION obviously is not meaningful as part of a REVOKE command. Incidentally, the revoke cascades, so if Johnson is granted privileges WITH GRANT OPTION and then Johnson grants these same privileges to Smith, revoking the privileges from Johnson revokes Smith’s privileges at the same time. Example 14 illustrates the use of the REVOKE command. EXAMPLE 14: User Johnson is no longer allowed to retrieve data from the SALES_REP table. The following REVOKE command revokes the SELECT privilege for the SALES_REP table from the user named Johnson: REVOKE SELECT ON SALES_REP FROM JOHNSON; The database administrator can also apply the GRANT and REVOKE commands to views to restrict access to only certain rows within tables.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  If Jim does decide to revoke Sue’s privileges, he can simply issue the REVOKE again with the CASCADE option. The RESTRICT option is just a way to let a user know that the REVOKE will cause a cascade REVOKE. In addition, the SQL2 syntax now includes the ability to specify the privilege in the REVOKE GRANT state- ment instead of just revoking all GRANT privileges. Consider the following statement: REVOKE GRANT OPTION ON INSERT, DELETE ON tblPersonnel FROM usrSue CASCADE This statement revokes the ability to GRANT OPTION on these privileges but does not take away the priv- ileges themselves. 347 SQL Security Summary Database security is a subject that simply cannot be ignored. The SQL standard contains statements designed to enforce privileges to use objects in the database, primarily tables and views but also other objects. This chapter covered the following topics: ❑ SQL security centers around users (or user IDs) that can grant privileges on database objects such as tables and views. ❑ Data in specific tables and views, and even columns of specific tables and views, should be available to some users but not available to others. ❑ Views may be required to restrict access to specific columns in some DBMSs where an extension to the SELECT syntax is not available. ❑ The GRANT statement grants SELECT, UPDATE, INSERT, and DELETE privileges to specific users. ❑ The GRANT OPTION allows a user to extend the privileges she has been granted down the line to other users. ❑ The REVOKE statement is used to revoke privileges granted to a user as well as revoke the GRANT OPTION itself. In the next chapter, you learn about optimizing your database and how to write efficient SQL queries to speed up data retrieval. Exercises 1. Create DataEntry, Supervisor, and Management groups. 2. Create users John, Joe, Fred, Lynn, Amy, and Beth. 3. Add John, Joe, and Lynn to the DataEntry group, add Fred to the Supervisor group, and add Amy and Beth to the Management group.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Professional Microsoft SQL Server 2012 Administration

  • Adam Jorgensen, Steven Wort, Ross LoForte, Brian Knight(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  This is because the username and password are simply sent to the SQL Server instead of the Windows authentication process that passes in the Windows token. AUTHORIZING SECURABLES Proper object level security within the database is key to keeping data within the SQL Server instance safe from intruders. This object level security extends from instance level objects, such as availability groups, and the ability to view the instances’ server state objects to securing specific objects within the user databases. Rights can be granted at both the server level, the database level, or to specific objects. Permissions can also be chained together, which simplifies the permissions both within the database, using permissions chains, as well as across databases by using the cross databases chaining. Three statements are used when changing permissions in SQL Server. GRANT is used to assign rights. DENY is used to prevent access. REVOKE is used to remove either a GRANT or a DENY . When granting permissions in SQL Server, you need to remember that DENY always overwrites a GRANT . If a user is a member of three different roles, and two of the roles have been granted rights to query from a table and the third role has been denied rights to query the table, then the user cannot query from the table. ➤ ➤ ➤ Figuring out many of the object rights in this chapter can be hard to visualize when simply reading through descriptions of the rights. Microsoft has a visual diagram that can make this easier. You can download it from http://social .technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/5710.Permissions_5F00_ Poster_5F00_2008_5F00_R2_5F00_Wiki.pdf . The same applies if higher level sets of rights are granted. For example, if you have been granted rights to SELECT from a schema and denied the right to query a specific table, you cannot query from the table.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  5. Assuming the dept table defined earlier with schema dept(deptid, dname, mgrid), update the manager of the 'Research' department to the employee with id '123'. 6. For the EMPLOYEE TRAINING database, write a statement to remove all rows in the takes table. 6.3 Database User Privileges Use grant and revoke statements to manage privileges on a database table. The security component of a database system is responsible for enforcing the access rules based on the discretionary privileges on a database object. There are account-level privileges for creating, dropping, or altering database components, such as tables and views. These would be typically granted by a database administrator. This discussion covers the table privileges that are granted to or revoked from an authorized database user on a table. In this exposition, the table refers to either a stored table or a virtual table. The owner of the database table is the authorized database user who created the table. The table owner is automatically granted all table privileges on the created table. The owner of the table may then use the grant and revoke statements to manage the privileges on the database table. The table privileges include select, insert, update, delete and references. An authorized database user must have the select table privilege to access any column of a table. To insert data into a table requires the insert table privilege, which can be restricted to specify a list of named columns. Similarly, the update privilege allows a user to update data in a table, and this privilege can be restricted to specify the columns of the table that the authorized user is allowed to update. An authorized database user must have the delete table privilege to delete any tuples from a table. The references table privilege, which may be restricted to columns of a table, allows the referencing of the table columns in the specification of an integrity constraint.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn PostgreSQL

  Use, manage, and build secure and scalable databases with PostgreSQL 16

  • Luca Ferrari, Enrico Pirozzi(Authors)
  • 2023(Publication Date)
  • Packt Publishing

    (Publisher)

  f for routines and procedures. Other types are available. Please refer to the official documentation. It is now time to see how to manipulate ACLs and permissions in a practical way. In the next section, you will learn how to deal with permission management.

  Granting and revoking permissions

  As you saw in Chapter 3, Managing Users and Connections , a role is associated with a collection of permissions, which are provided by means of a GRANT statement and removed by means of a REVOKE statement. Permissions are stored internally as ACLs, as you saw in the previous section.

  This section revisits the GRANT and REVOKE statements to better help you understand how to use them, with respect to different database objects.

  The GRANT statement has the following synopsis:

  GRANT <permission, permission, ...> ON <database -object > TO <role >;

  Here, you list all the permissions you want to associate with the target role for the specified database object. It is also possible to extend the GRANT statement with the WITH GRANT OPTION clause, which will cause the target role to be able to grant the same permissions it has received to another role.

  The REVOKE statement has a similar synopsis:

  REVOKE <permission, permission, ..> ON <database -object > FROM <role >;

  There is a special role, named PUBLIC , that can be used when dealing with permission management. It is not a concrete role, but rather a marker to indicate “all available roles.” In other words, if you grant a permission to PUBLIC , you are implicitly granting this permission to all available roles.

  But what does “all available roles” mean? It means all existing and future roles. The PUBLIC

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Oracle SQL

  Jumpstart with Examples

  • Gavin JT Powell, Carol McCullough-Dieter(Authors)
  • 2004(Publication Date)
  • Digital Press

    (Publisher)

  This brings us to the REVOKE command. 23.2.2 Revoking Privileges You use the REVOKE command to remove both system privileges and object privileges. Like the GRANT command, the REVOKE command has two similar formats: one for revoking system privileges and one for revoking object privileges. Figure 23.9 shows the syntax for the REVOKE command. Revoke System Privileges . To revoke a system privilege, you must have been granted the same system privilege WITH ADMIN OPTION. The SYSTEM user has this privilege. Figure 23.9 Revoking Privileges. 23.2 Privileges 519 Chapter 23 Revoke Object Privileges . To revoke an object privilege, you must either have granted the privilege originally or you must have the GRANT ANY OBJECT PRIVILEGE system privilege. As with the GRANT command, let’s go through a sequence of steps demonstrating use of the REVOKE command. Let’s revoke privileges from the two users, PRINCE and ARIEL. First, connect as PRINCE. CONNECT PRINCE/CHARMING@OLTP; Now we can revoke an object privilege that was granted by PRINCE. Revoke the SELECT privilege on the MYHORSES table from ARIEL. ARIEL will no longer be able to read PRINCE’s MYHORSES table. REVOKE SELECT ON MYHORSES FROM ARIEL; Next we can connect to the SYSTEM user and revoke a system privilege granted earlier. CONNECT SYSTEM/password@OLTP; We have decided that PRINCE should not be allowed to create views. REVOKE CREATE VIEW FROM PRINCE; What happens to ARIEL’s ability to create views (granted by PRINCE) when PRINCE loses his privilege to create views? System privileges remain until specifically revoked from a user, even if the granting user loses the privilege. We can verify this fact by connecting to ARIEL. CONNECT ARIEL/MERMAID@OLTP; Now create a view on the MUSIC.ARTIST table by running the next command. ARIEL has the ability to SELECT from that table because the object privilege was granted to PUBLIC. This verifies that even though PRINCE has been denied the ability to create views, ARIEL has not.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Database Security

  Problems and Solutions

  • Christopher Diaz(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  To demonstrate the removal of privileges, let’s consider the principle of least privilege, which defines that a user should be given only the access or privileges needed to carry out their task or role, and nothing more. Suppose we determine that the previous decision to give roberts all privileges to BusinessTLS raises a security vulnerability and is insecure, and that read-only access is appropriate. We can lower that user’s privileges by first removing all of the privileges and then adding only the privilege to read or retrieve data. As shown in Figure 5.18, we first issue the REVOKE statement that takes away all of the privileges that roberts has, and then with GRANT add only the privilege to SELECT on the database. If we list the privileges of roberts now, we will see that only SELECT privilege remains. FIGURE 5.18 Reducing a user’s privileges. We can also revoke specific privileges rather than all privileges, and in certain situations we may be able to remove certain privileges without the need to add back others and achieve our security objective. For example, if a user currently has only SELECT and UPDATE privileges, and we determine that only SELECT is necessary, we only need to revoke the UPDATE privilege and not have to subsequently grant any privileges back. However, we must also be careful when removing privileges from a user that currently has all privileges assigned. Assigning privileges with the SQL keyword ALL can refer to a large set of privileges (and that set can vary among DBMSs). So unless we can be sure that we revoke a complete set of extra privileges, we risk leaving more privileges than is necessary and compromising the principle of least privilege. As an example of this situation, suppose we gave 'chu'@'localhost' all privileges, then decide that we only want that user to have SELECT privilege

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Access Control in Data Management Systems

  A Visual Querying Perspective

  • Elena Ferrari(Author)
  • 2022(Publication Date)
  • Springer

    (Publisher)

  Additionally, SQL adds to the System R access control model a new set of privileges and authorization objects, to reflect the extensions made to the relational data model since the design of System R. For instance, SQL supports the usage privilege, to authorize a user to make use of a user-defined data type in the definition of the database schema. Additional privileges provided by SQL are references, to allow a subject to use an attribute in the specification of a constraint/assertion; trigger, that allows one to specify triggers on a given relation; under, that allows one to create sub-types and sub-tables, and execute, to allow the execution of a procedure/function. There are some restrictions on the granting of these privileges. For instance, usage and execute privileges cannot be specified for relations, whereas trigger can only apply to base relations. We refer the reader to ISO [2003] for all the details. Other extensions related to the set of supported privileges refer to the select and insert privileges that, differently from the System R access control model, can also be given on selected attributes of a relation. In addition to the grant option, the SQL GRANT command may contain the hierarchy op- tion. This clause can be specified for the select privilege only, and it allows one to propagate authorizations from a table to all its sub-tables. Example 2.13 The following are examples of SQL GRANT commands: GRANT usage ON TYPE address TO Gena WITH GRANT OPTION; GRANT execute ON updateCustomers TO Helen; GRANT select(name, address), references(customer_id) ON Customers TO Marc; The first command authorizes Gena to use the type address to define other schema ob- jects. Moreover, since the privilege is granted with grant option, she can authorize other users to make use of the address type in the definition of schema objects.

  Sign up to read

  Learn more about book