-- 例如,我要把scott用户下的所有表的select 权限给eygle用户, -- 可以先以scott用户登录,执行下面的语句,然后将其执行结果再次执行:scott@SZTYORA> select 'grant select on '||user||'.'||table_name||' to eygle;' from user_tables;'GRANTSELECTON'||USER||'.'||TABLE_NAME||'TOEYGLE;' ------------------------------------------------------------------------------------------------------------------------------- grant select on SCOTT.MYTB to eygle; grant select on SCOTT.DIANPINGCHANNELS to eygle; grant select on SCOTT.TT to eygle; grant select on SCOTT.T2 to eygle; grant select on SCOTT.MOBILES to eygle; grant select on SCOTT.EMP2 to eygle; grant select on SCOTT.LINKTIME to eygle; grant select on SCOTT.TMP1 to eygle; grant select on SCOTT.TEST to eygle; grant select on SCOTT.G_TEST to eygle; grant select on SCOTT.TABLE2 to eygle; grant select on SCOTT.TABLE1 to eygle; grant select on SCOTT.MUSIC_SEARCHLOG to eygle; grant select on SCOTT.TTT to eygle; grant select on SCOTT.CUST_ORDERS to eygle; grant select on SCOTT.T_HASHED to eygle; grant select on SCOTT.DEPT to eygle; grant select on SCOTT.EMP to eygle; grant select on SCOTT.BONUS to eygle; grant select on SCOTT.SALGRADE to eygle; grant select on SCOTT.WJT_201102161528 to eygle; grant select on SCOTT.USERINFO to eygle; grant select on SCOTT.USERINFO2 to eygle; grant select on SCOTT.EG to eygle; grant select on SCOTT.TB_TITLE to eygle; grant select on SCOTT.TEST1 to eygle; grant select on SCOTT.TB_REPLAY to eygle; grant select on SCOTT.MOBILEAREAMAP to eygle; grant select on SCOTT.TB to eygle; grant select on SCOTT.T1 to eygle; grant select on SCOTT.USERBUY to eygle; grant select on SCOTT.ATB to eygle; grant select on SCOTT.B to eygle; grant select on SCOTT.SERV_D10 to eygle; grant select on SCOTT.BIG_TABLE to eygle; grant select on SCOTT.num_tb to eygle; grant select on SCOTT.CONNECTION_LOG to eygle; grant select on SCOTT.WJT_201102151200 to eygle; grant select on SCOTT.MUSIC_CLICK_STAT_MEM to eygle; grant select on SCOTT.STUD to eygle; grant select on SCOTT.ALERT_LOG to eygle;已选择41行。
-- 当然:最简单的方法也可以这样! -- (但是:这样会对授权以后scott用户再创建的表,eygle用户也会有select权限)scott@SZTYORA> grant select any table to eygle;授权成功。
-- grant select any table to C -- 但是现在需要在DBA的A用户下使C有访问B所有表的权限 怎么做呢? -- 在DBA用户A下去将B用户的所有表的select权限授予C用户, -- 跟:直接在B用户下 grant select any table to C有区别么?-- grant select any table to C -- 默认不仅仅是当当前用户下的所有表、视图; -- 而是当前数据库中的所有表、所有视图的select权限给C用户,所以唯一的方法还是1楼的方法!
grant select on "B"."TableName" to "C"
方便还是可以的, 这个 SQL "grant xxx.yyy to C" 还是可以用前面那个 select 语句来生成的嘛。比如 select 'grant select on '||user||'.'||table_name||' to C;' from all_tables where owner='B'忘记了 Oracle 里面的那个是用 OWNER = 'B' 还是 Schema='B' 或是别的。只要到 all_tables 里面看一条数据就知道了。不能用 user_tables 因为 user_tables 只能看当前用户的表,这时只看到 A 的表,而不是 B 的表。
-- 可以先以scott用户登录,执行下面的语句,然后将其执行结果再次执行:scott@SZTYORA> select 'grant select on '||user||'.'||table_name||' to eygle;' from user_tables;'GRANTSELECTON'||USER||'.'||TABLE_NAME||'TOEYGLE;'
-------------------------------------------------------------------------------------------------------------------------------
grant select on SCOTT.MYTB to eygle;
grant select on SCOTT.DIANPINGCHANNELS to eygle;
grant select on SCOTT.TT to eygle;
grant select on SCOTT.T2 to eygle;
grant select on SCOTT.MOBILES to eygle;
grant select on SCOTT.EMP2 to eygle;
grant select on SCOTT.LINKTIME to eygle;
grant select on SCOTT.TMP1 to eygle;
grant select on SCOTT.TEST to eygle;
grant select on SCOTT.G_TEST to eygle;
grant select on SCOTT.TABLE2 to eygle;
grant select on SCOTT.TABLE1 to eygle;
grant select on SCOTT.MUSIC_SEARCHLOG to eygle;
grant select on SCOTT.TTT to eygle;
grant select on SCOTT.CUST_ORDERS to eygle;
grant select on SCOTT.T_HASHED to eygle;
grant select on SCOTT.DEPT to eygle;
grant select on SCOTT.EMP to eygle;
grant select on SCOTT.BONUS to eygle;
grant select on SCOTT.SALGRADE to eygle;
grant select on SCOTT.WJT_201102161528 to eygle;
grant select on SCOTT.USERINFO to eygle;
grant select on SCOTT.USERINFO2 to eygle;
grant select on SCOTT.EG to eygle;
grant select on SCOTT.TB_TITLE to eygle;
grant select on SCOTT.TEST1 to eygle;
grant select on SCOTT.TB_REPLAY to eygle;
grant select on SCOTT.MOBILEAREAMAP to eygle;
grant select on SCOTT.TB to eygle;
grant select on SCOTT.T1 to eygle;
grant select on SCOTT.USERBUY to eygle;
grant select on SCOTT.ATB to eygle;
grant select on SCOTT.B to eygle;
grant select on SCOTT.SERV_D10 to eygle;
grant select on SCOTT.BIG_TABLE to eygle;
grant select on SCOTT.num_tb to eygle;
grant select on SCOTT.CONNECTION_LOG to eygle;
grant select on SCOTT.WJT_201102151200 to eygle;
grant select on SCOTT.MUSIC_CLICK_STAT_MEM to eygle;
grant select on SCOTT.STUD to eygle;
grant select on SCOTT.ALERT_LOG to eygle;已选择41行。
-- (但是:这样会对授权以后scott用户再创建的表,eygle用户也会有select权限)scott@SZTYORA> grant select any table to eygle;授权成功。
-- 还要注意:这样 eygle 用户 会对 scott用户 的 所有表和视图 均有 select 权限!
-- 但是现在需要在DBA的A用户下使C有访问B所有表的权限 怎么做呢?
-- 在DBA用户A下去将B用户的所有表的select权限授予C用户,
-- 跟:直接在B用户下 grant select any table to C有区别么?-- grant select any table to C
-- 默认不仅仅是当当前用户下的所有表、视图;
-- 而是当前数据库中的所有表、所有视图的select权限给C用户,所以唯一的方法还是1楼的方法!
select 'grant select on '||user||'.'||table_name||' to C;' from all_tables where owner='B'忘记了 Oracle 里面的那个是用 OWNER = 'B' 还是 Schema='B' 或是别的。只要到 all_tables 里面看一条数据就知道了。不能用 user_tables 因为 user_tables 只能看当前用户的表,这时只看到 A 的表,而不是 B 的表。