// 1. DBMS 登录帐号管理
CREATE LOGIN SQLUSER WITH PASSWORD = 'pswdforUser_1'
CREATE LOGIN SQLUSER2 WITH PASSWORD = 'pswdforUser_123'
CREATE LOGIN USELESSUSER WITH PASSWORD = 'useless_123'
ALTER LOGIN SQLUSER WITH NAME = SQLUSER1
ALTER LOGIN SQLUSER2 WITH PASSWORD = 'pswdforUser_2'
DROP LOGIN USELESSUSER
// 2. 数据库用户管理
USE mobilephone
CREATE USER SQLUSER1 WITH DEFAULT_SCHEMA=dbo
CREATE USER SQLUSER2
DROP USER SQLUSER2
CREATE USER DB_USER FOR LOGIN SQLUSER2 WITH DEFAULT_SCHEMA=dbo
// 3. 对数据库用户进行权限/收回权限
USE MobilePhone
GRANT SELECT, UPDATE(CName)
ON Company
TO SQLUSER1
WITH GRANT OPTION
GRANT SELECT
ON Phone
TO PUBLIC
GRANT ALL PRIVILEGES
ON OperatingSystem
TO DB_USER
GRANT ALTER ON SCHEMA::DBO TO SQLUSER1
GRANT CREATE VIEW, CREATE PROCEDURE, BACKUP DATABASE
TO SQLUSER1, DB_USER
REVOKE INSERT, DELETE
ON OperatingSystem
FROM DB_USER
REVOKE CREATE TABLE
FROM SQLUSER1, DB_USER
DENY CREATE VIEW
TO DB_USER
// 4. 创建角色,为角色分配用户及权限
CREATE ROLE UPDATOR
AUTHORIZATION SQLUSER1
CREATE ROLE DELETER
AUTHORIZATION DB_USER
EXEC sp_addrolemember 'DELETER', 'SQLUSER1'
CREATE ROLE theOtherRole
AUTHORIZATION SQLUSER1
GRANT UPDATE ON Phone TO UPDATOR
GRANT DELETE ON Phone TO DELETER
EXEC sp_addrolemember 'UPDATOR', DB_USER
DROP ROLE theOtherRole
//1. 存储过程的创建、执行与管理
CREATE PROC phoneCount
@osName char(20),
@cnt int output
AS
SELECT @cnt = COUNT(PhoneName)
FROM Phone
WHERE OS = @osName
DECLARE @cnt int
EXEC phoneCount 'iOS', @cnt output
PRINT @cnt
ALTER PROCEDURE phoneCount
@introduced_year int,
@cnt int output
AS
SELECT @cnt = COUNT(PhoneName)
FROM Phone
WHERE introducedyear = @introduced_year
DROP PROCEDURE phoneCount
DECLARE @cnt int
EXEC phoneCount 2014, @cnt output
PRINT @cnt
DROP PROC phoneCount
//2. 触发器的创建与管理
CREATE TRIGGER tri_newPhone
ON Phone INSTEAD OF INSERT
AS
IF NOT EXISTS(
SELECT * FROM INSERTED
WHERE introducedyear > YEAR(GETDATE())
)
INSERT INTO Phone SELECT * FROM INSERTED
ELSE PRINT '不得插入今年未上市的手机'
DROP TRIGGER tri_newPhone
INSERT INTO Phone
VALUES('NewPhone', 'Apple', 'iOS', 'FDD-LTE', 6.3, 2015)
SELECT * FROM PHONE
CREATE TRIGGER tri_notphone
ON Phone AFTER INSERT
AS
IF EXISTS(
SELECT * FROM Phone
WHERE DisplaySize NOT BETWEEN 1.0 AND 6.2
)
ROLLBACK
DROP TRIGGER tri_notphone
//1. 对所创建的数据库进行完整、差异、日志备份
EXEC sp_addumpdevice 'disk', 'MobilePhoneBak', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MP.bak'
BACKUP DATABASE MobilePhone
TO MobilePhoneBak WITH init
BACKUP DATABASE MobilePhone
TO MobilePhoneBak WITH differential
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat'
USE master
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat' WITH NORECOVERY
//2. 数据库恢复
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog.dat' WITH NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=1
BACKUP LOG MobilePhone TO disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MPlog2.dat' WITH NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=1, NORECOVERY
RESTORE DATABASE MobilePhone
FROM MobilePhoneBak WITH FILE=2
数据库实验2
最新推荐文章于 2021-11-11 18:59:28 发布