G'day.
This has had me stumped all day. I have a requirement to copy new users,
change account statuses and adjust grants across a number of databases. There
is one source (the production database) which is the point of truth and all
lower environments are managed from this source.
Up to now, the process simply drops all users and runs an import from the
production database. As this is a bit crude, I am trying to device a more
gentle process where we can have users in lower environments that don't
necessarily exist in the source, e.g. for new projects.
The solution that I am working towards is a pl/sql block that uses a number of
dictionary tables, both over a database link and local. The problem that I
encounter is this:
declare
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
SYS.DBA_ROLE_PRIVS@DB_LINK
ORA-04020: deadlock detected while trying to lock object SYS.USER$@DB_LINK
It is not consistent - sometimes I can run the program for an hour or so
without the deadlock occurring. I have determined that this occurs in the
declaration of the cursors, it even happens when the body is null. Commenting
one or more cursors will make the error also go away.
Even stranger is that although Oracle reports a deadlock, no message is logged
in the alert log on the local database or the remote.
Below is the code:
1 declare
2 -- 0.0 list roles that do not exist in this db
3 cursor cNewRole is
4 select 'create role '||role as stmt
5 from sys.dba_roles@DB_LINK
6 minus
7 select 'create role '||role as stmt
8 from dba_roles;
9 -- 0.1 identify remote ordinary users, and account status
10 cursor cRmtUsr is
11 select u.name as username
12 , u.password||';'||u.spare4 as pwdhash
13 , m.status as actst
14 , case
15 when u.astatus in ( 1, 2 ) then 'PASSWORD EXPIRE'
16 when u.astatus in ( 8 ) then 'ACCOUNT LOCK'
17 when u.astatus in ( 5,6,9,10 ) then 'ACCOUNT LOCK PASSWORD
EXPIRE'
18 else 'OPEN'
19 end as actact
20 , dts.name as deftbs
21 , tts.name as tmptbs
22 from sys.user$@DB_LINK u
23 , sys.user_astatus_map m
24 , sys.ts$@DB_LINK dts
25 , sys.ts$@DB_LINK tts
26 where m.status# = u.astatus
27 and dts.ts# = u.datats#
28 and tts.ts# = u.tempts#
29 and dts.name not in ( 'SYSTEM', 'SYSAUX', 'TOOLS', 'XDB',
'ADHOC' )
30 and u.name not in ( 'DBSNMP', 'XS$NULL' )
31 and u.name not like 'ABC-%'
32 ;
33 -- 0.2 check if a user exists in this DB and get the account status
34 cursor cExist ( bUid varchar2 ) is
35 select account_status
36 from sys.dba_users
37 where username = bUid;
38 -- 0.3 Retrieve a list of privileges that are granted to the user
locally
39 cursor cPrivs ( bUid varchar2 ) is
40 select granted_role as priv
41 from sys.dba_role_privs
42 where grantee = bUid
43 union
44 select privilege
45 from sys.dba_sys_privs
46 where grantee = bUid
47 union
48 select listagg(privilege, ', ') within group ( order by owner,
table_name )
49 || ' on ' ||owner||'.'||table_name
50 from sys.dba_tab_privs
51 where grantee = bUid
52 and owner not in ( 'SYS', 'SYSTEM' )
53 group by owner, table_name
54 ;
55 -- 0.4 Create a table of privileges for the user in the source (remote)
database
56 cursor cPrivsRem ( bUid varchar2 ) is
57 select granted_role as priv
58 from sys.dba_role_privs@DB_LINK
59 where grantee = bUid
60 union
61 select privilege
62 from sys.dba_sys_privs@DB_LINK
63 where grantee = bUid
64 union
65 select listagg(privilege, ', ') within group ( order by owner,
table_name )
66 || ' on ' ||owner||'.'||table_name
67 from sys.dba_tab_privs@DB_LINK
68 where grantee = bUid
69 and owner not in ( 'SYS', 'SYSTEM' )
70 group by owner, table_name
71 ;
72 begin
73 null;
74 end;
75 /
Cheers,
Tony
--
//www.freelists.org/webpage/oracle-l
