您好,登錄后才能下訂單哦!
創(chuàng)建用戶并賦予該用戶授權(quán)權(quán)限。
create user ken identified by ken;
授予權(quán)限并附帶admin option
grant create session, create table to ken with admin option;
授予權(quán)限,不帶with admin option
grant create view to ken;
新建tom用戶
create user tom identified by tom;
使用ken用戶登錄后對tom授權(quán), 語句執(zhí)行成功
grant create session, create table to tom; Grant succeeded. 對tom授權(quán),語句執(zhí)行失敗 grant create view to tom; grant create view to tom * ERROR at line 1: ORA-01031: insufficient privileges
note 注意,回收權(quán)限不是級聯(lián)的。
從ken回收登錄權(quán)限
revoke create session from ken;
此時tom依然可以登錄,但Ken無法登錄了
ERROR: ORA-01045: user KEN lacks CREATE SESSION privilege; logon denied
select distinct privilege from dba_tab_privs;
select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'TOM';
grant select on t to tom
grant all on emp to monkey;
grant update on emp(sal) to monkey
grant execute on dbms_transaction to ken;
grant index on scott.emp to blake;
revoke select on emp from blake
note 回收Object Privilege 會導(dǎo)致級聯(lián)回收。
角色Role,定義一組權(quán)限。
select * from role_sys_privs where role='角色名'
select * from role_sys_privs where role='CONNECT';
select * from role_sys_privs where role='RESOURCE';
dba角色具有所有的系統(tǒng)權(quán)限,及with admin option選項(xiàng),默認(rèn)的dba用戶為sys和system,它們可以將任何系統(tǒng)權(quán)限授予其他用戶。但是要注意的是dba角色不具備sysdba和sysoper的特權(quán)(啟動和關(guān)閉數(shù)據(jù)庫)
note 一般而言,創(chuàng)建用戶后,給與connect角色和resource就夠了。
create role 角色名 not identified;
建立角色(數(shù)據(jù)庫驗(yàn)證)
create role 角色名 identified by 密碼;
grant create session to 角色名 with admin option grant select on scott.emp to 角色名; grant insert, update, delete on scott.emp to 角色名;
grant 角色名 to blake with admin option;
select * from dba_roles;
select privilege, admin_option from role_sys_privs where role='角色名';
select granted_role, default_role from dba_role_privs where grantee = '用戶名';
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。