1 / 33

Password Policies, Privileges, and Roles

Password Policies, Privileges, and Roles. Dr. Gabriel. Designing and Implementing Password Policies. Password is the key to open a user account; strong passwords are harder to break User authentication depends on passwords Hacker violations begin with breaking a password

nelle-bullock
Download Presentation

Password Policies, Privileges, and Roles

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Password Policies, Privileges, and Roles Dr. Gabriel

  2. Designing and Implementing Password Policies • Password is the key to open a user account; strong passwords are harder to break • User authentication depends on passwords • Hacker violations begin with breaking a password • Companies conduct users training/education

  3. What Is a Password Policy? • Set of guidelines: • Enhances the robustness of a password • Reduces the likelihood of password breaking • Deals with: • Complexity • Change frequency • Reuse

  4. Importance of Password Policies • First line of defense • Most companies invest considerable resources to strengthen authentication by adopting technological measures that protect their assets • Forces employees to abide by the guidelines set by the company and raises employee awareness of password protection • Helps ensure that a company does not fail audits

  5. Designing Password Policies • Complexity: set of guidelines for creating passwords • Aging: how long a password can be used • Usage: how many times a password can be used • Storage: storing a password in an encrypted manner

  6. Implementing Password Policies • Kerberos: • Requires a Key Distribution Center (KDC) • Generates a secret key for each session • Has a time-out value • A key known by client and server encrypts handshake data • Tickets • Time must be synchronized networkwide

  7. Implementing Password Policies (continued)

  8. Implementing Password Policies (continued)

  9. Granting and Revoking User Privileges • Permit or deny access to data or to perform database operations • In SQL server: • 4 levels of permissions • System/server • Database • Object (table) • Column • Not hierarchical

  10. Roles • Role: • Used to organize and administer privileges • It is like a user, except it cannot own object • Can be assigned privileges • Can be assigned to users

  11. Permissions: Fixed Server Roles • bulkadmin -can run the BULK INSERT statement • dbcreator -can create databases, and can alter and restore their own databases • diskadmin - is used for managing disk files. • Processadmin -can terminate processes that are running in an instance of SQL Server • securityadmin -manage logins and their properties. • can GRANT, DENY, and REVOKE server-level permissions. • can GRANT, DENY, and REVOKE database-level permissions. • can reset passwords for SQL Server logins.

  12. Permissions: Fixed Server Roles • serveradmin -can change server-wide configuration options and shut down the server • setupadmin - can add and remove linked servers, and also execute some system stored procedures. • sysadmin - can perform any activity in the server • By default, all members of the Windows BUILTIN\Administrators group are members of sysadmin

  13. Permissions: Fixed Database Roles • db_accessadmin - can add or remove access for Windows logins, Windows groups, and SQL Server logins. • db_backupoperator -can backup the database • db_datareader -can run a SELECT statement against any table or view in the database • db_datawriter -can add, delete, or change data in all user tables • db_ddladmin -can run any Data Definition Language (DDL) command in a database

  14. Permissions: Fixed Database Roles • db_denydatareader -cannot read any data in the user tables within a database • db_denydatawriter -cannot add, modify, or delete any data in the user tables within a database. • db_owner -can perform all configuration and maintenance activities on the database • db_securityadmin -can modify role membership and manage permissions • public Database Role • Every database user belongs to the public database role. • When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

  15. Database Privileges: Statement permissions • Grant permission using the GRANT statement • Revoke permission using the REVOKE statement • Deny permission using the DENY statement

  16. Granting and Revoking User Privileges:Object-level and Column-level • Table and database objects privileges: • GRANT, REVOKE, and DENY • EXECUTE permission • Column privileges: • GRANT, REVOKE, and DENY

  17. Creating, Assigning, and Revoking User Roles • CREATE ROLE • Creates a new database role in the current database • database-level securables !!! • CREATE ROLE role_name [ AUTHORIZATION owner_name ] • Examples • Creating a database role that is owned by a database user • CREATE ROLE HR_Users AUTHORIZATION msmith • Creating a database role called that is owned by a fixed database role • CREATE ROLE Claims_Processors AUTHORIZATION db_securityadmin

  18. Creating, Assigning, and Revoking User Roles • sp_addrolemember • Adds a database user, database role, Windows login, or Windows group to a database role in the current database. • sp_addrolemember [ @rolename = ] 'role',[ @membername = ] 'security_account' • Example • EXEC sp_addrolemember ‘HR’, ‘gbush’

  19. Creating, Assigning, and Revoking User Roles • sp_droprolemember • Removes a security account from a SQL Server role in the current database • sp_droprolemember [ @rolename = ] 'role' ,           [ @membername = ] 'security_account' • Example • EXEC sp_droprolemember ‘HR’, ‘gbush’

  20. Viewing Users/Roles Info • sp_helpuser • Reports information about database-level principals in the current database. • sp_helpuser [ [ @name_in_db = ] 'security_account' ] • Example • lists all users in the current database • sp_helpuser • lists information about a user • EXEC sp_helpuser ‘username’ • lists information about a fixed database role • EXEC sp_helpuser ‘db_datareader'

  21. Viewing Users/Roles Info • sys.server_principals • Contains a row for every server-level principal. • sys.database_principals • Returns a row for each principal in a database. • sys.server_role_members • Returns one row for each member of each fixed server role. • sys.database_role_members • Returns one row for each member of each database role.

  22. Assigning Users to Fixed Server Roles • sp_addsrvrolemember • Adds a login as a member of a fixed server role. • sp_addsrvrolemember [ @loginame= ] 'login'     , [ @rolename = ] 'role' • EXEC sp_addsrvrolemember ‘loginname', 'sysadmin'; • sp_dropsrvrolemember • Removes a SQL Server login or a Windows user or group from a fixed server role. • sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role' • EXEC sp_dropsrvrolemember ‘loginname', 'sysadmin';

  23. Application Roles • An application role is a database principal that enables an application to run with its own, user-like privileges. • It is used to allow access to specific data to only those users that connect through a particular application. • Application roles contain no members and are inactive by default. • Application roles are activated by using sp_setapprole, which requires a password.

  24. Application Roles • The following steps make up the process by which an application role switches security contexts: • A user executes a client application. • The client application connects to an instance of SQL Server as the user. • The application then executes the sp_setapprole stored procedure with a password known only to the application. • If the application role name and password are valid, the application role is activated. • At this point the connection loses the permissions of the user and assumes the permissions of the application role. • The permissions acquired through the application role remain in effect for the duration of the connection.

  25. Application Roles • CREATE APPLICATION ROLE • Adds an application role to the current database. • CREATE APPLICATION ROLE application_role_name WITH PASSWORD ='password' [ , DEFAULT_SCHEMA =schema_name ] • CREATE APPLICATION ROLE claimanalysis WITH PASSWORD = ‘B78_kil),mnu' , DEFAULT_SCHEMA = Claims; • DROP APPLICATION ROLE • Removes an application role from the current database. • DROP APPLICATION ROLE rolename • ALTER APPLICATION ROLE • Changes the name, password, or default schema of an application role.

  26. Application Roles • sp_setapprole • Activates the permissions associated with an application role in the current database. • sp_setapprole [ @rolename = ] 'role', [ @password = ] { encrypt N'password' } | 'password' [ , [ @encrypt = ] { 'none' | 'odbc' } ] [ , [ @fCreateCookie = ] true | false ] [ , [ @cookie = ] @cookie OUTPUT ] • EXEC sp_setapprole ‘claimanalysis’ , ‘B78_kil),mnu’;

  27. GRANT • GRANT • Grants permissions on a securable to a principal. • GRANT { PRIVILEGES } | permission [ (column [ ,...n ] ) ] [ ,...n ] [ ON [ class:: ] securable ] TO principal [ ,...n ] [ WITH GRANT OPTION ] [ AS principal ] • Examples • GRANT CREATE TABLE TO principal; • GRANT CREATE VIEW TO principal WITH GRANT OPTION; • GRANT SELECT, INSERT on objectname to principal • GRANT SELECT on objectname(column1,column2,…) to principal • GRANT EXECUTE on SPname to principal

  28. REVOKE • REVOKE • Removes a previously granted or denied permission. • REVOKE [ GRANT OPTION FOR ] { [ PRIVILEGES ] | permission [ (column [ ,...n ] ) ] [ ,...n ] } [ ON [ class:: ] securable ] { TO | FROM } principal [ ,...n ] [ CASCADE] [ AS principal ] • Examples • REVOKE EXECUTE on SPname from principal

  29. DENY • DENY • Denies a permission to a principal. • Prevents that principal from inheriting the permission through its group or role memberships. • DENY { ALL [ PRIVILEGES ] } | permission [ (column [ ,...n ] ) ] [ ,...n ] [ ON [ class:: ] securable ] TO principal [ ,...n ] [ CASCADE] [ AS principal ] • Examples • DENY EXECUTE on SPname to principal

  30. Best Practices • Develop a secure environment: • Never store passwords for an application in plaintext • Change passwords frequently • Use passwords at least eight characters long • Pick a password that you can remember • Use roles to control and administer privileges • Report compromise or loss of a password • Report any violation of company guidelines

  31. Best Practices (continued) • Develop a secure environment (continued): • Never give your password to anyone • Never share your password with anyone • Never give your password over the phone. • Never type your password in an e-mail • Make sure your password is complex enough • Use Windows integrated security mode • In Windows 2000/3 domain use domain users and take advantage of Kerberos

  32. Best Practices (continued) • When configuring policies: • Require complex passwords with special characters in the first seven bytes • Require a password length of at least eight • Set an account lockout threshold • Do not allow passwords to automatically reset • Expire end-user passwords • Do not expire application-user passwords • Enforce a password history

  33. Questions?

More Related