在数据库中对于修改用户名,在11g以前一直有一种攻略,那就是修改数据字典基表user$,这种方式的优点就是简单粗暴,当然缺点就是后果不可控。至于有什么更多的风险,其实还是未知。当然从官方的文档和支持中来看,是没有这个功能的,推荐的做法也都是数据迁移这种方式,这一点上和其它的数据库相比,感觉Oracle是比较苛刻。
Oracle和MySQL中的用户
在mysql之中这是一件很轻松的事情。其实mysql之中的用户和Oracle的用户的概念和含义也有很大的差别。所以在此不偷换概念。从我的简单认识来看,用户在Oracle和MySQL里面的概念是截然不同的,可以用下面的示意图来说明。
11g以前的rename user方式
今天在微信社群里,有一个群友提了一个问题,说数据库里的用户能不能修改,对于这个问题,有些群友的反馈是可以,直接修改基表即可。
数据库的表名可以修改,这个是很容易支持的,也很容易理解,但是修改用户这个情况下就很纠结,纠结的也还是这些用户下所属的对象的归属,有的朋友可能会说,用户名就跟一个code一样,又不修改id,修改的是name,对于数据库的影响范围应该是最小的吧。这一点还待商榷。稳妥起见,都是采用数据迁移的方式来完成简介的rename user.
11g开始的新特性rename user
在11g开始,有一个新特性改善了这个情况,有一个内置的隐含参数_enable_rename_user 可以rename user,当然有一定的前提条件,那就是在restrict模式下使用。
所以一个完整的修改流程就是
alter system set "_enable_rename_user"=true scope=spfile; --因为这个参数无法在线生效,所以需要重启生效
shutdown immediate
startup restrict
alter user test rename to test2 identified by test2; --可以使用这个语句来完成这个变更。当然我也是反复练习之后才敢这么说,简单秀一张图,
采用alter user test rename to test2这种方式就会报错。
好了,回到我们的问题,rename user这个操作内部到底在做什么。说有影响,到底内部是怎么实现的。我们来简单看一看。
开一个10046事件来看看。
alter session set events '10046 trace name context forever,level 12';
SQL> alter user test2 rename to test identified by test;
User altered.
...
然后查看后台的日志,就会发现大体是这样的流程。
PARSING IN CURSOR #140227963843008 len=47 dep=0 uid=0 oct=43 lid=0 tim=1454418745952811 hv=1940754017 ad='189db2a30' sqlid='9v7z4y9t
uv3m1'
alter user test2 rename t
END OF STMT
当然还有大量的递归sql,大体是这样的步骤,先delete,然后insert,然后两个update
delete from user$ where user#=:1
insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,ext_username,spare2)values (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)
update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12 where name=:2
update user$ set exptime=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2),ltime=DECODE(to_char(:3, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :3),astatus = :4, lcount = :5 where user#=:1
当然在11g中查看其实里面还有pl/sql代码块的校验,涉及的逻辑也不少,更多是关于sdo的。
所以在这一点上,单纯去修改数据字典基表理论上是可行的,但是风险点太多。还是谨慎,不建议这么使用。就跟exchange partition这个功能点,你肯定知道数据库内部会修改数据字典基表,但是你肯定不敢手工自己做。
一个dummy user _NEXT_USER
好了,我们继续深入一个话题,那就是user$,其实通过上面的递归sql,如果足够仔细,就会发现有一个重要的线索那就是user#,至于rename这个操作是用的哪个user#,可以从trace之中看到。
但是我查看了一下user$,发现了一个特殊的用户,_NEXT_USER,这个用户的功能有点类似dual,但是它是不公开的,不希望被使用到,是内部的逻辑调用,在rename之中不会使用到,但是create user的时候会。它会根据_NEXT_USER的user# 的基础上去递增。
简单模拟一下这个情况。还是10046
SQL> SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> create user aaa identified by aaa;
User created.
然后查看trace日志。会发现这么一段递归sql的内容。
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),s
pare1,spare4,ext_username,spare2 from user$ where name=:1
END OF STMT
PARSE #140238582732000:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1457651150,tim=1454424040452737
BINDS #140238582732000:
Bind#0
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=7f8bd6f61430 bln=32 avl=03 flg=05
value="AAA"
...
update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'),
'0000-00-00', to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12 where name=:2
END OF STMT
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8bd6cbdea0 bln=24 avl=01 flg=05
value=0
Bind#11
oacdty=01 mxl=128(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=128 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#12
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=189d76436 bln=32 avl=10 flg=09
value="_NEXT_USER"
。。。
这个dummy的用户在这个时候会被调用到。
后续跟进
好了,刚成文,有群友反馈11.2.0.4中碰到了rename失败的情况,我来找个环境试试先。
Oracle和MySQL中的用户
在mysql之中这是一件很轻松的事情。其实mysql之中的用户和Oracle的用户的概念和含义也有很大的差别。所以在此不偷换概念。从我的简单认识来看,用户在Oracle和MySQL里面的概念是截然不同的,可以用下面的示意图来说明。
![](http://img.blog.itpub.net/blog/attachment/201602/2/23718752_1454422230040v.png?x-oss-process=style/bb)
11g以前的rename user方式
今天在微信社群里,有一个群友提了一个问题,说数据库里的用户能不能修改,对于这个问题,有些群友的反馈是可以,直接修改基表即可。
数据库的表名可以修改,这个是很容易支持的,也很容易理解,但是修改用户这个情况下就很纠结,纠结的也还是这些用户下所属的对象的归属,有的朋友可能会说,用户名就跟一个code一样,又不修改id,修改的是name,对于数据库的影响范围应该是最小的吧。这一点还待商榷。稳妥起见,都是采用数据迁移的方式来完成简介的rename user.
11g开始的新特性rename user
在11g开始,有一个新特性改善了这个情况,有一个内置的隐含参数_enable_rename_user 可以rename user,当然有一定的前提条件,那就是在restrict模式下使用。
所以一个完整的修改流程就是
alter system set "_enable_rename_user"=true scope=spfile; --因为这个参数无法在线生效,所以需要重启生效
shutdown immediate
startup restrict
alter user test rename to test2 identified by test2; --可以使用这个语句来完成这个变更。当然我也是反复练习之后才敢这么说,简单秀一张图,
采用alter user test rename to test2这种方式就会报错。
![](http://img.blog.itpub.net/blog/attachment/201602/2/23718752_1454423433tQ56.png?x-oss-process=style/bb)
好了,回到我们的问题,rename user这个操作内部到底在做什么。说有影响,到底内部是怎么实现的。我们来简单看一看。
开一个10046事件来看看。
alter session set events '10046 trace name context forever,level 12';
SQL> alter user test2 rename to test identified by test;
User altered.
...
然后查看后台的日志,就会发现大体是这样的流程。
PARSING IN CURSOR #140227963843008 len=47 dep=0 uid=0 oct=43 lid=0 tim=1454418745952811 hv=1940754017 ad='189db2a30' sqlid='9v7z4y9t
uv3m1'
alter user test2 rename t
END OF STMT
当然还有大量的递归sql,大体是这样的步骤,先delete,然后insert,然后两个update
delete from user$ where user#=:1
insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,spare1,spare4,ext_username,spare2)values (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)
update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12 where name=:2
update user$ set exptime=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2),ltime=DECODE(to_char(:3, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :3),astatus = :4, lcount = :5 where user#=:1
当然在11g中查看其实里面还有pl/sql代码块的校验,涉及的逻辑也不少,更多是关于sdo的。
所以在这一点上,单纯去修改数据字典基表理论上是可行的,但是风险点太多。还是谨慎,不建议这么使用。就跟exchange partition这个功能点,你肯定知道数据库内部会修改数据字典基表,但是你肯定不敢手工自己做。
一个dummy user _NEXT_USER
好了,我们继续深入一个话题,那就是user$,其实通过上面的递归sql,如果足够仔细,就会发现有一个重要的线索那就是user#,至于rename这个操作是用的哪个user#,可以从trace之中看到。
但是我查看了一下user$,发现了一个特殊的用户,_NEXT_USER,这个用户的功能有点类似dual,但是它是不公开的,不希望被使用到,是内部的逻辑调用,在rename之中不会使用到,但是create user的时候会。它会根据_NEXT_USER的user# 的基础上去递增。
简单模拟一下这个情况。还是10046
SQL> SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> create user aaa identified by aaa;
User created.
然后查看trace日志。会发现这么一段递归sql的内容。
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),s
pare1,spare4,ext_username,spare2 from user$ where name=:1
END OF STMT
PARSE #140238582732000:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1457651150,tim=1454424040452737
BINDS #140238582732000:
Bind#0
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=7f8bd6f61430 bln=32 avl=03 flg=05
value="AAA"
...
update user$ set user#=:1,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'),
'0000-00-00', to_date(NULL), :9),defschclass=:10, spare1=:11, spare4=:12 where name=:2
END OF STMT
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8bd6cbdea0 bln=24 avl=01 flg=05
value=0
Bind#11
oacdty=01 mxl=128(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=128 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#12
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=871 siz=32 off=0
kxsbbbfp=189d76436 bln=32 avl=10 flg=09
value="_NEXT_USER"
。。。
这个dummy的用户在这个时候会被调用到。
后续跟进
好了,刚成文,有群友反馈11.2.0.4中碰到了rename失败的情况,我来找个环境试试先。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1985813/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1985813/