77
In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.
See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.
You create users ...
46
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 also log in as. So "user" and "group" are essentially terms that indicate the intended usage of a role, there's no real distinction between them. Even in the ...
30
You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of things to worry about.
Don't worry about sharding until you need to do it. You are attempting to optimize prematurely for a problem that may or may not ever exist ...
27
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 dedicated service account is used to connect to the database back-end.
As a DBA, I don't want to have to manage, at the database level, the 10,000 active users of ...
23
You can query the system catalog with a recursive query, in particular pg_auth_members:
WITH RECURSIVE cte AS (
SELECT oid FROM pg_roles WHERE rolname = 'maxwell'
UNION ALL
SELECT m.roleid
FROM cte
JOIN pg_auth_members m ON m.member = cte.oid
)
SELECT oid FROM cte;
BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...
18
Short version:
SELECT a.oid
FROM pg_authid a
WHERE pg_has_role('maxwell', a.oid, 'member');
Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships.
The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...
18
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 easiest way to demonstrate this is:
CREATE LOGIN u1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE tempdb;
GO
CREATE USER u1 FROM LOGIN u1;
GO
ALTER LOGIN u1 ...
17
I think you have anonymous users
Try running this:
SELECT user,host,password FROM mysql.user WHERE user='';
This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:
mysql> select user,host,password from mysql.user;
+-----------+-------------+-----------------------------...
16
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 essentially a view on pg_authid accessible to public, as it doesn't reveal passwords, contrary to pg_authid. The base oid is even exported into the view. When not ...
15
The following script from the Brent Ozar Unlimited site iterates through all databases and lists the orphaned users by database, along with the drop command to remove them. There may be a neater/newer way of handling this but this appears to function correctly on 2005-2012.
DECLARE @SQL nvarchar(2000)
DECLARE @name nvarchar(128)
DECLARE @database_id int
...
12
According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:
mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE USER 'name'@'%' IDENTIFIED BY '...
12
Your answer is right here in SO already: Restrict SQL Server Login access to only one database. Just in case the link is broken:
1. Connect to your SQL server instance using management studio
2. Goto Security -> Logins -> (RIGHT CLICK) New Login
3. fill in user details
4. Under User Mapping, select the databases you want the user to be able to access
...
11
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';
You should the following number
If you get 43, MySQL 5.6
If you get 42, MySQL 5.5
If you get 39, MySQL 5.1
If you get 37, MySQL 5.0
It simply means you forgot ...
11
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 though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10.
Your SaaS application should connect ...
11
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).
11
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 "string" form (SID String Format Syntax), which looks like S-1-5-32-545 (referred to as being the "SDDL" format in some MSDN documentation, though SDDL covers ...
10
I know what you did.
Do this:
SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';
You will probably notice it returns a 'N' for Grant_priv. So do this:
UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
FLUSH PRIVILEGES;
SELECT `User`, `Grant_priv` FROM `mysql`.`user`;
And walla! Hope that helps.
10
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, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;
And this command will show you the user name, profile and account status i.e. which profile is assigned to which user
10
The fastest way I know of is a lookup in the system catalog view pg_roles:
SELECT * FROM pg_roles WHERE rolname = 'postgres';
Strictly speaking, it would be even slightly faster to use the underlying table pg_authid, but access to it is restricted to superusers for good reasons.
There is no object identifier type like for tables or types, which would ...
10
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(':',host) - 1) hst
FROM information_schema.processlist
WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;
This will handle host ...
9
You can use the below query to pull currently executing requests and the corresponding session/connection information:
select
r.session_id,
s.login_name,
c.client_net_address,
s.host_name,
s.program_name,
st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys....
9
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 5.6.
The column plugin is column #41 in mysql.user in MySQL 5.5/5.6
mysql> SELECT column_name,ordinal_position FROM information_schema.columns
-> ...
9
The group or login that is member of the sysadmin role is indepentent of who owns the database. The login that is mapped to the dbo of the database is basically the owner. You can change this via SSMS or with the query below:
ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[ReportServerTempDB] TO [sa];
You can find ...
8
you can use the grant assign to a netmask like:
GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240';
It will give you access from 81.10.20.1 -> 81.10.20.14, just change the subnet part to fit the range you need.
You can see more info at mysql doc account names
8
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 are connected to.
If you want to get a list of databases a user is allowed to connect to, you can do that with this query:
select u.usename,
(select ...
answered Jul 7 '16 at 10:58
a_horse_with_no_name
47.4k9 gold badges95 silver badges126 bronze badges
8
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 applications have no place modifying the schema (there are, as always, exceptions to this rule).
I'd rather not give the application(s) the permissions they need to ...
8
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 $$$ DISABLE;'
+ CHAR(13) + CHAR(10);
SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(userid))
FROM dbo.tbl_user;
PRINT @sql;
-- EXEC ...
7
First of all, don't separate users into separate tables. It'll make the things complex and pointless. Databases like MySQL and other can work with the databases of millions of records in the same table without any problem (having the right PRIMARY KEYS set up).
Use the database AUTO_INCREMENT AND PRIMARY unique key field for each user (in the main user table)...
7
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 FK
UserID FK
RoleID FK
(PK on all three, with perhaps other constraints)
Your queries (say, to find the admins of a certain group) should be of the form: ...
Only top voted, non community-wiki answers of a minimum length are eligible
