在Oracle数据库中,可以创建只读的数据库用户吗?如何创建?
在 Oracle 数据库中,用户只拥有你授予的权限。因此,你可以通过不授予其他权限来创建只读用户。
当你创建一个用户时
CREATE USER ro_user
IDENTIFIED BY ro_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
用户甚至没有登录到数据库的权限。你可以授予该权限。
GRANT CREATE SESSION to ro_user
然后你可以开始授予任何你想要的读取权限。例如,如果你想让RO_USER
能够查询SCHEMA_NAME.TABLE_NAME
,你需要执行类似以下的操作:
GRANT SELECT ON schema_name.table_name TO ro_user
通常情况下,最好创建一个角色,并将对象权限授予该角色,以便您可以将该角色授予不同的用户。类似于
创建角色
CREATE ROLE ro_role;
授予该模式中每个表的SELECT访问权限
BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name ||
' TO ro_role';
END LOOP;
END;
然后将该角色授予用户
GRANT ro_role TO ro_user;
create user ro_role identified by ro_role;
grant create session, select any table, select any dictionary to ro_role;
create user readonly_user identified by "password"; grant create session, select any table, select any dictionary to readonly_user;
- Dlongneckercreate or replace
procedure createReadOnlyUser(p_owner in varchar2, p_readonly in varchar2)
AUTHID CURRENT_USER is
BEGIN
execute immediate 'create user '||p_readonly||' identified by '||p_readonly;
execute immediate 'grant create session to '||p_readonly;
execute immediate 'grant select any dictionary to '||p_readonly;
execute immediate 'grant create synonym to '||p_readonly;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('TABLE', 'VIEW') and owner=p_owner) LOOP
execute immediate 'grant select on '||p_owner||'.'||R.object_name||' to '||p_readonly;
END LOOP;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('FUNCTION', 'PROCEDURE') and owner=p_owner) LOOP
execute immediate 'grant execute on '||p_owner||'.'||R.object_name||' to '||p_readonly;
END LOOP;
FOR R IN (SELECT owner, object_name FROM all_objects WHERE object_type in('TABLE', 'VIEW') and owner=p_owner) LOOP
EXECUTE IMMEDIATE 'create synonym '||p_readonly||'.'||R.object_name||' for '||R.owner||'."'||R.object_name||'"';
END LOOP;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('FUNCTION', 'PROCEDURE') and owner=p_owner) LOOP
execute immediate 'create synonym '||p_readonly||'.'||R.object_name||' for '||R.owner||'."'||R.object_name||'"';
END LOOP;
END;
你可以创建用户并授予权限
创建只读用户:create user read_only identified by read_only; 授予会话创建和选择所有表的权限给只读用户:grant create session,select any table to read_only;
由于每个用户通过公共获得的许多公共执行,因此在默认数据库中严格来说不可能。