when new user is created ,they get default access to DBC.tables ?. I do not want them to have DBC.tables access. Please suggest me
By default, some DBC views have SELECT access granted to PUBLIC. Some of these rights can be revoked, but the client drivers need some way to retrieve metadata in order to process queries and result sets properly.
There are views with X suffix (collectively called "X Views") that filter metadata based on the user's other access rights; SELECT access must not be revoked from those. And if you revoke SELECT access on any of the corresponding views without the X, then users will need to configure their client drivers to "Use X Views" instead. There is a significant performance impact to using X Views, which is why this is not the default behavior. Collecting statistics on DBC tables may reduce this impact (but will not eliminate it entirely).
i currently have 4200 users on the production system. i need to find which users only have select access to edw objects and classify them as analytic users, what would be the best way to achieve this task. some of my users have their own databases on which they have select, insert, create, delete. the only way i can truly classify an analytic user is a user which only has select, help, show on a view in edw. any help is greatly appreciated.
sel c.name as User_Name, b.databasename as Database_Name, a.accessright from dbc.accessrights a
join dbc.dbase b
on a.databaseid = b.databaseid
join dbc.UserDB c
on a.userid = c.id
where c.name = 'User_Name'
User Name list can be appended using in (,,,,,)
Try this and check if it will give you desired output.