创建用户
CREATE USER user_name IDENTIFIED BY password;
DROP USER user_name [cascade];
ALTER USER user_name ACCOUNT [UN]LOCK;
什么是权限
权限就是用来控制特定的用户发送特定的SQL语句是否允许正确的执行
在多用户环境,DBA需要维护数据库访问的安全
以确保特定用户拥有特定的操作权限
数据库的权限分为
系统权限
对象权限
系统权限
大部分是针对对象的创建,删除,修改,审计等等的操作
查看系统都具备哪些权限
select PRIVILEGE from dba_sys_privs;
查看当前会话都具备哪些权限
select * from session_privs;
对象权限
授予特定用户对特定对象的权限
特定对象:表、视图、序列、过程、函数、程序包上。
角色
系统中有166个权限 我们不便于管理
于是oracle对很多需求的权限定义成了角色的模式 来方便我们管理
角色就是一组权限的集合
我们也可以自己建立包含自己拟定权限的角色
查看oracle给你定制了哪些角色
select distinct GRANTEd_role from dba_role_privs ;
常见的角色中包含的权限
dba connect resource
查看角色中都包含了哪些权限
session A:> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='RESOURCE';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
RESOURCE CREATE TRIGGER
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TYPE
RESOURCE CREATE PROCEDURE
RESOURCE CREATE CLUSTER
RESOURCE CREATE OPERATOR
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE TABLE
8 rows selected.
session A:> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='CONNECT';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
CONNECT CREATE SESSION
session A:>
CREATE any table和create table他们是有区别的
可以在任意用户模式下建表
只可以在自己的模式下建表
用户被创建后,就需要授予他系统权限,刚创建的用户连登陆权限都没有(create session)
对于应用开发用户需要的基本权限:
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
授权通过grant
语法:
GRANT object_priv[(columns)]
[ON object]
TO {user|role|public}
[WITH GRANT OPTION]
回收通过revoke
语法:
REVOKE {privilege[,privilege...]|ALL}
[ON object]
FROM {user[,user...]|role|PUBLIC}
[CASCADE CONSTRAINTS]
SYS@ora10g> create user u1 identified by p1;
User created.
SYS@ora10g> grant connect,resource to u1;
Grant succeeded.
SYS@ora10g> desc dba_role_privs;
Name Null? Type
----------------------------------------------------------- -------- ----------------------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SYS@ora10g> select * from dba_role_privs where GRANTEE='U1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
U1 RESOURCE NO YES
U1 CONNECT NO YES
SYS@ora10g> revoke RESOURCE,CONNECT from u1;
Revoke succeeded.
SYS@ora10g> select * from dba_role_privs where GRANTEE='U1';
no rows selected
session A:> alter user u1 account lock;
User altered.
session A:> alter user u1 account unlock;
User altered.
session A:>
SYS@ora10g> drop user u1;
User dropped.
如果用户模式下有对象是不能删除用户的 可以加cascade连同对象一起删
session A:> create table u1.t as select * from scott.emp;
Table created.
session A:> drop user u1;
drop user u1
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'U1'
session A:> drop user u1 cascade;
User dropped.
session A:>
角色操作
session B:> drop role r1;
Role dropped.
session B:> create role r1;
Role created.
session B:> grant connect,create table to r1;
Grant succeeded.
session B:> create role r2;
Role created.
session B:> grant create view to r2;
Grant succeeded.
session B:> grant r2 to r1;
Grant succeeded.
session B:> grant r1 to scott;
Grant succeeded.
session B:>
授权的级联
grant选项
with admin option 权限回收无级联 适用系统权限和角色
with grant option 权限回收有级联 适用对象权限
session B:> create user u1 identified by u1;
User created.
session B:> create user u2 identified by u2;
User created.
session B:> grant connect to u1 with grant option;
grant connect to u1 with grant option
*
ERROR at line 1:
ORA-01939: only the ADMIN OPTION can be specified
session B:> grant connect to u1 with admin option;
Grant succeeded.
session B:> conn u1/u1
Connected.
session B:> grant connect to u2 ;
Grant succeeded.
session B:> conn / as sysdba
Connected.
session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
U2 CONNECT NO YES
U1 CONNECT YES YES
session B:> revoke connect from u1;
Revoke succeeded.
session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
U2 CONNECT NO YES
session B:>
恢复U1的connect权限 再测试对象权限
session B:> select * from dba_role_privs where GRANTEE in ('U1','U2');
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
U2 CONNECT NO YES
U1 CONNECT NO YES
session B:>
session B:> grant select on scott.emp to u1 with admin option;
grant select on scott.emp to u1 with admin option
*
ERROR at line 1:
ORA-00993: missing GRANT keyword
session B:> grant select on scott.emp to u1 with grant option;
Grant succeeded.
session B:> conn u1/u1
Connected.
session B:> grant select on scott.emp to u2;
Grant succeeded.
session B:> conn / as sysdba
Connected.
session B:> COL GRANTEE FOR A20
session B:> COL OWNER FOR A20
session B:> COL TABLE_NAME FOR A20
session B:> COL GRANTOR FOR A20
session B:> COL PRIVILEGE FOR A20
session B:>
session B:> select * from dba_TAB_privs where GRANTEE in ('U1','U2');
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ---------- ---------- ---------- -------------------- --- ---
U2 SCOTT EMP U1 SELECT NO NO
U1 SCOTT EMP SCOTT SELECT YES NO
session B:> revoke select on scott.emp from u1;
Revoke succeeded.
session B:> select * from dba_TAB_privs where GRANTEE in ('U1','U2');
no rows selected
session B:>
U2的也被回收了 回收级联
访问不同模式中的表
模式schema
模式是一个逻辑的概念 是一组对象的集合
一个用户创建了,那么该用户的schema名等于用户名,并作为该用户缺省schema。
这也就是我们看到schema名都为数据库用户名的原因
模式是可以切换的 这样可以实现以A用户的身份去访问B用户的模式
SQL> conn / as sysdba
SQL> drop user u2 cascade;
User dropped.
SQL> drop user u1 cascade;
User dropped.
SQL> create user u1 identified by u1;
User created.
SQL> grant connect,resource,select any table to u1;
Grant succeeded.
SQL> create user u2 identified by u2;
User created.
SQL> grant connect,resource,select any table to u2;
Grant succeeded.
SQL> create table u1.tt(ename varchar2(10));
Table created.
SQL> insert into u1.tt values('U1');
1 row created.
SQL> create table u2.tt(ename varchar2(10));
Table created.
SQL> insert into u2.tt values('U2');
1 row created.
SQL> conn u1/u1
Connected.
SQL> select * from tt;
ENAME
----------
U1
SQL> alter session set current_schema=u2;
Session altered.
SQL> select * from tt;
ENAME
----------
U2
SQL> show user
USER is "U1"
SQL>
了解这个概念即可 因为我们基本不会改用户的模式,而是通过在对象前使用schema前缀 例如scott.emp来实现模式转换的.