经典SQL学习笔记 (六)－用户管理、视图、序列、索引、权限

最新推荐文章于 2024-06-08 09:42:14 发布

陆讯

最新推荐文章于 2024-06-08 09:42:14 发布

阅读量1.5k

点赞数

分类专栏： SQL结构化查询文章标签：序列权限索引管理实例

本文链接：https://blog.csdn.net/wangzi11322/article/details/44222809

版权

SQL结构化查询专栏收录该内容

10 篇文章 9 订阅

订阅专栏

用户管理、视图、序列、索引、权限

用户管理

--连接数据库

CONN 用户名/密码@本地网络服务名；
CONN SYS/ITANY AS SYSDBA； --连接超级管理员数据库
CONN / AS SYSDBA；
SHOW USER；  --查看数据库
DISCONNECT  简写为DISC --断开连接
DISC --断开连接
EXIT --退出用户
EDIT   盘符：/文件名   简写为ED --编辑文件 
START  盘符：/文件名
SPOOL  盘符：/文件名    例：d:\dd.sql
SPOOL OFF      --保存信息

1）创建用户
语法：create user 用户名 identified by 密码;
create user wzl identified by itany123;

--授予对象权限
语法：grant 对象权限 on 表 to 用户   
grant 权限 to 用户 with grant option | with admin option

with grant option 系统权限
with admin option 对象权限

--权限的传递
sys->u1->u2
如果收回u1的权限，问u2的权限是否存在?
A:u2的系统权限存在，u2的对象权限不存在

--------角色：一组权限的组合，简化权限的管理
预定义角色：  connect 角色、resource 角色、dba 角色
自定义角色：  
语法：create role 角色名 identified by 密码 ；
grant 权限 to 角色名；
grant 角色名 to 用户；
revoke 权限 from 用户；
revoke 角色 from 用户；

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。 
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。 
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。 

grant dba to sx;

--锁定用户
alter user 用户名 account lock; --锁定用户
alter user sx account unlock;  --解锁用户

--修改密码
alter user 用户名 identified by 密码;
alter user 用户名 password expire;

conn sys as sysdba;


--删除用户
 drop user 用户名 cascade;

视图

视图 view

数据字典：数据库的系统信息。基表和视图
基表：普通的用户是不能访问基表的，只有超级管理员可以访问
视图:是一张特殊的表，是一个虚拟表，结构上与表相同，实际上视图中封装了一条查询语句
视图中的数据来自于查询语句所对应的表，在引用视图时会动态生成


user_xxx:当前用户所拥有的信息
all_xxx;当前用户所拥有的+可以访问别人的信息
dba_xxx：所有用户的所有信息（dba)

视图与表的区别：
1、表：占用空间，视图：不占用空间
2、视图提高数据的安全性
3、视图可以 简化复杂查询，
4、视图不可以创建索引，导致他的查询的速度变慢

创建视图
语法：create [or replace] view 视图名 as 子查询 with read only|with check option;
with read only:创建只读视图。
with check option:以后对该视图插入、修改、删除操作时,都会自动加上子查询中的条件

create view v1 as select empno,ename,hiredate from emp;
create or replace view v_emp20 as select * from emp where deptno=20 with read only;
删除视图：drop view 视图名

注：视图的创建需要具有相应的权限 grant create view to scott;

update v_emp20 set sal=4444 where ename='SCOTT';
对视图的插入、修改、删除，实际是删除原表数据，但视图一般主要用于数据的检索

如果不希望视图被更新，可以创建只读视图read only

序列

--序列（sequeence）：一组连续的而不重复的值
创建序列
语法：create sequence 序列名；
序列的属性


5、序列sequence：一组连续且不重复的值
自增 自动增长
Oracle中使用序列实现自增，序列是使用对象存在的
SQL Server：identity(1,4)  种子 增量
MySQL：auto_increment

创建序列：

create sequence 序列名
[increment by n]  ----- 每次加几个
[start with n]    ----- 从N开始计数
[maxvalue n|nomaxvalue] ----- 最大值|不设最大值
[minvalue n]      ----- 最小值
[cycle|nocycle]   ----- 到最大值后从头开始|一直累加，不循环
[cache n]         ----- 启用序列数字缓存机制，缓存中存放的序列值个数

创建一个名为myseq的序列:create sequence myseq;

调用序列：
currval 表示产生序列的当前值 select myseq.currval from dual;
nextval 表示生成序列的下一个值 select myseq.nextval from dual;
注：当地一次调用序列，必须先调用nextval，取值后才会产生当前的值

序列名.属性

索引权限

--索引
1、

create 
grant create session to sx;
grant create table to sx;


2)权限管理
分类：系统权限、对象权限
系统权限：允许用户对数据库执行的操作
（）
对象权限：允许用户对特定的对象执行的操作
（常用对象权限：select ，insert，update，delete）；
授予系统权限的语法：GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...][WITH ADMIN OPTION]; 
       PUBLIC ： 所有用户
       WITH ADMIN OPTION 使用户同样具有分配权限的权利，可将此权限授予别人
常用的系统权限：
       CREATE SESSION                     创建会话
       CREATE SEQUENCE                    创建序列
       CREATE SYNONYM                     创建同名对象
       CREATE TABLE                       在用户模式中创建表
       CREATE ANY TABLE                   在任何模式中创建表
       DROP TABLE                         在用户模式中删除表
       DROP ANY TABLE                     在任何模式中删除表
       CREATE PROCEDURE                   创建存储过程
       EXECUTE ANY PROCEDURE              执行任何模式的存储过程
       CREATE USER                        创建用户
       DROP USER                          删除用户
       CREATE VIEW                        创建视图


查看系统权限：
        dba_sys_privs  --针对所有用户被授予的系统权限
        user_sys_privs --针对当前登陆用户被授予的系统权限
回收系统权限语法：REVOKE {privilege | role} FROM {user_name | role_name | PUBLIC}
revoke create session from sx;


授予对象权限的语法：GRANT object_priv｜ALL [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];
       ALL：所有对象权限
       PUBLIC：授给所有的用户
       WITH GRANT OPTION：允许用户再次给其它用户授权


回收对象权限：REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];
查询权限分配情况：
    数据字典视图          描述
    ROLE_SYS_PRIVS            角色拥有的系统权限
    ROLE_TAB_PRIVS            角色拥有的对象权限
    USER_TAB_PRIVS_MADE      查询授出去的对象权限(通常是属主自己查）
    USER_TAB_PRIVS_RECD      用户拥有的对象权限
    USER_COL_PRIVS_MADE      用户分配出去的列的对象权限
    USER_COL_PRIVS_RECD      用户拥有的关于列的对象权限
    USER_SYS_PRIVS            用户拥有的系统权限
    USER_TAB_PRIVS            用户拥有的对象权限
    USER_ROLE_PRIVS           用户拥有的角色