Administration of Users

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 its 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
  ltwindows_optionsgt)
• Options
• DEFAULT_DATABASE database
• (Default master)
• DEFAULT_LANGUAGE language
• (servers default language)
• Can be associated local, domain, group usernames
• Management Studio
• Use the Security container
• Logins -gt 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 ltoption_list1gt
  FROM ltsourcesgt
• ltsourcesgt
• CERTIFICATE certname
• ASYMMETRIC KEY asym_key_name
• ltoption_list1gt
• PASSWORD 'password' HASHED MUST_CHANGE
  , ltoption_list2gt ,...
• ltoption_list2gt
• 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_expirationon
• CREATE LOGIN loginname WITH PASSWORD '
  A725skjdm,kwjd)5 ', CREDENTIAL credname
• USE MASTER
• CREATE MASTER KEY ENCRYPTION BY PASSWORD
  '23987hxJKL95234nl0zBe'
• 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 -gt 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
  ltschema_elementgt , ...n ltschema_name_clausegt
          schema_name     
• AUTHORIZATION owner_name      schema_name
  AUTHORIZATION owner_name
• ltschema_elementgt table_definition
  view_definition grant_statement
  revoke_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
• ltstatus_optiongt WITH ltset_optiongt ,...  
• ltstatus_optiongt ENABLE DISABLE
• ltset_optiongt           
• PASSWORD 'password' OLD_PASSWORD
  'oldpassword' ltsecadmin_pwd_optgt
  ltsecadmin_pwd_optgt
• DEFAULT_DATABASE database DEFAULT_LANGUAGE
  language  
• NAME  login_name
• CHECK_POLICY ON OFF
• CHECK_EXPIRATION ON OFF
• CREDENTIAL credential_name
• NO CREDENTIAL   
• ltsecadmin_pwd_optgt 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 _at_server 'server' ,
• _at_srvproduct 'product_name'       ,
• _at_provider 'provider_name'       ,
• _at_datasrc 'data_source'       ,
• _at_location 'location'       ,
• _at_provstr 'provider_string'       ,
• _at_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 _at_server'S1_instance1',
  _at_srvproduct'', _at_provider'SQLNCLI',
  _at_datasrc'S1\instance1'

33
Linked Servers

• Examples
• Linking to Access DB
• EXEC sp_addlinkedserver
• _at_server 'SEATTLE Mktg',
• _at_provider 'Microsoft.Jet.OLEDB.4.0',
  _at_srvproduct 'OLE DB Provider for Jet', _at_datasrc
  'C\MSOffice\Access\Samples\Northwind.mdb'

34
Linked Servers

• Examples
• Linking to Oracle
• EXEC sp_addlinkedserver
• _at_server 'LONDON Mktg',
• _at_srvproduct 'Oracle',
• _at_provider 'MSDAORA',
• _at_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 _at_server 'server'       ,
• _at_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
• _at_rmtsrvname 'rmtsrvname'       ,
• _at_useself 'useself'       ,
• _at_locallogin 'locallogin'       ,
• _at_rmtuser 'rmtuser'       ,
  _at_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
• _at_rmtsrvname 'rmtsrvname'       ,
• _at_useself 'useself'       ,
• _at_locallogin 'locallogin'       ,
• _at_rmtuser 'rmtuser'       ,
  _at_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 companys 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 ?