oracl-grant 使用

—建立检查的视图 --------创建用户-------
select ‘grant all on ‘||owner||’.’||table_name||’ to dhf;’ from dba_tables where owner Like ‘%INPBILL%’;–全部表格授权
revoke unlimited tablespace from dhf;–移除权限
Drop User dhf Cascade;–删除用户
create User dhf Identified By dhf;–用户名及密码
Grant Connect To dhf;–增加连接权限
Grant Select On dhf_ls To dhf with grant option; Grant Update On dhf_ls To dhf; Grant Dba To dhf Grant Connect, Dba,Resource To dhf;–三大权限， dba数量最高权限
Grant unlimited Tablespace To dhf;–使用表空间–需单独设置
Grant Create Synonym To z66812; --授权同义词
Create Public Synonym dhf_ls For z66812.dhf_ls; --授权公共同义词 ，任何用户都可以不带前缀名访问 select username,default_tablespace,temporary_tablespace from dba_users Where username Like ‘%DHF%’;–查询表空间使用

1创建表空间----
create tablespace dhf_tablespace datafile ‘D:\app\Administrator\oradata\orcl\dhf\dhf.dbf’ size 200M –
create temporary tablespace dhf_temp_tablespace tempfile D:\app\Administrator\oradata\orcl\dhf\dhf_temp.dbf’ size 150M;
------2删除表空间-----
drop tablespace dhf_tablespace including contents and datafiles;
drop tablespace dhf__temp_tablespace including contents and datafiles;

–赋予用户dhf表空间d hf_tablespace
alter user dhf quota unlimited on dhf_tablespace;–1
alter user dhf default tablespace dhf_tablespace;—2，1.、2均可
alter user dhf temporary tablespace dhf_temp_tablespace;
–表空间自动增长
alter database datafile ‘D:\app\Administrator\oradata\orcl\dhf\dhf.dbf’ autoextend on next 100M maxsize 10000M
–查询表空间使用情况
select username,temporary_tablespace from dba_users
select username,default_tablespace,temporary_tablespace from dba_users;