Access rights of dropped Database

Database
Enthusiast

Access rights of dropped Database

Hello

Let's say that i have  a database D1_DQ for which there are a number of roles with rights assigned on it like the ones below: 

GRANT DROP VIEW ON "D1_DQ" TO "RL_DQ_BAR";

GRANT CREATE TABLE ON "D1_DQ" TO "RL_DQ_BAR";

GRANT DROP VIEW ON "D1_DQ" TO "RL_DQ_BAR";

.

.

.

GRANT CREATE TRIGGER ON "D1_DQ" TO "RL_DQ_BAR";

If i delete all the objects of the database and then drop the database are the rights above also deleted from the relevant DBC tables or are they remain orphaned there forever??

Also if i have a data and dictionary backup of the dropped database descibed above and i use it to COPY(using ARCMAIN) the data to a new database will the rights also be copied??

Thanks.

3 REPLIES
Enthusiast

Re: Access rights of dropped Database

If i delete all the objects of the database and then drop the database are the rights above also deleted from the relevant DBC tables or are they remain orphaned there forever??

They are usually dropped but some may get orphaned.

You can identify with this query.

The GSC will help if cleanup is needed.

SELECT
CAST(ta.count1 AS BIGINT) (TITLE 'NO OBJ count'),
CAST(tb.count2 AS BIGINT) (TITLE 'NO DB count'),
CAST(tc.count3 AS BIGINT) (TITLE 'NO USR/ROLE count'),
CAST(td.count4 AS BIGINT) (TITLE 'Total count'),
(
ta.count1/td.count4)*100 (TITLE 'NO OBJ %', FORMAT '----9.99'),
(
tb.count2/td.count4)*100 (TITLE 'NO DB %', FORMAT '----9.99'),
(
tc.count3/td.count4)*100 (TITLE 'NO USR/ROLE %', FORMAT '----9.99')
FROM
(
SELECT CAST(COUNT(*) AS FLOAT) as count1
FROM DBC.AccessRights a1
WHERE a1.TVMID <> '000001000000'xb
AND NOT EXISTS (SELECT 1 FROM DBC.TVM t1
WHERE t1.tvmid = a1.tvmid)) ta,
(
SELECT CAST(COUNT(*) AS FLOAT) as count2
FROM DBC.AccessRights a2
WHERE NOT EXISTS (SELECT 1 FROM DBC.DBase d2
WHERE d2.databaseid = a2.databaseid)) tb,
(
SELECT CAST(COUNT(*) AS FLOAT) as count3
FROM DBC.AccessRights a3
WHERE NOT EXISTS (SELECT 1 FROM DBC.DBase d3
WHERE d3.databaseid = a3.userid)
AND NOT EXISTS (SELECT 1 FROM DBC.Roles r3
WHERE r3.roleid = a3.userid)) tc,
(
SELECT CAST(COUNT(*) AS FLOAT) as count4 FROM DBC.AccessRights) td;

.quit;
EOF
# chmod +x check.sh
# ./check.sh 2>&1|tee check.sh.out

NO OBJ count 0
NO DB count 0
NO USR/ROLE count 0
Total count 23363
NO OBJ % 0.00 - ok. If > 10%, please escalate.
NO DB % 0.00 - ok. If > 10%, please escalate.
NO USR/ROLE % 0.00 - ok. If > 10%, please escalate.

Also if i have a data and dictionary backup of the dropped database descibed above and i use it to COPY(using ARCMAIN) the data to a new database will the rights also be copied??

No.

Rglass

Teradata Employee

Re: Access rights of dropped Database

If you use DROP and DELETE DATABASE the rights will always be dropped from the dictionary as well. Orphan rights occur when dictionary restores are done but some of the objects are not restored.

Enthusiast

Re: Access rights of dropped Database

Thanks both of you for clarifying the above.