Transcript Powerpoint - Chapter 11

Chapter Overview
•
Granting Database-Specific Permissions
•
Using Application Roles
• Designing an Access and Permissions Strategy
1
Inherited Permissions
•
•
Membership in the sysadmin server role
•
Membership in a user-defined database role with
assigned permissions
Membership in a fixed database role with predefined
permissions
• Public role
• Guest user account
• Object ownership
2
Permission Actions and
Conflicts
3
Statement Permissions
CREATE DATABASE BACKUP DATABASE BACKUP LOG
CREATE TABLE
CREATE VIEW
CREATE
PROCEDURE
CREATE DEFAULT
CREATE RULE
CREATE FUNCTION
CREATE TRIGGER
4
Creating and Owning
Objects
•
Objects created by sysadmin role members are
owned by dbo role.
•
Objects created by db_owner and db_ddladmin
members are owned by the creator or can be
designated as owned by the dbo role.
•
Objects created by any other user are owned by the
user.
•
When a user references an object without an owner
name, SQL Server looks for the object as owned by
the user and then by dbo.
5
Changing Object
Ownership
•
Use the sp_changeobjectowner system stored
procedure.
• Must be member of sysadmin, db_owner,
db_ddladmin, or db_securityadmin roles.
•
All existing permissions are removed when
ownership changes.
6
Granting and Viewing
Statement Permissions
7
Object Permissions
SELECT
INSERT
UPDATE
DELETE
EXECUTE
REFERENCES
8
Granting and Viewing
Object Permissions
9
Understanding Application
Roles
10
Creating Application Roles
•
Using SQL Server Enterprise Manager, create a
database role in a database and designate it as
an application role.
•
In Transact-SQL, use the sp_addapprole system
stored procedure.
•
Configure permissions for the role.
11
Activating and Using
Application Roles
•
Use the sp_setapprole system stored procedure.
•
Password can be transmitted in clear text and
encrypted using ODBC.
•
Access is limited to the database in which the
application role exists, plus any database
containing the Guest user account.
•
Application role is deactivated when connection
is closed.
12
Administrator Access
•
•
Add to local Administrators group.
•
Create a Windows group containing the
administrator, and make it a member of the local
Administrators group.
•
Create a Windows group containing the
administrator, create a login for that group, and add
the login to sysadmin group (or other server role).
Create a login using administrator’s Windows user
account, and add the logon to the sysadmin group
(or other server role).
13
SQL Server Access
•
Grant login access to Windows users individually
or as members of one or more groups.
• Use a Windows group for all users specifically
denied access.
•
Use SQL Server logins only if absolutely
necessary.
14
Database Access
•
If one individual will be responsible for a
database, make that user the owner of the
database.
•
Create separate Windows groups for users
requiring different access rights, and grant
database access to each group.
•
Grant database access to each SQL Server login
requiring database access.
15
Administrative Database
Access
•
Add Windows users or groups to fixed database
roles.
• Add SQL logins individually, or as members of a
user-defined database role, to fixed database
roles.
•
Consider adding users, groups, and roles to the
model database so that they are added
automatically to all new databases.
16
Data Access
•
Guest access—Use only for limited access rights.
•
Public role—Grant rights required by all users to
the public role.
•
Avoid granting similar rights to users on a loginby-login basis—Use Windows groups, userdefined database roles, and fixed database
roles.
17
Chapter Summary
•
Statement and object permissions can be inherited
through roles or granted directly.
•
•
Permissions can be granted, denied, or revoked.
•
Use application roles to limit access to certain
applications.
•
Use Windows groups and user-defined database
roles to apply permissions in as few places as
possible.
DENY statements take precedence; otherwise,
permissions are cumulative.
18