用戶、概要文件、權限及角色實例 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

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的oracle查询用户的概要文件,Oracle用户、概要文件、权限及角色实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。