1 / 42

Administration of Users

Administration of Users. Dr. Gabriel. Documentation of User Administration. Part of the administration process Reasons to document: Provide a paper trail Ensure administration consistency What to document: Administration policies, staff and management Security procedures

paytah
Download Presentation

Administration of Users

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. Administration of Users Dr. Gabriel

  2. Documentation of User Administration • Part of the administration process • Reasons to document: • Provide a paper trail • Ensure administration consistency • What to document: • Administration policies, staff and management • Security procedures • Procedure implementation scripts or programs • Predefined roles description • Administration staff and management

  3. Documentation of User Administration • Failure reasons: • Lack of time • Assumption that it’s already been done • Non-willingness to complicate the process

  4. Documentation of User Administration: Account Access Procedures

  5. Database Account Access Application Form Sample

  6. Operating System Authentication • Many databases (including Microsoft SQL Server 2005) depend on OS to authenticate users • Reasons: • Once an intruder is inside the OS, it is easier to access the database • Centralize administration of users • Users must be authenticated at each level

  7. Operating System Authentication (continued)

  8. Creating Users • Must be a standardized, well-documented, and securely managed process

  9. Creating a SQL Server User • Create a login ID first; controls access to SQL Server system • Associate login ID with a database user • Creator must be member of fixed server roles (SYSADMIN or SECURITYADMIN) • Two types of login IDs: • Windows Integrated (trusted) login • SQL Server login

  10. Creating Windows Integrated Logins • Command line: • SP_GRANTLOGIN system stored procedure • Will be decommissioned soon • Use CREATE LOGIN • CREATE LOGIN [domain\user] FROM WINDOWS (WITH <windows_options>) ; • Options: • DEFAULT_DATABASE =database (Default: master) • DEFAULT_LANGUAGE =language (server’s default language) • Can be associated local, domain, group usernames • Management Studio: • Use the Security container • Logins -> New Login

  11. Creating SQL Server Logins • Command line: • SP_ADDLOGIN system stored procedure • Will be decommissioned • Use CREATE LOGIN

  12. Creating SQL Server Logins • CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> } • <sources> ::= • CERTIFICATE certname • ASYMMETRIC KEY asym_key_name • <option_list1> ::= • PASSWORD ='password' [ HASHED ] [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ] • <option_list2> ::= • SID = sid • DEFAULT_DATABASE =database • DEFAULT_LANGUAGE =language • CHECK_EXPIRATION = { ON | OFF} • CHECK_POLICY = { ON | OFF} • CREDENTIAL =credential_name ]

  13. Creating SQL Server Logins • Credentials • Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. • Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup. • A credential can be mapped to several SQL Server logins at the same time. • A SQL Server login can only be mapped to one credential at a time. • Asymmetric key • An asymmetric key is a securable entity at the database level. • Certificate • A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields.

  14. Creating SQL Server Logins • Examples • CREATE LOGIN loginname WITH PASSWORD = ‘A725skjdm,kwjd)5' MUST_CHANGE, check_expiration=on; • CREATE LOGIN loginname WITH PASSWORD = ' A725skjdm,kwjd)5 ', CREDENTIAL = credname; • USE MASTER; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; go CREATE CERTIFICATE certname WITH SUBJECT = ‘certname in master db', EXPIRY_DATE = ‘12/31/2009'; GO CREATE LOGIN loginname FROM CERTIFICATE certname ; GO

  15. Creating SQL Server Logins • Management Studio: • Security container • Logins -> New Login • SQL Server Authentication option • System view • sys.syslogins

  16. Creating SQL Server Users • CREATE USER user_name [ { { FOR | FROM } { LOGIN login_name | CERTIFICATE cert_name | ASYMMETRIC KEY asym_key_name } | WITHOUT LOGIN ]      [ WITH DEFAULT_SCHEMA =schema_name ]

  17. Creating SQL Server Users • Schemas • A schema is a collection of database entities that form a single namespace. • A namespace is a set in which every element has a unique name. • For example, to avoid name collisions, no two tables in the same schema can have the same name. • Two tables can have the same name only if they are in separate schemas. • Default: dbo

  18. Creating SQL Server Users • CREATE SCHEMA schema_name_clause [ <schema_element> [ , ...n ] ] <schema_name_clause> ::= {         schema_name     | AUTHORIZATION owner_name     | schema_name AUTHORIZATION owner_name } <schema_element> ::= { table_definition | view_definition | grant_statementrevoke_statement | deny_statement }

  19. Creating SQL Server Users • Schema Example • CREATE SCHEMA Sprockets AUTHORIZATION Annik CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT TO Mandar DENY SELECT TO Prasanna; GO • System view • sys.schemas

  20. Creating SQL Server Users • Create USER Examples • CREATE USER username FOR LOGIN loginname; • CREATE USER username FOR LOGIN loginname WITH DEFAULT_SCHEMA = schemaname; • System view • sys.sysusers

  21. Removing/Modifying Users • Simple process • Make a backup first • Obtain a written request (for auditing purposes)

  22. SQL Server: Removing Windows Integrated Logins • Command line: SP_DENYLOGIN system stored procedure • Will be decommissioned • Use ALTER LOGIN instead • ALTER LOGIN loginname DISABLE

  23. SQL Server: Modifying Windows Integrated Login Attributes • Command line: • SP_DEFAULTDB system stored procedure • Will be decommissioned • Use ALTER LOGIN instead • SP_DEFAULTLANGUAGE stored procedure • Will be decommissioned • Use ALTER LOGIN instead • Management Studio: • Expand the security container • Select desired login • Properties (on the Action Menu)

  24. SQL Server: Removing/Modifying SQL Server Logins • ALTER LOGIN login_name { <status_option> | WITH <set_option> [ ,... ] }  <status_option> ::=ENABLE | DISABLE <set_option> ::=            PASSWORD ='password' [ OLD_PASSWORD ='oldpassword' | <secadmin_pwd_opt> [ <secadmin_pwd_opt> ] ] | DEFAULT_DATABASE =database | DEFAULT_LANGUAGE =language   | NAME =login_name | CHECK_POLICY = { ON | OFF } | CHECK_EXPIRATION = { ON | OFF } | CREDENTIAL =credential_name | NO CREDENTIAL    <secadmin_pwd_opt> ::= MUST_CHANGE | UNLOCK

  25. SQL Server: Removing/Modifying SQL Server Logins • Examples • Enabling a disabled login • ALTER LOGIN loginname ENABLE; • Changing the password of a login • ALTER LOGIN loginname WITH PASSWORD = '3948wJ698FFF7'; • Changing the name of a login • ALTER LOGIN loginname WITH NAME = newname;

  26. SQL Server: Removing/Modifying SQL Server Logins • DROP LOGIN • DROP LOGIN loginname • Management Studio: • Highlight the desired login • Right click and Choose Delete

  27. Modifying Users • ALTER USER • Renames a database user or changes its default schema • Examples: • ALTER USER username WITH NAME = newname • ALTER USER username WITH DEFAULT_SCHEMA = newschema

  28. Database Links • Connection from one database to another: allow DDL and SQL statements • Types: PUBLIC and PRIVATE • Authentication Methods: • CURRENT USER • FIXED USER • CONNECT USER

  29. Linked Servers • Linked servers are used to handle distributed queries • Allow you to connect to almost any: • Object Linking and Embedding Database (OLEDB) • Open Database Connectivity (ODBC)

  30. Linked Servers • You can use stored procedures and catalog views to manage linked server definitions: • Create a linked server definition by running sp_addlinkedserver. • View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views. • Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server. • You can also define linked servers by using SQL Server Management Studio. • In the Object Explorer, right-click Server Objects, select New, and select Linked Server. • You can delete a linked server definition by right-clicking the linked server name and selecting Delete.

  31. Linked Servers • sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]      [ , [ @provider= ] 'provider_name' ]      [ , [ @datasrc= ] 'data_source' ]      [ , [ @location= ] 'location' ]      [ , [ @provstr= ] 'provider_string' ]      [ , [ @catalog= ] 'catalog' ]

  32. Linked Servers • Examples: • Linking to other SQL servers • USE master; GO EXEC sp_addlinkedserver 'SEATTLESales', N'SQL Server' GO • EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct='', @provider='SQLNCLI', @datasrc='S1\instance1'

  33. Linked Servers • Examples: • Linking to Access DB • EXEC sp_addlinkedserver @server = 'SEATTLE Mktg', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'

  34. Linked Servers • Examples: • Linking to Oracle • EXEC sp_addlinkedserver @server = 'LONDON Mktg', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'MyServer' GO

  35. Linked Servers • SP_DROPSERVER • Removes a server from the list of known remote and linked servers on the local instance of SQL Server. • sp_dropserver [ @server = ] 'server'      [ , [ @droplogins = ] { 'droplogins' | NULL} ] • related remote and linked server logins for server must also be removed if droplogins is specified. • Example: • sp_dropserver 'ACCOUNTS', 'droplogins';

  36. Linked Servers • When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name • OPENQUERY function • Executes the specified pass-through query on the specified linked server • Ex: • SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT name, id FROM joe.titles')

  37. Linked Servers • SP_ADDLINKEDSRVLOGIN • Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server. • sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'      [ , [ @useself = ] 'useself' ]      [ , [ @locallogin = ] 'locallogin' ]      [ , [ @rmtuser = ] 'rmtuser' ]      [ , [ @rmtpassword = ] 'rmtpassword' ]

  38. Linked Servers • SP_ADDLINKEDSRVLOGIN • Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server. • sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'      [ , [ @useself = ] 'useself' ]      [ , [ @locallogin = ] 'locallogin' ]      [ , [ @rmtuser = ] 'rmtuser' ]      [ , [ @rmtpassword = ] 'rmtpassword' ] • Example: • EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

  39. Practices for DB Administrators and Managers • Manage: • Accounts • Data files • Memory • Administrative tasks: • Backup • Recovery • Performance tuning

  40. Best Practices • Follow company’s policies and procedures • Always document and create logs • Educate users • Keep abreast of database and security technology • Review and modify procedures

  41. Best Practices (continued) • For SQL server: • Create a local windows group called DBA • Grant login access for this group to SQL server instance • Make the trusted login a sys admin • Use local Windows or domain Windows accounts • What about DB logins for Internet applications? • Block direct access to database tables • Limit and restrict access to the server • Use strong passwords • Patches, patches, patches

  42. Questions ?

More Related