Unwanted databases gets assigned whenever a new user is created.

Database
Enthusiast

Unwanted databases gets assigned whenever a new user is created.

Hi,

I am facing an issue while creating an user. There are few databases which are assigned(user holds certain accesses on the databases) to the user without even granting the access. I don't want the users to hold the accesses to those databases without DBAs granting it. Please help me to resolve the issue. 

Thanks

Tags (1)
4 REPLIES
Junior Contributor

Re: Unwanted databases gets assigned whenever a new user is created.

Hi Guru,

a new user gets access based on the public role:

select * from dbc.allrightsv
where username = 'PUBLIC'

or due to inherited rights of an owner of the new user:

select * from dbc.allrightsv
where AllnessFlag = 'Y'
Enthusiast

Re: Unwanted databases gets assigned whenever a new user is created.

Hi Dieter,

Thanks for clarifying. It is because of allnessflag issue. Is there any way to change the flag value?.

Thanks

Junior Contributor

Re: Unwanted databases gets assigned whenever a new user is created.

Hi Guru,

this flag has been switched on using GRANT ... ON ALL db TO userx, you can switch it off using either REVOKE ... ON db FROM userx (only switch of inheritance, but don't remove the inherited rights) or REVOKE ... ON db FROM ALL userx (switch of inheritance and remove inherited rights)

Enthusiast

Re: Unwanted databases gets assigned whenever a new user is created.

Thanks a lot. It's resolved.