用户、概要文件、权限及角色实例
1、创建角色change
SQL> create role change identified by zhubajie;
Role created
SQL> select * from dba_roles where role='CHANGE';
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CHANGE YES PASSWORD
2、给角色赋权select any table
SQL> grant select any table to change;
Grant succeeded
SQL> select * from role_sys_privs where role='CHANGE';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ -------------------- ------------
CHANGE SELECT ANY TABLE NO
3、创建用户sudaji
SQL> create user sudaji
2 identified by sudaji
3 default tablespace lianxi
4 temporary tablespace lianxi_temp
5 quota 38M on lianxi
6 quota 28M on lianxi_index;
User created
4、给用户sudaji授权
SQL> grant connect,resource,change to sudaji;
Grant succeeded
5、给用户sudaji默认角色
SQL> alter user sudaji default role all except change;
User altered
6、查看sudaji用户信息
select * from dba_users where username='SUDAJI';
7、查看用户sudaji使用表空间限制
SQL> select username,tablespace_name,BYTES/1024/1024 MB,
2 MAX_BYTES/1024/1024 MAX_BYTES
3 from dba_ts_quotas where username='SUDAJI';
USERNAME TABLESPACE_NAME MB MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
SUDAJI LIANXI 0 38
SUDAJI LIANXI_INDEX 0 28
8、新建4个用户
SQL> create user tangceng identified by tangceng default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user sunwukong identified by sunwukong default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user zhubajie identified by zhubajie default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user shaheshang identified by shaheshang default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
9、给新建4个用户授权
SQL> grant connect,resource,change to tangceng,sunwukong,zhubajie,shaheshang;
Grant succeeded
10、新建的4个用户默认角色
SQL> alter user tangceng default role all except change;
User altered
SQL> alter user sunwukong default role all except change;
User altered
SQL> alter user zhubajie default role all except change;
User altered
SQL> alter user shaheshang default role all except change;
User altered
11、查看新建用户的表空间信息
SQL> select username,default_tablespace,temporary_tablespace,created,profile
2 from dba_users where default_tablespace like 'LIAN%';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ ------------------------------ ----------- ------------------------------
ZHUBAJIE LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SUDAJI LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SHAHESHANG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
TANGCENG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SUNWUKONG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
CAT LIANXI LIANXI_TEMP 2017-9-22 1 DEFAULT
6 rows selected
SQL> select username,tablespace_name,BYTES/1024/1024 MB,
2 MAX_BYTES/1024/1024 MAX_BYTES
3 from dba_ts_quotas where tablespace_name like 'LIAN%';
USERNAME TABLESPACE_NAME MB MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
CAT LIANXI 0 50
TANGCENG LIANXI_INDEX 0 28
SHAHESHANG LIANXI 0 38
SUNWUKONG LIANXI_INDEX 0 28
SUNWUKONG LIANXI 0 38
ZHUBAJIE LIANXI_INDEX 0 28
ZHUBAJIE LIANXI 0 38
SUDAJI LIANXI_INDEX 0 28
SHAHESHANG LIANXI_INDEX 0 28
TANGCENG LIANXI 0 38
SUDAJI LIANXI 0 38
11 rows selected
12、查询权限及角色
SQL> select * from dba_role_privs where grantee in ('TANGCENG','SUNWUKONG','ZHUBAJIE','SHAHESHANG');
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
--------------- --------------- ------------ ------------
ZHUBAJIE CONNECT NO YES
TANGCENG CONNECT NO YES
SUNWUKONG CONNECT NO YES
SHAHESHANG CONNECT NO YES
TANGCENG RESOURCE NO YES
SUNWUKONG RESOURCE NO YES
ZHUBAJIE CHANGE NO NO
SUNWUKONG CHANGE NO NO
TANGCENG CHANGE NO NO
SHAHESHANG CHANGE NO NO
ZHUBAJIE RESOURCE NO YES
SHAHESHANG RESOURCE NO YES
12 rows selected
1、创建角色change
SQL> create role change identified by zhubajie;
Role created
SQL> select * from dba_roles where role='CHANGE';
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CHANGE YES PASSWORD
2、给角色赋权select any table
SQL> grant select any table to change;
Grant succeeded
SQL> select * from role_sys_privs where role='CHANGE';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ -------------------- ------------
CHANGE SELECT ANY TABLE NO
3、创建用户sudaji
SQL> create user sudaji
2 identified by sudaji
3 default tablespace lianxi
4 temporary tablespace lianxi_temp
5 quota 38M on lianxi
6 quota 28M on lianxi_index;
User created
4、给用户sudaji授权
SQL> grant connect,resource,change to sudaji;
Grant succeeded
5、给用户sudaji默认角色
SQL> alter user sudaji default role all except change;
User altered
6、查看sudaji用户信息
select * from dba_users where username='SUDAJI';
7、查看用户sudaji使用表空间限制
SQL> select username,tablespace_name,BYTES/1024/1024 MB,
2 MAX_BYTES/1024/1024 MAX_BYTES
3 from dba_ts_quotas where username='SUDAJI';
USERNAME TABLESPACE_NAME MB MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
SUDAJI LIANXI 0 38
SUDAJI LIANXI_INDEX 0 28
8、新建4个用户
SQL> create user tangceng identified by tangceng default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user sunwukong identified by sunwukong default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user zhubajie identified by zhubajie default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
SQL> create user shaheshang identified by shaheshang default tablespace lianxi temporary
tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;
User created
9、给新建4个用户授权
SQL> grant connect,resource,change to tangceng,sunwukong,zhubajie,shaheshang;
Grant succeeded
10、新建的4个用户默认角色
SQL> alter user tangceng default role all except change;
User altered
SQL> alter user sunwukong default role all except change;
User altered
SQL> alter user zhubajie default role all except change;
User altered
SQL> alter user shaheshang default role all except change;
User altered
11、查看新建用户的表空间信息
SQL> select username,default_tablespace,temporary_tablespace,created,profile
2 from dba_users where default_tablespace like 'LIAN%';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
------------------------------ ------------------------------ ------------------------------ ----------- ------------------------------
ZHUBAJIE LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SUDAJI LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SHAHESHANG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
TANGCENG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
SUNWUKONG LIANXI LIANXI_TEMP 2017-9-25 1 DEFAULT
CAT LIANXI LIANXI_TEMP 2017-9-22 1 DEFAULT
6 rows selected
SQL> select username,tablespace_name,BYTES/1024/1024 MB,
2 MAX_BYTES/1024/1024 MAX_BYTES
3 from dba_ts_quotas where tablespace_name like 'LIAN%';
USERNAME TABLESPACE_NAME MB MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
CAT LIANXI 0 50
TANGCENG LIANXI_INDEX 0 28
SHAHESHANG LIANXI 0 38
SUNWUKONG LIANXI_INDEX 0 28
SUNWUKONG LIANXI 0 38
ZHUBAJIE LIANXI_INDEX 0 28
ZHUBAJIE LIANXI 0 38
SUDAJI LIANXI_INDEX 0 28
SHAHESHANG LIANXI_INDEX 0 28
TANGCENG LIANXI 0 38
SUDAJI LIANXI 0 38
11 rows selected
12、查询权限及角色
SQL> select * from dba_role_privs where grantee in ('TANGCENG','SUNWUKONG','ZHUBAJIE','SHAHESHANG');
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
--------------- --------------- ------------ ------------
ZHUBAJIE CONNECT NO YES
TANGCENG CONNECT NO YES
SUNWUKONG CONNECT NO YES
SHAHESHANG CONNECT NO YES
TANGCENG RESOURCE NO YES
SUNWUKONG RESOURCE NO YES
ZHUBAJIE CHANGE NO NO
SUNWUKONG CHANGE NO NO
TANGCENG CHANGE NO NO
SHAHESHANG CHANGE NO NO
ZHUBAJIE RESOURCE NO YES
SHAHESHANG RESOURCE NO YES
12 rows selected