Questions tagged [users]

For questions about database user accounts.

Filter by
Sorted by
Tagged with
78
votes
7answers
198k 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? ...
29
votes
4answers
148k 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
1answer
18k 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 ...
23
votes
6answers
40k 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; ...
17
votes
5answers
26k 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 ...
12
votes
3answers
33k 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) ...
12
votes
4answers
12k 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 they show with a RED X. What does this mean? Thanks.
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 ...
9
votes
2answers
2k 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
41k 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'@'%' ...
9
votes
2answers
19k 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 ...
8
votes
2answers
5k 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
2answers
51k 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
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
53k 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
1answer
4k 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
3answers
11k 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
2answers
57k 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
838 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
1answer
31k 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
17k 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
1answer
10k 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 ...
6
votes
3answers
30k 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 ...
6
votes
1answer
524 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
6k 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 ...
5
votes
2answers
26k 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 ...
5
votes
3answers
2k 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
73k 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 ...
5
votes
1answer
16k 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
1answer
1k 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 ...
4
votes
3answers
2k 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
2k 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
1answer
16k 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 ...
4
votes
2answers
12k 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 ...
4
votes
1answer
7k 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 ...
4
votes
1answer
331 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-...
4
votes
1answer
33k 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
2answers
3k 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 ...
4
votes
1answer
11k views

Create new Read Only user on DB2 on the whole database

I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. ...
4
votes
2answers
5k 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 ...
4
votes
1answer
995 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 ...
4
votes
1answer
96 views

Why is this user “Admin” created in all databases?

When I create database in SQL server Management Studio, user with name "admin" is also created for all databases: Why this user is created? And how I can change this behavior?
4
votes
1answer
2k views

Adding group rights in db2 for a stored procedure

I have a list of user accounts on AIX server, where the db2 database resides in. The user accounts all belong to a specific group which I can confirm from aix shell. User accounts work in DB2. ...
4
votes
1answer
2k views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
4
votes
1answer
936 views

InfluxDB - How can I limit the measures that a user can read by their tags?

I have some data that is collected by several sensors. Some users may own many of these sensors. I want to keep all the measures in a single database in InfluxDB. To differentiate the sensors I ...
3
votes
1answer
9k views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
3
votes
2answers
2k views

Database User not reported as orphaned after Windows login dropped

I have dropped a SQL Server Windows Login. I then ran the below code to check for orphaned database users. However, the database user corresponding to the dropped Windows login does not appear as an ...
3
votes
4answers
6k 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 ...
3
votes
2answers
501 views

Who is using a SQL Server database user credential?

I have a database user -- let's say "x" -- and I want to know how many Windows (desktop) users are using this database user (who2 or who is only give the current result, I want to know the entire ...
3
votes
1answer
6k views

DROP LOGIN fails on SQL Server 2014

We have a database with an owner 'dbowner'. Our install procedure drops the login for user 'dbowner' and creates it again. On any other version of SQL Server the following SQL query succeeds: USE [...