Transcript Unix System Administration

2. SQL Security
• Objectives
– Learn SQL Server 2000 components
• Contents
–
–
–
–
–
–
Understanding the Authentication Process
Understanding the Authorization Process
Creating and Managing Logins
Users rights
Roles
SQL versus Windows users
• Practicals
– Adding/Manipulating and Deleting logins
• Summary
SQL
Understanding the Authentication Process
• Windows Authentication
• SQL Server Authentication
• Client Net-Libraries and Authentication
– You access the SQL Server Client Network Utility from the Microsoft
SQL Server program group on the Start menu.
Selecting a SQL Server 2000 Authentication Mode
• During installation, you selected an authentication mode
for your SQL Server 2000 instance.
• The default mode is Windows Authentication Mode
• You can also choose to operate a SQL Server 2000
instance in Mixed Mode.
• Switching Authentication Modes
– After installation, you must use SQL
Server Enterprise Manager to switch
authentication modes
Understanding the Authorization Process
• Server-Wide Permissions
– predefined server roles with associated administration permissions
– To grant a user these permissions, you add their login to the server role.
• Server Roles in SQL Server 2000
Sysadmin
Serveradmin
Securityadmin Processadmin
Diskadmin
bulkadmin
Setupadmin
Dbcreator
• A member of any server role can add other users to that
serverrole.
Database-Specific Permissions
• Permissions can be granted, denied, or revoked
• The right to create objects, administer the database,
• Execute certain Transact-SQL statements, insert data to a
table, or simply view data within a view.
• Database-Specific Permissions
Database owner
DBO role
User
Guest user
Public role
Fixed database roleUser-defined database role
Statement permissions
Object permissions
Application role
Fixed Database Roles
• Each database contains nine predefined database
• roles with associated database-wide permissions to perform
various tasks.
• To grant a user these permissions within a database, you
add the user's database user account to the database role.
• you can create user-defined database roles with custom
rights
• Database roles
db_owner
db_accessadmin
db_backupoperator
db_denydatareader
db_securityadmin db_ddladmin
db_datareader
db_datawriter
db_denydatawriter
Creating and Managing Logins
• Granting Access Using SQL Server Enterprise Manager
• Using the Create Login Wizard Granting Access
• Using Transact-SQL Statements
Sp_addlogin ’Joe’ , ’123456’ , ’Northwind’
– Server login for Joe
Sp_addlogin ’Joe’ , ’123456’ , ’Northwind’
– Would add Joe with password 123456
to database Northwind, with default rights
Sample Creating logins Login Wizard
• To create a login using the Create Login Wizard
1. Ensure that you are logged on as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise
Manager.
3. In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server
Group container, and then click the default instance.
4. On the Tools menu, click Wizards. The Select Wizard dialog box appears.
5. Expand Database and then double-click Create Login Wizard. The Welcome To The Create
Login Wizard page appears.
6. Click Next.
7. Click the SQL Server Login Information That Was Assigned To Me By The System
Administrator (SQL Server Authentication) option button and then click Next.
8. In the Login ID text box, type Joe.
9. In the Password and Confirm Password text boxes, type password and then click Next.
10. Click Next. Do not select any security roles.
11. Select the Northwind check box to permit access to this database only, and then click
Next.
12. Click the Finish button.
Creating a Login Directly
1. right-click Logins in the Security container for the SQL Server 2000
instance
2. click New Login
• You have two choices:
–
–
Using Windows login,
Click on dotted button next to User
Browse user from list
Using SQL login, C
Click on SQL Server Authentication
Enter name and Password for
SQL user
3. Select the default database, The default database will be the current
database when a user logs in.
Viewing/edit Access Information
• Using SQL Server Enterprise Manager
–
–
–
–
Security -> Logins
Security -> Server Roles (double click on role)
+<Database> -> Users (double click on a user)
+<Database> -> Roles (double click on role)
• Using Transact-SQL
– As usal, everything you can do with wizards and management consoles can
be done within SQL scripts, for example:
Su_helplogins ’gus’
– Would return information regarding all logins or a specified login gus
– Su_helplogins belong to the default System Stored Procedures