2. 支持DDL语句的审计,数据库表创建表、删除表、修改表结构(DDL)
1)创建数据库
create database zyh1029
2)使用数据库
use zyh1029
3)删除数据库(先删除在使用,不然)
drop database zyh1029
4)创建表:
create table Student(
Sno Char(7) primary key,--学号
Sname Char(10) not null,--学生姓名
Ssex Char(2) not null,--性别
Sage Smallint,--年龄
Clno Char(5) not null--学生所在班级号
);
2)查看表定义:
sp_columns Student
3)增加表字段:
alter table student add address varchar(20);
5)字段改名:
sp_rename 'Student.Clno','Class';
alter table Student alter column Class char(10);
6)删除字段:
alter table student drop column address;
4)删除表:
drop table Student;
5)修改表名:
exec sp_rename 'Student','Student1'
5)清空表:
create TABLE tb_AW(
name varchar(10),
sex varchar(30)
);
insert into tb_AW values('Joe','男');
insert into tb_AW values('周欣红','女');
select * from tb_AW;
truncate table tb_AW;
3. 支持DCL语句的审计,授予、解除用户访问权限、拒绝用户访问(DCL)
1)添加用户并设置密码:
create login test with password='test',default_database=zyh1028
create user test for login test with default_schema=dbo
exec sp_addrolemember 'db_owner', 'test'
2)拒绝test用户对Student表执行insert语句
deny insert on Student to test
3)查询用户:
Select name FROM Sysusers where status='0' and islogin='1'
4)授予权限:
GRANT SELECT to test
5)修改用户密码:
alter login test with password='Sqlserver123456'
6)收回权限:
revoke SELECT from test
7)删除用户:
DROP USER test;
4. 支持DML语句的审计,检索、更新数据、添加行、删除行(DML)
CREATE TABLE Students(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
s_age VARCHAR(10) NOT NULL DEFAULT '',
);
1)添加:
insert into Students values('01' , '赵雷' , '2021-1-1' , '男' , '22');
insert into Students values('02' , '钱电' , '2021-1-1' , '男' , '22');
insert into Students values('03' , '孙风' , '2021-1-1' , '男' , '22');
2)修改表数据:
update Students set s_name = '周生' where s_id = '01'
3)查找表数据:
SELECT * from Students
4)条件查询
select s_name as '姓名' from Students;
select * from Students where s_name = '周生';
select * from Students where s_name like '%生%';
select * from Students where s_id between 01 and 02
select * from Students where s_name in('周生','孙风');
select u.s_id,u.s_name from Students u order by u.s_id desc;
5)删除表数据:
delete from Students where s_name = '周生'
5. 支持数据库存储过程审计
1)新建存储过程:
create procedure proctest
@id INT=1
as
begin
update Students set s_name = '周生' where s_id = @id;
end;
2)调用:
exec proctest 01;
3)查看存储过程
SELECT TEXT FROM syscomments WHERE id=object_id('proctest');
SP_HELPTEXT 'proctest'
4)修改存储过程
alter procedure proctest
@id INT=1
as
begin
update Students set s_name = '赵雷' where s_id = @id;
end
5)删除:
drop procedure proctest
6. 函数:
1)创建函数:
Create FUNCTION getsum
(@num1 int,@num2 INT
)
returns INT
as
BEGIN
DECLARE @return INT
SET @return=@num1+@num2
RETURN @return
END;
2)调用:
SELECT dbo.getsum(1,3);
3)删除:
DROP FUNCTION getsum;
7. 视图:
1)创建视图:
create view testview as select * from Students;
2)调用视图:
SELECT * FROM testview;
3)查看视图:
select * from syscomments testview;
4)删除视图:
DROP VIEW testview
8. 索引:
1)创建索引:
create unique index Stusno on Student1(Sno);
2)查看索引:
SELECT * FROM sys.sysindexes WHERE id=object_id('Student1')
3)重命名索引:
exec sp_rename 'Student1.Stusno','Stuno';
4)删除索引:
drop index Student1.Stuno;
9. 事务:
1)提交事务:
begin transaction
begin
update Students set s_age=s_age-1000 where s_id=01
commit transaction
end
2)回滚事务:
begin transaction
begin
update Students set s_age=s_age-1000 where s_id=01
rollback transaction
end
3)使用savepoint部分回滚
savepoint-sql server.txt
10. 超长SQL语句审计:
超长sql语句-sql server.txt
11. 中文乱码审计:
create table student4
(
stuid varchar(11) not null,--学号:'S'+班号(7位数)+学生序号(3位数)(1)
stuname varchar(50) not null,--学生姓名
sex char(20) not null,--性别
age int not null,--年龄
classno varchar(20) not null,--班号:'C'+年级(4位数)+班级序号(2位数)
idnumber varchar(20) default '身份证未采集' not null--身份证
)
insert into student4 values('01' , '赵雷' , '男' , '22' , '11' , '41130319970513680X');
insert into student4 values('02' , '钱电' , '男' , '23' , '21' , '41130319980513680X');
12. 报表特权操作补充:
1)创建角色:
create role testRole;
2)授权角色:
grant select on student4 to testRole;
3)删除角色:
drop role testRole;
13. 超长执行时长语句的审计
SELECT GETDATE()
WAITFOR delay '00:05:00'
SELECT GETDATE();