Oracle 用户、角色管理简介
by:授客 QQ：1033553122
创建用户
形式1：创建名为testacc2的用户
CREATE USER testacc2
IDENTIFIED BY abc1234
DEFAULT TABLESPACE testtbs001
QUOTA 10M ON testtbs001
TEMPORARY TABLESPACE testtbs002
QUOTA 5M ON SYSTEM
PASSWORD EXPIRE;
说明：
1）testacc2：用户名
2）abc1234 ：用户密码（大小写敏感
3）DEFAULT TABLESPACE testtbs001：指定在用户方案(schema)中创建的对象的默认表空间为testtbs001
说明：
1.如果不指定，则用户对象存储在数据库默认表空间中。如果没有为数据库指定默认表空间，那么用户对象将存储在SYSTEM表空间
2.限制：不能指定本地管理的临时表空间，包括undo表空间，或者字典管理的临时表空间作为用户默认表空间。
4）QUOTA 10M ON testtbs001：设置用户在指定表空间中testtbs001的可用配额
说明：
1.一个CREATE USER语句可有多个QUOTA子句。
2.QUOTA子句的限制：不能为临时表空间指定该子句
5）TEMPORARY TABLESPACE testtbs002：为用户临时段指定表空间或表空间组。这里指定临时表空间为testtbs002。
说明：
1. 如果不指定该语句，那么用户临时段存储在数据库默认的临时表空间中，或者没有为数据库指定默认表空间，那么存储在SYSTEM表空间中。
2. 如果指定的是表空间名，则表示用户的临时表空间
3. 如果指定是表空间组名，则表示用户可以在由表空间组名指定的表空间组中的任意表空间中存储临时表空间段
4.限制：表空间必须为临时表空间，且具有标准块大小；表空间不能是UNDO表空间或有自动段空间管理的表空间
6）PASSWORD EXPIRE：设置用户密码过期。这样，当用户登录数据库时，必须重新设置密码。
如下：
SQL> conn testacc2
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for testacc2
New password:
Retype new password:
ERROR:
ORA-01045: user TESTACC2 lacks CREATE SESSION privilege; logon denied
Password changed
Warning: You are no longer connected to ORACLE.
SQL> conn testacc2
Enter password:
ERROR:
ORA-01045: user TESTACC2 lacks CREATE SESSION privilege; logon denied
说明：提示错误是因为此时没有授予用户权限
形式2：创建名为testacc2的用户，在默认表空间中的可用配额无限制
CREATE USER testacc2
IDENTIFIED BY abc1234
DEFAULT TABLESPACE testtbs001
QUOTA UNLIMITED ON testtbs001
TEMPORARY TABLESPACE testtbs002
QUOTA 5M ON SYSTEM;
说明：
QUOTA UNLIMITED ON testtbs001：设置用户在指定表空间testtbs001中可用配额无限制
形式3：创建名为testacc2的用户，指定profile
CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;
说明： PROFILE app_user ，指定分配给用户的profile。profile限制用户可使用的数据库资源的数量。如果未指定，oracle会使用默认的profile.
注意：oracle推荐使用Database Resource Manager,而不是SQL profile来建立数据库资源限制。
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8003.htm#SQLRF01503
修改用户
形式1：修改用户口令为abc123
ALTER USER testacc2 IDENTIFIED BY abc123;
形式2：设置用户口令过期
ALTER USER testacc2 PASSWORD EXPIRE;
形式3：修改用户默认表空间为testbs01
ALTER USER testacc2 DEFAULT TABLESPACE testtbs01;
形式4：修改用户临时表空间为TEMP
ALTER USER testacc2 TEMPORARY TABLESPACE TEMP;
说明：如果要修改用户所在临时表空间组名，如下
ALTER USER username TEMPORARY TABLESPACE tablespace_group_name;
形式5：修改用户在表空间testtbs001中的配额为10M（注：如果无限制则设置QUOTA UNLIMITED
ALTER USER testacc2 QUOTA 10M ON testtbs001;
形式5：锁定用户、解锁用户
锁定
ALTER USER testacc2 ACCOUNT LOCK;
解锁
ALTER USER testacc2 ACCOUNT UNLOCK;
形式6：修改用户的默认角色
ALTER USER username DEFAULT ROLE option;
作用：指定登录时，授权给用户的默认角色。
选项可以是：
ALL:所有角色
ALL EXCEPT role_name：除角色role_name之外的所有角色
NONE：没任何角色
role_name：由role_name指定的角色
如下：
ALTER USER testacc2 DEFAULT ROLE ALL;
ALTER USER testacc2 DEFAULT ROLE ALL EXCEPT role_name;
ALTER USER testacc2 DEFAULT ROLE NONE;
说明：
以下情况不能使用上述语句：
a) 角色为未授权给用户的角色
b) 角色为通过其他角色授权给用户的角色
c) 角色为由外部服务（如操作系统）或Oracle Internet Directory管理的角色或者external service (such as the operating system), or by the
d) 由SET ROLE开启的角色，如密password-authenticated和secure application 角色
形式7：修改用户的profile文件
ALTER USER testacc2 PROFILE new_profile;
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103
创建角色
形式1：创建角色，不使用密码
CREATE ROLE test_role;
说明：被授予test_role角色的用户将继承授权给test_role角色的所有权限。
形式2：创建角色，并使用密码
CREATE ROLE test_role2 IDENTIFIED BY abc1234;
说明：使用了IDENTIFIED BY，被授予角色（例中为test_role2）的用户必须使用SET ROLE来激活角色：SET ROLE roel_name IDENTIFIED BY role_password
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311
CREATE ROLE test_role IDENTIFIED BY abc123;
形式1：修改角色为不需要验证激活
ALTER ROLE test_role NOT IDENTIFIED;
形式2：修改角色验证密码
ALTER ROLE test_role IDENTIFIED BY abc1234;
注意：
如果NOT IDENTIFIED 角色已经被授权给其它角色， 则不能修改NOT IDENTIFIED 为IDENTIFIED
参考链接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2010.htm#SQLRF00815
用户授权
1.授予用户连接权限
GRANT CONNECT TO testacc2;
授予权限后，sqlplus连接
SQL> CONN testacc2
Enter password: #输入登录密码
Connected.
2.授予用户创建会话权限
GRANT CREATE SESSION TO testacc2;
注意：用户如果需要登录oracle，必须有 CONNECT、CREATE SESSION的权限
3.授予用户系统权限
GRANT system_privillege TO testacc2;
GRANT ALL PRIVILEGES TO testacc2;
说明：ALL PRIVILEGES：代表表Table 18-1中，除ANY DICTIONARY, ALTER DATABASE LINK,和ALTER PUBLIC DATABASE LINK权限外的所有系统权限
4.授予用户所有对象权限
GRANT object_privillege[(colument)] ON object TO testacc2;
GRANT ALL [PRIVILEGES] ON object TO username;
例：授予用户testacc2 test_table表上的所有权限
GRANT ALL ON test_table TO testacc2;
或
GRANT ALL PRIVILEGES ON test_table TO testacc2;
说明：
1.ALL [PRIVILEGES]，授予对象的所有权限给用户，授权者必须有该对象的GRANT OPTION权限.方案的所有者用户自动包含所有方案(schema)中所有对象的所有权限，且对每个对象都有GRANT OPTION权限。
2.被授权用户查询授权者用户方案下的对象时，必须加上方案名(通常为用户名)，如下，加方案名testacc
SQL> SELECT * FROM testacc.test_table;
no rows selected
否则，会提示ORA-00942: table or view does not exist的错误
注：此处，表test_table为testacc用户所创建的
5.授予用户在某个表或视图的数据列上的权限
GRANT UPDATE(id) ON test_table TO testacc2;
说明：仅在授予INSERT, REFERENCES, 或UPDATE权限时才可指数据列
6.授予用户权限，且被授予者拥有把权限授予其他人、角色的权限
GRANT SELECT, DELETE ON test_table TO testacc2
WITH GRANT OPTION;
说明：
授予用户testacc2对表test_table的查询，删除权限，WITH GRANT OPTION,使用户testacc2可把获取的权限再授予其他用户、角色
7．授予用户权限，被授权用户有管理权限
GRANT UPDATE ON test_table TO testacc2 WITH GRANT OPTION;
注意：WITH GRANT OPTION仅适用于角色、对象权限
8.授予用户角色
形式1：授予用户角色，不指定WITH ADMIN OPTION;
GRANT test_role TO testacc2;
形式2：授予用户角色，指定WITH ADMIN OPTION;
GRANT test_role2 TO testacc2 WITH ADMIN OPTION;
注意：
WITH ADMIN OPTION允许被授权用户:
1）授权角色、权限给其他角色、用户，GLOBAL角色除外
2）从其他用户、角色回收权限、角色
3）修改权限、角色
4）删除权限、角色
WITH ADMIN OPTION仅适用于角色，系统权限
9.授予所有用户权限
GRANT SELECT, UPDATE ON test_table TO public;
说明：public,代表所有用户
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#SQLRF01603
回收权限、角色
形式1：回收某个、多个对象权限
REVOKE UPDATE ON test_table FROM testacc2;
REVOKE UPDATE,SELECT ON test_table FROM testacc2;
形式2：回收所有对象权限
REVOKE ALL ON test_table FROM testacc2;
或者
REVOKE ALL PRIVILEGES ON test_table FROM testacc2;
形式3：回收所有授予用户的系统权限
REVOKE ALL PRIVILEGES FROM testacc2;
形式4：回收授予用户的某个、多个系统系统权限
REVOKE CONNECT FROM testacc2;
REVOKE CREATE ANY TABLE, CREATE TABLE FROM testacc2;
形式4：回收授权给用户的角色
REVOKE test_role FROM testacc2;
形式5：级联回收对象权限
REVOKE REFERENCES ON test_table FROM testacc2 CASCADE CONSTRAINTS;
说明：
CASCADE CONSTRAINTS,仅和移除REFERENCES或ALL [PRIVILEGES]对象权限相关.也就是说该语句的作用为：1.删除由testacc2所创建的，REFERENCES 指定的所有参照完整性约束外键约束；2.回收testacc2用户创建外键的权限
形式6：从多用户回收权限
REVOKE CREATE SESSION FROM testacc2,testacc2
形式7：收回所有用户的权限
REVOKE SELECT, UPDATE ON test_table FROM public;
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9020.htm#SQLRF01609
删除用户
形式1：删除用户，不删除用户用户方案中包含的对象
DROP USER testacc2;
形式2：删除用户，并删除用户方案中包含的方案对象，比如表，视图等
DROP USER testacc2 CASCADE;
参考连接：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9008.htm#SQLRF01811
删除权限
DROP ROLE test_role;