administration of users dr. gabriel. 2 documentation of user administration part of the...
TRANSCRIPT
2
Documentation of User AdministrationDocumentation 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 AdministrationDocumentation 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: Documentation of User Administration: Account Access ProceduresAccount Access Procedures
6
Operating System AuthenticationOperating 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
8
Creating UsersCreating Users
• Must be a standardized, well-documented, and securely managed process
9
Creating a SQL Server UserCreating 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 LoginsCreating 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 LoginsCreating SQL Server Logins
• Command line:– SP_ADDLOGIN system stored procedure
• Will be decommissioned• Use CREATE LOGIN
12
Creating SQL Server LoginsCreating 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 LoginsCreating 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 LoginsCreating 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 LoginsCreating SQL Server Logins
• Management Studio:– Security container
– Logins -> New Login
– SQL Server Authentication option
• System view– sys.syslogins
16
Creating SQL Server UsersCreating 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 UsersCreating 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 UsersCreating 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_statement revoke_statement | deny_statement }
19
Creating SQL Server UsersCreating 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 UsersCreating 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 UsersRemoving/Modifying Users
• Simple process• Make a backup first• Obtain a written request (for auditing purposes)
22
SQL Server: Removing Windows SQL Server: Removing Windows Integrated LoginsIntegrated Logins
• Command line: SP_DENYLOGIN system stored procedure– Will be decommissioned
– Use ALTER LOGIN instead• ALTER LOGIN loginname DISABLE
23
SQL Server: Modifying Windows SQL Server: Modifying Windows Integrated Login AttributesIntegrated 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 SQL Server: Removing/Modifying SQL Server LoginsServer 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 SQL Server: Removing/Modifying SQL Server LoginsServer 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 SQL Server: Removing/Modifying SQL Server LoginsServer Logins
• DROP LOGIN– DROP LOGIN loginname
• Management Studio:– Highlight the desired login
– Right click and Choose Delete
27
Modifying UsersModifying 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 LinksDatabase 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 ServersLinked 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 ServersLinked 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 ServersLinked Servers
• sp_addlinkedserver [ @server= ] 'server' [ ,
[ @srvproduct= ] 'product_name' ] [ ,
[ @provider= ] 'provider_name' ] [ ,
[ @datasrc= ] 'data_source' ] [ ,
[ @location= ] 'location' ] [ ,
[ @provstr= ] 'provider_string' ] [ ,
[ @catalog= ] 'catalog' ]
32
Linked ServersLinked 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 ServersLinked 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 ServersLinked Servers
• Examples:– Linking to Oracle
• EXEC sp_addlinkedserver
@server = 'LONDON Mktg',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyServer'
GO
35
Linked ServersLinked 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 ServersLinked 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 ServersLinked 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 ServersLinked 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 Practices for DB Administrators and ManagersManagers
• Manage:– Accounts
– Data files
– Memory
• Administrative tasks:– Backup
– Recovery
– Performance tuning
40
Best PracticesBest 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)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