二、Oracle用户权限管理和Sql*plus 基本命令

最新推荐文章于 2024-09-05 10:00:22 发布

weixin_34351321

最新推荐文章于 2024-09-05 10:00:22 发布

阅读量146

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1757476/blog/488224

版权

2019独角兽企业重金招聘Python工程师标准>>>

Oracle和SQL Server的区别：

SQL Server用户sa登录后，可以看到很多数据库；而Oracle的启动其实是启动一个Oracle的实例，相当于一个数据库。那么Users只能对这个Oracle实例中的数据对象拥有权限。而每个用户拥有的数据对象可能不相同。
Oracle的用户管理
Oracle安装完成后，会默认生成三个用户，sys、system、scott，其中scott是普通用户；sys是超级管理员，权限最高，角色是dba，并且具有create database的权限；system是系统管理员，权限也很高，角色是dbaoper，没有create database权限。
Oracle权限介绍：

（1）权限：
查询oracle中所有的系统权限，一般是DBA去操作
select * from system_privilege_map order by name;
查询所有的对象权限： select distinct privilege from dba_tab_privs;
查询数据库的表空间： select tablespace_name from dba_tablespaces;
回收系统权限和对象权限的区别：系统权限不是级联回收，而对象权限会级联回收。比如：A 将系统权限授予B， B又将权限授予C，如果A收回B的权限，那么C的系统权限不会被回收。而对象权限则不是，如果A收回B的对象权限，那么C的对象权限也将被收回。
（2）角色
角色是为了简化权限的管理，是相关权限的命令集合。角色一般是由DBA来建立，如果别的用户建立，需要有create role的系统权限。那么如何创建角色？
a. 创建角色：
create role roleName not identified; create role roleName identified by password; 后者加口令验证，如果要修改角色则需要提供password。

b. 给角色授权：

（注意：系统权限的unlimited tablespace 和对象权限的 with grant option 选项不能授予角色）

grant create session to roleName [with admin option]; grant select on dog.emp to roleName;

前者授权系统权限，后者授权其他用户的对象权限。一般授权对象权限，最好是对应用户去授权。

c. 删除角色：drop role roleName; 需要注意的是：一旦role被删除，那么会影响到拥有该角色的用户。

d. 显示角色信息：

显示所有角色： select * from dba_roles;

显示角色具有的系统权限： select privilege, admin_option from role_sys_privs where role = 'roleName'; 显示角色具有的对象权限： role_tab_privs

（2）User

1. create user username identified by password
  创建一个新的用户，一般是具有DBA（数据库管理员）权限的user才能创建。
  在这里引出方案的概念。方案的定义是：数据库对象的集合，用来管理该用户的数据对象。在创建一个新的用户之后，oracle数据库会自动为该用户创建方案，并以用户名命名方案。
2. passw[ord] [userName] 或者 alter user username identified by newPassword
  该命令用于修改用户的密码。如果想要修改其他用户的密码，则使用后者，并且操作的用户需要具有sys/system等DBA的权限或alter user的系统权限。
3. drop user userName [cascade]
  删除用户，一般以DBA的身份去删除某个用户；如果其他用户去删除用户需要具有drop user的权限。而且当前用户自己不可以删除自己。如果用户已经创建了表等，删除时需要带cascade参数进行级联删除。
4. 显示用户具有的角色： select granted_role, default_role from dba_role_privs where grantee = 'userName';
5. 用户管理案例：创建的新用户是没有任何权限的，甚至连connnect数据库的权限都没有，需要为其指定相应的权限。赋权限 grant，收回权限 revoke。
  create user dog identified by dog;
  --connect 角色可使用户连接到数据库，resuorce可以使用户建表等
  grant connect, resource to userName;
  创建emp等表，同时再创建一个user muscleDog/muscleDog
6. ```
DROP TABLE DEPT;
CREATE TABLE DEPT(
		DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
		DNAME VARCHAR2(14) ,
		LOC VARCHAR2(13));
		
DROP TABLE EMP;
CREATE TABLE EMP(
        EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
		ENAME VARCHAR2(10),
		JOB VARCHAR2(9),
		MGR NUMBER(4),
		HIREDATE DATE,
		SAL NUMBER(7,2),
		COMM NUMBER(7,2),
		DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
		
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
```
  a. 【dog】赋查询...dog用户emp表的权限给muscleDog用户。
  grant select[, insert, update, delete/ all] on tableName to userName;
  conn muscleDog 后，select * from dog.emp;
  由此可见，Oracle允许两张同名的表，只要不在同一个表空间当中。Oracle数据库数据对象的组织方式是以用户为单位来组织的。
  b. 【dog】收回muscleDog的权限。注意：收回权限必须由相应的授权人。
  revoke select[, insert, update, delete/ all] on tableName from userName;
  c. (权限的维护) 希望在给muscleDog用户权限的时候，允许muscleDog用户可以将权限传递给其他的用户。
  --【dog】如果是对象权限，则在授权后面加上 with grant option
  grant select[, insert, update, delete/ all] on tableName to userName with grant option;
  --【system】创建thinDog用户
  -- 【muscleDog】将dog的emp表赋select...权限给thinDog
  grant select on dog.emp to thinDog;
  --【sys/system】如果是系统权限，则在授权后面加上 with admin option
  grant connect[, resource, dba] to userName with admin option;
  d. 收回muscleDog的对dog.emp表的select权限，那么thinDog的权限还存在吗？
  thinDog的select权限也被收回了。株连。。。
7. 使用profile管理用户口令
  profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项，那oracle就会将default分配给用户。
  a. 账户锁定：指定用户登录最多可以输入的密码次数，也可以指定用户锁定的时间（天），一般以dba的身份去操作。
  sql> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
  sql> alter user userName profile lock_account.
  b. 解锁账户
  sql> alter user userName account unlock;
  c. 终止口令：为了让用户定期修改密码，可以使用终止口令来完成，同样需要dba身份。
  给用户创建一个profile 文件，要求该用户每隔10天修改密码，宽限期为2天。
  sql> create profile myProfile limit password_life_time 10 password_grace_time 2;
  sql> alter user userName profile myProfile;
  d. 口令历史：如果希望用户在修改密码时，不能使用以前使用过的密码，可以使用口令历史，这样Oracle讲口令修改的信息存放在数据字典中，当用户修改密码时，Oracle会比较新旧密码。
  sql> create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;
  password_reuse_time //指定口令可以重用时间，即10天后就可以重用
  sql> alter user userName profile myProfile;
  e. 删除profile： sql> drop profile password_history [cascade]
Sql * Plus 常用命令：
1. 连接命令
  1. conn[ect]
    用法： conn userName/password@网络服务名(数据库名) [as sysdba/sysoper]
    当时用特权用户（比如 sys）身份连接时，必须带上 as sysdba 或 as sysoper，
    否则会报：ERROR ORA-28009：connection as SYS should be as SYSDBA or SYSOPER。
  2. disc[onnect]
    该命令用来断开与当前数据库的连接。
  3. show user 显示当前用户名
  4. exist 该命令会断开与数据库的连接，同时推出Sql * plus
2. 文件操作命令
  1. start 和 @ 运行sql脚本
    如： sql> @ D:\test.sql 或者 sql> start D:\test.sql
  2. edit 该命令可以编辑置顶的sql脚本
    如： sql> edit D:\test.sql
  3. spool 该命令可以讲sql * plus屏幕上的内容输入到指定文件中。
    如： sql> spool D:\bb.sql; --建立文件
    sql> select * from emp; --输入文件的内容
    sql> spool off; --操作结束
3. 显示和设置环境变量
  可以用来控制输出的格式，set show 如果希望永久的保存相关设置，可以去修改glogin.sql 脚本。
  1. linesize 设置显示行的宽度，默认是80个字符
  sql> show linesize;