One place for hosting & domains

      User

      Security Controls for User Accounts


      Updated
      , by Linode

      Traducciones al Español

      Estamos traduciendo nuestros guías y tutoriales al Español. Es
      posible que usted esté viendo una traducción generada
      automáticamente. Estamos trabajando con traductores profesionales
      para verificar las traducciones de nuestro sitio web. Este proyecto
      es un trabajo en curso.

      Create a Linode account
      to try this guide with a $100 credit.

      This credit will be applied to any valid services used during your first 60 days.

      To protect your Linode user account against unauthorized access, there are several security controls you can implement. This guide covers several of these controls, including 2FA, security questions, and phone verification.

      2FA (Two-Factor Authentication)

      2FA (two-factor authentication) increases the security of your Linode account by requiring two forms of authentication: your password and an expiring token, also called an OTP (one-time passcode) or 2FA code. This follows the security principle of authenticating with something you know (a password) and something you have (the device used to generate the token). This additional layer of security reduces the risk that an unauthorized individual can gain access to your Linode account. Linode highly recommends enabling 2FA. See
      Managing Two-Factor Authentication (2FA) on a User Account
      to learn how to enable 2FA. To assist with account lockouts and recovery, you must first configure three
      security questions
      on your account before enabling 2FA.

      Note

      Managing 2FA through Linode is only available if Linode is selected as the Login Method. If you select a third-party authentication provider (such as Google or GitHub), 2FA is managed directly through that provider and not through Linode.

      Security Questions

      You can configure three security questions on your user account. Security questions provide our team with a secure method of verifying your identity as the owner of the user account. They can be used to help you regain access to your account in certain situations, such as when TFA is enabled and you no longer have access to the token or recovery codes. When configuring a security question, answers should not be easily guessed or discoverable through research.

      Configuring Security Questions.

      1. Log in to the
        Cloud Manager
        and navigate to the
        Login & Authentication
        page of your user profile. To do this, click on your username in the top right of the Cloud Manager and select Login & Authentication from the dropdown menu.

      2. Scroll down to Security Questions under the Security Settings section. Here, you can view the security questions available to you or, if you’ve already configured them, see the questions you have selected.

      3. To configure your security questions, click the drop-down field under Question 1 to select the question you wish to use. Then, type the answer in the corresponding box.

      4. Repeat this for Question 2 and Question 3. Once a particular question has been selected, you are not able to select the same one for any other question field.

      5. Once all fields have been configured, click Add Security Questions. You must fill out all 3 questions when adding security questions for the first time.

      6. After a security question has been configured, you can edit one or more questions by clicking the Edit button next to each question you’d like to change, updating the answer field with your new answer, and then clicking the Update Security Questions button.

      Phone Verification

      A verified phone number provides our team with a secure method of verifying access to your Linode user account. It is required for all new accounts created on or after June 27th, 2022, though any existing user can add a verified phone number to increase the security on their account.

      This phone number is only ever used to verify your identity when attempting to authenticate to a user account when contacting Linode Support. An SMS message with a verification code is sent to that phone number. Once received, you can provide that verification code to the Support representative you are in contact with. If you receive a verification SMS without contacting us, do not pass along the verification code to anyone.

      Note

      Standard carrier messaging fees apply for each SMS message

      Adding a Verified Phone Number

      1. Log in to the
        Cloud Manager
        and navigate to the
        Login & Authentication
        page of your user profile. To do this, click on your username in the top right of the Cloud Manager and select Login & Authentication from the dropdown menu.

      2. Scroll down to Phone Verification under the Security Settings section. Here, you can view, add, and remove your verified phone number.

      3. To add a phone number, select your country from the dropdown list. This populates the country code portion of the phone number. Then enter the remainder of your phone number.

      4. Click Verify Phone Number to send an SMS verification code.

      5. Once you receive the verification code on your phone, enter it within the Verification Code field. If you do not receive the code within a few minutes, you can click the Resend verification code button.

      6. After successfully entering the verification code, your phone number is verified and has been saved to your user account.

      Multiple User Accounts

      Organizations that require multiple individuals to access the same customer account should create separate user accounts for each individual. Once you’ve created the accounts, you can assign permissions to restrict access to certain Linode services and areas of the Cloud Manager. This is useful for providing all team members access to a single Linode account, allowing a billing department to view invoices and billing details, or granting access to outside developers. For more information, see our guide on
      Accounts and Passwords
      .

      This page was originally published on



      Join the conversation.
      Read other comments or post your own below. Comments must be respectful,
      constructive, and relevant to the topic of the guide. Do not post external
      links or advertisements. Before posting, consider if your comment would be
      better addressed by contacting our
      Support team or asking on
      our
      Community Site.



      Source link

      SQL Security and User Management


      User management and permissions are essential to SQL database security. Typically, SQL database security schemes consist of one or more users, their authentication, and permissions. The database engine validates a user’s permissions when they attempt to perform an operation against a SQL object —for example, a table, an index, a stored procedure, etc. The basic premise behind the assignment of SQL roles and permissions is to provide users of the database access to only what is necessary to perform their job. In this guide, you learn how to create and assign roles and permissions to users of relational database systems.

      Users and Groups

      In order to grant access rights and permissions, a relational database management system requires user identities.
      These rights and permissions can be assigned to either an individual user, or a group of users. If you have more than one user with similar access requirements and restrictions, you can define a group. Then, you add the collective set of users as members of the appropriate group. In this way, the authentication and validation process for a given SQL object is applied against the group instead of the user. This assumes that no restrictions have been established for individual users. In the case where a user and the user’s group both have access restrictions on a given SQL object, the database applies the most restrictive access rights of either the user or the user’s group.

      Roles

      Users of relational database systems are typically assigned roles. Different users might need to perform different tasks on the same database. For example, one user might be in charge of data entry, another user might be the database administrator, and an end-user may only need to retrieve data from the database. Typically, users that have the same type of role in an organization require the same type of database access. Each database role can have its own data access permission levels. Once the role is created and the appropriate permissions are applied, you can add individual users to that role. All users assigned to a particular role inherit its permissions.

      Permissions

      There are two different types of permissions that can be assigned to roles, users, and groups: statement permissions and object permissions. Statement permissions grant access to execute specific statements against a database. For example, a user could be granted access to create a stored procedure, but not be granted the right to create tables. Object permissions, on the other hand, grant the user the right to access a database object such as a table, a view, or to execute a stored procedure.

      Implementation of Users, Groups, Roles, and Permissions

      When it comes to the management of users, groups, roles, and permissions, the concepts stated in the previous sections are quite uniform across SQL-based database management systems. What may differ are the names of commands and the syntax used by different SQL database implementations.

      Note

      The examples below use Microsoft SQL Server syntax. All commands should be executed from the command line. The examples also assume that all server security hardening has already been implemented.

      To demonstrate SQL security principles, this guide uses an example database that is used by a school. The school’s database has tables for students and courses taken by each student. The definition of the Student table contains columns for the student’s SSNumber, Firstname, and Lastname, and the definition of the CourseTaken table contains columns for SSNumber, CourseId, NumericGrade, and YearTaken.

      The example further assumes that four employees in the school administer the school database. Their respective roles are defined as follows:

      Name Database Role
      Tom Database Administrator
      John Database Data Entry
      Mary Database Query and Reports
      Joan Database Query and Reports

      In the example below, assume that Tom, the database administrator (DBA), has created the school database via the CREATE DATABASE command:

      CREATE DATABASE School;
      

      Next, Tom creates database user login definitions for all four employees (including themselves) via the CREATE USER command:

      Use School;
      CREATE USER Tom WITH PASSWORD = 'Tompassword';
      CREATE USER John WITH PASSWORD = 'Johnpassword';
      CREATE USER Mary WITH PASSWORD = 'Marypassword';
      CREATE USER Joan WITH PASSWORD = 'Joanpassword';
      
      CREATE USER Tom IDENTIFIED BY 'TomPassword';
      

      After creating user login definitions, Tom creates generic roles that will later be assigned to each employee, by using the CREATE ROLE command:

      USE School;
      CREATE ROLE DBAdmin;
      CREATE ROLE DataEntry;
      CREATE ROLE QueryReports;
      

      Now that the roles exist, Tom assigns the roles to the appropriate users with the ALTER ROLE command as follows:

      USE School
      ALTER ROLE DBAdmin ADD MEMBER Tom;
      ALTER ROLE DataEntry ADD MEMBER John;
      ALTER ROLE QueryReports ADD MEMBER Mary;
      ALTER ROLE QueryReports ADD MEMBER Joan;
      

      The workflow demonstrated in this section reflects the user management steps a DBA might need to take when configuring a newly created database.

      Granting Permissions

      The GRANT statement is used to assign permissions to a user or to a role. You can also use the GRANT statement to assign specific statement permissions to a user or to a role. Some of the statement permissions that can be granted are: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.

      For example, to grant the CREATE PROCEDURE statement permission to a user or a role, use the following command:

      GRANT CREATE PROCEDURE TO <User or Role>;
      

      Continuing along with this guide’s school database example, you can grant various permissions to the database roles you created in the previous section. Tom first grants required privileges to the DBAdmin Role (Tom’s role), via the GRANT command, as follows:

      USE School;
      GRANT CREATE DATABASE TO DBAdmin;
      GRANT CREATE RULE TO DBAdmin;
      GRANT CREATE TABLE TO DBAdmin;
      GRANT CREATE VIEW TO DBAdmin;
      GRANT DUMP DATABASE TO DBAdmin;
      GRANT DUMP TRANSACTION TO DBAdmin;
      

      Now, Tom can create the two tables in the school’s database as follows:

      USE School;
      CREATE TABLE Student (
        SSNumber CHAR(9) NOT NULL,
        LastName VARCHAR(30) NOT NULL,
        FirstName VARCHAR(20) NOT NULL
      );
      
      CREATE TABLE CourseTaken (
        SSNumber CHAR(9) NOT NULL,
        CourseId CHAR(6) NOT NULL,
        NumericGrade TINYINT NOT NULL,
        YearTaken SMALLINT NOT NULL
      );
      

      Tom grants necessary database entry permissions (INSERT, UPDATE, DELETE) on both database tables, to employee John (DBEntry role), as follows:

      USE School;
      GRANT INSERT, UPDATE, DELETE ON Student TO DBEntry;
      GRANT INSERT, UPDATE, DELETE ON CourseTaken TO DBEntry;
      

      Note

      After executing the above GRANT commands, John is permitted to INSERT, UPDATE, and DELETE data in the two database tables, but is not permitted to read (SELECT) from it.

      Tom grants necessary database read permission (SELECT) on both database tables, to employees Mary and Joan, via the QueryReports role, as follows:

      USE School;
      GRANT SELECT ON Student TO QueryReports;
      GRANT SELECT ON CourseTaken TO QueryReports;
      

      Note

      After executing the above GRANT commands, Mary and Joan can only read the database tables (via the SELECT statement), but cannot manipulate the data (via the INSERT, UPDATE, or DELETE statements).

      Revoking Permissions

      Revoking permissions is the converse of granting permissions on database objects. You can revoke permissions from a table, view, table-valued function, stored procedure, and many other types of database objects.

      Continuing with the school database example, assume that John switches his role at the school from performing data entry to querying reports. Due to this change, John should no longer have the ability to manipulate data (INSERT, UPDATE, DELETE) in the school tables. John should also be granted the ability to read data from the table (via SELECT). Tom, the database administrator, needs to execute the following commands to revoke and grant the appropriate permissions to John:

      USE School;
      REVOKE INSERT, UPDATE, DELETE ON Students FROM John;
      REVOKE INSERT, UPDATE, DELETE ON CourseTaken FROM John;
      GRANT SELECT ON Student TO John;
      GRANT SELECT ON CourseTaken TO John;
      

      Alternatively, a simpler approach is to remove John from the DBEntry role and add him to the QueryReports role:

      USE School;
      ALTER ROLE DBEntry DROP MEMBER John;
      ALTER ROLE QueryReports ADD MEMBER John;
      

      Conclusion

      User management, permissions, and roles are essential to SQL database security. Create a new group and add users to that group if they require the same database access and permissions. To control access by the tasks users should be allowed to perform against a database, use database roles.

      In SQL databases, every action must pass through a validity check that determines if the database action can be completed by a particular user. The appropriate permissions are required to access SQL database objects and execute statements. The integrity of a SQL database relies on secure and well-designed user management.

      Now that you are familiar with SQL user management, you can learn about some different aspects of the SQL language, like
      joins
      ,
      data types
      , and
      grouping and totaling
      .



      Source link