Questions tagged [users]
For questions about database user accounts.
361 questions
110
votes
6
answers
295k
views
Difference between database vs user vs schema
What is the difference (if any) between a database, a user, and a schema?
- 1,565
53
votes
7
answers
158k
views
How to get all roles that a user is a member of (including inherited roles)?
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;
...
- 8,583
38
votes
3
answers
66k
views
What does a RED X on a database user mean?
I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?
user18718
36
votes
1
answer
25k
views
PostgreSQL roles versus users, grant permissions
I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
- 737
31
votes
7
answers
195k
views
Access denied for user 'root'@'%'
I used to access the root user in MySQL just fine. But recently, I am no longer able to.
I am able to login fine :
mysql -u root -p
Here is the mysql status after login :
mysql> status
-------...
jules testard
19
votes
5
answers
30k
views
How to manage millions of users?
I am about to launch something really big. I need to prepare my server and database.
I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one ...
Chris
16
votes
4
answers
60k
views
MySQL User with Host set to a Range of IP Addresses
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 ...
- 313
16
votes
2
answers
39k
views
View active MySQL connections per user
I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so ...
- 161
14
votes
3
answers
39k
views
Find orphaned users
In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) ...
- 3,068
13
votes
2
answers
32k
views
Is there a simple way to add a read-only user?
I have a hosted (shared) SQL Server database and I'd like to add a read-only login. (This login will be used to build Power BI data visualization.)
Looking into this, it's far more complex than I ...
- 347
13
votes
2
answers
6k
views
Feasible to have thousands of users in Postgres?
We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
- 1,195
12
votes
1
answer
45k
views
How to change the login for a database's dbo user?
I just finished a successful install of SQL Server 2014 Express, onto a domain-joined VM instance of Win8.1.
During setup which I ran under DOMAIN\Admin I accepted the default configuration of adding ...
- 559
12
votes
2
answers
80k
views
Adding a user to MySQL with 'name'@'%' fails with ERROR 1396
I just tried to add a new user to MySQL using
CREATE USER 'name'@'%' IDENTIFIED BY '...'
However, it fails with the following error:
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
...
- 537
12
votes
2
answers
14k
views
Which characters are allowed in MySQL/MariaDB user account names?
It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section ...
- 223
11
votes
5
answers
5k
views
Why storing users passwords at all?
I occasionally see questions asking how to safely store user passwords for a web application (using an RDBMS, I'm not talking of Facebook or Twitter). The usual answer is "salt the password, then hash ...
- 244
11
votes
2
answers
139k
views
How to see profile assigned to a user
Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it.
Now how can i see for TEST_USER which profile has been assigned?
I want some query that will check what profile a ...
- 223
11
votes
2
answers
45k
views
cannot execute as the database principal because the principal does not exist - DB Restoration
I have received one database backup from one of my client and now I'm trying to restore it into my system.
I tried to search this issue on google and tried most of the suggested solutions but none of ...
- 242
11
votes
2
answers
21k
views
Username length restriction in different MySQL environments
I am setting up a project to my local environment and at its initial script are some SQL commands for creating the database:
create database 'db-name' default character set utf8;
and to grant ...
- 213
9
votes
2
answers
61k
views
How to Create users in sql server for accessing only one database using Management Studio [closed]
How to Create users in sql server 2008 R2 for accessing only one database using Management Studio.
regards,
salman
- 129
9
votes
2
answers
20k
views
Why and how and where to use user without login
I've been trying to find why, how and where to use Users Without Login and I didn't find my exact question or anything that gets closer.
Where I did my research (I also read other posts, saw Youtube ...
9
votes
4
answers
12k
views
Cannot revoke permissions or drop user in PgSQL AWS RDS
I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
- 449
9
votes
3
answers
10k
views
How to know the user doing a delete action on an audit table, when using a shared login?
Background information:
I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app.
Audit records are created via triggers.
DML in my app's ...
- 193
9
votes
1
answer
56k
views
Error Code: 1133. Can't find any matching row in the user table
Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this:
mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password';
Query OK, 0 ...
- 193
8
votes
2
answers
15k
views
How to get user-specific settings in PostgreSQL?
I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters (...
- 31.6k
8
votes
2
answers
17k
views
How do I translate a Windows SID to an SQL Server server_user_sid?
There is this nice SQL Server function SUSER_SNAME which translates a server_user_sid to a user name. This is useful for translating well-known Windows SIDs to (potentially localized) user names.
...
- 3,180
8
votes
2
answers
22k
views
How to set statement timeout per user?
I have multiple users in Postgres. I would like to set up different statement timeouts for different users.
Eg: Guest 5 minutes and Admin 10 minutes.
Is it possible in Postgres 11.11?
8
votes
1
answer
24k
views
how to check list of users present in all postgres database
I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like,
List of roles
...
- 153
8
votes
5
answers
29k
views
Export all MySQL users
I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this?
Should I just dump the mysql.user table?
- 449
8
votes
2
answers
16k
views
Create a user with privileges to create users
For our software we need a MySQL user who is able to create other MySQL users. This is possible with the MySQL root user but we do not want to use the root user in our software application. Is there a ...
- 183
8
votes
1
answer
6k
views
postgres create limited user that can select tables and columns of those tables
I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be ...
- 237
8
votes
2
answers
12k
views
Rebinding a SQL Server user to its login after a restore with this user
After performing a restore of a SQL Server (v12.0.2000) database, I figured that a user needs to be remapped to its login (this issue seems to be known as orphan users). And one of the simplest way to ...
- 291
8
votes
2
answers
63k
views
How do I automatically grant all privileges to a schema created by a user in mysql?
Someone new joined the company and I created an account for her in mysql using the following:
CREATE USER 'username' IDENTIFIED BY 'password'
She needs to create her own schema and so used:
GRANT ...
8
votes
2
answers
74k
views
MySQL user permission on stored procedure
I've created a simple stored procedure:
mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected, 1 ...
- 11.9k
7
votes
1
answer
10k
views
Fastest way to get current user's OID in Postgres?
I am thinking of storing the user's OID in the "owner" column of a table, so I don't have to do anything if they change their username.
I understand one can use pg_has_role() with OIDs, so that's ...
- 8,583
7
votes
1
answer
11k
views
Create postgres schema in db with multi user in AUTHORIZATION
Is it possible to create a schema that is owned or accessible by more than one user in the same database?
I was reading postgres create schema documentation and postgres alter schema documentation ...
- 175
7
votes
1
answer
14k
views
Does PostgreSQL allow multiple grantees in GRANT commands?
Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone.
Isn't possible to do something like GRANT SELECT ...
- 1,093
7
votes
1
answer
4k
views
Is there any way to prevent a PostgreSQL user to change his/her own password?
I'm administering a PotsgreSQL server and recently I realized that my "nonsuperuser" users have the possibility to change the secure passwords that I assign them for insecure ones.
I searched in the ...
- 73
7
votes
1
answer
6k
views
What is the purpose of a user's default database?
As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct?
I've never seen a connection string ...
7
votes
1
answer
12k
views
How do you list all logins for Sql Azure?
What queries can I use to list all logins for a server and database in SQL Azure?
I tried running select * from sys.sysusers but I'm guessing that's just showing the users
- 185
6
votes
3
answers
35k
views
Get username and/or IP address responsible for a query
I use the following query to find performance improvements in queries:
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN ...
- 585
6
votes
2
answers
20k
views
ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'
I am trying to grant privileges for a user to a new database
mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword';
I get the following error
ERROR 1054 (...
- 163
6
votes
3
answers
10k
views
Postgres Large Objects & Multiple Users
Problem
Dynamically created users are not able to query a table that contains Large Objects as it did not originally create it.
Background
I have an Java Atom Hopper application deployed to AWS ...
- 163
6
votes
1
answer
2k
views
Disable multiple logins in one step
Is there a way to disable multiple sql logins based on a set of users in a table? For example:
ALTER LOGIN (Select userid from tbl_user) DISABLE;
- 61
6
votes
1
answer
11k
views
Drop privilege to tables only, Not to database
I created a user with drop privilege to a database.
How can I create a user who has the privilege to drop tables of a specified database, but not database.
- 1,536
6
votes
1
answer
5k
views
What is the significance of the principal_id in sys.schemas?
What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id?
1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas;
2> GO
...
- 64.7k
6
votes
2
answers
33k
views
GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables
I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to ...
- 63
6
votes
1
answer
4k
views
Postgres 10. Hide users/roles from other users
I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this:
REVOKE SELECT ON pg_catalog.pg_authid FROM ...
- 163
6
votes
1
answer
2k
views
What is the proper setup for PG user for web application
We have a web app that detects if the DB does not exist and creates it.
Users like it, but my thought is that such user has too much privileges.
I would optimally propose to change the DB creation ...
- 471
5
votes
3
answers
4k
views
Who is the person using a SQL login?
Is it possible to find out who used a SQL-Server login?
Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain ...
- 4,807
5
votes
2
answers
10k
views
Can I GRANT SELECT on all schemas in a database?
I need to create role users of which can only read (select) data.
I can do it for a particular schema.
But I need it to work with all existing and future schemas (existing at least). How can I do it?
...
- 165