Authentication for SQL Server 2000

1
Authentication for SQL Server 2000
2
Executing System Proceduresp_addlogin,
sp_grantdbaccess

• EXEC procedure_name param ,
• e.g. EXEC sp_addlogin hellologin,
  worldpass, mydb

3
Permissions

• Permissions authorize database users to perform
  various operations such as CREATE DATABASE and
  SELECT.
• Permissions are divided into two categories
  statement and object.

4
Granting Statement Permissions

• Use the GRANT statement to grant permissions.
• GRANT statement permission1, 2 TO user1, 2
• Statement permissions include
• CREATE DATABASE
• CREATE DEFAULT
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE RULE
• CREATE TABLE
• CREATE VIEW
• BACKUP DATABASE
• BACKUP LOG

5
Granting Statement Permissions Contd.

• Grant all permissions to user1.
• e.g. GRANT ALL TO user1
• Grant two permissions to user1 and user2
• e.g. GRANT CREATE TABLE, CREATE VIEW TO
• user1, user2
• Grant permissions to a role.
• e.g. GRANT CREATE PROCEDURE, CREATE FUNCTION
  TO role_name_here

6
Granting Object Permissions Contd.

• Object permissions apply to a specific database
  object such as a table.
• Permissions include SELECT, UPDATE, INSERT,
  DELETE, and other object manipulations.

7
Granting Object Permissions

• General form of the GRANT statement
• GRANT ALL permission_list ON table/view
  column_list procedure TO account_list
• WITH GRANT OPTION AS group_name
  role_name
• The object is specified with the ON clause.
• The TO clause specifies who receives the
  permission.
• The WITH GRANT OPTION enables the grantee to
  grant the permission to others.

8
Granting Object Permissions Contd.

• Grant SELECT on a table.
• GRANT SELECT ON table_name TO user
• Grant SELECT and DELETE on a table.
• GRANT SELECT, DELETE ON table_name TO user
• Grant UPDATE on specific table columns.
• GRANT UPDATE ON table_name( att1, att2, )
• TO user
• Grant ALL permissions to everyone (PUBLIC).
• GRANT ALL ON table_name TO PUBLIC

9
Granting Object Permissions Enterprise Manager
10
Denying Permissions

• Denying permissions removes existing permissions
  from a database user account or role and prevents
  granting the permission to the database user
  account by accident through any other defined
  role.
• / Examples /
• DENY CREATE TABLE TO user
• DENY SELECT, UPDATE ON table_name
• TO user

11
Revoking Permissions

• You can revoke both statement and object
  permissions. Permissions that are revoked can
  later be allocated again.
• / Examples /
• REVOKE SELECT ON table_name FROM PUBLIC
• REVOKE UPDATE ON table_name FROM user
• You can also revoke GRANT options and cause
  system users who have been granted permissions by
  a specific database user to also lose their
  permissions through the CASCADE clause.
• REVOKE GRANT OPTION FOR DELETE ON
• table_name FROM user CASCADE

12
Fixed Database Roles

• db_owner has full access to the database.
• db_accessadmin can manage user IDs
• db_datareader can give the read (SELECT)
  permissions on any database object.
• db_datawriter can give the write (INSERT, UPDATE,
  and DELETE) permissions on any database object.
• db_ddladmin can issue ALL DDL (Database
  Definition Language) which is used to define and
  declare all database objects.
• db_securityadmin can manage all permissions,
  ownerships, and roles.
• db_backupoperator can check for database
  consistency and make backup by executing the
  DBCC, CHECKPOINT, and ACKUP statements.
• db_denydatareader can deny the read (SELECT)
  permissions on any database object. The opposite
  of db_datareader.
• db_denydatawriter. Can deny the write (INSERT,
  UPDATE, and DELETE) permissions on any database
  object.

13
SQL Server Roles

• Fixed Server roles have permissions on all
  databases in a server. All members of this role
  gain all permissions applied to it.
• Fixed database role only exists in the database
  level. Every member added to this role gains all
  permissions applied to it.
• User-defined database
• Public database

14
Fixed Server Roles

• sysadmin has full access to all database objects
  and is able to perform any task.
• serveradmin can shutdown server and set all
  server-related options.
• setupadmin can install replication and have full
  access to extended procedures.
• securityadmin has access to set login and CREATE
  DATABASE permissions.
• processadmin can manage all SQL Server-related
  processes
• dbcreator has authority to create and alter all
  the databases in the server.
• diskadmin has full access to all disk files.
• bulkadmin has full authority to perform bulk copy
  operations such as executing the BULK INSERT
  statement.

15
Allocating Fixed Server Roles Enterprise
Manager

• Expand the security folder and select the Server
  Roles option.
• Right-click the role as shown.

16
Allocating Fixed Server Roles Enterprise
Manager Contd.

• Click the Add button to display the Add Members
  window as shown and add the members desired and
  click OK.

17
Default Database User Accounts

• Users needing access to specific application
  databases must have a valid database user account
  for that database.
• Each database has two default accounts dbo and
  guest.
• The dbo account owns a database and the account
  cannot be dropped.
• The guest account is used by users who have no
  specific database user account.

18
Creating an SQL Server Database

• The CREATE DATABASE statement can be used with
  SQL Query Analyzer to create a database.
• Enterprise Manager is easier to use right-click
  the database node or use the Action menu - New
  Database option

19
Adding a new user
20
Adding a new userEXEC SP_ADDLOGIN
user_name, password, database ,
language
21
Adding a new userEXEC SP_ADDLOGIN demoUser,
pass
22
Adding a new userEXEC SP_ADDLOGIN demoUser,
pass, X
23
Adding a new user
24
Adding a new user
25
Granting permissions
Right-click on a user
26
Granting permissionsEXEC SP_GRANTDBACCESS
user_nameEXEC SP_GRANTDBACCESS DEMOUSER
27
Granting permissionsGRANT UPDATE, DELETE ON
DAKINE TO DEMOUSER
28
Creating an SQL Server Databaseby Query Analyzer
29
Creating an SQL Server Databaseby Enterprise
Manager
30
(No Transcript)
31
Creating an SQL Server Databasefull syntax

• SEE CREATE DATABASE