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

256 questions
44
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
  • 1,763
  • 4
  • 19
  • 20
42
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,483
  • 4
  • 20
  • 29
41
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,301
  • 4
  • 15
  • 21
37
votes
6 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
  • 7,653
  • 3
  • 36
  • 52
33
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
  • 349
  • 1
  • 3
  • 4
26
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,331
  • 14
  • 32
  • 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
18
votes
4 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
  • 181
  • 1
  • 1
  • 4
15
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,243
  • 4
  • 17
  • 24
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
  • 767
  • 3
  • 9
  • 14
14
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?
Gugelhupf
  • 243
  • 1
  • 2
  • 4
10
votes
1 answer

Is there a difference between granting SELECT to a user and adding them to the db_datareader role?

I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level. But that got me thinking, what is the difference…
Kenneth Fisher
  • 23,537
  • 9
  • 54
  • 106
9
votes
2 answers

Create database role to allow read access to all tables; write access to some

We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables. I was messing around with database roles, but whenever I went to add the tables, I had to hand…
Wes
  • 1,126
  • 1
  • 10
  • 30
9
votes
1 answer

Map Oracle roles to Active Directory groups?

Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
Vladimir Kravets
  • 193
  • 3
9
votes
1 answer

Grant deny permission stacking

For the role db_denycustomer, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this: DENY SELECT ON dbo.customer TO db_denycustomer GRANT SELECT ON dbo.customer (code) TO db_denycustomer ...and it…
George Menoutis
  • 317
  • 1
  • 7
1
2 3
…
17 18