Database Administrators

Q&A for database professionals who wish to improve their database skills

Latest Questions

0 votes

1 answers

143 views

Make all users within group role inherit permissions

I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead. What would be the way to do it without writing 1,000 lines of PostgreSQL code?

                                  I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead.

What would be the way to do it without writing 1,000 lines of PostgreSQL code?

TimesAndPlaces (101 rep)

Oct 25, 2022, 10:24 AM • Last activity: Jul 20, 2025, 01:06 AM

2 votes

1 answers

176 views

Moved database to new server, not sure about NT AUTHORITY\* users

sql-server sql-server-2012 users

I just moved database to new instance of SQL Server (SQL Server 2012 Web from Amazon EC2) and am now cleaning up database users. I am not sure if/why these 2 users are needed (they exist in original database): - NT AUTHORITY\IUSR - NT AUTHORITY\NETWORK SERVICE Note that there are also 2 database sch...

                                  I just moved database to new instance of SQL Server (SQL Server 2012 Web from Amazon EC2) and am now cleaning up database users.

I am not sure if/why these 2 users are needed (they exist in original database):

 - NT AUTHORITY\IUSR
 - NT AUTHORITY\NETWORK SERVICE

Note that there are also 2 database schemas with exactly the same names.

I am more clear on NT AUTHORITY\IUSR - this seems to be a user for IIS. IIS is not even installed on the new instance, so this one probably can be deleted.

I am not sure about NT AUTHORITY\NETWORK SERVICE (or how it got created to begin with - new databases created on new server don't have it, or NT AUTHORITY\IUSR)

Neither of these 2 users exist at server level on a new instance or on an old instance.

Joe Schmoe (401 rep)

Jan 21, 2014, 02:28 AM • Last activity: Jul 2, 2025, 08:03 PM

1 votes

1 answers

204 views

How do you create an atlas user in a non-admin db?

mongodb users mongodb-atlas

There doesn't seem to be an option in mongo atlas UI to create a user in any database other than admin.

                                  There doesn't seem to be an option in mongo atlas UI to create a user in any database other than admin.
                                

Sudh33ra (111 rep)

Oct 5, 2022, 06:27 AM • Last activity: Jun 16, 2025, 04:01 PM

1 votes

1 answers

1065 views

What's wrong with my user mapping in pg_ident?

postgresql users

I have successfully setup certificate based authentication in postgresql-11. Now I want to do a user mapping: pg_ident.conf ``` # MAPNAME SYSTEM-USERNAME PG-USERNAME mymap /^(.*)\.testnet\.com$ \1 ``` pg_hba.conf ``` hostssl all all all cert map=mymap ``` Then I signed a certificate with CN `user1.t...

I have successfully setup certificate based authentication in postgresql-11. Now I want to do a user mapping: pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap /^(.*)\.testnet\.com$    \1

pg_hba.conf

hostssl all all all cert map=mymap

Then I signed a certificate with CN user1.testnet.com, and it won't match the existing user:

2022-04-30 15:17:27.036 CST  LOG:  no match in usermap "mymap" for user "user1.testnet.com" authenticated as "user1.testnet.com"
2022-04-30 15:17:27.036 CST  FATAL:  certificate authentication failed for user "user1.testnet.com"
2022-04-30 15:17:27.036 CST  DETAIL:  Connection matched pg_hba.conf line 79: "hostssl all all all cert map=mymap"

What was wrong?

daisy (1338 rep)

Apr 30, 2022, 07:26 AM • Last activity: Jun 11, 2025, 11:22 AM

1 votes

1 answers

59 views

Created a 'datareader' user linked to Windows AD to embed in SSRS reports; and user has authentication issues

sql-server security users active-directory

I am attempting to add a Windows AD User account in order to embed it in SSRS reports for org wide reports. Using SSMS (with \ \Administrator access): - Server Level: - Created the account on SQL Server > Security - Logins: General (Windows Authentication) - Default Database: \ - Server Roles: Publi...

                                  I am attempting to add a Windows AD User account in order to embed it in SSRS reports for org wide reports.

Using SSMS (with \\Administrator access):

 - Server Level:
   - Created the account on SQL Server > Security
   - Logins: General (Windows Authentication)
   - Default Database: \
   - Server Roles: Public
   - User Mapping: \ (selected)
   - Status:
     - Connect: Grant
     - Login: Enabled

 - Database Level:
   - Membership: db_datareader
                                

S M (11 rep)

Jun 3, 2025, 01:44 PM • Last activity: Jun 4, 2025, 09:57 AM

0 votes

1 answers

260 views

How to allow creation and deletion of a database or collections with user and password

mongodb users

I created a user with the role of **userAdminAnyDatabase** db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdminAnyDatabase", db: "admin"}]}) and also user with the role of **userAdmin** for a single db in the mongo shell. db.createUser({user: "Username", pwd: "password", roles...

                                  I created a user with the role of **userAdminAnyDatabase** 

    db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdminAnyDatabase", db: "admin"}]})

and also user with the role of **userAdmin** for a single db in the mongo shell.

    db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdmin", db: "myDB"}]})

But when I create or delete collections in **myDB** it's not asking me for the user name or password. What is the point of creating a user then?

I want the creation and deletion of databases and collections only when I give in the admin credentials.

DragonBorn (101 rep)

Nov 6, 2017, 07:56 AM • Last activity: May 28, 2025, 12:01 PM

0 votes

1 answers

240 views

MySQL logging max_questions resource

mysql-5.6 users limits mariadb-10.3

How would I know in MySQL if the user has exceeded his max_queries per hour? I know that I can see this error on the client side, but I just want to know that before client is reporting to me, somewhere on the server side, would be nice to have it in error_log at least.

                                  How would I know in MySQL if the user has exceeded his max_queries per hour?
I know that I can see this error on the client side, but I just want to know that before client is reporting to me, somewhere on the server side, would be nice to have it in error_log at least. 
                                

Ilie Soltanici (101 rep)

Mar 13, 2019, 04:27 PM • Last activity: May 23, 2025, 07:10 PM

1 votes

1 answers

1070 views

Changing default permanent USERS tablespace to bigfile

oracle oracle-11g-r2 users tablespaces

I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile. How can I either install brand new database with users being bigfile or changing the existing USERS tablespace to bigfile?

                                  I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile.   How can I either install brand new database with users being bigfile or changing the existing USERS tablespace to bigfile?
                                

Alkey29 (11 rep)

May 14, 2020, 07:44 PM • Last activity: May 23, 2025, 10:00 AM

0 votes

1 answers

249 views

How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

postgresql users access-control postgresql-11

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup things in a way that allows all of the users to work with all of the objects in the database. The problem...

                                  
I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees".

The goal: To setup things in a way that allows all of the users to work with all of the objects in the database.

The problem: I can't expect the users to set the owner to "employees" when they create a new object, because they use various limited interfaces to work with the database. When they create a schema or a table, it gets created with the user as its owner, which means that the other users don't have rights on that schema/table.

I'm using PostgreSQL 11.2.

user183865

Jun 23, 2019, 09:45 PM • Last activity: May 21, 2025, 07:03 PM

1 votes

1 answers

1850 views

How to view the user limits like max_queries or max_updates in MySQL?

mysql users

How to show that I've imposed limitations for queries per hour ? Can't find anything online or notes. Would appreciate anybody's help on this. I use MySQL 8.0.16.

                                  How to show that I've imposed limitations for queries per hour ?

Can't find anything online or notes. Would appreciate anybody's help on this.

I use MySQL 8.0.16.

Anderson (21 rep)

Nov 15, 2019, 01:43 PM • Last activity: May 20, 2025, 05:17 AM

0 votes

1 answers

306 views

Track MySQL Procedure Changes in MySQL

mysql security audit users change-tracking

We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did this. We were able to track thread-id from mysqlbinlog but were not able to determine whose userid was...

                                  We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did this. We were able to track thread-id from mysqlbinlog but were not able to determine whose userid was using that thread-id.   Is there any way by which we can do following

**•	Track user-id from mysqlbinlog thread-id  
•	Keep track of every create procedure/drop procedure** 

We are using MySQL 5.7.20 community edition on windows 10 & CentOS7 and we are using row level bin-logging 

Any help is greatly appreciated.

Nawaz Sohail (1480 rep)

Mar 6, 2018, 05:32 PM • Last activity: May 5, 2025, 03:04 AM

0 votes

1 answers

5986 views

Postgresql: Permission denied to schema to user created based on master with rights

permissions postgresql-9.5 users

I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the master user, ie, can do everything. select mysch.dblink('dbname=mydb user=themasteruser password=abc12...

                                  I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the master user, ie, can do everything.

    select mysch.dblink('dbname=mydb user=themasteruser password=abc123 connect_timeout=200000 host=localhost', 'CREATE USER newuname WITH PASSWORD ''pass1'' CREATEDB CREATEROLE;GRANT rds_superuser TO newuname;');

    DROP SERVER IF EXISTS fs_link_b CASCADE;
	CREATE SERVER fs_link_b FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'mydb ', connect_timeout '200000');
	CREATE USER MAPPING FOR newuname SERVER fs_link_b OPTIONS ("user" 'newuname',password 'pass1');
	ALTER SERVER fs_link_b OWNER TO newuname;

However, when I make a connection with the above created user and I try to run the query below:

    select * from mysch.dblink('link_b', 'select usename from PG_USER limit 1;') as t(uu text);

I get this error:

> SQL Error : ERROR: permission denied for schema mysch

If I created the user based on a master user, then should it not have all rights as the master user as created above? Or Do I need to do a bunch of individual GRANTS still?

user1013388 (111 rep)

Apr 1, 2020, 03:55 AM • Last activity: Apr 16, 2025, 01:06 AM

1 votes

1 answers

1576 views

How to create a mysql user that can access from any host but localhost

mysql users

I was asked to create a mysql user that can connect to the server from any host but localhost. I tend to believe that "%" also means localhost but according to my teacher, localhost is not part of "%" and that `CREATE USER user@"%";` should do the job but my tests proof otherwise. [![enter image des...

                                  I was asked to create a mysql user that can connect to the server from any host but localhost.

I tend to believe that "%" also means localhost but according to my teacher, localhost is not part of "%" and that CREATE USER user@"%";  should do the job but my tests proof otherwise.

The only solution that I've found to solve the problem is with CREATE USER user@localhost ACCOUNT LOCK;

Can anybody educate me about this?

ptor (11 rep)

Nov 24, 2021, 03:38 AM • Last activity: Mar 6, 2025, 10:05 PM

0 votes

1 answers

30 views

How to list known users on Apache Derby server?

users derby

In the documentation to Apache Derby I've seen [documentation][1] on functions to administrate user accounts. However, I did not manage to find documentation on how to get a listing of all registered users. After failing to find a respective answer on a Web search likewise I tried all methods sugges...

In the documentation to Apache Derby I've seen documentation on functions to administrate user accounts. However, I did not manage to find documentation on how to get a listing of all registered users. After failing to find a respective answer on a Web search likewise I tried all methods suggested by perplexity.ai, which just failed altogether:

SELECT * FROM SYS.SYSUSERS

> "Table/View 'SYS.SYSUSERS' does not exist."

SHOW ROLES

> "Syntax error: Encountered "SHOW" at line 1, column 1." and

SELECT * FROM SYS.SYSCS_UTIL.SYSCS_GET_ALL_PROPERTIES()
WHERE PROPERTY_NAME LIKE 'derby.user.%'

> "Syntax error: Encountered "." at line 1, column 29.". There is **no** derby.properties file either. How can I get a listing of all known users?

jf1 (101 rep)

Feb 13, 2025, 10:03 AM • Last activity: Feb 13, 2025, 02:01 PM

16 votes

4 answers

61267 views

MySQL User with Host set to a Range of IP Addresses

mysql users

I have a hosted MySQL DB that I need to give access to from my customers site. They have a fast broadband connection which can present them with a range of IP addresses from a fixed pool. So I need to lock down the access to their MySQL user so that the Host setting is a range of addresses within a...

                                  I have a hosted MySQL DB that I need to give access to from my customers site. They have a fast broadband connection which can present them with a range of IP addresses from a fixed pool.

So I need to lock down the access to their MySQL user so that the Host setting is a range of addresses within a class. Not the whole class.

I realise I can use a wildcard such as 81.91.71.% but that is too generous. Their range is more like

81.10.20.1 --> 81.10.20.15

Is there a way to do this other than create 15 users, one for each individual IP address?
thanks
Mark

Mark Ogier (313 rep)

Aug 30, 2013, 07:20 AM • Last activity: Feb 12, 2025, 04:38 AM

0 votes

1 answers

347 views

Query for: view users and permissions (roles) from root security folder (MS SQL)

sql-server permissions users

On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example `sp_helpuser`. Currently, however, I need to use a query to extract users and their permissions from the root of Security folder in Management S...

                                  On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example sp_helpuser.
Currently, however, I need to use a query to extract users and their permissions from the root of Security folder in Management Studio.

Can you please tell me how to extract users and permissions (roles) from this folder using the script?
Thank you all in advance for your advice.
                                

H0nzin (13 rep)

Jan 30, 2020, 04:19 PM • Last activity: Jan 22, 2025, 12:04 AM

55 votes

7 answers

167133 views

How to get all roles that a user is a member of (including inherited roles)?

postgresql users role postgresql-9.3 access-control

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; grant authors to editors; --editors can do what authors can do grant editors to maxwell; --maxwell is an...

                                  Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest".

    create role authors;
    
    create role editors;
    
    create user maxwell;

    create user ernest;
    
    grant authors to editors; --editors can do what authors can do
    
    grant editors to maxwell; --maxwell is an editor

    grant authors to ernest; --ernest is an author

I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this:

    create or replace function get_all_roles() returns oid[] ...

It should return the oids for maxwell, authors, and editors (but not ernest).

But I am not sure how to do it when there is inheritance.

Neil McGuigan (8653 rep)

Jan 3, 2014, 10:07 PM • Last activity: Jan 16, 2025, 10:43 AM

3 votes

1 answers

96 views

Permission denied for index idx_my_index on using pg_prewarm

postgresql index permissions functions users

I work on a project that uses PostgreSQL. Some analytic queries take a lot of time, and we would like to prewarm two indexes and one table. Here are the prewarming queries: - `SELECT pg_prewarm('idx_my_first_index');` - `SELECT pg_prewarm('idx_my_second_index');` - `SELECT pg_prewarm('public.my_tabl...

I work on a project that uses PostgreSQL. Some analytic queries take a lot of time, and we would like to prewarm two indexes and one table. Here are the prewarming queries: - SELECT pg_prewarm('idx_my_first_index'); - SELECT pg_prewarm('idx_my_second_index'); - SELECT pg_prewarm('public.my_table'); The prewarm on the table works perfectly, but we encounter a PostgreSQL error for the prewarm on indexes. Here is the error using psql logged in the web process user:

ERROR:  permission denied for index idx_my_index

The PostgreSQL user who creates tables and indexes is different from the one who accesses the database in our web process. The user who runs migrations owns everything in the database, and the other user can write and read every table. We can prewarm the table, but when it comes to prewarm indexes, we have a permission error. ## The question is : What privilege would we give to our web process user to prewarm indexes created by another user? ## What I did I looked in the Postgresql documentation, and there is nothing about indexes and privileges, and there is nothing in the pg_prewarm page documentation. I tried to create the index from the web process user, but we cannot create an index on a table we don't own. We don't want to give all the privileges to our web process user.

Emmanuel Guefif (31 rep)

Jan 7, 2025, 07:56 PM • Last activity: Jan 8, 2025, 10:47 AM

1 votes

1 answers

46 views

Grant execute only in localhost

mysql permissions users

I have a user that can be accessed from any host (we are currently in development). But I want to limit the execution of stored procedures only for localhost. The problem is that the `localhost` user is not recognized. ```mysql DROP USER IF EXISTS 'admin'@'%'; CREATE USER IF NOT EXISTS 'admin'@'%' I...

I have a user that can be accessed from any host (we are currently in development). But I want to limit the execution of stored procedures only for localhost. The problem is that the localhost user is not recognized.

DROP USER IF EXISTS 'admin'@'%';
CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE TO 'admin'@'%' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'%' WITH GRANT OPTION;
.
.
.

Executing this code throws me the next error. [2024-11-23 20:59:09] You are not allowed to create a user with GRANT Changing the host "solves" the error, but I want to limit the execution to localhost. I don't know if I just have to add a new user like this.

CREATE USER IF NOT EXISTS 'admin'@'127.0.0.1' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE1 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
.
.
.

Or if there's another way without repeating the user definition

edjmir (125 rep)

Nov 24, 2024, 03:07 AM • Last activity: Nov 24, 2024, 03:47 PM

2 votes

2 answers

14902 views

Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'

sql-server sql-server-2016 users logins

I had a database which i dropped using the below command. USE master; GO ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE dbname; Now when i refresh the connection in SSMS i get the error message `Cannot open database "dbname" requested by login.The login failed.Login...

                                  I had a database which i dropped using the below command.

    USE master;
    GO
    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    DROP DATABASE dbname;

Now when i refresh the connection in SSMS i get the error message Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'.Microsoft SQL Server,Error 4060

I remember that the owner for that database was the same login "DOMAIN\Windowsusername" .
So i think now when i connect using the same user login ,it is trying to connect to the dropped database.
Since the database is dropped i cannot change the owner of the db.
How can i get rid of this error.

Any help will be appreciated.

                                

user9516827 (1345 rep)

Apr 5, 2019, 09:36 PM • Last activity: Sep 26, 2024, 09:06 AM

Showing page 1 of 20 total questions