Hot answers tagged

49

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


41

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


31

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


22

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


20

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


19

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

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a ...


17

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


15

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


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

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


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

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

It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup. http://www.sqlbackuprestore.com/backupandrestorerights.htm


9

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ...


8

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


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

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


8

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


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

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

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


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

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

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


6

It looks like you need something like this: GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE contacts TO "user"; By the way, I hope "user" is not an actual user name you chose. Not only does it fail to convey much about the semantics of the role, but you will need to quote it everywhere, which can be something of a bother and lead to confusion when there is ...


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