56
votes
Accepted
PostgreSQL roles versus users, grant permissions
A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can ...
- 52.2k
33
votes
What does a RED X on a database user mean?
It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the ...
- 176k
27
votes
Accepted
Why storing users passwords at all?
Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single ...
- 9,614
24
votes
How to get all roles that a user is a member of (including inherited roles)?
This is a simplified version of Craig Ringer's answer that a non superuser can use directly:
SELECT oid, rolname FROM pg_roles WHERE
pg_has_role( 'maxwell', oid, 'member');
pg_roles is ...
- 27.4k
20
votes
Accepted
How to see profile assigned to a user
In order to find out the users and the profile assigned you can use the commands below.
DESC DBA_USERS;
This will show you all the fields name for which you want to query
SELECT USERNAME, ...
- 316
20
votes
View active MySQL connections per user
What you need is a breakdown by user and hostname along with a total
SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
SELECT user usr,LEFT(host,LOCATE('...
- 174k
15
votes
Accepted
Username length restriction in different MySQL environments
The maximum length for a user name in MySQL does depend on the version, as stated in the manual:
MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).
- 21.3k
14
votes
Accepted
Feasible to have thousands of users in Postgres?
Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK).
More than 500 simultaneous connections per box will be a problem ...
- 7,813
14
votes
Accepted
How do I translate a Windows SID to an SQL Server server_user_sid?
SIDs in the form of 0x01020000000000052000000021020000 are not "SQL Server" SIDs. That is simply the underlying binary value of the SID. Another form it can take (and still be the same value) is the "...
- 65.8k
11
votes
Accepted
MySQL service stops after trying to grant privileges to a user
I think your problem boils down to a misalignment of grants.
When you run this query
SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';
...
- 174k
11
votes
Accepted
Export all MySQL users
If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user ...
- 174k
11
votes
cannot execute as the database principal because the principal does not exist - DB Restoration
When you restore a database it contains database users. These users are transported with the database, whenever you perform a backup and a restore to a different SQL Server.
On your source and target ...
- 15k
9
votes
Accepted
ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'
Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6
If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL ...
- 174k
9
votes
Accepted
how to check list of users present in all postgres database
Users are global to the Postgres instance (aka "cluster"). So there is no such thing as "their respective database name". \du will always show you the same list of users regardless of the database you ...
- 71.3k
8
votes
How to get user-specific settings in PostgreSQL?
Unfortunately pg_shadow does not show roles with no LOGIN privilege.
I find it more useful to use @dezso hint view in the following query:
SELECT coalesce(role.rolname, 'database wide') as role,
...
- 281
8
votes
Accepted
Create postgres schema in db with multi user in AUTHORIZATION
Owned by more than one user: no
Accessible by more than one user: yes
To give a user (or role) access to the tables in a schema you can use something like this:
grant all on schema foobar to ...
- 71.3k
8
votes
Should I disable the guest user on system databases
No you shouldn't disable the guest account on the system databases. You also shouldn't disable it on the user databases. This will stop anyone from using SQL Server Management Studio to connect to ...
- 26.9k
8
votes
Why storing users passwords at all?
The dividing line is getting a little bit fluffy at that point, but I would consider users and permissions at the database level to be part of the schema not part of the data, and in general ...
- 30.3k
8
votes
Disable multiple logins in one step
No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL:
DECLARE @sql nvarchar(max) = N'',
@cmd nvarchar(550) = N'ALTER LOGIN $$$ ...
- 176k
8
votes
Accepted
How to set statement timeout per user?
Yes, Postgres allows settings per user or even per database and user, including statement_timeout:
ALTER ROLE foo SET statement_timeout = 12345; -- milliseconds
Related:
How does the search_path ...
- 152k
7
votes
Difference between database vs user vs schema
To begin to understand the difference I think we must start saying that this nomenclature was a mistake from Oracle.
I assume that if you have an ERP called TRITON you want your database to be named ...
7
votes
Accepted
Comma List vs Multiple Records
Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately.
Table GroupMemberRoles
GroupID ...
- 176k
7
votes
Accepted
Do not have permission to use 'dbo' when creating a view
Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:
So its like:
CREATE ROLE Limitedaccess;
GO
...
- 6,970
7
votes
Who is the person using a SQL login?
No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has ...
- 176k
7
votes
Compare two users permissions on SQL Server database?
Here are two queries I have used to compare permissions between database users.
The first shows the database roles (if any) to which the database user has membership. The second shows individual ...
- 845
7
votes
Accepted
Which characters are allowed in MySQL/MariaDB user account names?
User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running:
MariaDB [(none)]> SHOW ...
- 14k
6
votes
Find orphaned users
I wanted to first THANK Mark for posting the script. It saved me a bunch of time writing it from scratch. I modified it a little bit since I ran into the issue where i received an error stating the "...
- 61
6
votes
Accepted
Database User not reported as orphaned after Windows login dropped
Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:
SELECT p.name
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'...
- 176k
6
votes
DROP LOGIN fails on SQL Server 2014
For SQL Authentication logins, this is no different in SQL Server 2014 than in previous versions. I just did this in 2008:
CREATE LOGIN dbowner WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE ...
- 176k
Only top scored, non community-wiki answers of a minimum length are eligible