How to check access privileges??

Database

How to check access privileges??

Hi all,

Is there any query to check what all access we got for the given databases.

Thanks in advance
-TeradataUser
Tags (1)
14 REPLIES
Enthusiast

Re: How to check access privileges??

Join of the tables 'DBC.Dbase' and 'DBC.AccessRights' should give you what you need.
Enthusiast

Re: How to check access privileges??

You can try the following

SELECT
A.ROLENAME,
A.GRANTEE,
A.GRANTOR,
B.DATABASENAME,
B.TABLENAME,
B.COLUMNNAME,
D.ACCESSRIGHT_DESC ACCESSRIGHT,
B.GRANTORNAME,
C.DEFAULTDATABASE,
C.DEFAULTACCOUNT,
C.ROLENAME DEFAULTROLENAME,
C.PROFILENAME,
C.OWNERNAME
FROM
DBC.ROLEMEMBERS A
JOIN
DBC.ALLROLERIGHTS B
ON
A.ROLENAME=B.ROLENAME
JOIN
DBC.USERS C
ON
C.USERNAME=A.GRANTEE
JOIN
DBA.ACCESSRIGHT_REF D
ON
B.ACCESSRIGHT=D.ACCESSRIGHT
;

Re: How to check access privileges??

Thanks a lot!!
Enthusiast

Re: How to check access privileges??

Here is where I'm having trouble:
How about a query that picks up accessrights directly granted to a user, and accessrights granted through a role (like the query in the post ) and accessrights granted through a sub-role (granteekind=role) for a particular database ?

result set might look like:

accessthru useraccount role subrole database accessright
DIRECT user1 N/A N/A testdb1 CT
ROLE user1 R_role1 N/A testdb2 R
SUBROLE user1 R_role2 R_subr1 testdb3 R
SUBROLE user1 R_role2 R_subr2 testdb4 R

where
r_subr1 has R on testdb3,
r_subr2 has R on testdb4,
r_subr1 and r_subr2 were granted to r_role2,
and r_role2 was granted to user1 ;

Thanks for your assistance.

Enthusiast

Re: How to check access privileges??

Looks like a recursive SQL statement would do the trick here.

REPLACE MACRO RecursiveRights
(
inUserName VARCHAR(30)
)
AS
(
WITH RECURSIVE rights_query(AccessThru, Username, RoleName, SubRoleName, DatabaseName, AccessRight) AS
(
SELECT
AccessThru
,:inUserName
,RoleName
,'N/A'(VARCHAR(30))
,DatabaseName
,AccessRight
FROM
(
SELECT
'USER'(VARCHAR(30)) AS AccessThru
,'N/A'(VARCHAR(30)) AS RoleName
,DatabaseName
,AccessRight
FROM
"DBC".AllRights
WHERE
UserName = :inUserName
UNION ALL
SELECT
'ROLE'(VARCHAR(30)) AS AccessThru
,DBC.RoleMembers.RoleName AS RoleName
,DatabaseName
,AccessRight
FROM
"DBC".RoleMembers
JOIN
"DBC".AllRoleRights
ON "DBC".RoleMembers.RoleName = "DBC".AllRoleRights.RoleName
AND "DBC".RoleMembers.Grantee = :inUserName
) AS DT1
UNION ALL
SELECT
'SUBROLE'(VARCHAR(30))
,rights_query.UserName
,rights_query.RoleName
,DBC.RoleMembers.RoleName
,"DBC".AllRoleRights.DatabaseName
,"DBC".AllRoleRights.AccessRight
FROM
"DBC".RoleMembers
JOIN
"DBC".AllRoleRights
ON "DBC".RoleMembers.RoleName = "DBC".AllRoleRights.RoleName
JOIN
rights_query
ON
(
"DBC".RoleMembers.Grantee = rights_query.RoleName
OR "DBC".RoleMembers.Grantee = rights_query.SubRoleName
)
)
SELECT
AccessThru
,Username
,RoleName
,SubRoleName
,DatabaseName
,AccessRight
FROM
rights_query
GROUP BY
AccessThru
,Username
,RoleName
,SubRoleName
,DatabaseName
,AccessRight
;
);

Creates a macro that takes in a UserName parameter. Hope this helps.
Enthusiast

Re: How to check access privileges??

mnylin - Thanks - This will help a lot.

When I ran the macro, it took off and did not return a result set after several minutes. Do I need to add a "level" limiting parameter ?

Also, I'll need to flip the question around a bit. In addition to seeing all rights by user, I need to see all users who have access to a particular database.

as in,
SELECT username
, accessright
, [ 'N/A' or rolename]
, [direct|role|subrole]
FROM
WHERE Databasename = 'some-db' ;

Again - Thank you - your assistance is greatly appreciated.

Best Regards.

Enthusiast

Re: How to check access privileges??

If you've got a large number of nested roles, you might want to modify it to restrict from going too far down the path. You should be able to modify the existing macro to filter on a database instead of a user. The same basic approach should work there.
Enthusiast

Re: How to check access privileges??

Thanks - my further work with your macro shows if works fine if there are no nested roles. In TD 12 (which we use), I believe roles can nest only one level deep. It shouldn't be possible for circular nesting, correct ? Also, EXPLAIN shows a product join.
I'll keep working with it. Thank you again.
Enthusiast

Re: How to check access privileges??

Not sure about the product join. It's probably one of the ways the optimizer evaluates the recursive query, but I could be wrong. This macro would also take a while if there are a lot of rights assigned to a user and the roles they belong to. It's not ideal, but it gives you a pretty good picture.