Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [role]

A set of permissions.

In Oracle it is important to note that roles are disabled in PL/SQL run with definer rights. In PL/SQL permissions must be granted directly or invoker rights can be used in the block by adding the phraseAUTHID CURRENT_USER.

In SQL Server "All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them."

References:

Server and Database Roles in SQL Server

298 questions
55
votes
7 answers

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; grant authors to editors; --editors can do what…
Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
55
votes
1 answer

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role "myrole" does not exist So apparently the dump of…
Rob Bednark
  • 2,253
  • 6
  • 22
  • 22
51
votes
2 answers

SQL Server - granting permissions to an entire schema vs. object?

I'm very green when it comes to the world of database permissions management in SQL Sever. Let's keep an example simple. Say account 'admin' is the owner of schemas A, B, and C. There another account 'minion' that you want to have full rights…
user45867
  • 1,739
  • 5
  • 24
  • 41
44
votes
4 answers

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange (Active Directory) Group a role in SQL Server…
Michael Hedgpeth
  • 1,361
  • 4
  • 15
  • 21
35
votes
2 answers

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not…
dgf
  • 369
  • 1
  • 3
  • 4
29
votes
2 answers

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. However, the Owned Schemas is not compulsory. It…
Jack
  • 2,557
  • 14
  • 37
  • 42
25
votes
1 answer

Reporting Service and Application Role

First poster, long time lurker here. What is the best way to activate application role in a report ? I've tried different things and so far the only method that works is to embed the call to the application role like so :- EXEC sp_setapprole…
YS.
  • 351
  • 2
  • 4
23
votes
5 answers

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I would like to grant Select access to the role for…
Curtis Beiersdorf
  • 331
  • 1
  • 2
  • 4
23
votes
2 answers

PostgreSQL - Who or what is the "PUBLIC" role?

I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist. => Who or what is the public role?
gokan
  • 333
  • 1
  • 2
  • 4
18
votes
2 answers

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it possible to acheive the same result without being…
Stephan
  • 1,513
  • 4
  • 18
  • 27
15
votes
1 answer

Postgres get function owner

I would like to be able to get a table of function names and function owner names. Currently, this query returns a list of functions for a given schema: SELECT proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON …
4x0v7
  • 153
  • 1
  • 1
  • 5
14
votes
1 answer

PostgreSQL: permission denied for relation

I'm a bit confused about setting permissions in PostgreSQL. I have these roles: List of roles Role name | Attributes | Member of…
Meltemi
  • 777
  • 3
  • 9
  • 14
13
votes
2 answers

PostgreSQL, display role members

Is there a way to visualize which users are members of a role in PostgreSQL / pgAdminIII, like role: council_stuff members: Harry Ben Steve Melinda
Jochen Schwarze
  • 327
  • 2
  • 4
  • 15
12
votes
2 answers

postgres: how can I allow index creation but no table mutations or table drops by the same user?

I am setting up a postgres server with what is going to be a (mostly) read-only workload. I would like to allow myself and colleagues to create/drop indices as we see fit, without allowing ourselves to drop tables or mutate the tables. I spent…
orm
  • 263
  • 1
  • 3
  • 8
10
votes
1 answer

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute stored procedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server role of sysadmin in addition to public. the later of…
BenVlodgi
  • 285
  • 1
  • 12
1
2 3
…
19 20