关于oracle 用户去DBA权限的管理

为了规范数据库用户的操作，特制订如下规范：
一、每个系统建立自己的数据库文件和索引文件，每个文件不允许超过2G，不支持自动扩展，系统根据自己的情况申请相应大小的数据文件；
创建文件系统的命令为：
（1）创建表空间
Create tablespace coredb_data logging datafile
'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_data01.dbf'
size 2G autoextend off
（2）创建索引空间
Create tablespace coredb_idx logging datafile
'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx01.dbf'
size 1G autoextend off
（3）增扩表空间
alter tablespace coredb_idx add datafile
'/home/oracle/oracle/product/10.2.0/oradata/orcl/coredb_idx02.dbf' size 2G
autoextend off
（4）查看表空间
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
二、创建用户的命令
使用ORACLE用户执行如下命令（无DBA权限）：
sqlplus '/as sysdba' <<!
drop user <user> cascade;
create user <user> identified by <user> default tablespace coredb_data temporary tablespace temp;
grant connect to <user>;
grant resource to <user>;
grant create session to <user>;
grant create view to <user>;
alter user <user> quota unlimited on coredb_idx;
alter user <user> quota unlimited on coredb_data;
!
三、检查用户是否有DBA权限
select * from sys.dba_role_privs where granted_role='DBA';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---------------------- SYSTEM DBA YES YES
GLSDB DBA YES YES
SHENG DBA YES YES
SYS DBA YES YES COREDB DBA YES YES
四、去除DBA权限
1）Sqlplus “/as sysdba”
2）revoke dba from <user>;
3）exit;
五、用户的权限
（1）查看用户权限；
（2）增加用户权限；当系统报错：ORA-01031: 权限不足
grant create any synonym to dhrep;//增加同义词权限（3）解除用户权限；
五、异常情况的处理
（1）ora-01045:user system lacks create session privilege; logon denied 解决办法：
1）使用oracle 用户执行sqlplus “/as sysdba”
2）grant create session,resource to <user>;
3）exit;
（2）ORA-1536：space quota exceeded for tablespace
解决办法：
1）使用oracle 用户执行sqlplus “/as sysdba”
2）Grant unlimited tablespace to <user>;
3）Exit;
（3）ORA-01950: 对表空间'USERS' 无权限
解决办法：
1）使用oracle 用户执行sqlplus “/as sysdba”
2）Conn <user>/<passwd>;
3）select * from user_sys_privs;
USERNAME PRIVILEGE ADM
-----------------------------------------------------
coredb CREATE SESSION NO
4）grant connect,resource,create session,create view to glsdb_hn;
5）select * from user_sys_privs;
USERNAME PRIVILEGE
ADM
------------------------ ---------------------------------------- ---
coredb_HN CREATE VIEW NO
coredb_HN CREATE SESSION NO
coredb_HN UNLIMITED TABLESPACE NO 6）Exit;
（4）没有DBA权限后，如果涉及跨db_user访问其他用户的，需要以下方法：1）创建存储过程
将两个用户名作为参数传给存储过程：
/*把用户（From_user）所有表的读写权限赋给用户（To_user）的存储过程为*/
create or replace procedure grantUser(From_user in varchar2, To_user in varchar2) is
retval number;
scursor int;
v_tablename VARCHAR2(60);
sqlstr varchar2(200);
cursor c_tablename is select table_name from dba_tables where owner=From_user; BEGIN
for v_tablename in c_tablename
loop
sqlstr := 'grant select,delete,update,insert on '|| From_user||'.'||v_tablename.table_name || ' to '||To_user;
scursor := dbms_sql.open_cursor;
dbms_sql.parse(scursor,sqlstr, dbms_sql.native);
retval := dbms_sql.execute(scursor);
dbms_sql.close_cursor(scursor);
end loop;
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END grantUser;
/
/*把用户（From_user）所有表的读写权限从用户（To_user）收回的存储过程为*/ create or replace procedure revokeUser(From_user in varchar2, To_user in varchar2) is
retval number;
scursor int;
v_tablename VARCHAR2(60);
sqlstr varchar2(200);
cursor c_tablename is select table_name from dba_tables where owner=From_user; BEGIN
for v_tablename in c_tablename
loop
sqlstr := 'revoke select,delete,update,insert on '||
From_user||'.'||v_tablename.table_name || ' from '||To_user;
scursor := dbms_sql.open_cursor;
dbms_sql.parse(scursor,sqlstr, dbms_sql.native);
retval := dbms_sql.execute(scursor);
dbms_sql.close_cursor(scursor);
end loop;
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END revokeUser;
2）执行存储过程（注意，存储过程的名称不能一样，否则会被替换）Execute grantUser / revokeUser;
3）如果增加新的表，还需要充分执行同样的操作；。