95
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 ...
55
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 ...
32
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, oid::regrole::text AS rolename FROM cte; -- oid & name
The manual about ...
28
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 ...
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 ...
22
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 ...
20
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 ...
16
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
16
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
...
14
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 '...
14
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
14
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 ...
14
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).
13
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
...
13
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 ...
12
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.
12
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 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 ...
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 ...
10
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....
10
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 brings the user and the password.
Then, you would have to copy the following
mysql.db for database-level grants
mysql.tables_priv for table-level grants
mysql....
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
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 SQL Servers you will have SQL Server Logins. These logins are either SQL Server accounts (Native SQL Server logins) or they will be Windows Server/Domain ...
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
62.6k12 gold badges118 silver badges157 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
Do GRANT DROP ON TABLE thedatabase.* TO user1.
The MySQL Reference Manual is a great place to look for this kinds of things.
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: ...
7
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 role_name;
grant all on all tables in schema foobar to role_name;
grant all on all sequences in schema foobar to role_name;
grant all on all functions in schema foobar ...
answered Aug 4 '15 at 14:31
a_horse_with_no_name
62.6k12 gold badges118 silver badges157 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
