2. 支持DDL语句的审计,数据库表创建表、删除表、修改表结构(DDL)
1)创建表:
CREATE TABLE "PUBLIC"."student"(
"s_id" VARCHAR (100) NOT NULL ,
"s_name" VARCHAR (100) NOT NULL
)
2)增加表字段:
ALTER TABLE "student" ADD COLUMN s_age varchar(20) NULL;
3)字段改名:
ALTER TABLE "student" RENAME "s_age" TO "age";
4)删除字段:
alter table student drop column age;
5)删除表:
DROP TABLE IF EXISTS "student" CASCADE
6)字段注释:
COMMENT ON COLUMN student."s_name" IS '0全部发送(默认)1区别发送';
7)清空表:
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;
8)创建数据库:
create database zyh;
9)新建模式:
create schema test2;
10)在模式上创建表:
create table test2.table2 (id int, b text);
11)删除数据库:
drop database zyh;
12)删除模式
drop schema test2;
3. 支持DCL语句的审计,授予、解除用户访问权限、拒绝用户访问(DCL)
1)添加用户并设置密码:
CREATE USER sa CONNECTION LIMIT - 1 PASSWORD '123456'
2)拒绝test用户对Student表执行insert语句
deny insert on Student to test
3)查询用户:
select * from sys_user
4)授予权限:
ALTER USER sa LOGIN
5)修改用户密码:
alter USER sa with password 'Sqlserver123456'
6)收回权限:
待补充
7)删除用户:
DROP USER sa;
4. 支持DML语句的审计,检索、更新数据、添加行、删除行(DML)
CREATE TABLE "students"(
"s_id" VARCHAR (100) NOT NULL ,
"s_name" VARCHAR (100) NOT NULL
)
1)添加:
INSERT INTO students (s_id, s_name) VALUES ('01', '张三'),('02', '李四'),('03', '王五')
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 "PUBLIC".proctest(IN id varchar(100))
as
begin
update Student set s_name = '羊羊羊' where s_id = id;
end;
2)调用:
BEGIN
PUBLIC."proctest"('01');
END;
3)查看存储过程:
SELECT text FROM user_source WHERE NAME = 'proctest'
4)删除:
drop procedure proctest
6. 函数:
1)创建函数:
create or replace FUNCTION func_student1(v_id in varchar2)
RETURN varchar2
is
name varchar2(100);
BEGIN
SELECT s_name into name from student where s_id = v_id;
RETURN name;
END func_student1;
END;
2)调用:
select func_student1('01') from dual;
3)删除:
DROP FUNCTION func_student1;
7. 视图:
1)创建视图:
CREATE VIEW stutest
AS
SELECT * FROM student
2)调用视图:
SELECT * FROM stutest;
3)查看视图:
select * from user_views t
4)删除视图:
DROP VIEW stutest1
8. 索引:
1)创建索引:
CREATE UNIQUE INDEX UQ_ENAME_IDX ON Student(s_id);
2)查看索引:
select * from user_indexes where table_name=upper('student');
3)删除索引:
drop index UQ_ENAME_IDX;
9. 事务:
1)提交事务:
update Student set s_name = '杨过' where s_id = '02';
commit;
2)回滚事务:
update Student set s_name = '小龙女' where s_id = '02';
rollback;