Questions tagged [users]

For questions about database user accounts.

Filter by
Sorted by
Tagged with
97
votes
8answers
240k views

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? ...
33
votes
1answer
23k 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 ...
32
votes
6answers
64k 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; ...
29
votes
5answers
175k 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 -------...
24
votes
5answers
30k 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?
18
votes
5answers
28k 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 ...
13
votes
3answers
35k 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) ...
11
votes
4answers
43k 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 ...
11
votes
2answers
65k 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'@'%' ...
10
votes
5answers
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 ...
10
votes
2answers
26k 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 ...
9
votes
2answers
96k 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 ...
9
votes
2answers
3k 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 ...
9
votes
2answers
55k 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
9
votes
2answers
6k 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 ...
8
votes
2answers
10k 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. ...
8
votes
1answer
26k 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 ...
8
votes
1answer
15k 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 ...
8
votes
3answers
9k 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 ...
8
votes
1answer
63k 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 ...
7
votes
3answers
16k 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?
7
votes
1answer
5k 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 ...
7
votes
2answers
61k 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 ...
7
votes
1answer
2k 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 ...
7
votes
2answers
8k 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 ...
7
votes
1answer
40k 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 ...
6
votes
2answers
29k 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 ...
6
votes
2answers
19k 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 (...
6
votes
2answers
26k 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 ...
6
votes
1answer
852 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;
6
votes
2answers
15k 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 ...
5
votes
3answers
3k 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 ...
5
votes
2answers
12k 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 ...
5
votes
1answer
18k 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 ...
5
votes
3answers
5k 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 ...
5
votes
1answer
8k 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 ...
5
votes
1answer
7k 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.
5
votes
1answer
1k views

Why can't I create a new schema with the owner set to dbo?

I created a test account for checking permissions against. It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database. But when it tries to create a new schema ...
5
votes
1answer
2k 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 ...
5
votes
1answer
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 ...
5
votes
1answer
7k views

How to access anonymous user account in MySQL?

I have followed the MySQL on Ubuntu 18.04 installation tutorial on Digital Ocean, and during the process of running mysql_secure_installation noticed the following: By default, a MySQL installation ...
4
votes
3answers
3k views

Assign db_owner to all users at once

I need to assign db_owner to all users within an instance. There are a few hundred logins and same number of databases within the instance. I would like each user to get db_owner within the database ...
4
votes
2answers
303 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 ...
4
votes
2answers
4k views

SQL Server Service Accounts

We have a new Windows 2016 server and I have to install SQL Server 2016 standard edition. Once I install and configure the SQL server I will move my existing production database to the new db server. ...
4
votes
4answers
9k views

Compare two users permissions on SQL Server database?

Is it possible to compare permissions of two users on one SQL Server database? Is there any simple query to do it? I am asking this because I want to find out if user B has different or same level of ...
4
votes
2answers
7k 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 (...
4
votes
1answer
2k views

Do not have permission to use 'dbo' when creating a view

I want to create a user with minimal required access to two databases within a server. The user will be used by someone from a third party integrating a Business Intelligence tool into our database. ...
4
votes
1answer
39k views

How to find which tables and views a user has access to?

We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need. We ...
4
votes
1answer
3k 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 ...
4
votes
1answer
378 views

Schemas and user permissions

When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema. The issue is that our site-...

1
2 3 4 5
7