Hot answers tagged

58

You can GRANT schema permissions that are effective for everything existing and everything that will exist in that schema. Grant Schema Permissions GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>; Further to that, if you want to then deny permissions on a certain object within that schema, you can do. Denying Object ...


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 ...


25

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


23

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 ...


20

If all your tables are in the dbo schema then this should work, you can just deny single tables if required GRANT SELECT ON SCHEMA :: [dbo] TO myspecialrole


19

To simplify a bit further, you can use roles to do the job that you are looking for. Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role. Below is an example to get you started : -- Create the database role CREATE ROLE ...


17

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1; Add r1.rolname='council_stuff' to filter on only that one. Be aware that users ...


14

Members of the sysadmin role cannot be denied permission to anything in SQL Server. The documentation for DENY (Transact-SQL) states: ...except that DENY does not apply to object owners or members of the sysadmin fixed server role. So in order to prevent a sysadmin from running a stored procedure, you would need to put code in the stored procedure to ...


12

Right from the manual: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is ...


12

While this is true it is not the full picture. Public also acts as an implicit role that other roles belong to and that has its own permissions not always reflected and reported that get inherited. By default it gives create permission on the public schema. when you don't remove this all the other correct steps to create a read only user results in that ...


10

These terms are not new to SQL 2016, but they are also not server roles as far as I know. For all i know these are pre-built Parallel Data Warehouse resource classes. There isn't much documentation on that except some blog posts. Have a look at these PDW, Integration Services and Resource Classes A teched presentation from Channel 9 I only heard about ...


10

This is documented behavior provided for backwards compatibility. Documentation excerpt: Caution A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.


9

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the ...


9

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly: GRANT SELECT ON SCHEMA::dbo TO [role]; GO GRANT INSERT, UPDATE --, DELETE ON dbo.table_they_can_write_to TO [role]; DENY SELECT ON dbo.table_they_cannot_read TO [role];


9

At the SQL level you can't, since all those tasks are governed by table ownership. The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that ...


9

Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or contained database user) before an application role can be activated. Application roles mostly differ from regular database roles in that they: have no ...


8

From marc_s answering "How to add Active Directory user group as login in SQL Server": In SQL Server Management Studio, go to Object Explorer > (your server) > Security > Logins and right-click New Login: Then in the dialog box that pops up, pick the types of objects you want to see (Groups is disabled by default - check it!) and pick the location ...


8

Grant CONTROL SERVER to the login rather than adding it to the sysadmin role. As documented in Books Online: The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. CONTROL SERVER gives the same rights as sysadmin but without this side-...


8

in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database That's PowerQuery. Just ran through that on Excel, and stored procedures don't appear, but Table-Valued Functions do. And you can call a stored procedure, but it won't show up in the query designer. You just enter 'exec dbo.Proc1' in the SQL Statement dialog:


8

The only way for a member of the sysadmin fixed server role to use a non-dbo schema is to explicitly qualify their object names with the schema name: CREATE TABLE MySchema.TableName (... instead of: CREATE TABLE TableName (... There is no way around this. The documentation for ALTER USER (in the "Remarks" section) even has a note for: 🛈 ...


8

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came up with something else: Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that ...


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

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


7

You indeed have to add the user to the db_datareader and db_datawriter roles for each of your existing databases. Such a query can be used for existing (and ONLINE) user databases: DECLARE @user sysname = 'userTest1'; DECLARE @login sysname = 'userTest1'; DECLARE @SQL nvarchar(max) = ''; SELECT @SQL = @SQL + ' USE ' + QUOTENAME(NAME) + '; IF NOT ...


7

You did not mention it, but this is quite typical when using PL/SQL. Privileges granted through roles are disabled for named PL/SQL blocks that are defined to execute with definer's rights. How Roles Work in PL/SQL Blocks All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. ...


7

There is no builtin role like that in PostgreSQL. However, you can set up one: CREATE ROLE db_datawriter; After this, you have to assign write privileges (usually INSERT, UPDATE, DELETE, maybe TRUNCATE) to it. To see how to do this for all present and future tables, read my older answer. Normally, write access also implicates reads, therefore it make ...


7

Public is just an odd duck. But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not. The roles marked as is_fixed_role have fixed permissions and changeable membership. Public has fixed membership (everyone) and changeable permissions. The ...


6

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, coalesce(db.datname, 'cluster wide') as database, setconfig as what_changed FROM pg_db_role_setting role_setting LEFT JOIN pg_roles role ON role....


6

Starting with PostgreSQL 10 you can grant the role pg_read_all_stats in order to achieve the desired result.


Only top voted, non community-wiki answers of a minimum length are eligible