All Forums Database
30 Apr 2010
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

monisiqbal 119 posts Joined 07/09
30 Apr 2010

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

gotuchintu 32 posts Joined 12/05
30 Apr 2010

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
;

Somnath Roy

22 Oct 2010

Thanks a lot!!

altriadba 8 posts Joined 06/10
14 Jun 2011

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.

mnylin 118 posts Joined 12/09
14 Jun 2011

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.

altriadba 8 posts Joined 06/10
15 Jun 2011

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.

mnylin 118 posts Joined 12/09
15 Jun 2011

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.

altriadba 8 posts Joined 06/10
15 Jun 2011

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.

mnylin 118 posts Joined 12/09
15 Jun 2011

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.

altriadba 8 posts Joined 06/10
30 Mar 2012

Here is a query that seems to work for the info I'm trying to extract

select
  a.Grantee
, a.RoleName
, coalesce (  sr.subrolename ,  cast (  ''  as  varchar(32)))   as  SubRoleName

, case
      when  sr.subrolename IS NULL     then 'Role'
      when  sr.subrolename IS NOT NULL then 'Subrole'
      else  'Oops'  END  (varchar(10))   as GranteeKind

, case
      when  sr.subrolename IS NULL     then coalesce( c.DatabaseName, 'no db in role' )
      when  sr.subrolename IS NOT NULL then coalesce( b.DatabaseName, 'no db in subrole' )
      else  'Oops'  END  (varchar( 32 )) as DatabaseName

, case
      when  sr.subrolename IS NULL     then coalesce( c.TableName, cast ('' as varchar(32 ) ) )
      when  sr.subrolename IS NOT NULL then coalesce( b.TableName, cast ('' as varchar(32 ) ) )
      else  'Oops'  END  (varchar( 32 )) as TableName
, case
      when  sr.subrolename IS NULL     then coalesce( c.AccessRight, cast ('' as char(3) ) )
      when  sr.subrolename IS NOT NULL then coalesce( b.AccessRight, cast ('' as char(3) ) )
      else  'Oops'   END  (varchar(5))   as AccessRight

from dbc.RoleMembers a

LEFT OUTER JOIN
(select rolename as subrolename , grantee  as rolename  from dbc.rolemembers) sr
on a.RoleName = sr.RoleName

LEFT OUTER JOIN dbc.AllRoleRights b
on b.RoleName = sr.subRoleName

LEFT OUTER JOIN dbc.allrolerights c
on a.rolename = c.rolename

where
           a.grantee      like any  ( '%%'   )
   and  (  b.databasename like any  ( '%' )  or  c.databasename like any ( '%' ) )
   and  (  b.accessright  like any  ( '%' )  or  C.accessright  like any ( '%' ) )

UNION

SELECT
  UserName as Grantee
, cast ('' as varchar(32))   as RoleName
, cast ('' as varchar(32))   as subrolename
, 'DIRECT'   (varchar(10))   as GranteeKind
, DatabaseName as DatabaseName
, Tablename  as  TableName
, AccessRight as AccessRight
FROM DBC.AllRights
where
         grantee      like any  ( '%%'     )
   and   databasename like any  ( '%' )
   and   accessright  like any  ( '%' )

GROUP BY   1,2,3,4,5,6,7

 

sandyinside 1 post Joined 07/12
29 Aug 2012

Try this: Brief and nice.

 

 

SELECT

    A.ROLENAME,

    A.GRANTEE,

    A.GRANTOR,

    A.DefaultRole,

    A.WithAdmin,

    B.DATABASENAME,

    B.TABLENAME,

    B.COLUMNNAME,

    B.GRANTORNAME

    --,B.AccessRight

FROM

    DBC.ROLEMEMBERS A

    JOIN

    DBC.ALLROLERIGHTS B

        ON A.ROLENAME = B.ROLENAME

--WHERE Grantee='sandeep'

 GROUP BY 1,2,3,4,5,6,7,8,9--,10

 ORDER BY 2,1,6;

 

Krupakaran 7 posts Joined 07/12
21 Sep 2012

How to list access rights for a DB that what are the access rights its having on other DBs.

Qaisar Kiani 337 posts Joined 11/05
22 Sep 2012

You can use the view DBC.ALLRIGHTS and DBC.ALLROLERIGHTS to get the rights granted directly to a database and the ones that are granted through a role...

LUCAS 56 posts Joined 06/09
12 Feb 2013

Hi,
I just wonder how to make sure of privileges granted to a user:
As a common rule on our site Users are granted on "all" (the whole Database objects) via Roles, including DELETE privilege.
I want to revoke DELETE privilege on some critical tables with a "REVOKE DELETE on databasename.HUGETABLE to USERNAME"
Revoke command is accepted, but DELETE right is always active and  "All" privilege remains the only line displayed.
The matter is how to REVOKE a privilege in this case and how to state that DELETE is actually revoked or not ?
Thanks for help,
Pierre
PS: i tried to create a new subject, infortunately i couldn't so i come in addition on this subject

 

You must sign in to leave a comment.