一：创建用户语法
目的
使用
CREATE
USER
语句来创建和配置数据库。
用户，它是一个帐户，您可以通过该帐户登录数据库，并建立Oracle数据库允许用户访问的方法。
可以在Oracle自动存储管理(Oracle ASM)群集中发出此语句，以便将用户和密码组合添加到当前节点的Oracle ASM实例的本地密码文件中。每个节点的Oracle ASM实例都可以使用此语句更新自己的密码文件。密码文件本身必须是由
ORAPWD
效用。您可以使用户通过代理应用程序或应用程序服务器连接到数据库。
先决条件
你一定有
CREATE
USER
系统特权创建一个具有
CREATE
USER
语句时，用户的权限域为空。要登录到Oracle数据库，用户必须具有
CREATE
SESSION
系统特权因此，在创建用户之后，您应该至少将
CREATE
SESSION
系统特权参考
格兰特
想了解更多信息。只有经过身份验证的用户
AS
SYSASM
可以发出此命令修改Oracle ASM实例密码文件。句法
CREATE USER user IDENTIFIED { BY password | EXTERNALLY [ AS 'certificate_DN' | AS 'kerberos_principal_name' ] | GLOBALLY [ AS '[ directory_DN ]' ] } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace }... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE { tablespace | tablespace_group_name } | { QUOTA { size_clause | UNLIMITED } ON tablespace }... | PROFILE profile | PASSWORD EXPIRE | ACCOUNT { LOCK | UNLOCK } | ENABLE EDITIONS ]... ] ;
IDENTIFIED：
子句允许您指示Oracle数据库如何对用户进行身份验证。
BY
password：
子句允许您创建
本地用户并指示用户必须指定
password
登录到数据库。密码区分大小写。任何后续
CONNECT
中使用的相同情况(上、下或混合)，用于将此用户连接到数据库的字符串必须指定密码。
CREATE
USER
声明或后续
ALTER
USER
声明。密码可以包含数据库字符集中的任何单字节、多字节或特殊字符，或这些字符的任何组合。
EXTERNALLY：
若要创建一个
外部用户。这样的用户必须通过外部服务(如操作系统或第三方服务)进行身份验证。在这种情况下，Oracle数据库依赖于操作系统或第三方服务的身份验证，以确保特定的外部用户能够访问特定的数据库用户。
certificate_DN：
此子句仅适用于通过SSL身份验证的外部用户。大
证书DN
用户钱包中的PKI证书中的可分辨名称。最大长度
证书DN
是1024个字符
Kerberos_main_name ：
此子句仅用于Kerberos认证的外部用户。最大长度
Kerberos_main_name
是1024个字符。
警告：
Oracle强烈建议您不要使用
IDENTIFIED
EXTERNALLY
具有固有的弱登录安全性的操作系统。
对创建外部用户的限制
以下限制适用于创建外部用户：用户
SYS
不能是外部用户。Oracle ASM不支持创建外部用户。
GLOBALLY
：
子句允许您创建
全局用户
。这样的用户必须得到企业目录服务(Oracle Internet Directory)的授权。
directory_DN
字符串可以采取两种形式之一：
标识此用户的企业目录服务中的X.509名称。它应该是形式的
CN=
username,other_attributes
，在哪里
other_attributes
是目录中用户的其他可分辨名称(DN)。此表单创建
私有全局模式
.；一个空字符串(‘)，指示企业目录服务将通过身份验证的全局用户映射到此数据库模式，并具有适当的角色。此表单与指定
GLOBALLY
关键字并创建
共享全局模式
.
最大长度
directory_DN
是1024个字符。
您可以控制应用程序服务器以指定用户的身份连接并使用
ALTER
USER
声明
对创建全局用户的限制：Oracle ASM不支持创建全局用户。
DEFAULT TABLESPACE：
为在用户架构中创建的对象指定默认表空间。如果省略此子句，则用户的对象将存储在数据库默认表空间中。如果没有为数据库指定默认表空间，则用户的对象将存储在
SYSTEM
表空间。
对默认表空间的限制：
不能指定本地管理的临时表空间(包括撤消表空间)或字典管理的临时表空间作为用户的默认表空间。
TEMPORARY TABLESPACE ：
为用户的临时段指定表空间或表空间组。如果省略此子句，则用户的临时段存储在数据库默认临时表空间中，如果没有指定，则存储在
SYSTEM
表空间。
指定
tablespace
指示用户的临时表空间。指定
tablespace_group_name
指定的表空间组中的任何表空间中，用户可以保存临时段。
tablespace_group_name
.
对临时表空间的限制该条款受以下限制：
表空间必须是临时表空间，并且必须具有标准块大小。
表空间不能是撤消表空间或具有自动段空间管理的表空间。
QUOTA ：
配额条款使用
QUOTA
子句指定用户可以在表空间中分配的最大空间。A
CREATE
USER
语句可以有多个
QUOTA
用于多个表空间的子句。
UNLIMITED
允许用户不受绑定地在表空间中分配空间。您可以指定的最大空间为2TB(TB)。如果需要更多的空间，请指定
UNLIMITED
.
PROFILE：指定要分配给用户的配置文件。配置文件限制了用户可以使用的数据库资源的数量。如果省略此子句，则Oracle数据库将指定
DEFAULT
配置文件给用户。
注：
Oracle建议您使用数据库资源管理器而不是SQL配置文件来建立数据库资源限制。数据库资源管理器提供了一种更灵活的管理和跟踪资源使用的方法。有关数据库资源管理器的详细信息，请参阅
Oracle数据库管理员指南。
PASSWORD EXPIRE ：
指定
PASSWORD
EXPIRE
如果您希望用户的密码过期。此设置强制用户或DBA在用户登录到数据库之前更改密码。
ACCOUNT：
指定
ACCOUNT
LOCK
若要锁定用户帐户并禁用访问权限，请执行以下操作。指定
ACCOUNT
UNLOCK
若要解除用户帐户的锁定并启用对帐户的访问，请执行以下操作。
ENABLE ：
启用版本；这个条款是不可逆的。指定
ENABLE
EDITIONS
允许用户使用版本在此架构中创建多个版本的可编辑对象。不能编辑架构中未启用版本的可编辑对象。
二：用户管理实验操作
1：创建一个数据库验证用户，同时为它指定口令、默认表空间、临时表空间、空间配额，并要求他登录后更改口令。
--0)查表空间 select tablespace_name from dba_tablespaces; --1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL> CREATE USER w01 IDENTIFIED BY w01 DEFAULT TABLESPACE users TEMPORARY TABLESPACE tempts QUOTA 10M ON users PASSWORD EXPIRE; 用户已创建 --3）为用户授基本权限 SQL> GRANT CONNECT,RESOURCE TO w01; 授权成功。 --4）使用新用户登录数据库 SQL> CONN w01/w01 ERROR: ORA-28001: the password has expired 更改u01 的口令 新口令: 重新键入新口令: 口令已更改 已连接。
2、创建数据库验证用户，同时为它指定口令、缺省表空间、临时表空间、 空间配额，并设置该用户为锁定状态。
---0)查表空间 select tablespace_name from dba_tablespaces; --创建表空间 Create tablespace indx datafile '/u01/app/oracle/oradata/orcl/indx.dbf' size 2M; --1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL>CREATE USER w02 IDENTIFIED BY w02 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON indx QUOTA 1m ON users ACCOUNT LOCK; 用户已创建 --3）为用户授基本权限 SQL> grant connect,resource to w02; 授权成功。 --4）使用新用户登录数据库 SQL> CONN w02/w02 ERROR: ORA-28000: the account is locked 警告: 您不再连接到ORACLE。 --5）以管理员身份重新登录 SQL>CONNECT / AS SYSDBA 已连接。 --6）为账户解锁 SQL> ALTER USER w02 ACCOUNT UNLOCK; 用户已更改。 --7）使用新账户登录数据库 SQL> CONN w02/w02 已连接。
3、创建一个操作系统验证的用户ops$w03。（
外部用户
）
--1.使用root创建用户 [root@oracle ~]# useradd -G oinstall w03 [root@oracle ~]# passwd w03 --2.修改环境变量 [root@oracle ~]# su – w03 [w03@ocp ~]$ whoami [w03@ocp ~]$ vim .bash_profile export ORACLE_SID=orcl export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH [w03@ocp ~]$source .bash_profile --3.在ORACLE中创建 SQL> conn / as sysdba SQL>create user ops$w03 identified externally; ----4.为用户授权连接 SQL>grant connect to ops$w03; --5.尝试连接 [w03@ocp ~]$ sqlplus / SQL> show user USER is "OPS$W03"
4、用户w02修改自己的口令
5、把用户w02 在USERS 表空间上的配额改为0
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）修改配额 SQL>ALTER USER w02 QUOTA 0 ON USERS; 用户已更改。 CREATE USER w011 IDENTIFIED BY w011 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; SELECT tablespace_name,blocks*100/max_blocks || '%' "ok" FROM dba_ts_quotas WHERE username='W011' AND max_blocks<>-1; TABLESPACE_NAME ok ------------------------------ ----------------------------------------- USERS 0% SQL> alter user w011 quota 1k on users; SQL> grant connect to w011; Grant succeeded. SQL> grant create table to w011; Grant succeeded. SQL> conn w011/w011 SQL> create table ss(sdf int); insert into ss values(1); create table ss(sdf int) * ERROR at line 1: ORA-01536: space quota exceeded for tablespace 'USERS'
6、对用户进行修改，要求用户w02 登录后更改口令
7、锁定用户账号
8、解锁用户账号
9、删除用户
10、查询所有用户的默认表空间
11、显示用户w02的默认表空间和临时表空间信息
12、查看用户scott 的表空间配额
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）查询信息 SQL>SELECT tablespace_name,blocks*100/max_blocks || '%' FROM dba_ts_quotas WHERE username='SCOTT' AND max_blocks<>-1; TABLESPACE_NAME 已用空间百分比 ----------------------------- ------------------- USERS 6.25％
三：系统权限和对象权限
1、授予用户aaron1 具有创建表和视图的权限。
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 -2）创建用户 SQL> CREATE USER aaron1 IDENTIFIED BY aaron1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; 用户已创建 --3）授予权限 SOL>GRANT create session,CREATE TABLE,CREATE VIEW to aaron1; GRANT UNLIMITED TABLESPACE TO aaron1 ; 授权成功。 --4）使用权限创建表 SOL>connect aaron1／aaron1 已连接。 SOL>create table t1(cola int, colb char(3)); 表已创建。 SOL>insert into t1 values(1,'a'); 已创建1 行。 SOL>COMMIT; 提交完成。 create view a as select * from t1;
2、授予用户W01D连接数据库和创建表的权限
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL> CREATE USER w01d IDENTIFIED BY w01d DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; --3）授予权限 SOL>GRANT create session,CREATE TABLE to w01d; --4）使用权限创建表 SOL>connect w01d／w01d 已连接。 SOL>create table t1(cola int, colb char(3)); 表已创建。
3、其它用户授予SYSDBA权限
4、回收用户aaron1 的创建表的权限。
5、授予用户bb1有创建存储过程的权限，要求带WITH ADMIN OPTION参数。
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL> CREATE USER bb1 IDENTIFIED BY bb1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; SQL> CREATE USER bb2 IDENTIFIED BY bb2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; --3）管理员授予系统权限，并允许传递 SOL>GRANT create session,CREATE PROCEDURE to bb1 WITH ADMIN OPTION; 授权成功。 --4）以aa1用户登录,授予用户aa2创建存储过程的权限 SOL>CONNECT bb1/bb1 已连接。 SOL>GRANT create session,CREATE PROCEDURE to bb2; 授权成功。 --5）创建存储过程 SOL>connect bb2/bb2 已连接。 SOL> CREATE OR REPLACE PROCEDURE pp AS BEGIN DBMS_OUTPUT.PUT_LINE('ok '); END; / 过程已创建
6、回收用户aa1的创建存储过程的权限。
--1）以管理员身份登录 SOL>CONNECT / AS SYSDBA 已连接。 --2）回收权限 SOL>REVOKE CREATE PROCEDURE FROM bb1; 撤销成功。 --3）创建存储过程 SOL>connect bb2/bb2 已连接。 SOL> CREATE OR REPLACE PROCEDURE pp1 AS BEGIN DBMS_OUTPUT.PUT_LINE('ok'); END; / 过程已创建 --当DBA 收回了bb1的CREATE PROCEDURE 系统权限后，用户bb2仍然具有CREATE PROCEDURE 系统权限。因为系统权限不会被级联收回.
四：对象权限管理
1、ss用户把wq表的查询权限授予cc用户
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL> CREATE USER ss IDENTIFIED BY ss DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; SQL> CREATE USER cc IDENTIFIED BY cc DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; --3）管理员授予系统权限，并允许传递 SOL>GRANT create session,CREATE table to ss; Grant create session to cc; --4）以ss用户登录,创建表插入记录 SOL>CONNECT ss/ss Create table wq (ee int); Insert into wq values(12); --5）ss授予wq对象权限 SOL>GRANT SELECT ON wq to cc; 授权成功。 --6)cc查询wq表 SOL>connect cc/cc 已连接。 SOL>SELECT * FROM ss.wq;
2、ss用户把wq表的ee列的插入和更新权限授予cc用户。
3、为了使得用户cc可以在ss的wq表建立索引，就必须授予INDEX 对象权限。
4、cc用户要基于表ss.wq建立从表，则必须要被授予REFERENCES 对象权限。
--1）以SYSTEM 用户登录 SQL>connect ss/ss 已连接。 --2)建约束 SQL> alter table wq add constraint pk_wq primary key (ee); SQL> select constraint_name from user_constraints; 删除约束 alter table tempi drop constraint SYS_C002845; --2）授权 SQL>GRANT REFERENCES ON wq TO cc; 授权成功。 --3）创建外键 conn / as sysdba SYS@orcl> grant create table to cc; SQL>connect cc/cc 已连接。 SQL> CREATE TABLE tempi(cola int constraint fk_wq_tempi REFERENCES ss.wq(ee)); 表已创建。 SQL> select constraint_name from user_constraints;
5、回收用户cc的wq表的查询权限
6、回收用户cc在ss.wq表上创建索引的权限
7、使用WITH GRANT OPTION 参数，进行传递授权。
--1）以管理员身份登录 SQL>CONNECT / AS SYSDBA 已连接。 --2）创建用户 SQL> CREATE USER x1 IDENTIFIED BY x1_x1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; SQL> CREATE USER x2 IDENTIFIED BY x2_x2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; SQL> CREATE USER x3 IDENTIFIED BY x3_x3 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; --3）管理员授予系统权限，并允许传递 SOL>GRANT create session,CREATE table to x1; SOL>Grant create session to x2; SOL>Grant create session to x3; --4）以x1用户登录 SOL>CONNECT x1/x1_x1 已连接。 --5）建立表 SOL>create table ww(w1 int); Insert into ww values(123); --6）授予x2对象权限 SOL>GRANT select ON ww to x2 WITH GRANT OPTION; --7）以x2用户登录 SOL>CONNECT x2/x2_x2 已连接。 --8）授予x3对象权限 SOL>GRANT select ON x1.ww to x3; 授权成功。 --9）以x3用户登录 SOL>CONNECT x3/x3_x3 Select * from x1.ww;
8、验证对象权限的级联回收。
9、当把ORACLE resource 角色授予一个user 的时候，不但会授予ORACLE resource 角色，本身的权限，而且还有unlimited tablespace 权限。
conn / as sysdba SQL> create user desk identified by desk; SQL> grant create session to desk; SQL> conn desk SQL> select PRIVILEGE from user_sys_privs; SQL> conn / as sysdba DESK@orcl> conn / as sysdba SYS@orcl> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE'; SQL> grant resource to desk; SQL> conn desk SQL> select PRIVILEGE from user_sys_privs; SELECT username, granted_role FROM user_role_privs WHERE username='DESK'; SQL> show user USER is "SYS" SQL> create role testrole identified using testrole; SQL> revoke resource from desk; SQL> grant resource to testrole; SQL> grant testrole to desk; SQL> conn desk SQL> select privilege from user_sys_privs;
10、创建不需要验证的角色oe_clerk。
五:角色管理及角色赋权限
create user wl identified by wl DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; grant create session to wl; create role ro_wl identified by ro_wl; grant ro_wl to wl; create role ro1_wl identified by ro1_wl; grant ro1_wl to wl; SQL> conn wl/wl select PRIVILEGE from user_sys_privs; create table ww(w1 int); create view aa as select * from ww; SQL> conn / as sysdba grant create table to ro_wl; Grant create view to ro1_wl; SQL> conn wl/wl SELECT username, granted_role FROM user_role_privs WHERE username='WL'; SQL> create table ww(w1 int); 不可以建视图 SQL> create view aa as select * from ww; 不可以建视图 SQL> SET ROLE ro_wl IDENTIFIED BY ro_wl; SQL> create table ww1(w1 int); 可以建表 create view a1a as select * from ww1; 不能建视图 SQL> SET ROLE ro1_wl IDENTIFIED BY ro1_wl; create view a1a as select * from ww1; 可以建视图 SQL> create table ww2(w1 int); 不可以建表 --显示当前会话生效的角色 SOL>SELECT * FROM session_roles; SOL>SET ROLE NONE; create view a2a as select * from ww1; 不可以建视图 create table ww3(w1 int); 不可以建表
2、回收角色
3、删除角色
4、给角色授系统权限
5、给角色a3授予wl.aa 的DELETE 权限
SQL> CONNECT / AS SYSDBA SQL> CREATE USER wl IDENTIFIED BY wl_3 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; CREATE USER wll IDENTIFIED BY wll_3 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON users; CREATE ROLE a3; GRANT CREATE SESSION TO a3; CREATE ROLE a4; GRANT CREATE SESSION,create table TO a4; Grant a4 to wl; SQL> conn wl/wl_3 SQL> create table aa(ad int); SQL> insert into aa values(34); Conn / as sysdba Grant delete on wl.aa to a3; Grant a3 to wll; Conn wll/wll_3 SQL> delete from wl.aa; 1 row deleted.
6、查询数据库中所有的角色。
7、显示角色所具有的系统权限
8、角色所具有的对象权限
9、显示用户的默认角色
10、显示当前用户所具有的所有角色
11、显示当前会话生效的角色
此子句仅适用于通过SSL身份验证的外部用户。大
证书DN
用户钱包中的PKI证书中的可分辨名称。最大长度
证书DN
是1024个字符